SQL do określenia minimalnej liczby kolejnych dni dostępu?

Poniższa tabela historii użytkownika zawiera jeden rekord na każdy dzień, w którym dany użytkownik odwiedził witrynę (w okresie 24 godzin UTC). Ma wiele tysięcy rekordów, ale tylko jeden rekord dziennie na użytkownika. Jeśli użytkownik nie uzyskał dostępu do witryny w tym dniu, żaden rekord nie zostanie wygenerowany.

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

To, czego szukam, to zapytanie SQL w tej tabeli z dobrą wydajnością , które mówi mi, które identyfikatory użytkowników miały dostęp do witryny przez (n) ciągłych dni, nie tracąc ani jednego dnia.

Innymi słowy, ilu użytkowników ma (n) rekordów w tej tabeli z datami sekwencyjnymi (dzień przed lub dzień po) ? Jeśli w sekwencji brakuje któregokolwiek dnia, sekwencja jest przerywana i powinna zostać wznowiona od 1; szukamy użytkowników, którzy osiągnęli tutaj ciągłą liczbę dni bez przerw.

Każde podobieństwo między tym zapytaniem a konkretną odznaką przepełnienia stosu jest oczywiście czysto przypadkowe. :)

Odpowiedź brzmi oczywiście:

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

EDYTOWAĆ:

OK, oto moja poważna odpowiedź:

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

EDYTOWAĆ:

[Jeff Atwood] To świetne, szybkie rozwiązanie i zasługuje na akceptację, ale rozwiązanie Roba Farleya jest również doskonałe i prawdopodobnie nawet szybsze (!). Sprawdź też!

A co z (i upewnij się, że poprzednie stwierdzenie kończyło się średnikiem):

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  

Chodzi o to, że jeśli mamy listę dni (jako liczbę) i wiersz_numer, to pominięte dni sprawiają, że przesunięcie między tymi dwiema listami jest nieco większe. Szukamy więc zakresu o stałym przesunięciu.

Na końcu tego tekstu możesz użyć „ORDER BY NumConsecutiveDays DESC” lub powiedzieć „HAVING count (*)> 14” jako progu ...

Nie testowałem tego jednak - po prostu zapisałem to z czubka głowy. Mam nadzieję, że działa w SQL2005 i nowszych.

... i bardzo by pomógł indeks w nazwie tabeli (UserID, CreationDate)

Edytowano: Okazuje się, że Offset jest słowem zastrzeżonym, więc zamiast tego użyłem TheOffset.

Edytowano: Sugestia użycia COUNT (*) jest bardzo ważna - powinienem był to zrobić w pierwszej kolejności, ale tak naprawdę nie myślałem. Wcześniej zamiast tego korzystał z metody datediff (day, min (CreationDate), max (CreationDate)).

Obrabować

Jeśli możesz zmienić schemat tabeli, sugeruję dodanie kolumny LongestStreakdo tabeli, w której ustawisz liczbę kolejnych dni kończących się na CreationDate. Aktualizacja tabeli podczas logowania jest łatwa (podobnie jak to, co już robisz, jeśli w bieżącym dniu nie ma żadnych wierszy, sprawdzisz, czy istnieje żaden wiersz z poprzedniego dnia. Jeśli prawda, zwiększysz wartość LongestStreakw nowy wiersz, w przeciwnym razie ustawisz go na 1.)

Zapytanie będzie oczywiste po dodaniu tej kolumny:

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

Kilka ładnie wyrazistych SQL na wzór:

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

Zakładając, że masz zdefiniowaną przez użytkownika funkcję agregującą, coś w rodzaju (uwaga, to jest błędne):

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);
        }
    }
}

Wydaje się, że możesz skorzystać z faktu, że ciągłość przez n dni wymagałaby n wierszy.

Więc coś takiego:

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

Zrobienie tego za pomocą pojedynczego zapytania SQL wydaje mi się zbyt skomplikowane. Pozwólcie, że podzielę tę odpowiedź na dwie części.

1. Co powinieneś zrobić do tej pory i powinieneś zacząć robić teraz:
   Uruchom codzienne zadanie crona, które sprawdza każdego użytkownika, czy był dzisiaj zalogowany, a następnie zwiększa licznik, jeśli ma, lub ustawia go na 0, jeśli nie.
2. Co powinieneś teraz zrobić:
   - Wyeksportuj tę tabelę na serwer, na którym nie działa Twoja witryna i przez jakiś czas nie będzie potrzebna. ;)
   - Sortuj według użytkownika, a następnie daty.
   - przejdź przez to sekwencyjnie, zachowaj licznik ...

