[sql] 최소 연속 액세스 일수를 결정하는 SQL?

다음 사용자 기록 테이블에는 지정된 사용자가 웹 사이트에 액세스 한 매일 (24 시간 UTC 기간)에 대한 하나의 레코드 가 포함되어 있습니다 . 수천 개의 레코드가 있지만 사용자 당 하루에 하나의 레코드 만 있습니다. 사용자가 그날 웹 사이트에 접속하지 않은 경우 기록이 생성되지 않습니다.

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 쿼리로 , 어느 사용자 ID가 하루를 놓치지 않고 (n) 일 동안 웹 사이트에 액세스했는지 알려줍니다.

즉, 이 테이블에 순차 (전일 또는 후일) 날짜가있는 레코드가있는 사용자 수는 몇 명 입니까? 시퀀스에서 누락 된 날짜가 있으면 시퀀스가 ​​중단되고 1시에 다시 시작해야합니다. 여기에서 공백없이 연속적인 일수를 달성 한 사용자를 찾고 있습니다.

이 쿼리와 특정 Stack Overflow 배지 사이의 유사점 은 물론 순전히 우연입니다 .. 🙂



답변

대답은 분명히 다음과 같습니다.

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 @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

편집하다:

[Jeff Atwood] 이것은 훌륭하고 빠른 솔루션이며 받아 들일 가치가 있지만 Rob Farley의 솔루션도 훌륭하고 틀림없이 더 빠릅니다 (!). 꼭 확인 해주세요!


답변

(그리고 이전 문장이 세미콜론으로 끝나는 지 확인하세요) :

WITH numberedrows
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID
                                       ORDER BY CreationDate)
                - DATEDIFF(day,'19000101',CreationDate) AS TheOffset,
                CreationDate,
                UserID
         FROM   tablename)
SELECT MIN(CreationDate),
       MAX(CreationDate),
       COUNT(*) AS NumConsecutiveDays,
       UserID
FROM   numberedrows
GROUP  BY UserID,
          TheOffset  

날짜 목록 (숫자)과 row_number가 있으면 누락 된 날짜가이 두 목록 사이의 오프셋을 약간 더 크게 만든다는 아이디어입니다. 그래서 우리는 일관된 오프셋을 가진 범위를 찾고 있습니다.

이 끝에 “ORDER BY NumConsecutiveDays DESC”를 사용하거나 임계 값에 대해 “HAVING count (*)> 14″라고 말할 수 있습니다.

나는 이것을 테스트하지 않았습니다-단지 내 머리 꼭대기에 씁니다. SQL2005 이상에서 작동하기를 바랍니다.

… 그리고 tablename (UserID, CreationDate)의 인덱스에 의해 많은 도움이 될 것입니다.

편집 됨 : Offset이 예약어 인 것으로 밝혀 졌으므로 대신 TheOffset을 사용했습니다.

수정 됨 : COUNT (*)를 사용하라는 제안은 매우 유효합니다. 처음에는 그렇게 했어야했지만 실제로는 생각하지 않았습니다. 이전에는 대신 datediff (day, min (CreationDate), max (CreationDate))를 사용했습니다.

Rob


답변

당신이 테이블 스키마를 변경할 수 있다면, 나는 열을 추가 좋을 것 LongestStreak당신이 결말 연속 일수로 설정하려는 테이블에 CreationDate. 그것은 당신이 행이 현재의 날이 존재하지 않는 경우 모든 행이 전날이있는 경우, 당신은 확인할 것이다, 이미하고있는 일에 (유사한 로그인 시간에 테이블을 업데이트하는 것은 쉽다. true의 경우, 당신은 증가 것이다 LongestStreak의를 새 행, 그렇지 않으면 1로 설정합니다.)

이 열을 추가하면 쿼리가 명확 해집니다.

if exists(select * from table
          where LongestStreak >= 30 and UserId = @UserId)
   -- award the Woot badge.


답변

다음 행을 따라 멋지게 표현 된 SQL :

select
        userId,
    dbo.MaxConsecutiveDates(CreationDate) as blah
from
    dbo.Logins
group by
    userId

