内容简介:现在我们正在进行声明性编程,我们不关心维护任何状态和内存中的计数器。我们希望以在线分析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.
这个动作代表一个简单的判断:
- ROW_NUMBER()没有间隙。这就是它的定义方式
- 但是,我们的数据确实如此
因此,当我们从“连续”系列的非连续日期中减去“无间隙”系列连续整数时,我们将为连续日期的每个“无间隙”子系列获得相同的日期,我们将再次获得新的有间隙的日期系列。
这意味着我们现在只需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技巧之四:找到连续的没有间隙的最大系列的日期》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 内敛元素间隙问题
- Android 之路 (8) - Toolbar - NavigationIcon间隙、Title居中、BackText适配
- CSS 技巧篇(六):display设置元素为行内元素时,元素之间存在间隙问题
- 找到思聪王
- React发展历程中找到问题
- 如何找到APT攻击的“脉门”?
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
热搜:搜索排名营销大揭秘
【美】肖恩·布拉德利 / 中国人民大学出版社有限公司 / 2018-7-30 / CNY 55.00
首部大数据在我国政府管理场景中的应用实践案例读本,全面展示我国电子政务与数字化建设的成果,深度理解实施国家大数据战略的重要意义。 本书作者作为国内最早从事大数据应用研究的实践者之一,亲历了中国大数据的发展历程、主要事件、应用案例以及行业变化。 在本书中,作者将其所亲历的大数据发展历程进行了阐述,从大数据的基本概念、特点到实践解读,通俗易懂,给我们的实际工作提供了重要参考。作者将帮助读者......一起来看看 《热搜:搜索排名营销大揭秘》 这本书的介绍吧!