题解 | #连续签到领金币#

WITH t1 AS
(
    SELECT DISTINCT uid, DATE(in_time) AS dt
            ,DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS ranking
    FROM tb_user_log
    WHERE artical_id = 0 AND sign_in =1 AND DATE(in_time) BETWEEN "2021-07-07" AND "2021-10-31"
),
t2 AS
(
    SELECT *,DATE_SUB(dt, INTERVAL ranking day) AS dt_tmp
            ,CASE DENSE_RANK() OVER(PARTITION BY uid, DATE_SUB(dt, INTERVAL ranking day) 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') AS month
        ,SUM(day_coin) AS coin
FROM t2
GROUP BY uid, DATE_FORMAT(dt, '%Y%m')
ORDER BY month, uid

• 别人写的，非常好。对于日期，构造DENSE_RANK排序，并在下一步减去排名，从而对于连续的日期（公差=1）的插值都是一致的，对于不连续的日期，则呈现不一样。
• 基于上着，可以根据用户、日期差值进行分区，并进行排序可得连续日期段的最近排序情况，从而可以获得每日金币情况
• 以上代码来自于题解的第一个人的代码，写的非常好，建议大家去看。