SQL确定最小连续访问天数？ [英] SQL to determine minimum sequential days of access?

问题描述

以下用户历史记录表格包含特定用户每天访问一个网站的一条记录（UTC时间为24小时）。它有成千上万的记录，但每个用户每天只有一个记录。如果用户当天没有访问该网站，则不会生成任何记录。

 
 Id UserId CreationDate 
 --- --- ------ ------------ 
 750997 12 2009-07-07 18：42：20.723 
 750998 15 2009-07-07 18： 42：20.927 
 751000 19 2009-07-07 18：42：22.283 
 

我正在寻找的是一个SQL在这个表格上查询，表现良好，告诉我哪些用户名已连续访问网站（n），而不会丢失一天。

换句话说，这个表格中有多少用户有（n）个记录，其顺序（前一天或后天）日期？如果序列中缺少任何一天，则该序列将被破坏，并应在1处重新启动;我们正在寻找连续几天的用户，没有差距。

此查询与是纯粹巧合的，当然是...。）

解决方案

答案很明显：

  SELECT DISTINCT UserId 
 FROM UserHistory uh1 
 WHERE （
 SELECT COUNT（*）
 FROM UserHistory uh2 
 WHERE uh2.CreationDate 
 BETWEEN uh1.CreationDate AND DATEADD（d，@days，uh1.CreationDate）
） = @days OR UserId = 52551 
  

编辑：

这是我的严肃答案：

  DECLARE @days int 
 DECLARE @秒bigint 
 SET @days = 30 
 SET @seconds =（@days * 24 * 60 * 60） -  1 
 SELECT DISTINCT UserId 
 FROM（
 SELECT uh1 .UserId，Count（uh1.Id）as Conseq 
 FROM UserHistory uh1 
 INNER JOIN UserHistory uh2 ON uh2.CreationDate 
 BETWEEN uh1.CreationDate AND 
 DATEADD（s，@seconds，DATEADD（dd，DATEDIFF（dd，0， uh1.CreationDate），0））
 AND uh1.UserId = uh2.UserId 
 GROUP BY uh1.Id，uh1.UserId 
）as Tbl 
 WHERE Con​​seq> = @天
  

编辑：

$ b $ [Jeff Atwood]这是一个很棒的快速解决方案，值得被接受，但是 Rob Farley的解决方案也非常出色，可以说更快（！）。请检查一下！

The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per user. If the user has not accessed the website for that day, no record will be generated.

Id      UserId   CreationDate
------  ------   ------------
750997      12   2009-07-07 18:42:20.723
750998      15   2009-07-07 18:42:20.927
751000      19   2009-07-07 18:42:22.283

What I'm looking for is a SQL query on this table with good performance, that tells me which userids have accessed the website for (n) continuous days without missing a day.

In other words, how many users have (n) records in this table with sequential (day-before, or day-after) dates? If any day is missing from the sequence, the sequence is broken and should restart again at 1; we're looking for users who have achieved a continuous number of days here with no gaps.

Any resemblance between this query and a particular Stack Overflow badge is purely coincidental, of course.. :)

解决方案

The answer is obviously:

SELECT DISTINCT UserId
FROM UserHistory uh1
WHERE (
       SELECT COUNT(*) 
       FROM UserHistory uh2 
       WHERE uh2.CreationDate 
       BETWEEN uh1.CreationDate AND DATEADD(d, @days, uh1.CreationDate)
      ) = @days OR UserId = 52551

EDIT:

Okay here's my serious answer:

DECLARE @days int
DECLARE @seconds bigint
SET @days = 30
SET @seconds = (@days * 24 * 60 * 60) - 1
SELECT DISTINCT UserId
FROM (
    SELECT uh1.UserId, Count(uh1.Id) as Conseq
    FROM UserHistory uh1
    INNER JOIN UserHistory uh2 ON uh2.CreationDate 
        BETWEEN uh1.CreationDate AND 
            DATEADD(s, @seconds, DATEADD(dd, DATEDIFF(dd, 0, uh1.CreationDate), 0))
        AND uh1.UserId = uh2.UserId
    GROUP BY uh1.Id, uh1.UserId
    ) as Tbl
WHERE Conseq >= @days

EDIT:

[Jeff Atwood] This is a great fast solution and deserves to be accepted, but Rob Farley's solution is also excellent and arguably even faster (!). Please check it out too!

这篇关于SQL确定最小连续访问天数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！