SQL刷题查漏补缺7

SQL175、SQL166

1，SQL177

1)先把每日的订单完成量和订单取消量计算出来
2）"近7日"用窗口函数 sum(finish_num)over(order by dt rows 6 preceding)

select *
from
(select dt,
round(sum(finish_num)over(order by dt rows 6 preceding)/7,2) as finish_num_7d,
round(sum(cancel_num)over(order by dt rows 6 preceding)/7,2) as cancel_num_7d
from
(select date(order_time) dt,
sum(case when start_time is not null then 1 else 0 end) as finish_num,
sum(case when start_time is null then 1 else 0 end) as cancel_num
from tb_get_car_order
group by date(order_time)
order by dt )a ) b 
where dt between '2021-10-01' and '2021-10-03'
1
2
3
4
5
6
7
8
9
10
11
12
13

2，case when

(case
when 条件1 then 输出1 
when 条件2 then 输出2
when 条件3 then 输出3
else  输出4
end  ) as 别名
1
2
3
4
5
6

3，dayofweek(date) 函数

返回一个整数，范围从1到7，表示星期日到星期六。

4,WITH ROLLUP

with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。
例子：
原数据：

select name,count(ID),sum(score) from score
group by name
1
2

输出：

select name,count(ID),sum(score) from score
group by name
with rollup
1
2
3

输出:

使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。

5,SQL167 统计这是连续天数中的第几天

with t1 as (
select distinct uid,date(in_time) dt,dense_rank()over(partition by uid order by date(in_time)) rn
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31' 
and artical_id = 0 and sign_in = 1),
t2 as (
select *,
date_sub(dt,interval rn day) dt_tmp,
(case dense_rank()over(partition by date_sub(dt,interval rn day),uid order by dt) %7
when 3 then 3
when 0 then 7
else 1 end ) as day_coin
from t1)

select uid,date_format(dt,'%Y%m') 'month' ,sum(day_coin) coin
from t2
group by uid,date_format(dt,'%Y%m')
order by date_format(dt,'%Y%m'),uid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

步骤：
1）t1表作用：去重，加一列（给日期排序列），筛选掉不符合条件的记录
2）t2表：根据t1表，用日期减去排序列取名为dt_tmp（dt_tmp相同的即是连续天数），再根据dt_tmp与uid分组，日期排序后再加一列排序列（这次就是在连续天数和同一用户下的排序），次排序列%7余数即是在7天连续的设定中的第几天连续 ，命为day_coin
3）根据t2，分组后，直接对day_coin求和即获得金币总计

6,SQL171 复购率top3的商品

某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
1）加一列repurchase 标记是否为复购，是标为1，否标为0 ，复购率为sum(repurchase)/count(repurchase)
2）repurchase列如何做？if(count(event_time)>1,1,0) as repurchase 且将时间限定在where event_time >=
(select date_sub(max(event_time),interval 89 day)
即90天内。
3）top3的选择，排序后limit 3

select product_id,
round(sum(repurchase)/count(repurchase),3) as repurchase_rate
from
(select uid,product_id,if(count(event_time)>1,1,0) as repurchase
from tb_order_detail
join tb_order_overall using(order_id)
join tb_product_info using(product_id)
where tag = '零食' and event_time >= 
(select date_sub(max(event_time),interval 89 day)
from tb_order_overall)
group by uid,product_id) as t
group by product_id
order by repurchase_rate desc,product_id
limit 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14