SQL学习（11）：连续签到领金币问题
前言：本题来自于海*金融二面前的小测试题目，也是非常经典的高难度题目【牛客应该能够找到】。
主要难点在于如何对于多种规则进行划分标签和计算。
本文提供一种较易理解的思路和方法，其余思路可以在CSDN找到，
例如
HiveSql面试题：连续签到领金币问题【百度-困难题-通用解法】
1.题目描述
表名tb_user_log
2.问题分析
1.思路讲解
-
筛选条件有：
in_time从2021-07-07至2021-10-31，包含两边【跨天算进入，因此总体看进入】 article_id=0 sign_in=1 -
连续签到的规则：
每天1个 逢3多得2，逢7多得6——可以使用取余数得方式判断 断签重新计算 -
问题的关键在于”连续签到“和”断签“区分。
我的思路为：区分开每一次重新开始的连续签到。
借用通常的说法”从XX日开始连续签到了X天“，我将”每次连续签到的第一天“作为分类标签，获取同样的日期表示”同属于从该天起的连续签到，中途无断签“。
由于第一天一般比较难以获取，我们转为通过排序获得的签到排名和对应签到日期相减，获取所属类别。
（大家可以思考：为什么排序和对应签到日期相减得到的日期一定能够区分呢？会不会出现，实际断签但结果同属一天的情况呢？
显然不可能，连续签到的话一定同属一天，那么断签的新日期至少大了1，减去相同排名，获得的差值一定不同）
2.代码框架
遇到这种相对困难且很可能需要多重子查询的问题，with as是非常好的选择，它可以帮助思路更加清晰，且能够直接使用别名，避免重复书写。
【注意：MySQL在SELECT定义的新名字，在很多情况下都无法直接使用，需要重新书写】
接下来，我们分步骤说明每个子查询的思路，最后使用with as 连接
- 子查询a：筛选条件、获取排名
SELECT uid, date(in_time) as dt1,
row_number() over(partition by uid order by date(in_time) asc) as rn
FROM tb_user_log
WHERE dt1 between '2021-07-07' and '2021-10-31'
and article_id=0
and sign_in=1
- 子查询b: 通过相减，获取连续签到的分类
SELECT *, (dt1-rn) as dt2
FROM a
- 子查询c：再次排序，获取连续签到的天数
SELECT *,
row_number() over(partition by uid, dt2 order by dt1) as con_dt
FROM b
-
子查询d：通过取余数+分情况判断，确定对应金币数目。
需要注意，3和7天时，是额外获取2和6个，因此判断时返回的应加上本身签到获取的1个。
SELECT *,
(case when con_dt%7=3 then 3
when con_dt%7=0 then 7
else 1 end) as coin
FROM c
至此，我们已经得到了最终结果需要的所有子查询，结果为
SELECT uid,
date_format(dt1,'%Y%m') as `month`,
sum(coin) as coin
FROM d
GROUP BY uid, `month`
ORDER BY uid asc, `month` asc
3.完整代码
通过with as 连接，完整代码为
3.知识点总结
-
同一个with as 的嵌套：
同一个with as 下，后一个子查询切片可以直接引用前一个，即以下成立：with a as (),b as (select...from a) - date_format
-
SQL命名：
规范来说，任何SQL的名称都需要加上双着重号“区分
如果不使用双着重号，便应当避免和常用关键字冲突的命名。
【但在实际应用中，可以使用函数名进行命名，不会出现冲突报错。】
SQL分类和命名规范
SQL保留字