SQL для определения минимальных последовательных дней доступа?

при условии, что схема выглядит примерно так:

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 UserId from History 
WHERE CreationDate > ( now() - n )
GROUP BY UserId, 
DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) AS TruncatedCreationDate  
HAVING COUNT(TruncatedCreationDate) >= n

EDITED, чтобы использовать DATEADD (dd, DATEDIFF (dd, 0, CreationDate), 0) вместо convert (char (10), CreationDate , 101).

@IDisposable Раньше я хотел использовать datepart, но мне было лень искать синтаксис, поэтому я решил, что вместо этого id использует convert. Я не знаю, что это оказало значительное влияние. Спасибо! теперь я знаю.

Вне всяких сомнений, 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, но я думаю, что это дает некоторые идеи.

Как насчет того, чтобы использовать таблицы 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

Это должно делать то, что вы хотите, но у меня недостаточно данных для проверки эффективности. Запутанный материал CONVERT / FLOOR состоит в том, чтобы убрать временную часть из поля datetime. Если вы используете SQL Server 2008, вы можете использовать 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)

Сценарий создания

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

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

Я проверил это, и он будет запрашивать всех пользователей и все даты. Он основан на 1-м (шутка?) Решении Спенсера , но мой работает.

Обновление: улучшена обработка даты во втором решении.

Спенсер почти сделал это, но это должен быть рабочий код:

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

Я использовал простой math, чтобы определить, кто последовательно заходил на сайт. Это свойство заключается в том, что разница в днях между первым и последним доступом должна быть равна количеству записей в журнале таблицы доступа.

Вот сценарий 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);

Что-то вроде этого?

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 for Smarties (называя это Runs and Sequences). У меня дома нет этой книги, поэтому, когда я приду на работу ... Я отвечу на это. (при условии, что таблица истории называется dbo.UserHistory, а количество дней - @Days)

Еще одно замечание - из блога команды SQL о прогонах

Другая идея, которую я '

Вы можете использовать рекурсивный 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

Похоже, вы могли бы воспользоваться тем фактом, что для непрерывности в течение 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 в строках:

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

Если вы можете изменить схемы таблицы, я бы предложил добавить в таблицу столбец LongestStreak , в котором вы должны установить количество последовательных дней, заканчивающихся на CreationDate . Обновить таблицу во время входа в систему легко (аналогично тому, что вы делаете уже сейчас, если не существует строк текущего дня, вы проверяете, существует ли какая-либо строка за предыдущий день. Если это правда, вы увеличите LongestStreak в новой строке, в противном случае вы установите для него значение 1.)

После добавления этого столбца запрос будет очевиден:

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

Как насчет (и убедитесь, что предыдущий оператор закончился полу- двоеточие):

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)

Отредактировано: Оказывается, смещение - зарезервированное слово, поэтому я использовал вместо него TheOffset.

Отредактировано: предложение использовать COUNT (*) очень актуально - я должен был сделать это в первую очередь, но на самом деле не думал. Раньше использовался dateiff (day, min (CreationDate),