Проверять на наличие x последовательных дней - заданные временные метки в базе данных

Ответ 1

Вы можете выполнить это, используя сдвинутое само-внешнее соединение в сочетании с переменной. См. Это решение:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
    SELECT *
    FROM
    (
        SELECT IF(b.login_date IS NULL, @val:[email protected]+1, @val) AS consec_set
        FROM tbl a
        CROSS JOIN (SELECT @val:=0) var_init
        LEFT JOIN tbl b ON 
            a.user_id = b.user_id AND
            a.login_date = b.login_date + INTERVAL 1 DAY
        WHERE a.user_id = 1
    ) a
    GROUP BY a.consec_set
    HAVING COUNT(1) >= 30
) a

Это вернет либо 1, либо 0 на основании того, что пользователь входил в систему в течение 30 последовательных дней или более в ANYTIME в прошлом.

Основная часть этого запроса действительно находится в первом подзапросе. Давайте взглянем ближе, чтобы мы могли лучше понять, как это работает:

В следующем примере набор данных:

CREATE TABLE tbl (
  user_id INT,
  login_date DATE
);

INSERT INTO tbl VALUES
(1, '2012-04-01'),  (2, '2012-04-02'),
(1, '2012-04-25'),  (2, '2012-04-03'),
(1, '2012-05-03'),  (2, '2012-04-04'),
(1, '2012-05-04'),  (2, '2012-05-04'),
(1, '2012-05-05'),  (2, '2012-05-06'),
(1, '2012-05-06'),  (2, '2012-05-08'),
(1, '2012-05-07'),  (2, '2012-05-09'),
(1, '2012-05-09'),  (2, '2012-05-11'),
(1, '2012-05-10'),  (2, '2012-05-17'),
(1, '2012-05-11'),  (2, '2012-05-18'),
(1, '2012-05-12'),  (2, '2012-05-19'),
(1, '2012-05-16'),  (2, '2012-05-20'),
(1, '2012-05-19'),  (2, '2012-05-21'),
(1, '2012-05-20'),  (2, '2012-05-22'),
(1, '2012-05-21'),  (2, '2012-05-25'),
(1, '2012-05-22'),  (2, '2012-05-26'),
(1, '2012-05-25'),  (2, '2012-05-27'),
                    (2, '2012-05-28'),
                    (2, '2012-05-29'),
                    (2, '2012-05-30'),
                    (2, '2012-05-31'),
                    (2, '2012-06-01'),
                    (2, '2012-06-02');

Этот запрос:

SELECT a.*, b.*, IF(b.login_date IS NULL, @val:[email protected]+1, @val) AS consec_set
FROM tbl a
CROSS JOIN (SELECT @val:=0) var_init
LEFT JOIN tbl b ON 
    a.user_id = b.user_id AND
    a.login_date = b.login_date + INTERVAL 1 DAY
WHERE a.user_id = 1

Будет производить:

Как вы можете видеть, мы делаем перенос объединенной таблицы на +1 день. Для каждого дня, не следующего за предыдущим днем, значение NULL генерируется LEFT JOIN.

Теперь, когда мы знаем, где существуют нескончаемые дни, мы можем использовать переменную, чтобы различать каждый набор последовательных дней, определяя, были ли сдвинутые строки таблицы NULL. Если они NULL, дни не последовательны, поэтому просто увеличивайте переменную. Если они NOT NULL, то не увеличивайте переменную:

После того, как мы дифференцировали каждый набор последовательных дней с добавочной переменной, это просто просто вопрос группировки каждым "множеством" (как определено в столбце consec_set) и с помощью HAVING для фильтрации любых set, который имеет меньше заданных дней подряд (30 в вашем примере):

Затем, наконец, мы завершаем запрос THAT и просто подсчитываем количество наборов, которые имели 30 или более последовательных дней. Если был один или несколько из этих наборов, верните 1, в противном случае верните 0.

См. SQLFiddle пошаговая демонстрация

Ответ 2

Вы можете добавить X к дате timestamp и chech, если разные (даты) в этом диапазоне дат == X:

Не реже одного раза в день из этих 30 дней:

SELECT distinct 1 
FROM 
   login_dates l1 
inner join
   login_dates l2
      on l1.user = l2.user and 
         l2.timestamp between l1.timestamp and  
                              date_add( l1.timestamp, Interval X day )
where l1.user = some_user
group by 
   DATE(l1.timestamp)
having 
   count( distinct DATE(l1.timestamp) ) = X

(Вы не говорите о требованиях к производительности...;))

* Отредактировано * Запрос только за последние X дней: восток один раз в день из этих 30 дней

SELECT distinct 1 
FROM 
   login_dates l1 
where l1.user = some_user
      and l1.timestamp >  date_add( CURDATE() , Interval -X day )
group by
    l1.user
having 
   count( distinct DATE(l1.timestamp) ) = X

Ответ 3

Это сложная проблема для решения только SQL.

Ядро проблемы заключается в том, что вам нужно сравнивать динамические множества результатов друг с другом в одном запросе. Например, вам нужно получить все идентификаторы входа/сеанса для одной DATE, затем JOIN или UNION их со списком группировки логинов из DATE() (которую вы можете использовать DATE_ADD для определения). Вы можете сделать это для N числа последовательных дат. Если у вас осталось несколько строк, то эти сеансы были зарегистрированы в течение этого периода.

Предположим, что следующая таблица:

sessionid int, дата создания

Этот запрос возвращает все сеансы, у которых есть строки за последние два дня:

select t1.sessionid from logins t1 
  join logins t2 on t1.sessionid=t2.sessionid 
  where t1.created = DATE(date_sub(now(), interval 2 day)) 
    AND t2.created = DATE(date_sub(now(), interval 1 day));

Как вы можете видеть, SQL получит gnarly в течение 30 дней. Создайте script.:-D

Далее предполагается, что каждый день таблица входа обновляется сеансом.

Я не знаю, действительно ли это решает вашу проблему, но я надеюсь, что помог решить проблему.

Удачи.

Ответ 4

Не проще ли иметь дополнительный столбец sequence_days в таблице login_dates со значением по умолчанию 1. Это означало бы длину последовательных дат, заканчивающихся в этот день.

Вы создаете вставку после триггера в login_dates, где вы проверяете, есть ли запись за предыдущий день.

Если его нет, тогда поле будет иметь значение по умолчанию 1, означающее, что в эту дату начинается новая последовательность.

Если здесь есть запись за предыдущий день, вы изменяете значение days_logged_in из значения по умолчанию 1, чтобы быть больше, чем в предыдущий день.

Пример:

| date       | consecutive_days |
|------------|------------------|
| 2013-11-13 | 5                |
| 2013-11-14 | 6                |
| 2013-11-16 | 1                |
| 2013-11-17 | 2                |
| 2013-11-18 | 3                |