Forum PHP.pl > [MySQL] Wynik zapytania uzależniony od czasu powstania rekordu

Pomoc - Szukaj - Użytkownicy - Kalendarz

Pełna wersja: [MySQL] Wynik zapytania uzależniony od czasu powstania rekordu

Forum PHP.pl > Forum > Bazy danych > MySQL

Sokrates

2.11.2008, 13:21:22

Witam, mam taki problem,

potrzebuje z bazy news'ów wyciągnąć 5 rekordów (odpowiednio z dnia dzisiejszego,
z dnia wczorajszego, 2 dni do tyłu, 8 dni do tyłu, i 31 dni do tyłu ).

W bazie znajduje się kolumna 'dataadd' w formacie timestamp, oraz 'timeadd' w formacie INT
która trzyma datę dodania newsa.

Czy jest możliwość wyświetlenia tych wyników w jednym zapytaniu?
Tzn. otrzymania 5 rekordów w kolejności , news z dnia dzisiejszego, news z dnia wczorajszego,
news z dwóch dni do tyłu, news z ośmiu dni do tyłu i news z trzydziestu jeden dni do tyłu.
I żeby był to pierwszy news dodany w danym dniu.

Jakoś nie mogę sobie tego zapytania SQL wyobrazić, ma ktoś jakiś pomysł?

Dzięki za podpowiedzi,
Pozdrawiam

nevt

2.11.2008, 17:28:30

[SQL] pobierz, plaintext 
SELECT * FROM `tabela` WHERE DATEDIFF(NOW(), `dataadd`) IN (0, 1, 2, 8, 31) GROUP BY `dataadd`;
[SQL] pobierz, plaintext

melkorm

2.11.2008, 17:39:11

[SQL] pobierz, plaintext 
SELECT *, DATE(dateAdd) AS DATA FROM `test` GROUP BY DATA  HAVING
DATA = DATE(NOW() - INTERVAL 1 DAY) OR DATA = DATE(NOW() - INTERVAL 2 DAY) OR DATA = DATE(NOW() - INTERVAL 8 DAY) OR DATA = DATE(NOW() - INTERVAL 1 MONTH) OR DATA = DATE(NOW()) ORDER BY dateAdd ASC;
[SQL] pobierz, plaintext

Powinno śmigać tak jak chcesz ;]

Respekt dla kolegi powyżej XP 1 linijka > moje 3 xP

chyba trzeba dorzucić order by do zapytania powyżej xp

osiris

2.11.2008, 21:13:55

Rozwiazania obu kolegow sa niepoprawne, a w dodatku przy sporej ilosci rekordow w tabeli powolne.
Proponuje takie rozwiazanie (nie wiem czy jest najszybsze), przy zalozeniu ze nie jest wazne ktorego news z danego dnia chcesz pobrac:

[SQL] pobierz, plaintext 
SET @now = CURRENT_DATE();
(SELECT *, 'now' AS when FROM news WHERE dateadd = @now LIMIT 1  )
UNION
(SELECT *, 'yesterday' AS when FROM news WHERE dateadd = DATE(@now - INTERVAL 1 DAY)  LIMIT 1  )
UNION
(SELECT *, 'the day before yesterday' AS when FROM news WHERE dateadd = DATE(@now - INTERVAL 2 DAY)  LIMIT 1  )
UNION
(SELECT *, 'week before' AS when FROM news WHERE dateadd = DATE(@now - INTERVAL 8 DAY)  LIMIT 1  )
UNION
(SELECT *, 'month before' AS when FROM news WHERE dateadd = DATE(@now - INTERVAL 31 DAY)  LIMIT 1  )
[SQL] pobierz, plaintext

nevt

2.11.2008, 21:37:51

oziris - proszę o uzasadnienie tej wypowiedzi:

Cytat

Rozwiazania obu kolegow sa niepoprawne, a w dodatku przy sporej ilosci rekordow w tabeli powolne.

Przeprowadziłeś jakieś próby? Zwróciły ci niepoprawne wyniki? Jeżeli tak, pokaż je... Zrobiłeś testy wydajności? Jeżeli tak, pokaż je... A jak nic nie zobaczę, to twój post wyląduje w koszu i poleci warn za robienie młodzieży wody z mózgu...

W dodatku sam przedstawiasz rozwiązanie które działa

Cytat

