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保留字