一、知识点总结

和你们分享，通过这一题，又突破了几个原先的知识盲区，开心有所收获~
  • 收获1：如何判断连续日期？1、对日期去重后进行排序（或者直接用dense_rank进行排序）；2、日期减去排序对应序号的天数如果是一个相同的值，则说明这些日期就是连续的。3、对连续的日期再次排序就得到了最大的连续数列。
  • 收获2：计算周期，除以周期天数的余数，就是每个循环周期对应的天数（周期单位）啦~

二、解题步骤

题目解读：在7月7日—10月31日有签到得金币活动，每天签到得1金币，连续3天额外的2金币，连续7天额外得6金币。当artical_id=0且sign_in=1时，才算完成一次签到。计算每个用户每月分别获得的金币数。结果按照月份，和用户id升序排列。
  • 难点1：如何判断连续签到这个行为？
  • 难点2：如何统计连续3天和连续7天额外得的金币？
1）整理表格数据，按照题设要求取出用户签到的这部分数据，并且对用户的签到日期进行排序。
  • 活动日期：7月7日—10月31日
  • 当artical_id=0且sign_in=1时，才算完成一次签到
  • 对日期进行排序：DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) den_rk ，用DENSE_RANK是有可能存在一个用户在1天中多次签到的情况（照理说1天只能签到1次，但偶尔可能会数据出错） 
SELECT DISTINCT uid,sign_in,DATE(in_time) dt, 
        DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) den_rk 
FROM tb_user_log 
WHERE artical_id='0' AND sign_in=1 
AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
2）判断连续签到日期
  • 当数据是连续是，日期是递增的，也就意味着，日期-排序的序数，会等于一个定值（也就是签到开始的前一天）。DATE_SUB(dt,INTERVAL den_rk DAY) 是一个定值，定义为连续日期。
  • 将相同的 DATE_SUB(dt,INTERVAL den_rk DAY) （也就是连续日期）再次进行聚类排序，得到用户的连续天数。
  • 将连续签到日天数除以7求余数，就得到了以7为周期的连续签到循环数。
  • 如果余数是3，代表是连续签到的第3天，给加2金币，如果余数是0代表是7的整数也就是连续签到的第7天给加6个金币。 
SELECT uid,DATE_FORMAT(dt,'%Y%m') month,dt,
        DATE_SUB(dt,INTERVAL den_rk DAY) '连续签到',  
	DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt) '连续天数',
    DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt)%7 '以7为周期进行循环',
	CASE WHEN DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt)%7=3 THEN sign_in+2   
	WHEN DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt)%7=0 THEN sign_in+6         
    ELSE sign_in END '每日签到金币' 
FROM (
SELECT DISTINCT uid,sign_in,DATE(in_time) dt, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) den_rk

FROM tb_user_log WHERE artical_id='0' AND sign_in=1 AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
)act_table;

3）求每个用户每个用户每个月获得的金币数，结果按照月份以及用户id升序。 
WITH t1 AS(
SELECT uid,DATE_FORMAT(dt,'%Y%m') month,dt,DATE_SUB(dt,INTERVAL den_rk DAY) begin_0,  
	CASE WHEN DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt)%7=3 THEN sign_in+2   
	WHEN DENSE_RANK()OVER(PARTITION BY DATE_SUB(dt,INTERVAL den_rk DAY) ORDER BY dt)%7=0 THEN sign_in+6         
    ELSE sign_in END score 
FROM (
SELECT DISTINCT uid,sign_in,DATE(in_time) dt, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) den_rk

FROM tb_user_log WHERE artical_id='0' AND sign_in=1 AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
)act_table
)

SELECT uid,month ,SUM(score) coin FROM t1  GROUP BY uid,month ORDER BY month,uid;