zapytanie z data Opcje

[veyron]

witam
mamy tabele, w ktorej jest pole daty - pobierany miesiac za pomoca date format(%c) oraz pole kwota.
chcialbym zrobic zestawienie kwot dla danych miesiecy, czyli otrzymac tabelke w postaci:
miesiac | 01-2010 | 02-2010 itd...
kwota | 0 | 20 itd...
przy czym moze sie zdazyc, ze dany miesiac nie wystepuje w bazie i wowczas chcialbym otrzymac kwote 0.
i teraz mam pytanie - czy jest mozliwe, zeby jesli dany miesiac nie wystepuje w bazie np. styczen 2010, to zeby baza zwrocila kwote 0 dla tego miesiaca ? jesli tak, to w jaki sposob ?
pozdrawiam

[pmir13]

Może napisz dokładnie co masz w tabeli z przykładowymi danymi i jakiego spodziewasz się wyniku dla tych konkretnych danych.

[veyron]

to moze podam na konkretnym przykladzie:
w tabeli jest data (d.m.r) i ilosc zlecen odpowiadajaca danej dacie (sa jeszcze inne komorki, ale dla tego przykladu nie sa az tak istotne teraz), czyli dla przykladu:
data | ilosc zlecen
01.02.2010 | 15
15.02.2010 | 22
03.04.2010 | 2
03.04.2010 | 8

i teraz chce zestawienie za dany rok np. 2010 w tabelce, ktora ma format (dane dla ww przykladu):
miesiac | 01.2010 | 02.02010 | 03.2010 | 04.2010 | itd.
ilosc zlecen | 0 | 37 | 0 | 10 | itd.

poczatkowo mialem taki kod (rozwiazanie nr 1):

[PHP] pobierz, plaintext 
$sql->query('SELECT COUNT(id_zlecenie) AS ilosc, DATE_FORMAT(data_z, \'%m-%Y\') AS data FROM zlecenie INNER JOIN zleceniodawca AS firma3 ON zlecenie.id_zb=firma3.id_fast INNER JOIN firma AS firma1 ON zlecenie.id_zleceniobiorca=firma1.id_firma INNER JOIN firma AS firma2 ON zlecenie.id_zleceniodawca=firma2.id_firma WHERE czy_anulowane = 0 GROUP BY data ORDER BY id_zlecenie ASC');
 
 
 
print '<div class="statystyki">
<table>
<caption>Ilość zleceń w danych miesiącach</caption>
<tr><th>Miesiąc</th>';
 
while($statystyki = $sql->fetchArray()) {
    print '
 
    <th>'.$statystyki['data'].'
    </th>';
    }
 
 //czyli zapytanie i wyplucie wiersza z miesiacami 02.2010| 04.2010   
 
    print '</tr>
 
<tr><td>ilość zleceń</td>';
 
$sql->query('SELECT COUNT(id_zlecenie) AS ilosc, DATE_FORMAT(data_z, \'%m-%Y\') AS data FROM zlecenie INNER JOIN zleceniodawca AS firma3 ON zlecenie.id_zb=firma3.id_zb INNER JOIN firma AS firma1 ON zlecenie.id_zleceniobiorca=firma1.id_firma INNER JOIN firma AS firma2 ON zlecenie.id_zleceniodawca=firma2.id_firma WHERE czy_anulowane = 0 GROUP BY data ORDER BY id_zlecenie ASC');
 
while($statystyki = $sql->fetchArray()) {
    print '
 
    <td>'.$statystyki['ilosc'].'
    </td>';
    }
 
//czyli ponownie to samo zapytanie i wyplucie wiersza z iloscia zlecen w postaci 37 | 10 (odpowiadajace odpowiednim miesiacom z poprzedniego zapytania)
 
print '</tr>
</table></div>';
[PHP] pobierz, plaintext 

czyli, w zasadzie zapytanie do bazy bylo 2 razy to samo, ale wynik byl w tabeli, ktora:
po 1. ciagnela sie w nieskonczonosc i nie mozna bylo jej przelamac np jak zaczal sie nowy rok
po 2. wynik pomijal miesiace, gdzie byla ilosc 0

poradzilem sobie w taki sposob (rozwiazanie nr 2):

[PHP] pobierz, plaintext 
print '<div class="statystyki">
<h1>Ilość zleceń w danych miesiącach</h1>
';
$zapytanie = mysql_query('SELECT DATE_FORMAT(data_zaladunku, \'%Y\') AS rok FROM zlecenie INNER JOIN zleceniodawca AS firma3 ON zlecenie.id_zb=firma3.id_zb INNER JOIN firma AS firma1 ON zlecenie.id_zleceniobiorca=firma1.id_firma INNER JOIN firma AS firma2 ON zlecenie.id_zleceniodawca=firma2.id_firma WHERE czy_anulowane = 0 GROUP BY rok ORDER BY rok DESC');
 
//pobranie wszystkich lat, dla ktorych istnieja dane w bazie, ponizej petla tworzaca tabele z zestawieniem dla konkretnego roku
 
