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'
2,case when
(case
when 条件1 then 输出1
when 条件2 then 输出2
when 条件3 then 输出3
else 输出4
end ) as 别名
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
输出:
select name,count(ID),sum(score) from score
group by name
with rollup
输出:
使用 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)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