在SQL中查找最长连续事件:实例

原创|行业资讯|编辑:龚雪|2015-11-11 10:55:07.000|阅读 464 次

概述:我们用SQL可以轻松解决一个很有趣的问题,寻找时间序列中的系列连续事件。你知道什么是时间序列的系列连续事件吗?

以Stack Overflow为例。Stack Overflow的荣誉系统可以对用户的某些行为给予徽章奖励。作为一个社交网站,他们鼓励用户每天都访问该平台。因此,他们授予用户这两个徽章:

这个意思很明显,必须连续登陆,否则将会重新计数。

怎样在SQL中实现?

为了解决这个问题,我们将使用一个很棒的堆栈交换数据资源管理器。它提供了很多堆栈交换需要用到的信息。

这里需要注意,我们不会查询连续两天的访问记录,我们只查询用户的连续访问天数。

支持的数据库为SQL Server,我们可以运行下面的语句:

SELECT DISTINCT CAST(CreationDate AS DATE) AS date
FROM Posts
WHERE OwnerUserId = ##UserId##
ORDER BY 1

其中,对于我自己的用户ID生成是这样的:

date          
----------
2010-11-26
2010-11-27
2010-11-29
2010-11-30
2010-12-01
2010-12-02
2010-12-03
2010-12-05
2010-12-06
2010-12-07
2010-12-08
2010-12-09
2010-12-13
2010-12-14
...
(769 rows)

正如我们在数据中看到的那样,隔断在很早之前就已经出现了:

date          
--------------------------------------
2010-11-26
2010-11-27 <---- Gap here after 2 days

2010-11-29
2010-11-30
2010-12-01
2010-12-02
2010-12-03 <---- Gap here after 5 days

2010-12-05
2010-12-06
2010-12-07
2010-12-08
2010-12-09 <---- Gap here after 5 days

2010-12-13
2010-12-14
...

我们可以很容易看出有多少连续的天数。但是,如何使用SQL办到呢?

为了简化问题,让我们在通用表表达式中进行“store”个体查询。如下:

WITH 

  -- This table contains all the distinct date 
  -- instances in the data set
  dates(date) AS (
    SELECT DISTINCT CAST(CreationDate AS DATE)
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  )
...

这样,查询目标会把所有连续日期生成在同一组中。接下来的查询,我们这样写:

SELECT 
  COUNT(*) AS consecutiveDates,
  MIN(week) AS minDate,
  MAX(week) AS maxDate
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

我们将每组的“grp”集中起来并统计一组中日期的数目,同时找到每个组内日期的上下值。

生成一组连续日期

我们将添加连续的行数,忽略日期中的间隙:

row number   date          
--------------------------------
1            2010-11-26
2            2010-11-27

3            2010-11-29 <-- gap before this row
4            2010-11-30
5            2010-12-01
6            2010-12-02
7            2010-12-03

8            2010-12-05 <-- gap before this row

正如你所看到的,无论日期之间是否有间隙(两个日期是不连续的),他们的行号仍是连续的。我们可以使用ROW_NUMBER()函数轻松做到这一点:

SELECT
  ROW_NUMBER() OVER (ORDER BY date) AS [row number],
  date
FROM dates

现在,我们来检查一下刚才的语句:

WITH 

  -- This table contains all the distinct date 
  -- instances in the data set
  dates(date) AS (
    SELECT DISTINCT CAST(CreationDate AS DATE)
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  ),

  -- Generate "groups" of dates by subtracting the
  -- date's row number (no gaps) from the date itself
  -- (with potential gaps). Whenever there is a gap,
  -- there will be a new group
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY date) AS rn,
      dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp,
      date
    FROM dates
  )
SELECT *
FROM groups
ORDER BY rn

上面的查询得到:

rn  grp          date          
--- ----------   ----------
1   2010-11-25   2010-11-26
2   2010-11-25   2010-11-27
3   2010-11-26   2010-11-29
4   2010-11-26   2010-11-30
5   2010-11-26   2010-12-01
6   2010-11-26   2010-12-02
7   2010-11-26   2010-12-03
8   2010-11-27   2010-12-05
9   2010-11-27   2010-12-06
10  2010-11-27   2010-12-07
11  2010-11-27   2010-12-08
12  2010-11-27   2010-12-09
13  2010-11-30   2010-12-13
14  2010-11-30   2010-12-14

当日期天数连续时,下面的两个等式是成立的:

date2 - date1 = 1 // difference in days between dates
rn2   - rn1   = 1 // difference in row numbers

但是,当日期天数不连续时,不同的“row”数值仍是1,但是不同“date”不再是1。如下所示:

rn  grp          date          
--- ----------   ----------
1   2010-11-25   2010-11-26
2   2010-11-25   2010-11-27

3   2010-11-26   2010-11-29
4   2010-11-26   2010-11-30
5   2010-11-26   2010-12-01
6   2010-11-26   2010-12-02
7   2010-11-26   2010-12-03

8   2010-11-27   2010-12-05
9   2010-11-27   2010-12-06
10  2010-11-27   2010-12-07
11  2010-11-27   2010-12-08
12  2010-11-27   2010-12-09

13  2010-11-30   2010-12-13
14  2010-11-30   2010-12-14

我们可以在下面看到完整的查询:

WITH 

  -- This table contains all the distinct date 
  -- instances in the data set
  dates(date) AS (
    SELECT DISTINCT CAST(CreationDate AS DATE)
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  ),

  -- Generate "groups" of dates by subtracting the
  -- date's row number (no gaps) from the date itself
  -- (with potential gaps). Whenever there is a gap,
  -- there will be a new group
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY date) AS rn,
      dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp,
      date
    FROM dates
  )
SELECT 
  COUNT(*) AS consecutiveDates,
  MIN(week) AS minDate,
  MAX(week) AS maxDate
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

产生结果:

consecutiveDates minDate       maxDate       
---------------- ------------- ------------- 
14               2012-08-13    2012-08-26
14               2012-02-03    2012-02-16
10               2013-10-24    2013-11-02
10               2011-05-11    2011-05-20
9                2011-06-30    2011-07-08
7                2012-01-17    2012-01-23
7                2011-06-14    2011-06-20
6                2012-04-10    2012-04-15
6                2012-04-02    2012-04-07
6                2012-03-26    2012-03-31
6                2011-10-27    2011-11-01
6                2011-07-17    2011-07-22
6                2011-05-23    2011-05-28
...




标签:数据库数据库表

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处,尊重他人劳动成果

登录 慧都网发表评论


慧都网友 2015-11-11 11:00:46.000
0

详细,实用


为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
相关厂商
相关产品
Navicat for SQL Server

Navicat for SQL Server 是一套专为 Mircosoft SQL Server设计的强大数据库管理及开发工具

在线
客服
在线
QQ
电话
咨询
400-700-1020
购物车 反馈 返回
顶部
在线客服系统
live chat