Pętle w Mysql - wypisanie wszystkich dat z przedziału, Loop in MySQL Opcje

[Sztef89]

Witam,

Szukam polecenia pasującego do mojego zadania lecz nic nie pasuje a całość chciałbym zrealizować po stronie bazy danych.

Otóż mam dwie daty startową i końcową oraz rekordy w bazie, każdy z tych rekordów ma datę start i stop.
Chciałbym wyświetlić wszystkie dni od daty startowej do końcowej zliczając ile tych rekordów będzie pasowało do poszczególnych dat.

Nie chce rozbijać tych czynności na php.

Domyślam się że w MySQL trzeba będzie zastosować jakąś pętle. Najważniejsze jest dla mnie aby zapytanie było szybkie = wydajne.

Rekordów mam ponad 1000.

Z góry dziękuję za pomoc, jak coś uda mi się znaleźć to tu umieszczę, może wspólnymi siłami dojdziemy jak to zrobić

Pozdrawiam serdecznie
Stefan

[Kshyhoo]

1. Pokaż, co zrobiłeś do tej pory (kod).
2. Przeszukaj Forum, bo temat był już poruszany.

[Sztef89]

Nie wiem jak był temat zatytułowany, próbowałem na różne sposoby szukać ale nic z tego.

Ok, robię tak:
1. Pobieram dwie zmienne prostym zapytaniem SELECT MIN(start), MAX(stop) FROM tabela1 WHERE uid = 12
2. Teraz chciałbym wywołać polecenie które wypisze wszystkie daty od start do stop oraz zliczyć rekordy pasujące do wartości daty

Przez php mógłbym wygenerować sobie listę dat lecz bym musiał wywołać kilkaset poleceń SQL - odpada

Wynik powinien być w postaci:

DATA_____ | LICZ
2014-01-09 | 2
2014-01-10 | 5
2014-01-11 | 1
2014-01-12 | 6
...



Wpadłem na genialny pomysł

Zrobię sobie tabelkę w której umieszczę wszystkie daty i zrobię do niej warunek oraz JOIN

Jak macie inne pomysły to piszcie, póki co nic lepszego nie przyszło mi do głowy

[Kshyhoo]

Drugi wynik w Google.
Ogólnie w Google.

[sazian]

w skrócie

[SQL] pobierz, plaintext 
SELECT DATA,count(*) AS  LICZ FROM tabela WHERE DATA BETWEEN start AND stop GROUP BY DATA
[SQL] pobierz, plaintext 

[Sztef89]

Zamiast odsyłać mnie do Google gdzie nic nie znalazłem (pisałem o tym na początku) to wolałbym abyś po swojemu odpowiedział na mój problem.

Mój problem nie polega na wyświetlanych danych z jakiegoś przedziału datowego - do tego podajesz mi odnośniki.

Chodziło mi o WYGENEROWANIE wszystkich dat z jakiegoś przedziału datowego, a następnie przeszukanie bazy żeby dla każdej wartości datowej policzył wiersze.

Rozwiązałem to w taki sposób że zrobiłem nową tabelę która zawiera wygenerowane daty z przedziału na którym będę pracował (wyszło 2400 wartości), wszystko udało mi się zrobić tak jak chce lecz czas wykonania polecenia pozostawia wiele do życzenia ~0.03s (i7,ssd), przez co zapytanie staje się bezużyteczne bo liczyłem na przynajmniej 0.001s.

Im większy przedział datowy mam do przeliczenia tym zapytanie wykonuje się dłużej :/
Indeksy oczywiście dodane do pól które występują w warunkach i grupowaniu

Oto mój kod:

[SQL] pobierz, plaintext 
SELECT d.id, d.DATA, COUNT( * ) AS ilosc
FROM  `daty` d
LEFT JOIN  `dane` s ON d.DATA BETWEEN s.start AND s.end
WHERE d.`data` BETWEEN (SELECT MIN(`start`) FROM `dane`) 
    AND (SELECT LEAST(CURDATE(), MAX(`end`)) FROM `dane`)