przy zalozeniu ze nie jest wazne ktorego news z danego dnia chcesz pobrac

a kolega Sokrates napisał jak wół

Cytat

I żeby był to pierwszy news dodany w danym dniu.

osiris

2.11.2008, 22:00:27

Cytat(nevt @ 2.11.2008, 22:37:51 )

oziris - proszę o uzasadnienie tej wypowiedzi:
Przeprowadziłeś jakieś próby? Zwróciły ci niepoprawne wyniki? Jeżeli tak, pokaż je... Zrobiłeś testy wydajności? Jeżeli tak, pokaż je... A jak nic nie zobaczę, to twój post wyląduje w koszu i poleci warn za robienie młodzieży wody z mózgu...

Spokojnie. Nie napisalem przyczyny dlaczego zapytania sa niepoprawne gdyz juz kilka razy zwracalem na ten problem uwage w innych tematach i juz mi sie nie chce.
Ale prosze:
http://dev.mysql.com/doc/refman/5.0/en/gro...den-fields.html
a konkretnie ten akapit: "Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same. "

A nie jest wydajne bo uzywane jest grupowanie - a przy duzej ilosci grup (a tu taka bedzie bo dla kazdego dnia bedziesz mial osoba grupe) wydajnosc kuleje - to wiem z wlasnego doswiadczenia.

"a kolega Sokrates napisał jak wół
I żeby był to pierwszy news dodany w danym dniu."
Post czytalem pobieznie i musialem ominac to zadnie. Skoro tak to zostaja dwa rozwiazania: jedno z grupowanie, drugie procedura + kursor.

nevt

2.11.2008, 22:09:57

zwracam tylko uwagę - że jest znacząca różnica między zapytaniem niepoprawnym - czyli takim które zwraca niewłaściwe dane lub zawiera błąd składniowy - a zapytaniem niewydajnym bądź nieoptymalnym.

a co do samej wydajności, to również źle to interpretujesz. zgadzam się, że grupowanie po 100k różnych rekordów będzie niewydajne, ale w moim zapytaniu najpierw klauzula WHERE odfiltruje kilkanaście, maks. kilkadziesiąt rekordów (newsy z 5 dni), a dopiero ten wynik zostanie pogrupowany, a grup będzie raptem pięć. jeżeli bardzo chcesz - to mogę zrobić test który porówna wydajność jednego grupowania kontra unia z 5 selektami na 1M rekordów - zakładasz się o duże piwo które zapytanie okaże się szybsze?

osiris

2.11.2008, 22:25:27

Ok. Zle sie wyrazilem. Oba rozwiazania sa (najprawdopodobniej - bo nie probowalem tego uruchomic) poprawne pod wzgledem skladniowym, ale oba moga zwracac niepoprawne wyniki z powodu niewlasciwego wykorzystania grupowania z ukrytymi polami. Dodatkowo rozwiazanie kolegi melkorma jest malo wydajne z powodu przedstawionego w poprzednim poscie.

W Twoim przypadku zapytanie faktycznie wykona sie szybko, ale niestety zwrocone wyniki beda niepoprawne.

nevt

2.11.2008, 22:51:12

Cytat

ale niestety zwrocone wyniki beda niepoprawne....

no to widzę, że bez małego wykładu się nie obejdzie...

zalecenie, żeby nie stosować konstrukcji z grupowaniem po danym polu, jednocześnie wybierając pola które mogą posiadać różną zawartość w ramach grupy dotyczy sytuacji, gdy nie chcemy utracić informacji przekazywanej z bazy. w naszym przykładzie - gdybyśmy chcieli wyjąć wszystkie newsy z określonej daty to grupowanie po dacie dodania newsa byłoby oczywistym błędem.

ale ponieważ w tym konkretnym przypadku świadomie chcemy pozbyć się nadmiarowych danych, a interesuje nas tylko jeden news przypisany do danej daty - zwrócone wyniki będą jak najbardziej poprawne. silnik MySQL w takim przypadku zwróci po jednym pełnym rekordzie dla każdej daty - pola nie zostaną w żadnym przypadku pomieszane.

dodatkowy bonus przy prawidłowo zaprojektowanej bazie polega na tym, że jeżeli wpisy newsów będą dodawane fizycznie w kolejności takiej jaka wynika z pola `dataadd` (wystarczy, zeby to pole było typu TIMESTAMP i warunek jest spełniony), to rekordy do zwrotu będą wybierane właśnie w tej kolejności. dzięki temu nie musimy martwić się o dodatkowe sortowanie wyników a i wymóg, by dostać newsa pierwszego z dodanych dla każdego dnia również jest spełniony.

