当前位置:网站首页>求过去半年内连续30天以上每天都有1000元以上成交的商铺
求过去半年内连续30天以上每天都有1000元以上成交的商铺
2022-08-11 05:35:00 【吃再多糖也不长胖】
订单表T(user_id, shop_id, product_id, price, trans_dt)。 Q1: 求过去半年内连续30天以上每天都有1000元以上成交的商铺。
//1
select shop_id, f_date,count(1) as days from ( select
*,date_sub(t.trans_dt,rn) as f_date from (select
- ,ROW_NUMBER() over (partition by shop_id order by trans_dt) as rn from ( select shop_id,substr(trans_dt,1,10) as trans_dt,sum(price) as
price from T group by shop_id,substr(trans_dt,1,10) having
sum(price)>1000 ) )a )b group by shop_id, f_date having count(1) >=30
//2
select shop_id from ( select shop_id,trans_dt,lead(trans_dt)
over(partition by shop_id order by price order by trans_dt ) as n_day
from ( select shop_id,substr(trans_dt,1,10) as trans_dt,sum(price) as
price from T group by shop_id,substr(trans_dt,1,10) having
sum(price)>1000 )a )b where datediff(n_day,trans_dt)=1
Q2:随机取20个抽奖
select * from Q1 order by rand() limit 10;
边栏推荐
猜你喜欢
随机推荐
使用路由器DDNS功能+动态公网IP实现外网访问(花生壳)
HCIP MPLS/BGP Comprehensive Experiment
图的拉普拉斯矩阵
MySQL01
numpy和tensor增加或删除一个维度
华为防火墙-4-安全策略
ansible batch install zabbix-agent
HCIP WPN experiment
Raspberry Pi set static IP address
自定义MVC增删改查
获取拼多多商品信息操作详情
配置dns服务
亚马逊获得AMAZON商品详情 API 返回值说明
淘宝sku API 接口(PHP示例)
损失函数——交叉熵
【LeetCode】2034. 股票价格波动(思路+题解)双map
防火墙-0-管理地址
HCIP BGP建邻、联邦、汇总实验
SATA、SAS、SSD三种硬盘存储性能数据
OA project meeting notice (query & whether attending & feedback for details)