SQL pour déterminer le nombre minimum de jours d'accès séquentiels ?

Question

Le tableau suivant de l'historique de l'utilisateur contient un enregistrement pour chaque jour où un utilisateur donné a accédé à un site web (dans une période de 24 heures UTC). Il contient plusieurs milliers d'enregistrements, mais seulement un enregistrement par jour et par utilisateur. Si l'utilisateur n'a pas accédé au site web ce jour-là, aucun enregistrement ne sera généré.

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

Ce que je recherche, c'est une requête SQL sur cette table avec de bonnes performances qui m'indique quels sont les utilisateurs qui ont accédé au site web pendant (n) jours consécutifs sans en manquer un seul.

En d'autres termes, combien d'utilisateurs ont (n) enregistrements dans cette table avec des dates séquentielles (jour avant ou jour après). ? S'il manque un jour dans la séquence, celle-ci est interrompue et doit recommencer à 1. Nous recherchons des utilisateurs qui ont atteint un nombre continu de jours sans interruption.

Toute ressemblance entre cette requête et un badge Stack Overflow particulier est une pure coïncidence, bien sûr :)

Answer 1

Que diriez-vous de (et assurez-vous que la déclaration précédente se termine par un point-virgule) :

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  

L'idée est que si nous avons une liste de jours (sous forme de nombre) et un numéro de ligne, les jours manqués augmentent légèrement le décalage entre ces deux listes. Nous recherchons donc un intervalle qui présente un décalage cohérent.

Vous pourriez utiliser "ORDER BY NumConsecutiveDays DESC" à la fin, ou dire "HAVING count(*) > 14" pour un seuil...

Je n'ai pas testé cela, mais je l'ai écrit de tête. J'espère que cela fonctionnera avec SQL2005 et les versions ultérieures.

...et serait très aidé par un index sur tablename(UserID, CreationDate)

Édité : Il s'avère que Offset est un mot réservé, j'ai donc utilisé TheOffset à la place.

Édité : La suggestion d'utiliser COUNT(*) est très valable - j'aurais dû le faire en premier lieu mais je n'y ai pas vraiment pensé. Auparavant, on utilisait datediff(day, min(CreationDate), max(CreationDate)) à la place.

Rob

Answer 2

La réponse est évidente :

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 :

Voici ma réponse sérieuse :

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] Il s'agit d'une excellente solution rapide qui mérite d'être acceptée. La solution de Rob Farley est également excellente et sans doute encore plus rapide ( !). Jetez-y un coup d'œil aussi !

Answer 3

Si vous pouvez modifier le schéma de la table, je vous suggère d'ajouter une colonne LongestStreak dans le tableau que vous avez défini comme étant le nombre de jours séquentiels se terminant par le CreationDate . Il est facile de mettre à jour la table au moment de la connexion (comme vous le faites déjà, si aucune ligne n'existe pour le jour en cours, vous vérifierez s'il existe une ligne pour le jour précédent. Si c'est le cas, vous incrémenterez le champ LongestStreak dans la nouvelle ligne, sinon vous lui donnerez la valeur 1).

La requête sera évidente après l'ajout de cette colonne :

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

Answer 4

Quelques SQL joliment expressifs, du genre

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

En supposant que vous disposiez d'un fonction agrégée définie par l'utilisateur quelque chose du genre (attention, ceci est bogué) :

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

Answer 5

Il semble que l'on puisse tirer parti du fait que pour être continu sur n jours, il faut qu'il y ait n lignes.

Donc quelque chose comme :

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