Problem ze złożonymi zapytaniami i podzapytaniami Opcje

[Helios]

Witam

Mam problem z napisaniem zapytania do bazy MySQL. Mecze sie z tym juz 2 dni i nie widac konca.

Mam 2 tabele zawierajace wpisy uzytkownikow:

Articles (Id, AuthorId, Votes, Rate)
Texts (Id, AuthorId, Votes, Rate)

Pole AuthorId to odeslanie do Id z tabeli uzytkownikow. Pola Votes, Rate mam do wyliczania sredniej ocen oddanych na te publikacje obliczam przez zaokraglenie Rate/Votes (przy glosowaniu zwiekszam Votes o 1 i dodaje ocene do Rate).

Moj problem polega na tym, ze w jednym zapytaniu chce:

- chce pobrac dane z tabeli uzytkownika (Id, Login, etc.)
- posortowac uzytkownikow ze wzgledu na srednia dodanych przez nich publikacji (srednia razem z obu tabel)
- zbior wynikow ograniczyc tylko do tych uzytkownikow, ktorzy maja co najmniej 5 publikacji (obojetnie z ktorej tabeli)

Czyli staram sie wykombinowac cos w stylu:

[SQL] pobierz, plaintext 
SELECT
Users.Id, Users.Login,
(SELECT SUM(Articles.Rate) FROM Articles WHERE Articles.AuthorId=Users.Id) AS ArtRate,
(SELECT SUM(Articles.Votes) FROM Articles WHERE Articles.AuthorId=Users.Id) AS ArtVotes,
(SELECT SUM(Texts.Rate) FROM Texts WHERE Texts.AuthorId=Users.Id) AS TxtRate,
(SELECT SUM(Texts.Votes) FROM Texts WHERE Texts.AuthorId=Users.Id) AS TxtVotes
FROM (SELECT Users.Id, Users.Login FROM Users WHERE (SELECT COUNT(*) FROM Articles WHERE Articles.AuthorId=Users.Id)>5 OR (SELECT COUNT(*) FROM Texts WHERE Texts.AuthorId=Users.Id)>5) AS Users
ORDER BY ((ArtRate+TxtRate)/(ArtVotes+TxtVotes))
[SQL] pobierz, plaintext 

No i nie dziala i wiele innych kombinacji podobnego kodu tez nie dziala. ORDER BY nie widzi tak jakby tych pobranych pol.

Czy ktos moze mi pomoc, ewentualnie podeslac linka do jakiejs publikacji gdzie znajde informacje na temat popelnianego bledu?

Z gory dzieki!

Pozdrawiam

[phpion]

Problem leży pewnie w tym, że w klauzuli ORDER BY stosujesz aliasy kolumn, a z tego co się orientuję takie coś nie przejdzie. Nie wiem czy we wszystkich wersjach MySQL tak nadal jest ale wcześniej taki problem występował. Poza tym - w Twoim przypadku zdecydowanie lepiej złamać postać normalną i mimo wszystko wejść w redundację danych. Ja bym utworzył osobne kolumny w tabeli, które będą przechowywać wartości, które aktualnie obliczasz podzapytaniami. Dodatkowo należałoby odpalić triggera na wszystkich operacjach (INSERT, UPDATE, DELETE) aktualizującego pola dla danego rekordu. Wydajność powinna zwiększyć się znacząco i unikniesz problemów z ORDER BY.

[Helios]

Hm, nie wiem czy dobrze zrozumialem Twoje intencje, ale u mnie chyba to nie przejdzie, gdyz opisany problem to tylko jeden z wielu. Pozniej bede musial prowadzic statystyki i sortowac uzytkownikow z najwyzsza srednia publikacji wg. daty ich dodania. Czyli np. statystyki uzytkownikow z najwyzszymi srednimi za miesiac 02/2008.
Moze ma ktos jeszcze jakies pomysly. Skoro nie moge stosowac aliasow to jak to rozwiazac?

[nevt]

Problem leży pewnie w tym, że w klauzuli ORDER BY stosujesz aliasy kolumn, a z tego co się orientuję takie coś nie przejdzie.

Jeżeli MySQL ver > 5.0 to nieprawda - aliasy przejdą ....

