SQL, um minimale sequentielle Zugriffstage zu bestimmen?

Wie über (und bitte stellen Sie sicher, dass die Vorherige Anweisung endet mit einem Semikolon):

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  

Die Idee ist, dass, wenn wir haben die Liste mit den Tagen (als Zahl), und ein row_number, dann verpasste Tage machen den Versatz zwischen diesen beiden Listen etwas größer. So sind wir auf der Suche für einen Bereich mit einem gleichmäßigen Aufmaß.

Könnten Sie "ORDER BY NumConsecutiveDays DESC" am Ende dieses, oder sagen Sie "count(*) > 14" für einen Schwellenwert...

Ich noch nicht getestet, aber einfach nur zu schreiben es aus der Spitze von meinem Kopf. Hoffentlich funktioniert in SQL2005 und auf.

...und wäre sehr geholfen, indem Sie einen index auf tablename(Benutzer-id, ERSTELLUNGSDATUM)

Bearbeitet werden: Stellt sich heraus, Offset ist ein reserviertes Wort, so habe ich TheOffset statt.

Bearbeitet werden: Der Vorschlag, verwenden Sie COUNT(*) ist sehr gültig - sollte ich das getan haben, in Erster Linie aber war nicht wirklich zu denken. Zuvor war es mit datediff(day, min(CreationDate), max(CreationDate)) statt.

Rob

InformationsquelleAutor der Antwort Rob Farley

Einige schön ausdrucksstark SQL entlang der Linien von:

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

Vorausgesetzt, Sie haben eine benutzerdefinierte Aggregatfunktion etwas entlang der Linien von (Achtung dies ist 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);
        }
    }
}

InformationsquelleAutor der Antwort Joshuamck

Tun dies mit einer einzigen SQL-Abfrage scheint zu kompliziert für mich. Lassen Sie mich brechen diese Antwort nach unten in zwei Teile.

1. , Was Sie getan haben sollte, bis jetzt, und beginnen sollte das jetzt tun:
   
   Führen Sie einen täglichen cron-job, der überprüft, ob jeder Benutzer, ob er hat sich heute eingeloggt und dann erhöht einen Zähler, wenn er oder setzt ihn auf 0, wenn er noch nicht.

2. Was Sie jetzt tun sollten:
   
   - Exportieren Sie diese Tabelle auf einem server, der nicht läuft, Ihre website und wird nicht mehr benötigt für eine Weile. 😉
   
   - Sortieren nach Benutzer, dann Datum.
   
   - durch Sie gehen nacheinander, halten Sie einen Zähler...

InformationsquelleAutor der Antwort Kim Stebel

Könnte man eine rekursive 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

InformationsquelleAutor der Antwort OMG Ponies

Ein paar SQL Server 2012 Optionen (vorausgesetzt, N=100 unten).

;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 

Aber mit meinem Beispiel-Daten im folgenden herausgearbeitet effizienter

;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

Beide berufen sich auf die Einschränkung erwähnt in der Frage, dass es bei den meisten ein Datensatz pro Tag pro Benutzer.

InformationsquelleAutor der Antwort Martin Smith

Habe ich eine einfache mathematische Eigenschaft zu identifizieren, die nacheinander auf die Website zugreifen. Diese Eigenschaft ist, dass Sie den Tag Unterschied zwischen dem ersten Zugriff und dem letzten mal gleich der Anzahl der Datensätze in der access-Tabelle anmelden.

Hier sind SQL-Skript, das ich getestet habe in Oracle DB (sollte es in anderen 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;

Tabelle prep-Skript:

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

InformationsquelleAutor der Antwort Dilshod Tadjibaev

Diese sollte das tun, was Sie wollen, aber ich habe nicht genug Daten zum testen der Effizienz. Die gewundenen KONVERTIEREN/FLOOR-Sachen ist, Streifen Sie die Zeit Teil aus dem datetime-Feld. Wenn Sie SQL Server 2008 verwenden, dann könnten Sie verwenden Sie CAST(x.ERSTELLUNGSDATUM (DATUM).

DECLARE @Range als INT 
SET @Range = 10 

SELECT DISTINCT UserId, CONVERT(DATETIME, BODEN(CONVERT(FLOAT, ein.CreationDate -))) 
 VON tblUserLogin ein 
WO EXISTIERT 
 (WÄHLEN Sie 1 
 VON tblUserLogin b 
 WO eine.userId = b ist.userId 
 UND (SELECT COUNT(DISTINCT(CONVERT(DATETIME, BODEN(CONVERT(FLOAT, CreationDate))))) 
 VON tblUserLogin c 
 WO c ist.userid = b ist.userid 
 UND CONVERT(DATETIME, BODEN(CONVERT(FLOAT, c.CreationDate))) ZWISCHEN CONVERT(DATETIME, BODEN(CONVERT(FLOAT, ein.CreationDate -))) und CONVERT(DATETIME, BODEN(CONVERT(FLOAT, ein.CreationDate)))+@Palette-1) = @(Nahkampf) 

Erstellungsskript

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

InformationsquelleAutor der Antwort Dave Barker

Aus der Spitze von meinem Kopf, 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

Ungetestet, und fast braucht sicherlich einige Umstellung für MSSQL, aber ich denke, dass einige Ideen.

InformationsquelleAutor der Antwort Cebjyre

Tweaking-Bill-Abfrage ein bisschen. Haben Sie vielleicht abschneiden, das Datum vor der Gruppierung zu zählen, nur eine Anmeldung pro Tag...

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

BEARBEITET zu verwenden, DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) anstelle von convert( char(10) , CreationDate, 101 ).

@IDisposable
Ich war auf der Suche zu verwenden, datepart früher, aber ich war zu faul zum nachschlagen der syntax, so dass ich dachte, ich d verwenden, konvertieren statt. Ich kannte es hatte einen signifikanten Einfluss Dank! jetzt weiß ich,.

InformationsquelleAutor der Antwort Jaskirat