SQL для определения минимальных последовательных дней доступа?
Следующая таблица истории пользователей содержит одну запись за каждый день, когда данный пользователь заходил на веб-сайт (в течение 24 часов по UTC). У него много тысяч записей, но только одна запись в день на пользователя. Если пользователь не заходил на веб-сайт в этот день, запись не будет создаваться.
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
Я ищу SQL-запрос к этой таблице с хорошей производительностью, который сообщает мне, какие идентификаторы обращались к сайту в течение (n) дней подряд, не пропуская ни одного дня.
Другими словами, сколько пользователей имеет (n) записей в этой таблице с последовательными (днем или днем) датами? Если в последовательности отсутствует какой-либо день, последовательность прерывается и должна перезапуститься снова с 1; мы ищем пользователей, которые добились здесь непрерывного количества дней без пропусков.
Любое сходство между этим запросом и конкретным значком переполнения стека, конечно, чисто случайно...:)
19 ответов
Ответ очевиден:
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
РЕДАКТИРОВАТЬ:
Хорошо, вот мой серьезный ответ:
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
РЕДАКТИРОВАТЬ:
[Джефф Этвуд] Это отличное быстрое решение, и оно заслуживает принятия, но решение Роба Фарли также превосходно и, возможно, даже быстрее (!). Пожалуйста, проверьте это тоже!
Как насчет (и, пожалуйста, убедитесь, что предыдущий оператор завершился точкой с запятой):
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
Идея состоит в том, что если у нас есть список дней (как число) и row_number, то пропущенные дни делают смещение между этими двумя списками немного больше. Поэтому мы ищем диапазон, который имеет постоянное смещение.
Вы можете использовать "ORDER BY NumConsecutiveDays DESC" в конце этого или сказать "HAVING count(*) > 14" для порога...
Хотя я не проверял это - просто списал это с моей головы. Надеюсь, работает в SQL2005 и далее.
... и очень помог бы индекс по имени таблицы (UserID, CreationDate)
Отредактировано: Оказывается, Offset - зарезервированное слово, поэтому я использовал вместо него TheOffset.
Отредактировано: предложение использовать COUNT(*) очень правильно - я должен был сделать это в первую очередь, но на самом деле не думал. Раньше вместо этого он использовал datediff (день, мин (CreationDate), макс (CreationDate)).
обкрадывать
Если вы можете изменить схему таблицы, я бы предложил добавить столбец LongestStreak к таблице, которую вы установите на количество последовательных дней, заканчивающихся на CreationDate, Легко обновить таблицу во время входа в систему (аналогично тому, что вы уже делаете, если в текущем дне не существует ни одной строки, вы проверите, существует ли какая-либо строка за предыдущий день. Если true, вы увеличите LongestStreak в новом ряду, иначе вы установите его на 1.)
Запрос будет очевиден после добавления этого столбца:
if exists(select * from table
where LongestStreak >= 30 and UserId = @UserId)
-- award the Woot badge.
Некоторые красиво выразительные SQL по типу:
select
userId,
dbo.MaxConsecutiveDates(CreationDate) as blah
from
dbo.Logins
group by
userId
Предполагая, что у вас есть определенная пользователем агрегатная функция что-то вроде (будьте осторожны, это глючит):
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);
}
}
}
Похоже, вы могли бы воспользоваться тем фактом, что для непрерывности в течение n дней потребуется n строк.
Так что-то вроде:
SELECT users.UserId, count(1) as cnt
FROM users
WHERE users.CreationDate > now() - INTERVAL 30 DAY
GROUP BY UserId
HAVING cnt = 30
Выполнение этого с помощью одного SQL-запроса кажется мне слишком сложным. Позвольте мне разбить этот ответ на две части.
- Что вы должны были сделать до сих пор и должны начать делать сейчас:
Запускайте ежедневное задание cron, которое проверяет каждого пользователя, вошел ли он в систему сегодня, а затем увеличивает счетчик, если он имеет, или устанавливает его на 0, если его нет. - Что вы должны сделать сейчас:
- Экспортируйте эту таблицу на сервер, который не запускает ваш сайт и некоторое время не понадобится.;)
- Сортировать по пользователю, а затем по дате.
- пройти через это последовательно, держать счетчик...
Несколько вариантов SQL Server 2012 (при условии N=100 ниже).
;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
Хотя, с моими примерами, следующее сработало более эффективно
;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
Оба полагаются на ограничение, указанное в вопросе, что на пользователя приходится не более одной записи в день.
Вы можете использовать рекурсивный 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
У Джо Селко есть полная глава по этому вопросу в SQL for Smarties (называемая "прогоны и последовательности"). У меня нет этой книги дома, поэтому, когда я доберусь до работы... Я действительно отвечу на это. (при условии, что таблица истории называется dbo.UserHistory, а количество дней - @Days)
Еще одно упоминание в блоге команды SQL по пробежкам
Другая идея, которая у меня была, но у меня нет SQL-сервера, удобного для работы здесь, заключается в использовании CTE с разделенным ROW_NUMBER следующим образом:
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
Вышеприведенное, вероятно, НАМНОГО труднее, чем должно быть, но оставлено как мозговая щекотка, когда у вас есть какое-то другое определение "пробега", а не просто даты.
Если это так важно для вас, напишите об этом событии и составьте таблицу, чтобы дать вам эту информацию. Не нужно убивать машину со всеми этими безумными вопросами.
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
Заявление cast(convert(char(11), @startdate, 113) as datetime) удаляет временную часть даты, поэтому мы начинаем в полночь.
Я бы также предположил, что creationdate а также userid столбцы индексируются.
Я только что понял, что это не скажет вам всех пользователей и их общее количество дней подряд. Но скажет вам, какие пользователи будут посещать определенное количество дней с даты по вашему выбору.
Пересмотренное решение:
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
Я проверил это, и он будет запрашивать всех пользователей и все даты. Он основан на первом (шутка?) Решении Спенсера, но мой работает.
Обновление: улучшена обработка даты во втором решении.
Что-то вроде этого?
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
Я использовал простое математическое свойство, чтобы определить, кто последовательно заходил на сайт. Это свойство заключается в том, что дневная разница между первым и последним доступом должна быть равна числу записей в журнале таблицы доступа.
Вот SQL-скрипт, который я тестировал в БД Oracle (он должен работать и в других БД):
-- 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;
Скрипт подготовки таблицы:
-- 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);
Это должно делать то, что вы хотите, но у меня недостаточно данных для проверки эффективности. Запутанный материал CONVERT/FLOOR состоит в удалении части времени из поля даты и времени. Если вы используете SQL Server 2008, вы можете использовать CAST(x.CreationDate AS DATE).
ОБЪЯВИТЬ @Range как INT
SET @Range = 10
ВЫБЕРИТЕ РАЗЛИЧНЫЙ ИДЕНТИФИКАТОР, КОНВЕРТ (DATETIME, FLOOR(КОНВЕРТ (FLOAT, a.CreationDate))))
ОТ ТБлУзера
ГДЕ СУЩЕСТВУЕТ
(ВЫБЕРИТЕ 1
ОТ tblUserLogin b
ГДЕ a.userId = b.userId
И (ВЫБЕРИТЕ СЧЕТЧИК (DISTINCT(CONVERT(DATETIME, FLOOR) (CONVERT (FLOAT, CreationDate)))))
ОТ tblUserLogin c
ГДЕ c.userid = b.userid
AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, c.CreationDate))) между CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, a.CreationDate))) и CONVERT (DATETIME, FLOOR(CONVERT (FLOAT, a)))+@Range-1) = @Range)
Сценарий создания
CREATE TABLE [dbo].[TblUserLogin](
[Id] [int] IDENTITY(1,1) НЕ NULL,
[UserId] [int] NULL,
[CreationDate] [datetime] NULL) НА [ПЕРВИЧНО]
При условии, что схема выглядит следующим образом:
create table dba.visits
(
id integer not null,
user_id integer not null,
creation_date date not null
);
это извлечет смежные диапазоны из последовательности дат с пробелами.
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
Спенсер почти сделал это, но это должен быть рабочий код:
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
С моей головы, 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
Непроверенный и почти наверняка нуждается в некотором преобразовании для MSSQL, но я думаю, что это дает некоторые идеи.
Немного подправил запрос Билла. Возможно, вам придется усечь дату перед группировкой, чтобы считать только один логин в день...
SELECT UserId from History
WHERE CreationDate > ( now() - n )
GROUP BY UserId,
DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) AS TruncatedCreationDate
HAVING COUNT(TruncatedCreationDate) >= n
ИЗМЕНЕНО для использования DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) вместо convert (char (10), CreationDate, 101).
@IDisposable Я искал использовать datepart ранее, но мне было лень искать синтаксис, поэтому я решил использовать id вместо convert. Я не знаю, это оказало значительное влияние. Спасибо! теперь я знаю.
Как насчет использования таблиц Tally? Это следует за более алгоритмическим подходом, и план выполнения - бриз. Заполните tallyTable числами от 1 до MaxDaysBehind, которые вы хотите отсканировать в таблице (т. Е. 90 будет ждать 3 месяца и т. Д.).
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