专栏首页SQL实现SQL 获取最长的日期序列

SQL 获取最长的日期序列

有一张学习打卡表 his_sign 表,简单起见,只设置了两个字段(id,create_ts),一个是主键,另一个是打卡时间。his_sign 表的数据如下,我们要统计出这张表里面最长的连续打卡记录。

    id            create_ts  
------  ---------------------
     1    2020-05-01 09:04:26
     2    2020-05-02 11:54:45
     3    2020-05-04 23:05:03
     4    2020-05-06 07:12:31
     5    2020-05-06 08:01:52
     6    2020-05-07 22:06:48
     7    2020-05-08 12:36:58
     8    2020-05-09 11:49:13
     9    2020-05-12 08:52:35
    10    2020-05-13 23:45:57
    11    2020-05-14 00:02:24
    12    2020-05-14 09:24:18
    13    2020-05-19 15:34:45
    14    2020-05-21 21:10:02

先检查数据,我们发现在一天之内可以多次打卡,因此需要先去掉重复打卡的记录,并将字段 create_ts 使用日期格式展示。

SELECT DISTINCT 
  (DATE(create_ts)) AS create_ts 
FROM
  his_sign

去重并格式化后的数据如下:

create_ts   
------------
2020-05-01  
2020-05-02  
2020-05-04  
2020-05-06  
2020-05-07  
2020-05-08  
2020-05-09  
2020-05-12  
2020-05-13  
2020-05-14  
2020-05-19  
2020-05-21

由于数据量不大,我们观察表数据可知,2020-05-062020-05-09 是最长的序列,总共 4 天。

解题的思路就是把连续的日期编为一组,然后从多组数据中找到数量最多的一组数据,那组数据就是最长的序列。

将表里面的数据按日期的升序排序,并给每个日期分配一个连续的自然数序号,用日期减去它对应的序号,会得到一个新的日期值。我们发现,连续的日期它们对应的新的日期值为同一个,因此,这个新的日期值就是序列的组别。

找到连续日期的组的 SQL 如下:

WITH t1 AS 
(SELECT DISTINCT 
  (DATE(create_ts)) AS create_ts 
FROM
  his_sign),
t2 AS 
(SELECT 
  create_ts,
  row_number () over (
ORDER BY create_ts) AS rn 
FROM
  t1) 
SELECT 
  create_ts,
  DATE_SUB(create_ts, INTERVAL rn DAY) AS grp 
FROM
  t2

上面 SQL 执行后输出的结果:

create_ts   grp         
----------  ------------
2020-05-01  2020-04-30  
2020-05-02  2020-04-30  
2020-05-04  2020-05-01  
2020-05-06  2020-05-02  
2020-05-07  2020-05-02  
2020-05-08  2020-05-02  
2020-05-09  2020-05-02  
2020-05-12  2020-05-04  
2020-05-13  2020-05-04  
2020-05-14  2020-05-04  
2020-05-19  2020-05-08  
2020-05-21  2020-05-09

剩下的操作就简单多了,把数据最多的那组找出来就对了。只是需要注意,最长的序列有可能有多个,因此在找最长的序列的时候需要注意方法。

结合开窗函数 rank() over(ORDER BY xxx) 可以找到多个最长序列,完整的 SQL 如下:

# 1.去掉重复日期,并格式化
WITH t1 AS 
(SELECT DISTINCT 
  (DATE(create_ts)) AS create_ts 
FROM
  his_sign),
# 2.给每个日期指定一个序号
t2 AS 
(SELECT 
  create_ts,
  row_number () over (
ORDER BY create_ts) AS rn 
FROM
  t1),
# 3.找到分组的依据
t3 AS 
(SELECT 
  create_ts,
  DATE_SUB(create_ts, INTERVAL rn DAY) AS grp 
FROM
  t2),
# 4.分组 
t4 AS 
(SELECT 
  MIN(create_ts) AS start_date,
  MAX(create_ts) AS end_date,
  COUNT(*) AS cnt 
FROM
  t3 
GROUP BY grp),
# 5.对所有序列按照长度降序排序
t5 AS 
(SELECT 
  *,
  rank () over (
ORDER BY cnt DESC) AS rk 
FROM
  t4) 
# 6.只选择最长的序列
SELECT 
  start_date,
  end_date,
  cnt 
FROM
  t5 
WHERE rk = 1 

输出:

start_date  end_date       cnt  
----------  ----------  --------
2020-05-06  2020-05-09         4

为了让大家看得更明白,我用 CTE 表达式把每个过程都写出来了。每段表达式都加了注释,理解起来应该不难。

注意,上述的 SQL 需要在 MySQL 8.0 + 环境里才能正常执行。

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一道初级ETL BI的笔试题

    今天收到一个朋友发来的初级ETL BI 的笔试题,我觉得题目蛮有意思的,于是拿出来和大家分享。

    白日梦想家
  • SQL 的递归表达式

    MySQL 在 8.0 的版本引入了公共表表达式(Common Table Expressions),简称 CTE。CTE 在一些方面可以简化我们的 SQL 语...

    白日梦想家
  • SQL 打印成绩单

    这是 HackerRank 上的一道中级难度的 SQL 挑战题,实际上考察的是动态排序。

    白日梦想家
  • Python多进程之进程池

    由于Python中线程封锁机制,导致Python中的多线程并不是正真意义上的多线程。当我们有并行处理需求的时候,可以采用多进程迂回地解决。

    卡尔曼和玻尔兹曼谁曼
  • 存储过程中指定参数

    一个过程最多可以有 2100 个参数,每个参数都有名称、数据类型和方向。 还可以为参数指定默认值(可选)。

    Vincent-yuan
  • 每天一个小技巧:CSS clip-path 的妙用 Clip Path分类Clippy

    CSS 的 clip-path 属性是 clip 属性的升级版,它们的作用都是对元素进行 “剪裁”,不同的是 clip 只能作用于 position 为 abs...

    MudOnTire
  • 9个你不了解的关于物联网的事实

    IoT设备已经在我们周围,但是与未来几年下一代数据网络引入的这个市场将会出现的爆炸性相比,这是没有什么的。 物联网正式是大交易。你知道这个故事:机器到机器网络将...

    首席架构师智库
  • 去除 u200B

    前两天检测网站,发现有条连接的前边出现了一个空格,还删不掉,用 F12 看了下出现了一个 u200B 的字符,非常古怪。

    Savalone
  • 《Spring Boot极简教程》第7章 Spring Boot集成模板引擎

    其实,没有任何一个模板引擎(jsp,velocity,thymeleaf,freemarker,etc)可以完全实现MVC绝对的分层,只有“自由度”上的界定罢了...

    一个会写诗的程序员
  • 记一次python脚本的编写过程

    因工作需求要写一个python脚本,来测试memcache服务器。 因为第一次写python脚本在线上运行,所以不敢大意。而又因为我自己看着python教程自学...

    小小科

扫码关注云+社区

领取腾讯云代金券