本文目录

拆解题目：求什么，输出什么，筛选条件

解题步骤：清洗、判断连续、标记连续第几天、处理循环、分类计算金币数、聚合总计

题目

计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。（详见原题）

思路

拆解题目

1.求什么，输出什么

每人每月获得金币总数。

最后输出：人（uid）、月（dat）、总金币数（sum）

金币数怎么求：

用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。

7天一个循环：每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

2.筛选条件

2021年7月7日-2021年10月31日、artical_id为0,sign_in为1即为签到

求解步骤

概览

从上文的思路末端开始倒推，标红为难点，为了便于理解我会用excel可视化的解释

清洗：筛选，去重
判断连续：连续数列-日期，是否相等
判断连续的第几天：uid,相减后的日期相等，即为连续签到的第x天，给编号
处理循环：%7，处理每第3、7，其他天。不同天，不同值，可以用case when
求和：以人，月聚合，sum（金币数）

详细

1. 清洗：筛选，去重

涉及核心语句：with tmp as()，where，distinct

每人每天可能重复登录，以防万一distinct一下

with tmp as(
    select distinct
    uid,date(in_time) dt
    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.判断连续（row_number/date_sub）

数学原理：两个连续的数列相减，差相等

判断签到日期是否连续数列，造一个连续的序列，和它相减，差值相等，就说明日期连续

row_number造连续数列，要以uid分区，每个人连续天数

date_sub（dt,interval rk day）

代码

3.判断连续的第几天(dense_rank)

以uid、减后日期聚合，实现任何uid，datesub一个不一样，都会重新排序实现

dense_rank（）over(partition by uid，date_sub（dt,interval rk day）order by dt)

代码

一些延伸思考

（1）为什么不能直接用group by (uid,date_sub）——因为要保留dt，如果直接group by ,普通列就不能有dt，只能用窗口函数。

（2）为什么不用count()over(partition by uid,date_sub），而用dense_rank——因为我们要求具体第几天，以便后续求和。用count会聚合求连续天数，例如101就不会输出1-7，而是输出7,7,7,7,7,7...后续求和就会虚高。

总结：需要具体第几天，用dense_rank。需要天数，用count，可以窗口可以group by +count

4.处理循环(case when 天数%7)

（1）逻辑梳理：

每连续签到3天，获得3

每连续签到7天，获得7

其余1

（2）每7天一个周期，循环用%实现：

%求余数基础语法：被除数%除数，结果输出余数。10%7——结果是3,10/7，余3

%循环天数=特殊的几天，then 产出

%循环天数=能整除=0，then 产出

特殊处理3,7，7天一个循环

第三天：%7=3 then 3——3<7，商是0，余数为本身

第7天：%7=0 then 7———7是循环周期，整除=0

（3）case when ，实现分类输出

5.聚合计算

完整代码

with tmp as(
    select distinct
    uid,date(in_time) dt
    from tb_user_log
    where artical_id='0' and sign_in='1'
    and date(in_time) between '2021-07-07' and '2021-10-31'
),
xl as(
    select
    *,row_number()over(partition by uid order by dt) rk
    from tmp),
#uid，减后天数聚合，标记具体第几天
jh as(
    select
uid,dt,dense_rank()over(partition by uid,date_sub(dt,interval rk day) order by dt) xlday
    from xl)
select
uid,date_format(dt,'%Y%m') month,sum(coin) coin
from(
    select
    *,case
    when xlday%7=3 then 3
    when xlday%7=0 then 7
    else 1 end coin
    from jh)a
group by 1,2
order by 2,1

特别鸣谢

B站up主：是橘长不是局长哦

真的非常感谢局长的题解，给了很多灵感。有些思路好精彩，我经常会有：“还能这样？人怎么能聪明成这样”的感觉。

本题题解区的各位小伙伴

每次卡顿会看看大家的方法，获益良多。主要参考：王尼玛呢、骨碌圆

题解 | 连续签到领金币