Jeśli jest to dla Ciebie tak ważne, znajdź to wydarzenie i przygotuj tabelę, aby przekazać Ci te informacje. Nie ma potrzeby zabijania maszyny tymi wszystkimi szalonymi zapytaniami.

Możesz użyć rekurencyjnego CTE (SQL Server 2005+):

WITH recur_date AS (
        SELECT t.userid,
               t.creationDate,
               DATEADD(day, 1, t.created) 'nextDay',
               1 'level' 
          FROM TABLE t
         UNION ALL
        SELECT t.userid,
               t.creationDate,
               DATEADD(day, 1, t.created) 'nextDay',
               rd.level + 1 'level'
          FROM TABLE t
          JOIN recur_date rd on t.creationDate = rd.nextDay AND t.userid = rd.userid)
   SELECT t.*
    FROM recur_date t
   WHERE t.level = @numDays
ORDER BY t.userid

Joe Celko ma pełny rozdział na ten temat w SQL for Smarties (nazywając go Runs and Sequences). Nie mam tej książki w domu, więc kiedy dotrę do pracy ... Odpowiem na to. (zakładając, że tabela historii nazywa się dbo.UserHistory, a liczba dni to @Days)

Kolejny trop pochodzi z bloga SQL Team o uruchomieniach

Innym pomysłem, który miałem, ale nie mam pod ręką serwera SQL do pracy, jest użycie CTE z partycjonowanym ROW_NUMBER w następujący sposób:

WITH Runs
AS
  (SELECT UserID
         , CreationDate
         , ROW_NUMBER() OVER(PARTITION BY UserId
                             ORDER BY CreationDate)
           - ROW_NUMBER() OVER(PARTITION BY UserId, NoBreak
                               ORDER BY CreationDate) AS RunNumber
  FROM
     (SELECT UH.UserID
           , UH.CreationDate
           , ISNULL((SELECT TOP 1 1 
              FROM dbo.UserHistory AS Prior 
              WHERE Prior.UserId = UH.UserId 
              AND Prior.CreationDate
                  BETWEEN DATEADD(dd, DATEDIFF(dd, 0, UH.CreationDate), -1)
                  AND DATEADD(dd, DATEDIFF(dd, 0, UH.CreationDate), 0)), 0) AS NoBreak
      FROM dbo.UserHistory AS UH) AS Consecutive
)
SELECT UserID, MIN(CreationDate) AS RunStart, MAX(CreationDate) AS RunEnd
FROM Runs
GROUP BY UserID, RunNumber
HAVING DATEDIFF(dd, MIN(CreationDate), MAX(CreationDate)) >= @Days

Powyższe jest prawdopodobnie o wiele trudniejsze niż powinno, ale pozostawione jako łaskotanie mózgu, gdy masz inną definicję „biegania” niż tylko randki.

Kilka opcji SQL Server 2012 (przy założeniu, że N = 100 poniżej).

;WITH T(UserID, NRowsPrevious)
     AS (SELECT UserID,
                DATEDIFF(DAY, 
                        LAG(CreationDate, 100) 
                            OVER 
                                (PARTITION BY UserID 
                                     ORDER BY CreationDate), 
                         CreationDate)
         FROM   UserHistory)
SELECT DISTINCT UserID
FROM   T
WHERE  NRowsPrevious = 100 

Chociaż z moimi przykładowymi danymi, poniższe okazały się bardziej wydajne

;WITH U
         AS (SELECT DISTINCT UserId
             FROM   UserHistory) /*Ideally replace with Users table*/
    SELECT UserId
    FROM   U
           CROSS APPLY (SELECT TOP 1 *
                        FROM   (SELECT 
                                       DATEDIFF(DAY, 
                                                LAG(CreationDate, 100) 
                                                  OVER 
                                                   (ORDER BY CreationDate), 
                                                 CreationDate)
                                FROM   UserHistory UH
                                WHERE  U.UserId = UH.UserID) T(NRowsPrevious)
                        WHERE  NRowsPrevious = 100) O

Oba opierają się na ograniczeniu podanym w pytaniu, że na użytkownika przypada najwyżej jeden rekord dziennie.

Coś takiego?

select distinct userid
from table t1, table t2
where t1.UserId = t2.UserId 
  AND trunc(t1.CreationDate) = trunc(t2.CreationDate) + n
  AND (
    select count(*)
    from table t3
    where t1.UserId  = t3.UserId
      and CreationDate between trunc(t1.CreationDate) and trunc(t1.CreationDate)+n
   ) = n

