Sprawdzanie x kolejnych dni-podane znaczniki czasu w bazie danych

Można to osiągnąć za pomocą przesuniętego samo-zewnętrznego połączenia w połączeniu ze zmienną. Zobacz To rozwiązanie:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
    SELECT *
    FROM
    (
        SELECT IF(b.login_date IS NULL, @val:=@val+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

Zwróci 1 lub 0 w zależności od tego, czy użytkownik zalogował się przez 30 kolejnych dni lub więcej w ANYTIME w przeszłości.

Ciężar tego zapytania jest naprawdę w pierwszym podwyzszeniu. Przyjrzyjmy się bliżej, abyśmy mogli lepiej zrozumieć, jak to działa: {]}

Z poniższymi przykładowymi danymi zestaw:

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

To zapytanie:

SELECT a.*, b.*, IF(b.login_date IS NULL, @val:=@val+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

Wyprodukuje:

Jak widzisz, to co robimy to przesunięcie połączonej tabeli o +1 dzień. Dla każdego dnia, który nie jest kolejny z poprzednim dniem, wartość NULL jest generowana przez lewe połączenie.

Teraz, kiedy wiemy gdzie są kolejne dni, możemy użyć zmiennej do rozróżnienia każdego zbioru kolejnych dni poprzez wykrycie, czy przesunięta tabela jest wiersze to NULL. Jeśli są NULL, dni nie są kolejne, więc po prostu zwiększ zmienną. Jeśli są NOT NULL, to nie zwiększaj zmiennej:

Po zróżnicowaniu każdego zbioru kolejnych dni ze zmienną zwiększającą, jest to po prostu prosta sprawa grupowania przez każdy "zestaw" (zdefiniowany w kolumnie consec_set) i za pomocą HAVING aby odfiltrować dowolny zestaw, który ma mniej niż określone kolejne dni (30 w kolumnie). przykład):

W końcu owijamy że zapytanie i po prostu policz liczbę zestawów, które miały 30 lub więcej kolejnych dni. Jeśli był jeden lub więcej z tych zestawów, to return 1, w przeciwnym razie return 0.

Zobacz sqlfiddle krok po kroku demo