다음과 같은 사용자 정의 집계 함수 가 있다고 가정합니다 (버그가 있음을 유의하십시오).

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;

namespace SqlServerProject1
{
    [StructLayout(LayoutKind.Sequential)]
    [Serializable]
    internal struct MaxConsecutiveState
    {
        public int CurrentSequentialDays;
        public int MaxSequentialDays;
        public SqlDateTime LastDate;
    }

    [Serializable]
    [SqlUserDefinedAggregate(
        Format.Native,
        IsInvariantToNulls = true, //optimizer property
        IsInvariantToDuplicates = false, //optimizer property
        IsInvariantToOrder = false) //optimizer property
    ]
    [StructLayout(LayoutKind.Sequential)]
    public class MaxConsecutiveDates
    {
        /// <summary>
        /// The variable that holds the intermediate result of the concatenation
        /// </summary>
        private MaxConsecutiveState _intermediateResult;

        /// <summary>
        /// Initialize the internal data structures
        /// </summary>
        public void Init()
        {
            _intermediateResult = new MaxConsecutiveState { LastDate = SqlDateTime.MinValue, CurrentSequentialDays = 0, MaxSequentialDays = 0 };
        }

        /// <summary>
        /// Accumulate the next value, not if the value is null
        /// </summary>
        /// <param name="value"></param>
        public void Accumulate(SqlDateTime value)
        {
            if (value.IsNull)
            {
                return;
            }
            int sequentialDays = _intermediateResult.CurrentSequentialDays;
            int maxSequentialDays = _intermediateResult.MaxSequentialDays;
            DateTime currentDate = value.Value.Date;
            if (currentDate.AddDays(-1).Equals(new DateTime(_intermediateResult.LastDate.TimeTicks)))
                sequentialDays++;
            else
            {
                maxSequentialDays = Math.Max(sequentialDays, maxSequentialDays);
                sequentialDays = 1;
            }
            _intermediateResult = new MaxConsecutiveState
                                      {
                                          CurrentSequentialDays = sequentialDays,
                                          LastDate = currentDate,
                                          MaxSequentialDays = maxSequentialDays
                                      };
        }

        /// <summary>
        /// Merge the partially computed aggregate with this aggregate.
        /// </summary>
        /// <param name="other"></param>
        public void Merge(MaxConsecutiveDates other)
        {
            // add stuff for two separate calculations
        }

        /// <summary>
        /// Called at the end of aggregation, to return the results of the aggregation.
        /// </summary>
        /// <returns></returns>
        public SqlInt32 Terminate()
        {
            int max = Math.Max((int) ((sbyte) _intermediateResult.CurrentSequentialDays), (sbyte) _intermediateResult.MaxSequentialDays);
            return new SqlInt32(max);
        }
    }
}


답변

n 일 동안 연속하려면 n 개의 행이 있어야한다는 사실을 활용할 수있는 것 같습니다.

그래서 다음과 같습니다.

SELECT users.UserId, count(1) as cnt
FROM users
WHERE users.CreationDate > now() - INTERVAL 30 DAY
GROUP BY UserId
HAVING cnt = 30


답변

단일 SQL 쿼리로이 작업을 수행하는 것은 나에게 지나치게 복잡해 보입니다. 이 답변을 두 부분으로 나누겠습니다.

  1. 지금까지해야했고 지금 시작해야 할
    작업 : 오늘 로그인 한 모든 사용자를 확인하는 매일 크론 작업을 실행 한 다음 카운터가있는 경우 카운터를 증가 시키거나 그렇지 않은 경우 0으로 설정합니다.

  2. 지금해야 할 일 :
    -이 테이블을 웹 사이트를 실행하지 않고 잠시 동안 필요하지 않은 서버로 내 보냅니다. 😉
    -사용자별로 정렬 한 다음 날짜로 정렬합니다.
    -순차적으로 진행하고 카운터 유지 …

답변

이것이 당신에게 매우 중요하다면,이 이벤트를 소싱하고이 정보를 제공하는 테이블을 구동하십시오. 모든 미친 쿼리로 기계를 죽일 필요가 없습니다.