10个SQL技巧之四:找到连续的没有间隙的最大系列的日期

栏目: 数据库 · 发布时间: 6年前

内容简介:现在我们正在进行声明性编程,我们不关心维护任何状态和内存中的计数器。我们希望以在线分析SQL的形式表达这一点。即考虑这些数据:这没有多大帮助。让我们从时间戳中删除小时。这很简单:输出:

现在我们正在进行声明性编程,我们不关心维护任何状态和内存中的计数器。我们希望以在线分析 SQL 的形式表达这一点。即考虑这些数据:

| LOGIN_TIME | 
| --------------------- | 
| 2014-03-18 05:37:13 | 
| 2014-03-16 08:31:47 | 
| 2014-03-16 06:11:17 | 
| 2014-03-16 05:59:33 | 
| 2014-03-15 11:17:28 | 
| 2014-03-15 10:00:11 | 
| 2014-03-15 07:45:27 | 
| 2014-03-15 07:42:19 | 
| 2014-03-14 09:38:12 |

这没有多大帮助。让我们从时间戳中删除小时。这很简单:

SELECT DISTINCT
  <b>cast</b>(login_time AS DATE) AS login_date
FROM logins
WHERE user_id = :user_id

输出:

| LOGIN_DATE | 
| ------------ | 
| 2014-03-18 | 
| 2014-03-16 | 
| 2014-03-15 | 
| 2014-03-14 |

现在,我们已经了解了窗口函数,让我们只为每个日期添加一个简单的行号:

SELECT
  login_date,
  row_number() OVER (ORDER BY login_date)
FROM login_dates

输出:

| LOGIN_DATE | RN | 
| ------------ | ---- | 
| 2014-03-18 | 4 | 
| 2014-03-16 | 3 | 
| 2014-03-15 | 2 | 
| 2014-03-14 | 1 |

还很容易。现在,如果不是单独选择这些值,我们会减去它们会发生什么?

SELECT
  login_date -
  row_number() OVER (ORDER BY login_date)
FROM login_dates

我们得到这样的结果:

| LOGIN_DATE | RN | GRP | 
| ------------ | ---- | ------------ | 
| 2014-03-18 | 4 | 2014-03-14 | 
| 2014-03-16 | 3 | 2014-03-13 | 
| 2014-03-15 | 2 | 2014-03-13 | 
| 2014-03-14 | 1 | 2014-03-13 |

哇。有趣。所以,14 - 1 = 13,15 - 2 = 13,16 - 3 = 13,但是18 - 4 = 14.

这个动作代表一个简单的判断:

  1. ROW_NUMBER()没有间隙。这就是它的定义方式
  2. 但是,我们的数据确实如此

因此,当我们从“连续”系列的非连续日期中减去“无间隙”系列连续整数时,我们将为连续日期的每个“无间隙”子系列获得相同的日期,我们将再次获得新的有间隙的日期系列。

这意味着我们现在只需GROUP BY这个任意日期值:

SELECT
  min(login_date), max(login_date),
  max(login_date) - 
  min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC

我们已经完成了。已找到最大系列的连续日期,没有间隙:

| MIN        | MAX        | LENGTH |
|------------|------------|--------|
| 2014-03-14 | 2014-03-16 |      3 |
| 2014-03-18 | 2014-03-18 |      1 |

完整查询为:

WITH
  login_dates AS (
    SELECT DISTINCT <b>cast</b>(login_time AS DATE) login_date 
    FROM logins WHERE user_id = :user_id
  ),
  login_date_groups AS (
    SELECT
      login_date,
      login_date - row_number() OVER (ORDER BY login_date) AS grp
    FROM login_dates
  )
SELECT
  min(login_date), max(login_date), 
  max(login_date) - min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC

查询本身确实非常简单和优雅。你决不能以比这更精简的方式实现一些命令式算法。


以上所述就是小编给大家介绍的《10个SQL技巧之四:找到连续的没有间隙的最大系列的日期》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Windows核心编程(第5版)

Windows核心编程(第5版)

Jeffrey Richter、Christophe Nasarre / 葛子昂、周靖、廖敏 / 清华大学出版社 / 2008-9 / 99.00元

这是一本经典的Windows核心编程指南,从第1版到第5版,引领着数十万程序员走入Windows开发阵营,培养了大批精英。. 作为Windows开发人员的必备参考,本书是为打算理解Windows的C和C++程序员精心设计的。第5版全面覆盖Windows XP,Windows Vista和Windows Server 2008中的170个新增函数和Windows特性。书中还讲解了Windows......一起来看看 《Windows核心编程(第5版)》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

图片转BASE64编码
图片转BASE64编码

在线图片转Base64编码工具

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具