dla poparcia, mały cytacik z manula:

Cytat

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:

osiris

3.11.2008, 00:22:43

Nie masz racji. Grupowania z ukrytymi polami powinienes uzywac tylko wtedy jesli wszystkie polu ukryte maja te same wartosci dla danej grupy.

Przykladowo dla tabeli:
pole1, pole2, pole3
---------------------
1 2 100.0
1 2 99.0
2 3 2.0
2 3 120.0
2 3 -50.0

mozesz uzyc zapytania:

[SQL] pobierz, plaintext 
SELECT pole1, pole2, MAX(pole3)
FROM tabela GROUP BY pole1
[SQL] pobierz, plaintext

ale juz dla tabeli
pole1, pole2, pole3
---------------------
1 1 100.0
1 2 99.0
2 3 2.0
2 4 120.0
2 5 -50.0

to samo zapytanie moze zwracac niepoprawne wyniki.

W manualu do MySQL-a jest to wyraznie zaznaczone w dwoch miejscach:
"Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group"
oraz
"This extension assumes that the non-grouped columns will have the same group-wise values. Otherwise, the result is indeterminate."

nevt

3.11.2008, 01:23:54

ja swoje - ty swoje - nie podważam zapisu z manuala... tylko zwróć uwagę, że zwrot "Otherwise, the result is indeterminate." nie oznacza błędnych lub nieprawidłowych danych, oznacza natomiast dane niezdeterminowane przez warunki określone w zapytaniu. ale sam silnik bazy zwraca w takiej systuacji dane zawsze w taki sam, powtarzalny sposób, a konkretnie wybiera z takiego zbioru pierwszy rekord zapisany fizycznie - te dane są spójne, chociaż niepełne. i tu mamy taki zbieg okoliczności, że to co zwraca w tej sytuacji baza - jest dokładnie tym co potrzebujemy... świadome zastosowanie takiej konstrukcji jest jak najbardziej na miejscu. podważyć moją opinię możesz tylko w jeden sposób - podaj zbiór danych dla którego moje zapytanie zwróci BŁĘDNE wyniki... jak takowy znajdziesz postawię ci dużą wódkę i wszystko odszczekam.

pozdrawiam, idę spać

FanFataL

3.11.2008, 02:03:15

Cytat("nevt")

Oh ah uh, aż mnie krew zalewa widząc unoszących się pychą "wielkich" moderatorów forum php.pl (już wiem czemu się tu nie logowałem przez 2 lata).

@nevt - Twoje zapytanie na pierwszy rzut oka wygląda dobrze i bardzo mi się spodobało jak je pierwszy raz zobaczyłem ze względu na wydajność (dodajemy indeks na dataadd i śmiga ładnie pięknie).
Ale ...
- po pierwsze: opieranie się na danych zwracanych przez silnik mysqla w kolejności fizycznego zapisu na dysku jest ryzykowne, nieintuicyjne i nieprofesjonalne, a przede wszystkim "niezdeterminowane".
- po drugie: chyba pomyliłeś się w grupowaniu, ponieważ grupując po timestapmie dostaniesz więcej niż 5 rekordów (chciałeś chyba grupować po dacie bądź po datediff co?)

