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

A może (i upewnij się, że poprzednia wypowiedź zakończyła 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 numer wiersza, to pominięte dni sprawiają, że przesunięcie między tymi dwoma listami jest nieco większe. Więc szukamy zakresu, który ma stałe przesunięcie.

Możesz użyć "ORDER BY NumConsecutiveDays DESC" na końcu tego, lub powiedzieć "HAVING count(*) > 14" dla progu...

...i byłby bardzo pomocny indeks na tablename (UserID, CreationDate)

Edited: okazało się, że Offset jest słowem zastrzeżonym, więc użyłem TheOffset.

Edytowany: 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 używał datediff(day, min(CreationDate), max (CreationDate)).

Rob

Jeśli możesz zmienić schemat tabeli, sugeruję dodanie kolumny LongestStreak do tabeli, którą ustawisz na liczbę kolejnych dni kończących się CreationDate. Łatwo jest zaktualizować tabelę w czasie logowania (podobnie jak to, co już robisz, jeśli nie ma wierszy z bieżącego dnia, sprawdzisz, czy żaden wiersz nie istnieje z poprzedniego dnia. Jeśli to prawda, zwiększysz LongestStreak w nowym wierszu, 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.

Jakiś ładnie ekspresyjny SQL wzdłuż linii:

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

Zakładając, że masz user defined aggregate function coś w stylu (uwaga, to jest buggy):

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żna skorzystać z faktu, że aby być ciągłym przez n dni, wymagałoby to n wierszy.

Więc coś w stylu:

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

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

1. Co powinieneś zrobić do tej pory i powinieneś zacząć robić teraz:
   Uruchom codzienne zadanie cron, które sprawdza każdego użytkownika, który zalogował się dzisiaj, a następnie zwiększa licznik, jeśli ma lub ustawia go na 0, jeśli nie ma.
2. Co powinieneś teraz zrobić:
   - Eksportuj tę tabelę na serwer, który nie uruchamia Twojej witryny i nie będzie potrzebny przez jakiś czas. ;)
   - Sortuj według użytkownika, a następnie daty.
   - przejrzyj je kolejno, zachowaj licznik...

Jeśli jest to dla ciebie tak ważne, wywołaj to zdarzenie i prowadź tabelę, aby podać ci te informacje. Nie musisz zabijać maszyny tymi wszystkimi szalonymi pytaniami.

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 dla Smarties (nazywając go działa i sekwencje). Nie mam tej książki w domu, więc kiedy wrócę do pracy... Odpowiem na to. (zakładając, że tabela historii nazywa się dbo.UserHistory i liczba dni to @Days)

Kolejny trop pochodzi z bloga SQL Team na temat runów

Innym pomysłem, który miałem, ale nie mam serwera SQL pod ręką do pracy Tutaj jest użycie CTE z partycjonowanym numerem wiersza jak to:

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

The powyżej jest prawdopodobnie o wiele trudniejsze niż musi być, ale pozostawione jako łaskotanie mózgu, gdy masz inną definicję "biegu" niż tylko daty.

Kilka opcji SQL Server 2012 (przy założeniu 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 wyniki okazały się bardziej efektywne

;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 istnieje co najwyżej jeden rekord dziennie na użytkownika.

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 zidentyfikować, kto kolejno uzyskiwał dostęp do witryny. Ta właściwość polega na tym, że różnica dnia między pierwszym i ostatnim czasem dostępu jest równa liczbie rekordów w dzienniku tabeli dostępu.

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

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

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

Zakładam również, że kolumny creationdate i userid są indeksowane.

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

Poprawione 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. Jest on oparty na Spencer ' s 1st (joke?) rozwiązanie , ale moje działa.

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

To powinno robić, co chcesz, ale nie mam wystarczająco dużo danych, aby sprawdzić wydajność. Convoluted CONVERT / FLOOR stuff ma na celu usunięcie części czasu z pola datetime. Jeśli używasz SQL Server 2008, możesz użyć CAST (X. CreationDate jako data).

DECLARE @Range as INT
SET @Range = 10

SELECT DISTINCT UserId, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, a.CreationDate)))
  FROM tblUserLogin a
WHERE EXISTS
   (SELECT 1 
      FROM tblUserLogin b 
     WHERE a.userId = b.userId 
       AND (SELECT COUNT(DISTINCT(CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreationDate))))) 
              FROM tblUserLogin c 
             WHERE c.userid = b.userid 
               AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, c.CreationDate))) BETWEEN CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, a.CreationDate))) and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, a.CreationDate)))+@Range-1) = @Range)

Skrypt tworzenia

CREATE TABLE [dbo].[tblUserLogin](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL,
    [CreationDate] [datetime] NULL
) ON [PRIMARY]

Spencer prawie to zrobił, ale to powinien 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

Off the top of my head, 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

Untested, i prawie na pewno potrzebuje trochę konwersji dla MSSQL, ale myślę, że daje to kilka pomysłów.

Co powiesz na użycie tabel Tally? Podąża za bardziej algorytmicznym podejściem, a plan realizacji jest bardzo prosty. Wypełnij tallyTable liczbami od 1 do "MaxDaysBehind", które chcesz zeskanować tabelę (tj. 90 będzie szukać przez 3 miesiące z 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()[email protected] and 
      [CreationDate]<getdate()-t.Tally 
group by [UserId],t.Tally 
having count(*)>=@ContinousDays

delete #tallyTable

Trochę poprawiłem zapytanie Billa. Być może trzeba będzie obciąć datę przed zgrupowaniem, aby liczyć tylko jeden login 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

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

Zakładając, że schemat wygląda tak:

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

To wyodrębni sąsiadujące zakresy 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