funkcja agregująca i grupowanie Opcje

[bpskiba]

Witam
Mam taki problemik:
Jest sobie tabela, w której trzymane są kartoteki magazynowe

stan:
id_towaru int(11) NOT NULL
id_magazynu int(11) NOT NULL
id_stanu int(11) NOT NULL PRIMARY KEY
data datetime NOT NULL
ilosc double(15,4) NOT NULL
jedn int(20) NOT NULL
dest mediumint(9) NULL

Budowanie poszczególnych kartotek jest proste:

[SQL] pobierz, plaintext 
SELECT DATA, ilosc FROM stan WHERE id_towaru=880 AND dest=6 AND jedn=1 AND id_magazynu=2
ORDER BY DATA DESC, id_stanu DESC
[SQL] pobierz, plaintext 

określenie stanu na dany dzień dla kartoteki też jest proste:

[SQL] pobierz, plaintext 
SELECT ilosc FROM stan WHERE id_towaru=880 AND dest=6 AND jedn=1 AND id_magazynu=2 AND DATA<'2012-03-03 00:00:00'
ORDER BY DATA DESC, id_stanu DESC
LIMIT 1
[SQL] pobierz, plaintext 

Problem zaczyna się gdy chcę wyświetlić stan całego magazynu na żądany dzień.
Proste pytanie wyświetla bzdury:

[SQL] pobierz, plaintext 
SELECT MAX(DATA),ilosc,id_magazynu,jedn,dest,id_towaru FROM stan  
WHERE id_magazynu=1
GROUP BY id_towaru,id_magazynu,jedn,dest
[SQL] pobierz, plaintext 

Częściowo rozwiązałem to następująco:

[SQL] pobierz, plaintext 
SELECT s1.id_towaru,s1.dest,s1.ilosc,s1.jedn
FROM stan s1,(SELECT MAX(DATA) dd,id_stanu,id_towaru,dest,jedn,ilosc 
               FROM stan
               WHERE  DATA<'2012-03-01 00:00:00' AND id_magazynu=1
               GROUP BY id_towaru,dest,id_magazynu,jedn) s2
WHERE  s1.DATA=s2.dd AND s1.id_towaru=s2.id_towaru AND s2.dest=s1.dest AND s1.jedn=s2.jedn
[SQL] pobierz, plaintext 

Jest to wyjątkowo kiepskie rozwiązanie...
Po pierwsze jego czas jego wykonania jest nie do przyjęcia, a po drugie nie uwzględnia drugiego elementu sortowania (w przypadku tej samej wartości datetime sortuję malejąco po id_stanu).


Będę wdzięczny za wszystkie pomysły i sugestie.

Piotrek

[pmir13]

W zapytaniu, które wyświetla bzdury nie możesz polegać na wartości pola ilość, bo nie jest ani w group by ani nie używasz na nim funkcji agregującej. Trudno też oczekiwać stanu całego magazynu na dany dzień skoro tego dnia w zapytaniu nigdzie nie określasz.
Co oznaczają pola jedn i dest?
Ile jest różnych towarów i magazynów?
Skoro potrzebujesz sortować nie tylko po dacie, ale też po id, to czy na pewno data jest typu datetime, jeśli tak to jak często się zdarzają modyfikacje stanu tego samego towaru w tym samym magazynie w ciągu jednej sekundy? Bo taka jest właśnie dokładność datetime.

Ten post edytował pmir13 18.03.2012, 19:12:49

[bpskiba]

W zapytaniu, które wyświetla bzdury nie możesz polegać na wartości pola ilość, bo nie jest ani w group by ani nie używasz na nim funkcji agregującej. Trudno też oczekiwać stanu całego magazynu na dany dzień skoro tego dnia w zapytaniu nigdzie nie określasz.
Co oznaczają pola jedn i dest?
Ile jest różnych towarów i magazynów?
Skoro potrzebujesz sortować nie tylko po dacie, ale też po id, to czy na pewno data jest typu datetime, jeśli tak to jak często się zdarzają modyfikacje stanu tego samego towaru w tym samym magazynie w ciągu jednej sekundy? Bo taka jest właśnie dokładność datetime.

jedn - jednostka (kg,litr,itd. )
dest - przeznaczenie (produkcja własna, sprzedaż i kilka innych)
każda kartoteka ma 4 cechy (id_towaru, id magazynu, jednostka, przeznaczenie)
Towarów jest kilka tysięcy (i rośnie)
Magazynów jest kilkanaście

Magazynierzy wprowadzają towar na stan z datą bierzącą. Możliwa jest więc sytuacja gdy dwóch magazynierów w tej samej sekundzie przyjmie ten sam towar. Prawo Merfy'ego mówi, że jak coś się może sp....ić to kiedyś się sp...oli. Zresztą baza danych już pokazała taki przypadek (IMG:style_emoticons/default/smile.gif) Sortowanie po dacie oraz id_stanu przydało się (IMG:style_emoticons/default/businesssmiley.png)
Kartoteka układa się ok i to zostawmy.
Odnośnie zamego zapytania....
Uwagi niewątpliwie słuszne. Pytanie brzmi jak je rozwiązać. Samego dnia w sposób ostry nie można określić, gdyż towar mógł wpaść na kartotekę dużo wcześniej i nie na każdej kartotece operacje przeprowadza się codziennie.

Piotrek

Ten post edytował bpskiba 18.03.2012, 20:38:06