SQL学习（11）：连续签到领金币问题

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.知识点总结

1. 同一个with as 的嵌套：
   同一个with as 下，后一个子查询切片可以直接引用前一个，即以下成立：

   with a as (),b as (select...from a)
   

2. date_format
3. SQL命名：
   规范来说，任何SQL的名称都需要加上双着重号``区分
   如果不使用双着重号，便应当避免和常用关键字冲突的命名。
   【但在实际应用中，可以使用函数名进行命名，不会出现冲突报错。】
   SQL分类和命名规范
   SQL保留字