如何在SQL中找到最长的连续事件系列

最新推荐文章于 2023-04-26 16:25:44 发布

danpu0978

最新推荐文章于 2023-04-26 16:25:44 发布

阅读量343

点赞数

文章标签： java python 数据库 mysql sql

使用SQL可以轻松解决的一个非常有趣的问题是找到时间序列中的连续事件。但是，时间序列中的连续事件是什么？

以堆栈溢出为例。 Stack Overflow有一个很酷的信誉系统，该系统使用徽章奖励某些行为。作为社交网站，他们鼓励用户每天访问该平台。因此，将颁发两个不同的徽章：

非正式地，这显然意味着什么。您必须在第1天登录。然后在第2天再次登录。然后在第3天再次登录（也许几次，这都没有关系）。忘记在第4天登录吗？哎呀我们将再次开始计数。

如何在SQL中执行此操作？

在此博客上，每个问题都将在SQL中找到其解决方案。也是。为了解决这个问题，我们将使用令人敬畏的Stack Exchange Data Explorer ，它公开了许多Stack Exchange的公共使用信息。

请注意，我们不会查询访问的连续天数，因为此信息不会公开提供。相反，让我们查询用户发表帖子的连续天数。

支持数据库是SQL Server，因此我们可以运行以下语句：

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

……对于我自己的UserId生成如下内容：

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呢？

为了简化问题，让我们将单个查询“存储”在公用表表达式中。上面的查询，我们称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##
  )
...

现在，结果查询的目标是将所有连续的日期放在同一组中，以便我们可以对该组进行汇总。以下查询是我们要编写的：

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

（在此处自己运行语句）

我们要做的就是从日期中减去行号，以获得新的日期“ grp ”。通过这种方式获得的实际日期无关紧要。这只是一个辅助值。

但是，我们可以保证的是，对于连续的日期， grp的值将是相同的，因为对于所有连续的日期，以下两个等式得出的结果为true：

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

但是，对于非连续日期，尽管行号的差仍然为1，但天的差不再为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
...

（在此处自己运行语句）

奖金查询1：查找连续的星期

我们在上述查询中选择天数的事实是一个随机选择。我们只需从时间序列中获取时间戳，然后使用CAST函数将其“折叠”到所需的粒度即可：

SELECT DISTINCT CAST(CreationDate AS DATE)

如果我们想知道连续几周，我们只需将该函数更改为其他表达式即可，例如

SELECT DISTINCT datepart(year, CreationDate) * 100 
              + datepart(week, CreationDate)

这个新表达式使用年份和星期，并在2015年为03的周生成类似于201503的值。该语句的其余部分保持完全相同：

WITH 
  weeks(week) AS (
    SELECT DISTINCT datepart(year, CreationDate) * 100 
                  + datepart(week, CreationDate)
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  ),
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY week) AS rn,
      dateadd(day, -ROW_NUMBER() OVER (ORDER BY week), week) AS grp,
      week
    FROM weeks
  )
SELECT 
  COUNT(*) AS consecutiveWeeks,
  MIN(week) AS minWeek,
  MAX(week) AS maxWeek
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

我们将得到以下结果：

consecutiveWeeks minWeek maxWeek 
---------------- ------- ------- 
45               201401  201445  
29               201225  201253  
25               201114  201138  
23               201201  201223  
20               201333  201352  
16               201529  201544  
15               201305  201319  
12               201514  201525  
12               201142  201153  
9                201502  201510  
7                201447  201453  
7                201321  201327  
6                201048  201053  
4                201106  201109  
3                201329  201331  
3                201102  201104  
2                201301  201302  
2                201111  201112  
1                201512  201512

（在此处自己运行语句）

毫不奇怪，由于我通常广泛使用Stack Overflow，因此连续几周的时间跨度更长。

奖励查询2：使用DENSE_RANK（）简化查询

在上一篇文章中，我们显示了SQL技巧： ROW_NUMBER()是SELECT ， DENSE_RANK()是SELECT DISTINCT 。

如果我们回到连续的日期示例，则可以使用DENSE_RANK()重写查询以一次性找到不同的日期和组：

WITH 
  groups(date, grp) AS (
    SELECT DISTINCT 
      CAST(CreationDate AS DATE),
      dateadd(day, 
        -DENSE_RANK() OVER (ORDER BY CAST(CreationDate AS DATE)), 
        CAST(CreationDate AS DATE)) AS grp
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  )
SELECT 
  COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

（在此处自己运行语句）

如果上述方法没有意义，建议您在此处阅读我们的上一篇文章，其中对此进行了解释：

http://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/

进一步阅读

上面是在SQL中使用窗口函数（ ROW_NUMBER() ）的一个非常有用的示例。在以下任何文章中了解有关窗口函数的更多信息：

翻译自: https://www.javacodegeeks.com/2015/11/how-to-find-the-longest-consecutive-series-of-events-in-sql.html

danpu0978

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
如何在SQL中找到最长的连续事件系列

使用SQL可以轻松解决的一个非常有趣的问题是找到时间序列中的连续事件。但是，时间序列中的连续事件是什么？以堆栈溢出为例。 Stack Overflow有一个很酷的信誉系统，该系统使用徽章奖励某些行为。作为社交网站，他们鼓励用户每天访问该平台。因此，将颁发两个不同的徽章：非正式地，这显然意味着什么。您必须在第1天登录。然后在第2天再次登录。然后在第3天再次登录（也许几次，...
复制链接

扫一扫