GROUP BY d.`data`
ORDER BY d.`data` 
[SQL] pobierz, plaintext 

Tabela daty: 2400 wierszy
Tabela dane: 1450 wierszy

Ten post edytował Sztef89 9.01.2014, 19:40:05

[sazian]

to może tak

[SQL] pobierz, plaintext 
 d.id, d.DATA, (SELECT count(*) FROM  `dane` s WHERE  d.DATA BETWEEN s.start AND s.end )AS ilosc
FROM `daty` d
WHERE d.`data` BETWEEN start
AND stop
 
ORDER BY d.`data` 
[SQL] pobierz, plaintext 

a start i stop spróbuj pobrać w osobnym zapytaniu

jeśli dalej będzie wolne to pokaż explain obu zapytań

[Sztef89]

gdy wpisze ręcznie zakres to wydajność nie zmienia się ponieważ zapytanie tylko raz pobiera te dane w podzapytaniu.

Jak się robi explain w phpmyadmin ?

Dzięki za pomoc !

[sazian]

explain select ...

[Sztef89]

Twój kod nie przejdzie bo nie chce aby mi zliczało liczbe dni tylko liczbę wierszy z tabeli dane pasujących do daty z tabeli daty.

Oto explain:

http://imgur.com/r6vXeHQ

[sazian]

a sprawdziłeś ?
ma SELECT count(*) FROM `dane` czyli zliczanie z tabeli dane więc wydaje mi się że powinno zadziałać

explain mówi że jest do d****


spróbuj dodać wspólny indeks dla start i end

Ten post edytował sazian 9.01.2014, 22:00:02

[Sztef89]

Po czym wnioskujesz że jest do dupy ? Mógłbys jakoś w skrócie wyjaśnić ?

Dodałem wspólny index, mam wrażenie jakby było lekko lepiej ale stanowczo to za mało:
http://imgur.com/84sOCNn

przeanalizowałem polecenie które mi podałeś, faktycznie wyświetla też to co chce ale 10x szybciej !
zaraz zmodyfikuje większe polecenie i powiem Ci jakie efekty ! dzięki !

ehh tylko w tym podzapytaniu nie mogę użyć JOINa... pomyśle nad tym jeszcze jak wstanę

Problem rozwiązany w ten sposób, że zrobiłem kolejną tabele w której będę dopisywał wyniki. Pełne zapytanie generuje się 1.5s dlatego nie nadaje się aby wszyscy użytkownicy z niego korzystali, trzeba było zrobić ta agregację... dzięki za Waszą pomoc

[sazian]

przeanalizowałem polecenie które mi podałeś, faktycznie wyświetla też to co chce ale 10x szybciej !
zaraz zmodyfikuje większe polecenie i powiem Ci jakie efekty ! dzięki !

działa szybciej dlatego że unikasz tworzenia tabeli tymczasowej. Ale prawdopodobnie dalej musi przeszukiwać ponad 300000 rekordów

ehh tylko w tym podzapytaniu nie mogę użyć JOINa... pomyśle nad tym jeszcze jak wstanę

oczywiście że możesz użyć JOIN, tylko pamiętaj że podzapytanie w SELECT musi zwracać wartość.
Jeśli wynikiem jest wektor lub tabela to musisz potraktować podzapytanie jak tabelę czyli przenieść je do FROM lub JOIN.

Problem rozwiązany w ten sposób, że zrobiłem kolejną tabele w której będę dopisywał wyniki. Pełne zapytanie generuje się 1.5s dlatego nie nadaje się aby wszyscy użytkownicy z niego korzystali, trzeba było zrobić ta agregację... dzięki za Waszą pomoc

czyli jak dobrze rozumiem zrobiłeś dodatkową tabelę z "cache" ? Bardzo słusznie!
dobrym pomysłem może być użycie wyzwalacza który będzie tą tabelę automatycznie aktualizował