MySQL 高级SQL进阶真题讲解(二)

一、题目描述

二、数据构建

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-07-07 10:00:00', '2021-07-07 10:00:09', 1),
  (101, 0, '2021-07-08 10:00:00', '2021-07-08 10:00:09', 1),
  (101, 0, '2021-07-09 10:00:00', '2021-07-09 10:00:42', 1),
  (101, 0, '2021-07-10 10:00:00', '2021-07-10 10:00:09', 1),
  (101, 0, '2021-07-11 23:59:55', '2021-07-11 23:59:59', 1),
  (101, 0, '2021-07-12 10:00:28', '2021-07-12 10:00:50', 1),
  (101, 0, '2021-07-13 10:00:28', '2021-07-13 10:00:50', 1),
  (102, 0, '2021-10-01 10:00:28', '2021-10-01 10:00:50', 1),
  (102, 0, '2021-10-02 10:00:01', '2021-10-02 10:01:50', 1),
  (102, 0, '2021-10-03 11:00:55', '2021-10-03 11:00:59', 1),
  (102, 0, '2021-10-04 11:00:45', '2021-10-04 11:00:55', 0),
  (102, 0, '2021-10-05 11:00:53', '2021-10-05 11:00:59', 1),
  (102, 0, '2021-10-06 11:00:45', '2021-10-06 11:00:55', 1);

三、题目答案

with cte1 as
(
select uid,date_format(in_time,'%Y-%m-%d') as in_time  from tb_user_log
where artical_id = 0 and in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59' and sign_in = 1
group by uid,date_format(in_time,'%Y-%m-%d')
)
,
cte2 as
(
 select *,
 datediff(in_time,lag(in_time) over(partition by  uid order by in_time))  as flag
 from cte1
)
,cte3 as
(
select * ,
case when flag = 1 then  @inc :=  @inc  + 1 else @inc :=1 end as inc
from cte2,(select @inc := 0) as t
)
select uid,date_format(in_time,'%Y%m') as month,
sum(case when mod(inc,7) = 3 then 3 when mod(inc,7) = 0 then 7 else 1 end) as coin
from cte3
group by uid
order by uid,month

四、答案讲解

第一步

with cte1 as
(
select uid,date_format(in_time,'%Y-%m-%d') as in_time  from tb_user_log
where artical_id = 0 and in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59' and sign_in = 1
group by uid,date_format(in_time,'%Y-%m-%d')
)

筛选出符合题目条件的数据，并按天分组，确保每个uid,每天只有一行记录

第二步

cte2 as
(
 select *,
 datediff(in_time,lag(in_time) over(partition by  uid order by in_time))  as flag
 from cte1
)

给连续的天数行打上标记。flag = 1 代表天是连续的

第三步

select * ,
case when flag = 1 then  @inc :=  @inc  + 1 else @inc :=1 end as inc
from cte2,(select @inc := 0) as t

累计连续天数 这一步查询出来的结果是这样的

第四步

select uid,date_format(in_time,'%Y%m') as month,
sum(case when mod(inc,7) = 3 then 3 when mod(inc,7) = 0 then 7 else 1 end) as coin
from cte3
group by uid
order by uid,month

累计金币 以7天为一个周期，第3天3金币，第7天7金币。其它天是1金币