MYSQL签到表 mysql连续签到天数

转载

云端筑梦工匠 2023-10-20 17:03:54

文章标签 MYSQL签到表 mysql 连续签到天数数据结点递归查询 文章分类 MySQL 数据库

今天小刘看到一道有趣的SQL数据分析题目，准备和大家分享一下

不能只让我一个人秃头！！！

一软件中有用户每日签到功能，现在要用SQL计算出上月中各用户的最后一次连续签到的天数(只有一天的，算作连续一天)

MYSQL签到表 mysql连续签到天数_递归查询

01 数据准备

首先我们准备一下五个用户在七月份的随机签到数据

CREATE TABLE demo_checkin_history ASSELECT user_id, MAX(checkin_date) checkin_date  FROM (SELECT ceil(LEVEL / 31) user_id              ,to_date('20200701', 'yyyymmdd') + 31 * dbms_random.value checkin_date          FROM dual        CONNECT BY LEVEL <= 31 * 5) GROUP BY user_id, trunc(checkin_date);

MYSQL签到表 mysql连续签到天数_递归查询

02 题目分析

用户每次签到的记录在表中是行行分开的，想要检查日期是否连续，让人首先想到的是跨行引用，将一行前前后后的数据都引用到一行上，以便比较计算

这正是这道题迷惑人的地方，因为用户实际连续签到的天数是未知的，从而我们不能确定要跨行引用多少次才够用，相反如果是想要找到某月连续签到天数不小于N天的用户，这个思路倒也是可行的

既然实际连续签到了几天是未知数，那么小刘想到的是利用递归查询，从用户最后一次签到日期开始，每向下一层去寻找前一天的记录，直到断签，此时LEVEL还正好就是这组连续签到的天数

递归查询的根结点，选取每个用户的签到日期最大的那些行

递归的规则，即为同一用户的签到日期相差一天

另外我们只需得到连续签到的天数，即每个树最大的LEVEL值，从而我们将只筛选叶子结点的数据即可

SELECT t.user_id      ,t.checkin_date  checkin_date_from      ,connect_by_root t.checkin_date checkin_date_to      ,LEVEL           days  FROM demo_checkin_history t WHERE connect_by_isleaf = 1 START WITH t.checkin_date =            (SELECT MAX(u.checkin_date)               FROM demo_checkin_history u              WHERE u.user_id = t.user_id)CONNECT BY PRIOR trunc(t.checkin_date) = trunc(t.checkin_date) + 1       AND PRIOR t.user_id = t.user_id ORDER BY days DESC, checkin_date_from DESC;

MYSQL签到表 mysql连续签到天数_MYSQL签到表_03

对于递归查询不太熟悉的小伙伴，可以点击这里回顾

MYSQL签到表 mysql连续签到天数_递归查询

03 加大难度

前面是找出每个用户上月最后一次连续签到的数据，现在比方说老(diao)板(mao)改主意了，他想看看每个用户上月的最大连续签到天数对比

新需求对于我们上例程序的最大挑战在于，根结点定位规则变化了，而且是由相对固定变成了相对不定

当然，简单粗暴的办法是去掉START WITH子句，任由数据库从每一行分别去递归，最后找到每个客户的最大的LEVEL值就完事儿

但考虑到实际业务场景中，如果只有五个用户，那公司也就倒闭了，需要这么大费周章去计算报表的，用户量没准儿是上万的，百万行量级的表去放飞自我的反复递归，带来的必定是老板的怒火性能的浪费

所以，延续上例的思路，我们这里应当调整根结点定位规则，想办法去找到每一个断签点，从这些点开始往前捯

此时，跨行引用有了用武之地

在每一个用户的分区内，按日期排序后，排在某行前一位的日期不与本行日期连续，则为断签点

例如我们观察一下用户2的签到日期，断签点已被圈出

MYSQL签到表 mysql连续签到天数_递归查询_05

观察数据我们发现，该用户不仅常常断签，而且坚持签到总是不超三日

这里的关键在于同样天数的连续签到，一个用户可能会有多组，那么我们就要在查询时考虑只取其中某一组，本例我们取最后一组

定位断签点为根结点的递归查询，我们可以这样来写

WITH checkin_his AS (SELECT t.user_id        ,t.checkin_date        ,lag(t.checkin_date) over(PARTITION BY t.user_id ORDER BY t.checkin_date DESC) next_checkin_date    FROM demo_checkin_history t),conn_rst AS (SELECT h.user_id        ,h.checkin_date checkin_date_from        ,connect_by_root h.checkin_date checkin_date_to        ,LEVEL days        ,MAX(LEVEL) over(PARTITION BY h.user_id) max_days        ,MAX(h.checkin_date) keep(dense_rank FIRST ORDER BY LEVEL DESC) over(PARTITION BY h.user_id) last_checkin_date_from    FROM checkin_his h   WHERE connect_by_isleaf = 1   START WITH h.next_checkin_date IS NULL           OR trunc(h.next_checkin_date) - trunc(h.checkin_date) > 1  CONNECT BY PRIOR h.user_id = h.user_id         AND PRIOR trunc(h.checkin_date) = trunc(h.checkin_date) + 1)SELECT c.user_id, c.checkin_date_from, c.checkin_date_to, c.days  FROM conn_rst c WHERE c.days = c.max_days   AND c.checkin_date_from = c.last_checkin_date_from ORDER BY days DESC, checkin_date_from DESC;