Użyłem prostej właściwości matematycznej, aby określić, kto kolejno odwiedzał witrynę. Ta właściwość polega na tym, że różnica dnia między pierwszym a ostatnim dostępem powinna być równa liczbie rekordów w dzienniku tabeli dostępu.

Oto skrypt SQL, który przetestowałem w Oracle DB (powinien działać również w innych DB):

-- show basic understand of the math properties 
  select    ceil(max (creation_date) - min (creation_date))
              max_min_days_diff,
           count ( * ) real_day_count
    from   user_access_log
group by   user_id;


-- select all users that have consecutively accessed the site 
  select   user_id
    from   user_access_log
group by   user_id
  having       ceil(max (creation_date) - min (creation_date))
           / count ( * ) = 1;



-- get the count of all users that have consecutively accessed the site 
  select   count(user_id) user_count
    from   user_access_log
group by   user_id
  having   ceil(max (creation_date) - min (creation_date))
           / count ( * ) = 1;

Skrypt przygotowania tabeli:

-- create table 
create table user_access_log (id           number, user_id      number, creation_date date);


-- insert seed data 
insert into user_access_log (id, user_id, creation_date)
  values   (1, 12, sysdate);

insert into user_access_log (id, user_id, creation_date)
  values   (2, 12, sysdate + 1);

insert into user_access_log (id, user_id, creation_date)
  values   (3, 12, sysdate + 2);

insert into user_access_log (id, user_id, creation_date)
  values   (4, 16, sysdate);

insert into user_access_log (id, user_id, creation_date)
  values   (5, 16, sysdate + 1);

insert into user_access_log (id, user_id, creation_date)
  values   (6, 16, sysdate + 5);

declare @startdate as datetime, @days as int
set @startdate = cast('11 Jan 2009' as datetime) -- The startdate
set @days = 5 -- The number of consecutive days

SELECT userid
      ,count(1) as [Number of Consecutive Days]
FROM UserHistory
WHERE creationdate >= @startdate
AND creationdate < dateadd(dd, @days, cast(convert(char(11), @startdate, 113)  as datetime))
GROUP BY userid
HAVING count(1) >= @days

Twierdzenie cast(convert(char(11), @startdate, 113) as datetime) usuwa część godziny z daty, więc zaczynamy o północy.

Zakładam również, że creationdateiuserid kolumny są indeksowane.

Właśnie zdałem sobie sprawę, że to nie powie Ci wszystkich użytkowników i ich łącznej liczby kolejnych dni. Ale powie Ci, którzy użytkownicy będą odwiedzać określoną liczbę dni od wybranej przez Ciebie daty.

Zmienione rozwiązanie:

declare @days as int
set @days = 30
select t1.userid
from UserHistory t1
where (select count(1) 
       from UserHistory t3 
       where t3.userid = t1.userid
       and t3.creationdate >= DATEADD(dd, DATEDIFF(dd, 0, t1.creationdate), 0) 
       and t3.creationdate < DATEADD(dd, DATEDIFF(dd, 0, t1.creationdate) + @days, 0) 
       group by t3.userid
) >= @days
group by t1.userid

Sprawdziłem to i zapyta o wszystkich użytkowników i wszystkie daty. Opiera się na pierwszym (żartowym) rozwiązaniu Spencera , ale moje działa.

Aktualizacja: poprawiono obsługę dat w drugim rozwiązaniu.

Powinno to zrobić, co chcesz, ale nie mam wystarczających danych, aby przetestować wydajność. Zagmatwana funkcja CONVERT / FLOOR polega na usunięciu części czasu z pola daty i godziny. Jeśli używasz SQL Server 2008, możesz użyć CAST (x.CreationDate AS DATE).

ZADEKLAROWAĆ @Range jako INT
SET @Range = 10

SELECT DISTINCT UserId, CONVERT (DATETIME, FLOOR (CONVERT (FLOAT, a.CreationDate)))
  FROM tblUserLogin a
GDZIE ISTNIEJE
   (WYBIERZ 1 
      FROM tblUserLogin b 
     GDZIE a.userId = b.userId 
       AND (SELECT COUNT (DISTINCT (CONVERT (DATETIME, FLOOR (CONVERT (FLOAT, CreationDate))))) 
              FROM tblUserLogin c 
             GDZIE c.userid = b.userid 
               AND CONVERT (DATETIME, FLOOR (CONVERT (FLOAT, c.CreationDate))) BETWEEN CONVERT (DATETIME, FLOOR (CONVERT (FLOAT, a.CreationDate))) i CONVERT (DATETIME, FLOOR (CONVERT (FLOAT, a.CreationDate)) ) + @ Zakres-1) = @ Zakres)

