Nietypowe zapytanie - prośba o pomoc Opcje

[topcio]

Witajcie.
Potrzebuję wykonać takie zapytanie do bazy:
Z rekordów od teraz wstecz o 1440 minut wyszukaj rekordy, które po sobie miały wartość 0. Jeśli znajdzie zwróci true.
Dla zobrazowania.

2018-03-04 13:30:00 1
2018-03-04 13:31:00 1
2018-03-04 13:32:00 1
2018-03-04 13:33:00 0 |
2018-03-04 13:34:00 0 |
2018-03-04 13:35:00 0 |
2018-03-04 13:36:00 0 |
2018-03-04 13:37:00 0 | Tu znalazł 10 rekordów po sobie więc zwraca TRUE
2018-03-04 13:38:00 0 |
2018-03-04 13:39:00 0 |
2018-03-04 13:40:00 0 |
2018-03-04 13:41:00 0 |
2018-03-04 13:42:00 0 |
2018-03-04 13:43:00 1
2018-03-04 13:44:00 1
2018-03-04 13:45:00 0 |
2018-03-04 13:46:00 0 | Ale tu już nie było 10 po sobie więc je pomija.
2018-03-04 13:47:00 1
2018-03-04 13:48:00 1

Nie mam pojęcia jak się zabrać za takie zapytanie
Proszę o pomoc bardziej doświadczonych.

Na razie mam tylko do wyszukiwania ile razy było 0

[SQL] pobierz, plaintext 
public FUNCTION get_dvr_offline_status_count($dvr_mac_address) {
    $result = $this->sql->query("
    SELECT count(dvr_test_status) 
    FROM (
        SELECT dvr_test_status
        FROM dvr_status
        WHERE dvr_test_status = 0 
        AND dvr_status_dvr_mac_address = '$dvr_mac_address'
        AND dvr_status_timestamp BETWEEN NOW() - INTERVAL 1440 MINUTE AND NOW()
        ) count
    ");
    RETURN $result->fetch_row()[0];
    exit;
} 
[SQL] pobierz, plaintext 

Ten post edytował topcio 4.03.2018, 13:45:40

[trueblue]

Żeby wyjaśnić, to od środka:

To zapytanie ponumeruje grupy. Grupa to zakres od wybranej godziny do ostatniej w puli. Czyli pierwsza grupa to rekordy od 0 do N, druga od 1 do N, trzecia od 2 do N, itd.:

[SQL] pobierz, plaintext 
SELECT TIME_TO_SEC(TIMEDIFF(p2.godzina,(SELECT MIN(godzina) FROM pomiar))) AS grupa,p.godzina,p2.godzina AS godzina2,p.flaga
FROM pomiar AS p, pomiar AS p2
WHERE p.godzina>=p2.godzina
[SQL] pobierz, plaintext 

Teraz dodajemy licznik zmieniający się w ramach grupy. Licznik się przyda, bo chcemy wybrać z każdej grupy 10 rekordów. Nie możemy użyć aliasu grupa w warunkach, więc żeby go nie powtarzać, to obudowujemy w zapytanie:

[SQL] pobierz, plaintext 
SELECT grupa,godzina2,flaga,
IF(@poprzednia_grupa<>grupa,(SELECT @numer:=1),@numer:=@numer+1),
IF(@poprzednia_grupa<>grupa,(SELECT @poprzednia_grupa:=grupa),NULL),
@numer AS numer
FROM
(SELECT TIME_TO_SEC(TIMEDIFF(p2.godzina,(SELECT MIN(godzina) FROM pomiar))) AS grupa,p.godzina,p2.godzina AS godzina2,p.flaga FROM pomiar AS p, pomiar AS p2 WHERE p.godzina>=p2.godzina) AS tmp, (SELECT @numer:=1) AS numer, (SELECT @poprzednia_grupa:=-1) AS poprzednia_grupa
[SQL] pobierz, plaintext 

Tu już możemy zgrupować grupy wybierając z każdej tylko rekordy od numeru 1 do 10 i sumując ilość 0. To jest ostateczne zapytanie, które pokazuje ilość 0 od danej godziny włącznie + 9 kolejnych rekordów w przód:

[SQL] pobierz, plaintext 
SELECT grupa,godzina2,SUM(flaga=0)
FROM(
 SELECT grupa,godzina2,flaga,
 IF(@poprzednia_grupa<>grupa,(SELECT @numer:=1),@numer:=@numer+1),
 IF(@poprzednia_grupa<>grupa,(SELECT @poprzednia_grupa:=grupa),NULL), 
 @numer AS numer
 FROM
 (SELECT TIME_TO_SEC(TIMEDIFF(p2.godzina,(SELECT MIN(godzina) FROM pomiar))) AS grupa,p.godzina,p2.godzina AS godzina2,p.flaga FROM pomiar AS p, pomiar AS p2 WHERE p.godzina>=p2.godzina) AS tmp, (SELECT @numer:=1) AS numer, (SELECT @poprzednia_grupa:=-1) AS poprzednia_grupa
)
AS tmp2 WHERE numer<=10
GROUP BY grupa,godzina2
[SQL] pobierz, plaintext 

Gdybyś miał ciągłe godziny, to całość byłaby znacznie prostsza.