MYSQL签到表 mysql连续签到天数_数据_06

这里对于分析函数lag和dense_rank用法不太熟悉的小伙伴，请点击这里和这里回顾

MYSQL签到表 mysql连续签到天数_递归查询

04 转换思路

这个题目，无论是原题还是增加难度后的情况，事实上题干都可以回归到最基本的一个点——数(shǔ)数(shù)

SQL中有一个很朴素的实现计数的功能COUNT，但是我们看到题目后基本上第一个排除的就是用使用COUNT函数，因为使用GROUP BY分组的话，一定是要一致的值才能分到一组，而在本题中，连续签到对应的日期是变化的，所以不能分到一组，进而无法简单的计数

这就跟做选择题一样，往往我们首先排除的选项恰恰就是正确答案(猛男落泪)

此时此刻此情此景，我们需要运用一些数学课上学来的知识，数学中常用的一种思想就是

--= 化变元为常量 =--

这里我们仍以用户2的数据来举例

以日期为横轴，累积签到天数作为纵轴，可以得到下图折线

MYSQL签到表 mysql连续签到天数_结点_08

图中线形水平的地方，是用户没有签到的日子，它们在数据表中是不存在的，这里只在图中补充出来

而线形上升的地方，正是用户连续签到的阶段，由于每天最多签到一次，所以这些上升的地方斜率都是1

这时我们再虚构一条折线，这条折线代表着某个勤奋的用户，坚持每天签到

MYSQL签到表 mysql连续签到天数_结点_09

不积跬步无以至千里，虚构用户其线形的斜率无非也是1，但坚持的力量让用户2不能望其项背

喝完鸡汤，我们用纯粹的数学的眼光观察这两条折线，如果将紫线设为f1，红线设为f2，那么f1-f2，就能得到黑线，设为f3

MYSQL签到表 mysql连续签到天数_结点_10

这个黑线妙就妙在，红线水平的地方，它是上升的，而红线上升的地方，它却是水平的

(朋友们，我们休息的时候，正是别人和我们拉开差距的时候啊！)

至此，我们巧妙地构造了一个变元，与原来的变元相减后，得到了常量

换句话说，我们得以把连续的日期分到一组当中了！

第一步，我们需要用表中的数据，计算出每一行上的累积天数，可以使用

COUNT(1) OVER(PARTITION BY user_id ORDER BY checkin_date)

因为聚合函数在分析模式下默认的窗口正好是第一行到当前行，所以无需考虑窗口问题

不过，计算第一行到当前行有多少行，不正是计算排名吗？所以可以用排名函数来取代计数，尽管没有资料支持，但小刘觉得取名次比计数运算能让数据库少做些事情?

本题中同一用户的签到日期不会出现排名并列的情况，故选择ROW_NUMBER函数即可

ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY checkin_date)

第二步，实现构造函数f1，由于其表达式为f1(x)=x，换成人话就是日期

EXTRACT(DAY FROM checkin_date)

第三步，得到初代版本

WITH history AS (SELECT h.user_id        ,h.checkin_date        ,extract(DAY FROM h.checkin_date) f1        ,row_number() over(PARTITION BY h.user_id ORDER BY h.checkin_date) f2    FROM demo_checkin_history h),group_rst AS (SELECT h.user_id        ,MIN(h.checkin_date) checkin_date_from        ,MAX(h.checkin_date) checkin_date_to        ,COUNT(1) days    FROM history h   GROUP BY h.user_id, h.f1 - h.f2)SELECT g.user_id      ,MAX(g.checkin_date_from) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_from      ,MAX(g.checkin_date_to) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_to      ,MAX(g.days) max_days  FROM group_rst g GROUP BY g.user_id ORDER BY max_days DESC, checkin_date_from DESC;

第四步，上例是基于仅统计一个月份内的前提，如果时间范围放大就会出问题，所以需要把构造函数f1再还原到签到日期本身

WITH history AS (SELECT h.user_id        ,h.checkin_date        ,row_number() over(PARTITION BY h.user_id ORDER BY h.checkin_date) delta    FROM demo_checkin_history h),group_rst AS (SELECT h.user_id        ,MIN(h.checkin_date) checkin_date_from        ,MAX(h.checkin_date) checkin_date_to        ,COUNT(1) days    FROM history h   GROUP BY h.user_id, trunc(h.checkin_date) - h.delta)SELECT g.user_id      ,MAX(g.checkin_date_from) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_from      ,MAX(g.checkin_date_to) keep(dense_rank FIRST ORDER BY days DESC) checkin_date_to      ,MAX(g.days) max_days  FROM group_rst g GROUP BY g.user_id ORDER BY max_days DESC, checkin_date_from DESC;

MYSQL签到表 mysql连续签到天数_结点_11