Skrypt tworzenia

UTWÓRZ TABELĘ [dbo]. [TblUserLogin] (
    [Id] [int] TOŻSAMOŚĆ (1,1) NOT NULL,
    [UserId] [int] NULL,
    [CreationDate] [datetime] NULL
) ON [PODSTAWOWY]

Spencer prawie to zrobił, ale powinien to być działający kod:

SELECT DISTINCT UserId
FROM History h1
WHERE (
    SELECT COUNT(*) 
    FROM History
    WHERE UserId = h1.UserId AND CreationDate BETWEEN h1.CreationDate AND DATEADD(d, @n-1, h1.CreationDate)
) >= @n

Nie mogę się doczekać, MySQLish:

SELECT start.UserId
FROM UserHistory AS start
  LEFT OUTER JOIN UserHistory AS pre_start ON pre_start.UserId=start.UserId
    AND DATE(pre_start.CreationDate)=DATE_SUB(DATE(start.CreationDate), INTERVAL 1 DAY)
  LEFT OUTER JOIN UserHistory AS subsequent ON subsequent.UserId=start.UserId
    AND DATE(subsequent.CreationDate)<=DATE_ADD(DATE(start.CreationDate), INTERVAL 30 DAY)
WHERE pre_start.Id IS NULL
GROUP BY start.Id
HAVING COUNT(subsequent.Id)=30

Nie przetestowane i prawie na pewno wymaga konwersji dla MSSQL, ale myślę, że to daje kilka pomysłów.

A co powiesz na używanie tabel Tally? Opiera się na bardziej algorytmicznym podejściu, a plan wykonania jest bardzo prosty. Wypełnij tabelę tallyTable liczbami od 1 do „MaxDaysBehind”, które chcesz przeskanować w tabeli (tj. 90 będzie szukać za 3 miesiące do tyłu itp.).

declare @ContinousDays int
set @ContinousDays = 30  -- select those that have 30 consecutive days

create table #tallyTable (Tally int)
insert into #tallyTable values (1)
...
insert into #tallyTable values (90) -- insert numbers for as many days behind as you want to scan

select [UserId],count(*),t.Tally from HistoryTable 
join #tallyTable as t on t.Tally>0
where [CreationDate]> getdate()-@ContinousDays-t.Tally and 
      [CreationDate]<getdate()-t.Tally 
group by [UserId],t.Tally 
having count(*)>=@ContinousDays

delete #tallyTable

Trochę poprawiam zapytanie Billa. Być może trzeba będzie skrócić datę przed grupowaniem, aby liczyć tylko jedno logowanie dziennie ...

SELECT UserId from History 
WHERE CreationDate > ( now() - n )
GROUP BY UserId, 
DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) AS TruncatedCreationDate  
HAVING COUNT(TruncatedCreationDate) >= n

EDITED, aby użyć DATEADD (dd, DATEDIFF (dd, 0, CreationDate), 0) zamiast konwersji (char (10), CreationDate, 101).

@IDisposable Chciałem użyć datepart wcześniej, ale byłem zbyt leniwy, aby sprawdzić składnię, więc pomyślałem, że zamiast tego użyłem identyfikatora Convert. Nie wiem, że miało to znaczący wpływ. Dzięki! teraz wiem.

zakładając schemat, który wygląda następująco:

create table dba.visits
(
    id  integer not null,
    user_id integer not null,
    creation_date date not null
);

spowoduje to wyodrębnienie ciągłych zakresów z sekwencji dat z lukami.

select l.creation_date  as start_d, -- Get first date in contiguous range
    (
        select min(a.creation_date ) as creation_date 
        from "DBA"."visits" a 
            left outer join "DBA"."visits" b on 
                   a.creation_date = dateadd(day, -1, b.creation_date ) and 
                   a.user_id  = b.user_id 
            where b.creation_date  is null and
                  a.creation_date  >= l.creation_date  and
                  a.user_id  = l.user_id 
    ) as end_d -- Get last date in contiguous range
from  "DBA"."visits" l
    left outer join "DBA"."visits" r on 
        r.creation_date  = dateadd(day, -1, l.creation_date ) and 
        r.user_id  = l.user_id 
    where r.creation_date  is null