SQL para determinar los días de acceso secuencial mínimos
sql-server date (19)
La siguiente tabla de historial de usuarios contiene un registro por cada día que un usuario determinado ha accedido a un sitio web (en un período de 24 horas UTC). Tiene muchos miles de registros, pero solo un registro por día por usuario. Si el usuario no ha accedido al sitio web ese día, no se generará ningún registro.
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
Lo que estoy buscando es una consulta SQL en esta tabla con buen rendimiento , que me diga qué usuarios han accedido al sitio web durante (n) días continuos sin perder un día.
En otras palabras, ¿cuántos usuarios tienen (n) registros en esta tabla con fechas secuenciales (día anterior, o día posterior) ? Si falta un día en la secuencia, la secuencia se rompe y debe reiniciarse nuevamente en 1; estamos buscando usuarios que hayan logrado un número continuo de días aquí sin interrupciones.
Cualquier parecido entre esta consulta y una insignia particular de Stack Overflow es pura coincidencia, por supuesto ... :)
¿Algo como esto?
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
¿Qué tal (y asegúrese de que la declaración anterior terminó con un punto y coma):
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
La idea es que si tenemos una lista de los días (como un número) y un row_number, los días perdidos hacen que el desplazamiento entre estas dos listas sea ligeramente mayor. Así que estamos buscando un rango que tenga una compensación constante.
Puede usar "ORDER BY NumConsecutiveDays DESC" al final de este, o decir "TENER count (*)> 14" para un umbral ...
No he probado esto sin embargo, solo escribiéndolo en la cima de mi cabeza. Esperemos que funcione en SQL2005 y en adelante.
... y sería muy útil con un índice en tablename (UserID, CreationDate)
Editado: se desactiva Offset es una palabra reservada, así que usé TheOffset en su lugar.
Editado: la sugerencia de usar COUNT (*) es muy válida. Debo haber hecho eso en primer lugar, pero realmente no estaba pensando. Anteriormente usaba datediff (día, mínimo (CreationDate), max (CreationDate)) en su lugar.
Robar
¿Qué tal si usamos las tablas de Tally? Sigue un enfoque más algorítmico, y el plan de ejecución es muy sencillo. Llene la tabla Tally con los números del 1 al ''MaxDaysBehind'' que desea escanear la tabla (es decir, 90 buscará 3 meses atrás, etc.).
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
Ajustando la consulta de Bill un poco. Es posible que deba truncar la fecha antes de agrupar para contar solo un inicio de sesión por día ...
SELECT UserId from History
WHERE CreationDate > ( now() - n )
GROUP BY UserId,
DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) AS TruncatedCreationDate
HAVING COUNT(TruncatedCreationDate) >= n
EDITADO para usar DATEADD (dd, DATEDIFF (dd, 0, CreationDate), 0) en lugar de convert (char (10), CreationDate, 101).
@IDisposable Estaba buscando usar datepart antes, pero era demasiado flojo para buscar la sintaxis, así que pensé que id usar convertir en su lugar. No sé que tuvo un impacto significativo ¡Gracias! ahora sé.
Algunos SQL muy expresivos a lo largo de las líneas de:
select
userId,
dbo.MaxConsecutiveDates(CreationDate) as blah
from
dbo.Logins
group by
userId
Suponiendo que tiene una función agregada definida por el usuario, algo en la línea de (tenga cuidado con esto es defectuoso):
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);
}
}
}
Esto debería hacer lo que quiera, pero no tengo suficientes datos para probar la eficiencia. El complicado elemento CONVERT / FLOOR es quitar la porción de tiempo del campo de fecha y hora. Si está utilizando SQL Server 2008, entonces podría usar CAST (x.CreationDate AS DATE).
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)
Script de creación
CREATE TABLE [dbo].[tblUserLogin]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NULL, [CreationDate] [datetime] NULL ) ON [PRIMARY]
Fuera de mi cabeza, 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
No probado, y casi seguro necesita alguna conversión para MSSQL, pero creo que da algunas ideas.
Hacer esto con una sola consulta SQL me parece demasiado complicado. Permítanme dividir esta respuesta en dos partes.
- Lo que debería haber hecho hasta ahora y debería comenzar a hacerlo ahora:
Ejecute un trabajo cron diario que compruebe si hay usuarios conectados o no, y luego incremente un contador si lo tiene o lo configura en 0 si no lo ha hecho. - Qué debes hacer ahora:
- Exportar esta tabla a un servidor que no ejecuta su sitio web y no será necesario por un tiempo. ;)
- Ordenarlo por usuario, luego fecha.
- recorrerlo secuencialmente, mantener un contador ...
Joe Celko tiene un capítulo completo sobre esto en SQL para Smarties (llamándolo Runs and Sequences). No tengo ese libro en casa, así que cuando llegue al trabajo ... En realidad responderé esto. (suponiendo que la tabla de historial se llame dbo.UserHistory y la cantidad de días sea @Days)
Otra ventaja es del blog del Equipo SQL en ejecuciones
La otra idea que he tenido, pero no tengo un servidor SQL a mano para trabajar aquí, es usar un CTE con un ROW_NUMBER dividido como este:
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
Lo anterior es probablemente MUCHO MÁS DIFÍCIL de lo que debe ser, pero queda como un cosquilleo para el cerebro cuando tienes alguna otra definición de "carrera" que solo las fechas.
La respuesta es obviamente:
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
EDITAR:
Bien, aquí está mi respuesta seria:
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
EDITAR:
[Jeff Atwood] Esta es una gran solución rápida y merece ser aceptada, pero la solución de Rob Farley también es excelente y posiblemente aún más rápida (!). Por favor, échale un vistazo también!
Parece que podría aprovechar el hecho de que para ser continuo durante n días, sería necesario que haya n filas.
Entonces algo así como:
SELECT users.UserId, count(1) as cnt
FROM users
WHERE users.CreationDate > now() - INTERVAL 30 DAY
GROUP BY UserId
HAVING cnt = 30
Puede usar un CTE recursivo (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
Si esto es tan importante para usted, obtenga este evento y maneje una tabla para darle esta información. No es necesario matar la máquina con todas esas locas consultas.
Si puede cambiar el esquema de la tabla, le sugiero que agregue una columna LongestStreak a la tabla que establecería como el número de días consecutivos que terminan en CreationDate . Es fácil actualizar la tabla en el momento del inicio de sesión (similar a lo que ya está haciendo, si no existen filas del día actual, comprobará si existe alguna fila para el día anterior. Si es verdadero, LongestStreak el LongestStreak en la nueva fila; de lo contrario, la establecerá en 1.)
La consulta será obvia después de agregar esta columna:
if exists(select * from table
where LongestStreak >= 30 and UserId = @UserId)
-- award the Woot badge.
Spencer casi lo hace, pero este debería ser el código de trabajo:
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
Un par de opciones de SQL Server 2012 (suponiendo que N = 100 a continuación).
;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
Aunque con mis datos de muestra, los siguientes resultaron más eficientes
;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
Ambos dependen de la restricción establecida en la pregunta de que hay como máximo un registro por día por usuario.
Usé una propiedad matemática simple para identificar quién accedió al sitio de forma consecutiva. Esta propiedad es que debe tener la diferencia de días entre el primer acceso y la última vez igual al número de registros en su registro de la tabla de acceso.
Aquí están los scripts SQL que probé en Oracle DB (también deberían funcionar en otros 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;
Script de preparación de tabla:
-- 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);
asumiendo un esquema que sea como sigue:
create table dba.visits
(
id integer not null,
user_id integer not null,
creation_date date not null
);
esto extraerá rangos contiguos de una secuencia de fechas con espacios.
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
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
El cast(convert(char(11), @startdate, 113) as datetime) declaración cast(convert(char(11), @startdate, 113) as datetime) elimina la parte de tiempo de la fecha, así que comenzamos a la medianoche.
También supondría que las columnas creationdate y userid están indexadas.
Me acabo de dar cuenta de que esto no le dirá a todos los usuarios y sus días consecutivos totales. Pero le indicará qué usuarios habrán estado visitando un número determinado de días a partir de la fecha que usted elija.
Solución revisada:
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
Lo he comprobado y consultará todos los usuarios y todas las fechas. Se basa en la primera solución (¿chiste?) De Spencer , pero la mía funciona.
Actualización: mejoró el manejo de la fecha en la segunda solución.