No i nie dziala i wiele innych kombinacji podobnego kodu tez nie dziala. ORDER BY nie widzi tak jakby tych pobranych pol.

Czyli zapytanie działa (wyświetla ci prawidłowe dane) tylko źle posortowane?
Czy całe zapytanie jest do niczego (skąd zatem wiesz, że ORDER BY nie działa)?
Tak czy inaczej, ja bym spróbował czegoś w rodzaju (piszę z pamięci, więc wybacz ew. potknięcia):

[SQL] pobierz, plaintext 
SELECT Users.Id, Users.Login, COUNT(Articles.Id) AS ArtCnt, SUM(Articles.Rate) AS ArtRate, SUM(Articles.Votes) AS ArtVotes, COUNT(Texts.Id) AS TxtCnt, SUM(Texts.Rate) AS TxtRate, SUM(Texts.Votes) AS TxtVotes FROM Users LEFT JOIN Articles ON Users.Id = Articles.AuthorId LEFT JOIN Texts ON Users.Id = Texts.Author.Id GROUP BY Users.Id WHERE (ArtCnt + TxtCnt) > 5 ORDER BY ((ArtRate + TxtRate) / (ArtVotes + TxtVotes));
[SQL] pobierz, plaintext 

Ten post edytował nevt 19.02.2008, 15:10:24

[Helios]

Ok, poradzilem sobie z tym. Wniosek z tego, ze moj problem polegal na tym, ze wykorzystywalem aliasy w WHERE i ORDER BY, wiec podzapytania musialem przerzucic bezposrednio w te klauzule. Dodatkowo MySQL nie wyliczal mi poprawnie sredniej, gdyz w niektorych publikacjach liczba glosow i srednia byly rowne 0 i MySQL zwracal NULL, a ciekawy test SELECT 1+NULL dal mi wartosc NULL i tak o to wpadlem na takie rozwiazanie:

[SQL] pobierz, plaintext 
SELECT
Users.Id, Users.Name,
IFNULL(ROUND((SELECT SUM(Articles.Rate)/SUM(Articles.Votes) FROM Articles WHERE Articles.AuthorId=Users.Id), 2),0.00) AS ArticlesRate,
IFNULL(ROUND((SELECT SUM(Texts.Rate)/SUM(Texts.Votes) FROM Texts WHERE Texts.AuthorId=Users.Id), 2),0.00) AS TextsRate
FROM Users WHERE ((SELECT COUNT(*) FROM Articles WHERE Articles.AuthorId=Users.Id)+(SELECT COUNT(*) FROM Texts WHERE Texts.AuthorId=Users.Id))>=2
ORDER BY (IFNULL((SELECT SUM(Articles.Rate) FROM Articles WHERE Articles.AuthorId=Users.Id),0)+IFNULL((SELECT SUM(Texts.Rate) FROM Texts WHERE Texts.AuthorId=Users.Id),0))
/
(IFNULL((SELECT SUM(Articles.Votes) FROM Articles WHERE Articles.AuthorId=Users.Id),0)+IFNULL((SELECT SUM(Texts.Votes) FROM Texts WHERE Texts.AuthorId=Users.Id),0))
DESC
[SQL] pobierz, plaintext 

No i tak na marginesie, wybranie uzytkownikow i posortowanie wg. aktywnosci (ilosc publikacji w ciagu dnia od dnia rejestracji):

[SQL] pobierz, plaintext 
SELECT
Users.Id, Users.Name,
ROUND((SELECT COUNT(*) FROM Articles WHERE Articles.AuthorId=Users.Id)/DATEDIFF(CURRENT_DATE(), Users.RegistrationDate),2) AS ArticlesPerDay,
ROUND((SELECT COUNT(*) FROM Texts WHERE Texts.AuthorId=Users.Id)/DATEDIFF(CURRENT_DATE(), Users.RegistrationDate),2) AS TextsPerDay
FROM Users ORDER BY
ROUND(((SELECT COUNT(*) FROM Articles WHERE Articles.AuthorId=Users.Id)+(SELECT COUNT(*) FROM Texts WHERE Texts.AuthorId=Users.Id))/DATEDIFF(CURRENT_DATE(), Users.RegistrationDate),2) DESC
[SQL] pobierz, plaintext 

Pozdrawiam

Ten post edytował Helios 19.02.2008, 16:14:02