while($statystyki_lata = mysql_fetch_assoc($zapytanie)) {
 
    $statystyki_rok = $statystyki_lata['rok'];     
 
print '
<table>
<caption>Rok '.$statystyki_rok.'</caption>
<tr><th>Miesiąc</th>';
$miesiac = 1;
while($miesiac != 13) {
 
    print '
 
    <th>'.$miesiac.'-'.$statystyki_rok.'
    </th>';
    $miesiac++;
    }
 
 // wypisanie wszystkich miesiecy dla danego roku  
 
    print '</tr>
 
<tr><td>ilość zleceń</td>';
 
 
 
$miesiac = 1;
while($miesiac != 13) {
    $sql->query('SELECT COUNT(id_zlecenie) AS ilosc, DATE_FORMAT(data_z, \'%c-%Y\') AS data, DATE_FORMAT(data_z, \'%c\') AS miesiac, DATE_FORMAT(data_z, \'%Y\') AS rok FROM zlecenie INNER JOIN zleceniodawca AS firma3 ON zlecenie.id_zb=firma3.id_zb INNER JOIN firma AS firma1 ON zlecenie.id_zleceniobiorca=firma1.id_firma INNER JOIN firma AS firma2 ON zlecenie.id_zleceniodawca=firma2.id_firma WHERE czy_anulowane = 0 AND DATE_FORMAT(data_zaladunku, \'%Y\') = \''.$statystyki_rok.'\' AND DATE_FORMAT(data_zaladunku, \'%c\') = \''.$miesiac.'\' GROUP BY data ORDER BY id_zlecenie ASC');
    $statystyki = $sql->fetchArray();
    print '
 
    <td>';
    if (!empty($statystyki['ilosc'])) {
    print $statystyki['ilosc'];
    }
    else {
        print '0';
    }
 
    print '
    </td>';
    $miesiac++;
    }
 
// wypisanie ilosci zlecen dla konkretnego miesiaca, 
 
print '</tr>
</table>';
}
print '</div>';
[PHP] pobierz, plaintext 

i w zasadzie dziala idealnie, bo otrzymuje to co chcialem - jest tylko jedno, ale - dla kazdego miesiaca jest zapytanie do bazy, wobec czego dla dluzszego okresu (np. pare lat) zapytan sie zrobi wieksza ilosc,
i teraz pytanie brzmi czy moze da sie zrobic to jakos latwiej/prosciej/mniejsza iloscia zapytan (zmieniajac niekoniecznie samo zapytanie do bazy, ale moze rowniez sam kod php/html)?
mysle, ze zagadnienie jest dosc ogolne, bo wystepuje wszedzie tam gdzie chcemy otrzymac dane statystyczne w formie tabeli z podzialem na dane miesiace, przy czym wystepuja miesiace, ktore w bazie nie istnieja (bo bylo 0 zlecen).

ehh troche sie rozpisalem

w kazdym razie dzieki za zainteresowanie tematem
pozdrawiam.

[pmir13]

Szukasz optymalizacji w niewłaściwym miejscu.

Nie masz takiej tabeli jak napisałeś na początku, te statystyki i tak sobie liczysz na podstawie tabeli zlecenie.
Nie podałeś jak naprawdę te tabele wyglądają, właściwie to nie wiadomo nawet po co te wszystkie łączenia, skoro i tak informacji o firmach nie potrzebujesz.
Wygląda na to, że wszystko co trzeba wiedzieć masz w tabeli zlecenia i tylko na niej powinieneś operować.

Najwięcej czasu nie będzie pochłaniać wykonanie iluś tam zapytań zwracających po 1 linijce zamiast dużego zwracającego wszystko, tylko liczenie zleceń w obrębie jednego miesiąca. Jeżeli baza będzie duża, to liczenie tego bez indeksów będzie głównym źródłem spowolnień. Proponowałbym utworzenie indeksu na data_z w zleceniach, a w zapytaniach operowanie wyłącznie na porównaniach typu mniejsze, większe, równe itp. Czyli dla podanego zakresu dat do wypisania statystyk jaki sobie użytkownik zażyczy liczysz kolejne miesiące w php, w ten sposób by dla każdego miesiąca już przed złożeniem zapytania mieć dwie ograniczające daty, które możesz porównać bez konieczności formatowania dat w sql. Czyli na przykład gotowe zapytanie dla 04.2011 powinno wyglądać tak:

[SQL] pobierz, plaintext 
SELECT count(*) AS ilosc_zlecen FROM zlecenie WHERE data_z >=  '2011-04-01'  AND data_z < '2011-05-01'
[SQL] pobierz, plaintext 

Jeśli jednak upierasz się, by wykonać tylko jedno zapytanie, to musisz albo tworzyć tymczasową tabelę w procedurze, albo zdenormalizować trochę swoją bazę, wykorzystując dodatkową tabelę miesiace.

Ten post edytował pmir13 6.05.2011, 09:12:01