Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> Nietypowe zapytanie - prośba o pomoc
topcio
post 4.03.2018, 13:42:16
Post #1





Grupa: Zarejestrowani
Postów: 111
Pomógł: 0
Dołączył: 14.01.2017

Ostrzeżenie: (0%)
-----


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

  1. public FUNCTION get_dvr_offline_status_count($dvr_mac_address) {
  2. $result = $this->sql->query("
  3. SELECT count(dvr_test_status)
  4. FROM (
  5. SELECT dvr_test_status
  6. FROM dvr_status
  7. WHERE dvr_test_status = 0
  8. AND dvr_status_dvr_mac_address = '$dvr_mac_address'
  9. AND dvr_status_timestamp BETWEEN NOW() - INTERVAL 1440 MINUTE AND NOW()
  10. ) count
  11. ");
  12. RETURN $result->fetch_row()[0];
  13. exit;
  14. }



Ten post edytował topcio 4.03.2018, 13:45:40
Go to the top of the page
+Quote Post
trueblue
post 4.03.2018, 13:48:40
Post #2





Grupa: Zarejestrowani
Postów: 5 055
Pomógł: 1410
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Zawsze między rekordami jest różnica pełnej minuty?
Go to the top of the page
+Quote Post
topcio
post 4.03.2018, 13:55:12
Post #3





Grupa: Zarejestrowani
Postów: 111
Pomógł: 0
Dołączył: 14.01.2017

Ostrzeżenie: (0%)
-----


tak

Tym wstawiam rekordy

  1. "<?php
  2.  
  3. include_once '" . $_SERVER['DOCUMENT_ROOT'] . "/class/db_login.php';
  4. include_once '" . $_SERVER['DOCUMENT_ROOT'] . "/class/Sql.class.php';
  5. \$sql = new Sql( \$db_host, \$db_user, \$db_passwd, \$db_name );
  6. \$sock = @fsockopen( '" . long2ip($data['dvr_wan_ip_address']) ."', '" . $data['dvr_wan_tcp_port'] . "', \$num, \$error, 1 );
  7. if( !\$sock ){
  8. \$result = \$sql->query(\"
  9. INSERT INTO dvr_status (
  10. dvr_status_id,
  11. dvr_status_timestamp,
  12. dvr_status_dvr_mac_address,
  13. dvr_test_status
  14. )
  15. VALUES (
  16. NULL,
  17. '\" . date_create()->format('Y-m-d H:i') . \"',
  18. '{$data['dvr_mac_address']}',
  19. 0
  20. )\");
  21. }
  22. if( \$sock ){
  23. fclose(\$sock);
  24. \$result = \$sql->query(\"
  25. INSERT INTO dvr_status (
  26. dvr_status_id,
  27. dvr_status_timestamp,
  28. dvr_status_dvr_mac_address,
  29. dvr_test_status
  30. )
  31. VALUES (
  32. NULL,
  33. '\" . date_create()->format('Y-m-d H:i') . \"',
  34. '{$data['dvr_mac_address']}',
  35. 1
  36. )\");
  37. }
  38. ?>";


ale może się zdarzyć tak, że np między 11.55 a 12.04 jest przerwa konserwacyjna w testach.
Go to the top of the page
+Quote Post
trueblue
post 4.03.2018, 21:32:08
Post #4





Grupa: Zarejestrowani
Postów: 5 055
Pomógł: 1410
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Ż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.:
  1. SELECT TIME_TO_SEC(TIMEDIFF(p2.godzina,(SELECT MIN(godzina) FROM pomiar))) AS grupa,p.godzina,p2.godzina AS godzina2,p.flaga
  2. FROM pomiar AS p, pomiar AS p2
  3. WHERE p.godzina>=p2.godzina


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:
  1. SELECT grupa,godzina2,flaga,
  2. IF(@poprzednia_grupa<>grupa,(SELECT @numer:=1),@numer:=@numer+1),
  3. IF(@poprzednia_grupa<>grupa,(SELECT @poprzednia_grupa:=grupa),NULL),
  4. @numer AS numer
  5. FROM
  6. (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


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:
  1. SELECT grupa,godzina2,SUM(flaga=0)
  2. FROM(
  3. SELECT grupa,godzina2,flaga,
  4. IF(@poprzednia_grupa<>grupa,(SELECT @numer:=1),@numer:=@numer+1),
  5. IF(@poprzednia_grupa<>grupa,(SELECT @poprzednia_grupa:=grupa),NULL),
  6. @numer AS numer
  7. FROM
  8. (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
  9. )
  10. AS tmp2 WHERE numer<=10
  11. GROUP BY grupa,godzina2


Gdybyś miał ciągłe godziny, to całość byłaby znacznie prostsza.
Go to the top of the page
+Quote Post
mmmmmmm
post 4.03.2018, 23:29:22
Post #5





Grupa: Zarejestrowani
Postów: 1 386
Pomógł: 302
Dołączył: 18.04.2012

Ostrzeżenie: (0%)
-----


http://sqlfiddle.com/#!9/4d4383e/7
Go to the top of the page
+Quote Post
topcio
post 4.03.2018, 23:55:37
Post #6





Grupa: Zarejestrowani
Postów: 111
Pomógł: 0
Dołączył: 14.01.2017

Ostrzeżenie: (0%)
-----


trueblue -> dzięki za poświęcenie, jednak Twoje rozwiązanie to pożeracz zasobów, działa prawie OK, ale jedno zapytanie wykonuje się około 15s, a ma się to wykonywać co 1min przy odświeżaniu strony.
mmmmmmm -> jeszcze większe dzięki, to jest chyba to o co mi chodziło, teraz muszę do wpasować w interfejs i zobaczę w praktyce. Ale wykonuje się w 10ms, więc jest cool


Natrafiłem jednak na pewnie problem w rozwiązaniu mmmmmmmm, otóż gdy np mamy po sobie 14 rekordów z zerami on to zaliczy razy 3, a powinien tylko raz, to chyba powinno być w tym ustawieniu + interval 9 minut, aby więcej też zliczał, może jakieś MIN tam wstawić, ale jak tego nie wiem.

Ten post edytował topcio 5.03.2018, 00:18:55
Go to the top of the page
+Quote Post
trueblue
post 5.03.2018, 08:09:38
Post #7





Grupa: Zarejestrowani
Postów: 5 055
Pomógł: 1410
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Cytat(topcio @ 4.03.2018, 23:55:37 ) *
Natrafiłem jednak na pewnie problem w rozwiązaniu mmmmmmmm, otóż gdy np mamy po sobie 14 rekordów z zerami on to zaliczy razy 3, a powinien tylko raz, to chyba powinno być w tym ustawieniu + interval 9 minut, aby więcej też zliczał, może jakieś MIN tam wstawić, ale jak tego nie wiem.

Rozwiązanie mmmmmmm nie jest prawidłowe, bo opiera się na założeniu, że rekordy są w postaci ciągłej. Wystarczy zmienić 13:42 kończącą zakres zer na 13:50 i dalej kolejne godziny, i wynik jest niepoprawny. Pisałem, że rozwiązanie, które podałem można uprościć gdyby rekordy były w postaci ciągłej, ale w Twoim założeniu może być przerwa.


Zamiast zapytania, możesz wynik "otrzymywać" w trakcie zapisu rekordów. Mianowicie tworzysz drugą tabelę z godziną, flagą i ilością, i w momencie pojawienia się nowego rekordu:
1. Jeśli dla danej godziny jest flaga 0 i ostatnia zapisana godzina miała również flagę 0, to dodajesz do kolumny ilość ostatniego rekordu 1.
2. Jeśli dla danej godziny jest flaga 0 i ostatnia zapisana godzina miała flagę 1, to zapisujesz nową godzinę z ilością 0 i flagą 0.
3. Analogicznie robisz dla flagi 1.

Ten post edytował trueblue 5.03.2018, 08:39:15
Go to the top of the page
+Quote Post
topcio
post 6.03.2018, 00:03:44
Post #8





Grupa: Zarejestrowani
Postów: 111
Pomógł: 0
Dołączył: 14.01.2017

Ostrzeżenie: (0%)
-----


Cytat(trueblue @ 5.03.2018, 08:09:38 ) *
Rozwiązanie mmmmmmm nie jest prawidłowe, bo opiera się na założeniu, że rekordy są w postaci ciągłej. Wystarczy zmienić 13:42 kończącą zakres zer na 13:50 i dalej kolejne godziny, i wynik jest niepoprawny. Pisałem, że rozwiązanie, które podałem można uprościć gdyby rekordy były w postaci ciągłej, ale w Twoim założeniu może być przerwa.

Masz rację, nie pomyślałem o tym, przy sprawdzaniu.

Cytat(trueblue @ 5.03.2018, 08:09:38 ) *
Zamiast zapytania, możesz wynik "otrzymywać" w trakcie zapisu rekordów. Mianowicie tworzysz drugą tabelę z godziną, flagą i ilością, i w momencie pojawienia się nowego rekordu:
1. Jeśli dla danej godziny jest flaga 0 i ostatnia zapisana godzina miała również flagę 0, to dodajesz do kolumny ilość ostatniego rekordu 1.
2. Jeśli dla danej godziny jest flaga 0 i ostatnia zapisana godzina miała flagę 1, to zapisujesz nową godzinę z ilością 0 i flagą 0.
3. Analogicznie robisz dla flagi 1.


Że też na to nie wpadłem, to jest chyba najsensowniejsze. Zaraz coś napiszę o zobaczę, ale chyba to jest to.

Wydaje mi się, że ogarnąłem.
Zrobiłem tak.

1. Plik tworzony, dla każdego testowanego hosta
  1. "<?php
  2. require '" . $_SERVER['DOCUMENT_ROOT'] . "/class/db_login.php';
  3. require '" . $_SERVER['DOCUMENT_ROOT'] . "/class/Sql.class.php';
  4. require '" . $_SERVER['DOCUMENT_ROOT'] . "/class/Dvr.class.php';
  5. \$sql = new Sql( \$db_host, \$db_user, \$db_passwd, \$db_name );
  6. \$dvr = new Dvr( \$sql, null, null);
  7. \$sock = @fsockopen( '" . long2ip($data['dvr_wan_ip_address']) ."', '" . $data['dvr_wan_tcp_port'] . "', \$num, \$error, 1 );
  8. if( !\$sock ) { \$status = '0'; }
  9. if( \$sock ) { \$status = '1'; fclose(\$sock); }
  10. if (\$dvr->get_last_dvr_status_count_id('" . $dvr_mac_address . "') == null) {
  11. \$dvr->add_new_event_to_dvr_status_count('" . $dvr_mac_address . "', \$status);
  12. \$dvr->add_new_event_to_dvr_status('" . $dvr_mac_address . "', \$status);
  13. }
  14. else {
  15. if ( \$dvr->get_last_dvr_status('" . $dvr_mac_address . "') == \$status ) {
  16. \$dvr->add_count_to_dvr_status_count(\$dvr->get_last_dvr_status_count_id('" . $dvr_mac_address . "'));
  17. \$dvr->add_new_event_to_dvr_status('" . $dvr_mac_address . "', \$status);
  18. }
  19. if ( \$dvr->get_last_dvr_status('" . $dvr_mac_address . "') != \$status ) {
  20. \$dvr->add_new_event_to_dvr_status_count('" . $dvr_mac_address . "', \$status);
  21. \$dvr->add_new_event_to_dvr_status('" . $dvr_mac_address . "', \$status);
  22. }
  23. }
  24. ?>";


Objaśnienie:

Pobieramy z głównej tabeli ostatni status
  1. public function get_last_dvr_status($dvr_mac_address) {
  2. $result = $this->sql->query("
  3. SELECT dvr_test_status
  4. FROM dvr_status
  5. WHERE dvr_status_id=(
  6. SELECT MAX(dvr_status_id)
  7. FROM dvr_status
  8. WHERE dvr_status_dvr_mac_address = '$dvr_mac_address'
  9. )
  10. LIMIT 1
  11. ");
  12. if($result === false) {
  13. $info = "Wystąpił błąd z bazą danych. Błąd: " . $this->sql->error;
  14. return $info;
  15. }
  16. if($result->num_rows === 1) {
  17. return $result->fetch_array()[0];
  18. exit;
  19. }
  20. }


Pobieramy z tabeli pomocniczej ostatnie ID dla testowanego hosta
  1. public function get_last_dvr_status_count_id($dvr_mac_address) {
  2. $result = $this->sql->query("
  3. SELECT dvr_status_id
  4. FROM dvr_status_count
  5. WHERE dvr_status_id=(
  6. SELECT MAX(dvr_status_id)
  7. FROM dvr_status_count
  8. WHERE dvr_status_dvr_mac_address = '$dvr_mac_address'
  9. )
  10. LIMIT 1
  11. ");
  12. if($result === false) {
  13. $info = "Wystąpił błąd z bazą danych. Błąd: " . $this->sql->error;
  14. return $info;
  15. }
  16. if($result->num_rows === 0) {
  17. return null;
  18. exit;
  19. }
  20. if($result->num_rows === 1) {
  21. return $result->fetch_array()[0];
  22. exit;
  23. }
  24. }


Zwiększamy licznik
  1. public function add_count_to_dvr_status_count($last_dvr_status_count_id) {
  2. $result = $this->sql->query("
  3. UPDATE dvr_status_count
  4. SET dvr_status_test_count = dvr_status_test_count + 1
  5. WHERE dvr_status_id = '$last_dvr_status_count_id'
  6. ");
  7. }


Jeśli w tabeli pomocniczej nie istnieje żaden wpis dla testowanego hosta, albo status jest inny
  1. public function add_new_event_to_dvr_status_count($dvr_mac_address, $status) {
  2. $status_info = ($status == '0') ? "OffLine" : 'OnLine';
  3. $result = $this->sql->query("
  4. INSERT INTO dvr_status_count (
  5. dvr_status_id,
  6. dvr_status_timestamp,
  7. dvr_status_dvr_mac_address,
  8. dvr_status_test_count,
  9. dvr_status_info
  10. )
  11. VALUES (
  12. NULL,
  13. '" . date_create()->format('Y-m-d H:i') . "',
  14. '$dvr_mac_address',
  15. 1,
  16. '$status_info'
  17. )");
  18. }


I na koniec tak jak było do tabeli głównej dopisujemy zdarzenie (1/0)
  1. public function add_new_event_to_dvr_status($dvr_mac_address, $status) {
  2. $result = $this->sql->query("
  3. INSERT INTO dvr_status (
  4. dvr_status_id,
  5. dvr_status_timestamp,
  6. dvr_status_dvr_mac_address,
  7. dvr_test_status
  8. )
  9. VALUES (
  10. NULL,
  11. '" . date_create()->format('Y-m-d H:i') . "',
  12. '$dvr_mac_address',
  13. '$status'
  14. )");
  15. }





Wszystko działa poprawnie, ale taka konkluzja mnie naszła, że w sumie mógłbym to zrobić na jednej tabeli.
W tabeli pomocniczej dodać tylko kolumnę ostatni status i zamiast pobierać go z głównej wszystko robić w tej pomocniczej.
Bo ta główna jest mi potrzebna do zliczenia ile razy był offline w ciągu np ostatnich 24h, ale przecież mogę sumować licznik, choć będzie to mniej dokładne,
bo jeśli licznik zaczął się 25h tamu i jest cały czas OffLine to mi nie zliczy poprawnie - jeśli się nie mylę.
Narobiłem się z tym, więc chyba zostawię tak jak jest - przynajmniej na razie. Mam jeszcze sporo innych rzeczy do ogarnięcia, więc niech działa jak działa.

Dzięki wszystkim za pomoc, temat rozwiązany - można zamknąć.
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 25.06.2018 - 03:23