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

[skowron-line]

A dlaczego nie grupujesz po dacie

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

[bpskiba]

A dlaczego nie grupujesz po dacie

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

Witaj samo grupowanie nie wystarczy gdyż chcę wyszukać dla każdej kartoteki tylko jedną datę - najpóźniejszą, więc musi być SELECT max(data)...... a wtedy grupowanie po dacie traci sens.

Dobrze myślę?

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 Sortowanie po dacie oraz id_stanu przydało się
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

[pmir13]

Jeśli chodzi o rozwiązanie to proponuję:

[SQL] pobierz, plaintext 
ALTER TABLE stan ADD INDEX towary_data( id_towaru, id_magazynu, jedn, dest, DATA );
[SQL] pobierz, plaintext 

oraz

[SQL] pobierz, plaintext 
SELECT  di.id_stanu, di.DATA, di.id_towaru, di.ilosc, di.jedn, di.dest
FROM
  ( SELECT  id_towaru, jedn, dest, MAX(DATA) AS DATA
    FROM    stan
    WHERE DATA < '2012-03-03'
    AND id_magazynu = 1
    GROUP BY id_towaru, jedn, dest
  ) dd
JOIN stan di
ON di.id_stanu = ( SELECT MAX(id_stanu)
                   FROM    stan ds
                   WHERE   ds.id_towaru = dd.id_towaru
		      AND  ds.id_magazynu = 1
		      AND  ds.jedn = dd.jedn
		      AND  ds.dest = dd.dest
                      AND  ds.DATA = dd.DATA  )
 
[SQL] pobierz, plaintext 

Ten indeks wygląda groźnie, ale jest konsekwencją takiej a nie innej struktury danych. Nie wiem jaki czas wykonania zapytania byłby akceptowalny, ale nie sądze by przy tym rozmiarze bazy przekraczał 1s, poza tym pole do optymalizacji jest niewielkie, raczej ciężko będzie znaleźć coś, co da poprawne wyniki w zauważalnie krótszym czasie.

[bpskiba]

oooooooooo o
To zapytanie wygląda bardzo zachęcająco
Moje zapytanie trwało 4-5 s i to było nie do przyjęcia. Potrzebuję nieco czasu na testy i przemyślenia, ale pierwsze efekty wyglądają doskonale.
Wielkie dzięki za pomoc

Piotrek