Cytat("nevt|)

podaj zbiór danych dla którego moje zapytanie zwróci BŁĘDNE wyniki... jak takowy znajdziesz postawię ci dużą wódkę i wszystko odszczekam.

Kod

INSERT INTO `test` VALUES (1, 'news 1', '2008-10-03 01:50:27');
INSERT INTO `test` VALUES (2, 'news 2', '2008-10-26 01:49:15');
INSERT INTO `test` VALUES (3, 'news 3', '2008-11-01 11:51:21');
INSERT INTO `test` VALUES (4, 'news 4', '2008-11-01 01:51:34');
INSERT INTO `test` VALUES (5, 'news 5', '2008-11-02 11:50:06');
INSERT INTO `test` VALUES (6, 'news 6', '2008-11-02 01:50:06');
INSERT INTO `test` VALUES (7, 'news 7', '2008-11-03 01:50:13');
INSERT INTO `test` VALUES (8, 'news 8', '2008-11-03 01:50:13');

Lubie finlandie żurawinową

Dla Twojego zapytania zwraca 7 wierszy, a nie 5 jak powinno, a nawet jeśli traktować że ztym TIMESTAMP-em przy grupowaniu w napływie emocji i euforii zapomniałeś grupować po dacie (dniu) to i tak zwraca 5 rekordów ale i wtedy dla daty 2008-11-01 oraz 2008-11-02 zwraca NIE pierwsze newsy.

@osiris - Twoje zapytanie z UNION-em będzie tu najlepszym rozwiązaniem aczkolwiek dodaj w każdej grupie sortowanie po dacie dodania newsa

Pozdrawiam

...

nevt

3.11.2008, 11:44:57

witam.

FanFatal - masz rację - przeoczyłem ten szczegół i zasugerowany nazwą założyłem że pole `dataadd` to to tylko data dodania newsa, bez czasu.

Masz też rację, że w cytowanym fragmencie trochę mnie poniosło - efekt przemęczenia i złego nastroju - za co przepraszam kolegę osisris'a.

Przedstawiam skorygowaną wersję swojego zapytania (tu, dzieki podzapytaniu, zarówno wyniki jak ich kolejność są jednoznacznie zdeterminowane) :

[SQL] pobierz, plaintext 
SELECT * FROM (SELECT * FROM `news` WHERE DATEDIFF(NOW(), `dataadd`) IN (0, 1, 2, 8, 31) ORDER BY `dataadd`) AS tmp GROUP BY DATE(`dataadd`);;
[SQL] pobierz, plaintext

a wracając do wydajności i optymalizacji. pozwoliłem sobie wygenerować bazę testową z losowymi danymi - ok. 100k newsów, średnio po 300 na dzień dla całego 2008 roku... oraz przetestować czas wykonania 100 zapytań w obu wersjach (grupowanie przedstawione powyżej, oraz 5x union z podzapytaniami w wersji osiris'a), oto wyniki:

1. bez indeksu na polu 'dataadd'
1.1. wersja z grupowaniem: 9081ms, średnia = 90.81ms, prędkość = 11 wywołań/s
1.2. wersja z union: 31655ms, średnia = 316.55ms, prędkość = 3 wywołań/s

2. z indeksem na polu 'dataadd'
2.1. wersja z grupowaniem: 21449ms, średnia = 214.49ms, prędkość = 5 wywołań/s
2.2. wersja z union: 31719ms, średnia = 317.19ms, prędkość = 3 wywołań/s

i kto tu robi z kogo wariata? nie mam nic więcej do dodania...

pozdrawiam.

Sokrates

3.11.2008, 16:51:13

Dobra nie będę się wtrącać :-) bo widzę że to nie mój poziom.
generalnie zaptanie z UNION nie udało mi się odpalić pewnie gdzieś popełniłem literówkę.

Zapytanie z gr. (nie wiem czy dobrze) ale trochę zmieniłem

[SQL] pobierz, plaintext 
SELECT * FROM `tabela` WHERE DATEDIFF(NOW(), `dataadd`) IN (0, 1, 2, 8, 31) GROUP BY DATEDIFF(NOW(), `dataadd`);
[SQL] pobierz, plaintext

I wyświetlił mi dokładnie po jednym rekordzie z dnia.

Ale nie do końca o to mi chodziło...
Może źle się na początku wyraziłem.

Mam 2 tabele jedna trzyma tak jakby menu newsów -> 'dziesiaj' , 'wczoraj' , 'w tym tygodniu' , 'w tym miesiacu' , 'archiwum'

a w drugiej tabeli mam te newsy które są filtrowane po dacie.
Obie tabele nie są ze sobą powiązane.

i teraz najlepiej w jednym zapytaniu chce wyświetlić menu dla newsów.

[SQL] pobierz, plaintext 
SELECT * FROM kategoria WHERE kategory = 'news'
[SQL] pobierz, plaintext

Co daje wynik:
'dzisiaj' , 'wczoraj' , 'w tym tygodniu' , 'w tym miesiącu' , 'archiwum'

I teraz bym chciał jeszcze w tym jednym zapytaniu aby pobrał po jednym z newsie dla każdej kategorii np tym wywołaniem co napisał
nevt, lekko zmienionym

[SQL] pobierz, plaintext 
SELECT * FROM `tabela` WHERE DATEDIFF(NOW(), `dataadd`) IN (0, 1, 2, 8, 31) GROUP BY DATEDIFF(NOW(), `dataadd`);
[SQL] pobierz, plaintext

Ale podane przykłady zakładają że w danym dniu jest news
a generalnie kategorie odnoszą sie do dziś -> 0, wczoraj -> 1, w tym tygodniu -> od 2 do 7, w tym miesiącu -> od 8 do 31,
i archiwalne -> powyżej 31

Czy da rade jakoś wydajnie połączyć te zapytania?
Czy lepiej zrobić to w 2 zapytaniach i będzie wydajniej i lepiej...

Dzięki, za pomoc....

osiris

3.11.2008, 17:03:34

Cytat

Przedstawiam skorygowaną wersję swojego zapytania (tu, dzieki podzapytaniu, zarówno wyniki jak ich kolejność są jednoznacznie zdeterminowane) :

[SQL] pobierz, plaintext 
SELECT * FROM (SELECT * FROM `news` WHERE DATEDIFF(NOW(), `dataadd`) IN (0, 1, 2, 8, 31) ORDER BY `dataadd`) AS tmp GROUP BY DATE(`dataadd`);;
[SQL] pobierz, plaintext

Takie zapytanie tez nie gwarantuje ze otrzymane wyniki beda zgodne z oczekiwanymi!!

Co do wydajnosci to z ciekawosci sam przeprowadzilem testy i otrzymalem wyniki calkowicie odwrotne. Testy wykonalem na bazie employees-db (https://launchpad.net/test-db/ jest to baza na ktorej testowane sa kolejne wersje MySQL-a przed wydaniem) na tabeli salaries.
Wersja MySQL: 5.0.67 dostarczana z systemem Ubuntu 8.10.
Sprzet to Athlon X2 64 BE-2350 (2x 2.1Ghz), 3GB DDR2, 2xSATAII 320GB - RAID1

Oto zapytania jakich uzywalem do testowania:
zapytanie z uzyciem grupowania:

[SQL] pobierz, plaintext 
SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date IN ('2002-07-21', '2001-08-13', '2000-02-20', '1999-12-10', '1998-05-04') GROUP BY from_date
[SQL] pobierz, plaintext

zapytanie z uzyciem UNION:

[SQL] pobierz, plaintext 
(SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date = '2002-07-21' ORDER BY from_date ASC LIMIT 1  )
UNION ALL
(SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date = '2001-08-13' ORDER BY from_date ASC LIMIT 1  )
UNION ALL
(SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date = '2000-02-20' ORDER BY from_date ASC LIMIT 1  )
UNION ALL
(SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date = '1999-12-10' ORDER BY from_date ASC LIMIT 1  )
UNION ALL
(SELECT SQL_NO_CACHE * FROM `salaries` WHERE from_date = '1998-05-04' ORDER BY from_date ASC LIMIT 1  )
[SQL] pobierz, plaintext

Wyniki (wartosci usrednione, zbierane dopiero po ustabilizowaniu wynikow):
silinik tabeli: myisam (klucze oryginalne, sztuczny klucz glowny, bez indeksow - te same czasy):
uniony: 0.0135 s
grupowanie: 0.95 s

silinik tabeli: myisam (klucz na polu from_date)
uniony: 0.0014 s
grupowanie: 0.0165 s

silinik tabeli: innodb (klucze oryginalne)
uniony: 0.0280 s
grupowanie: 3.2 s

silinik tabeli: innodb (klucz na polu from_date)
uniony: 0.0015 s
grupowanie: 0.29 s

Jak widac w kazdym przypadku czas wykonania zapytan z UNIONami jest kilkakrotnie mniejszy od zapytan grupujacych.
Jesli ktos nie wierzy to niech sam wykona testy. Wszystkie potrzebne informacje sa podane.

Sokrates

5.11.2008, 10:43:58

Widzę że temat odbieg znacząco od mojego pytania....
To co tu takie poważne rozmowy prowadzicie, a teraz cisza...

LordRobert1

2.01.2009, 13:29:28

nie wiem czy Ci sie przyda ale sproboj moze jeszcze taki warunek date_placed BETWEEN '2000-01-01' AND '2000-06-31' gdzie nastepnie pod podane zakresy wrzucic podzapytania

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.