Niby proste a nie do końca, złączenie dwóch tabel z 'max' Opcje

[Sedziwoj]

Tzn. już coś sklepałem, ale bym chciał abyście przedstawili jakieś dobre wydajnościowo zapytania, bo ja niewiele jeszcze z tej dziedziny umiem :|

Sprawa jest taka są dwie tabele, pierwsza
id | user | e_mail
druga
id | user_id | date | news
(mniej więcej)

No i chciał bym wybrać dla każdego user'a jego najnowszy (po date) news (dobrze jakby nie wyświetlało tych user'ów co nie mają news'ów)

Dodam jeszcze, że date nie jest z systemu i może być wystawione 'wstecz', czyli ostatni 'news' nie musi być najnowszym.

P.S. Przeglądałem już trochę forum i znalazłem coś co by mogło działać (nie spr.) ale mi nie chodzi aby działało, tylko aby robiło to w miarę możliwości optymalnie.

EDIT poprawiona lit. w tytule

Ten post edytował Sedziwoj 13.04.2007, 23:30:56

[devnul]

[SQL] pobierz, plaintext 
SELECT * FROM pierwsza JOIN druga ON pierwsza.id=druga.user_id WHERE 1=1 GROUP BY pierwsza.id ORDER BY druga.date DESC
[SQL] pobierz, plaintext 

pisane z palca - jak sie nie dziabnołem to zadziała

//btw - tytuł ma chyba mało wspólnego z postem?

Ten post edytował devnul 14.04.2007, 00:19:48

[Sedziwoj]

Wiem że tytuł niezbyt odpowiedni, ale jakoś nie mam pomysłu na lepszy.

Co do Twojego zapytania, po co "WHERE 1=1"? Czy przypadkiem jak jest "GROUP BY pierwsza.id" to ta kolumna musi być w wyniku, ale na resztę nałożyć trzeba funkcje agregujące.

I do tego nie zrozumiałeś o co chodzi, pewnie zawile opisałem...
Bo dla każdego user'a jego ostatni post, ostatni to znaczy ten z najnowszą datą (jak jest kilka o tej samej dacie i jest to ta najnowsza, to wtedy i tylko wtedy może być dla danego user'a podane więcej niż jeden rekord wyników)

[DeyV]

Najprostsze rozwiązanie - lista autorów najnowszych newsów

[SQL] pobierz, plaintext 
SELECT 
 DISTINCT( user_id ) , date
FROM newsy LEFT JOIN users ON ( users.id = newsy.user_id )
ORDER BY DATE DESC
[SQL] pobierz, plaintext 

Aby jednak osiągnąć efekt wybrania większej ilości "ostatnich newsów" danego usera, konieczne byłoby skorzystanie z SubQuery, i nie ukrywam - znacznie utrudni i spowolni zapytanie.

[Sedziwoj]

I działa niepoprawnie, to wiem testowałem (no chyba że ja coś skopałem).
Ponieważ DISTINCT nie patrzy na kolejność posortowanej tablicy, tylko na swoją wewnętrzną kolejność, która odpowiada kolejności dodania/modyfikacji, a w moim konkretnym przypadku takie coś może zajść (tu dałem 'obciętą' strukturę).

No i doczytałem z manuala:

Note that the “first row” of a set is unpredictable unless the query is sorted on enough columns
to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing
occursafterORDER BY sorting.)

Czyli tak jak napisałem, który jest 'pierwszy' zależy od wewnętrznej struktury.

[DeyV]

nie - nie do końca masz rację.
Sprawdź taką konstrukcję:

[SQL] pobierz, plaintext 
SELECT
DISTINCT ON ( user_id ) user_id  , date
FROM newsy LEFT JOIN users ON ( users.id = newsy.user_id )
ORDER BY user_id, DATE  DESC
[SQL] pobierz, plaintext 

[Sedziwoj]

Jak mówię że testowałem... no chyba że "ORDER BY user_id" coś zmienia, ale nie sądzę.
Jeśli masz już coś w bazie to zrób UPDATE któregoś ze starszych i zobaczysz że ten będzie jako 'najnowszy', co oczywiście nie będzie prawdą.
Do tego jak działanie jest zależne od czegoś innego niż ja chcę, to nie będę tego stosować.

I prosił bym o nie upieranie się przy swoim rozwiązaniu, bo DISTINCT ON odpada, przez to że jest nieprzewidywalny. (w manual'u jest zaznaczone również, że nie jest częścią SQL i doradzane jest użycie podzapytania)

[DeyV]

Oj... Czytanie ze zrozumieniem nawal jednak .

CREATE TABLE test
(
id serial NOT NULL,
imie character varying,
data date DEFAULT now(),
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

INSERT INTO test (id, imie, data, "order") VALUES (3, 'dawid', '2007-04-15', 3);
INSERT INTO test (id, imie, data, "order") VALUES (4, 'Marek', '2007-04-15', 2);
INSERT INTO test (id, imie, data, "order") VALUES (1, 'dawid', '2007-04-15', 4);
INSERT INTO test (id, imie, data, "order") VALUES (2, 'dawid', '2007-04-15', 1);

Zapytanie:

[SQL] pobierz, plaintext 
SELECT DISTINCT ON ( imie ) imie, *
FROM test ORDER BY imie, "order" DESC
[SQL] pobierz, plaintext 

Wynik:

Kod

"dawid";   1;"dawid";"2007-04-15";4
"Marek";   4;"Marek";"2007-04-15";2

Wniosek?
znaczenie ma ON w Distinct oraz właśnie pole imie w klauzuli ORDER BY (czyli pole DISTINCT'owane)


ps.
Przykład zastosowania właśnie tej formu disinct w manualu - http://www.postgresql.org/docs/8.2/interac...sql-select.html - zakładka DISTINCT Clause

[Sedziwoj]

Odpalam co podałeś i

[SQL] pobierz, plaintext 
ERROR:  COLUMN "order" of relation "test" does NOT exist
[SQL] pobierz, plaintext 

Tak więc dałem:

[SQL] pobierz, plaintext 
INSERT INTO test (id, imie, DATA) VALUES (3, 'dawid', '2007-04-15');
INSERT INTO test (id, imie, DATA) VALUES (4, 'Marek', '2007-04-16');
INSERT INTO test (id, imie, DATA) VALUES (1, 'dawid', '2007-04-17');
INSERT INTO test (id, imie, DATA) VALUES (2, 'dawid', '2007-04-15');
[SQL] pobierz, plaintext 

teraz:

[SQL] pobierz, plaintext 
SELECT DISTINCT ON ( imie ) imie, *
FROM test ORDER BY imie DESC
[SQL] pobierz, plaintext 

i wynik:

[SQL] pobierz, plaintext 
"dawid";2;"dawid";"2007-04-15"
"Marek";4;"Marek";"2007-04-16"
[SQL] pobierz, plaintext 

dobrze? nie więc nie wiem dlaczego się upierasz.
(mam PostgreSQL 8.1 ale to nie ma nic do rzeczy)

Zrobiłem parę update'ów

[SQL] pobierz, plaintext 
SELECT * FROM test ORDER BY imie DESC
[SQL] pobierz, plaintext 

wynik:

[SQL] pobierz, plaintext 
1;"dawid";"2007-04-17"
2;"dawid";"2007-04-15"
3;"dawid";"2007-04-19"
4;"Marek";"2007-04-16"
[SQL] pobierz, plaintext 

zapytani, wynik:

[SQL] pobierz, plaintext 
SELECT DISTINCT ON ( imie ) imie, *
FROM test ORDER BY imie DESC;
"dawid";1;"dawid";"2007-04-17"
"Marek";4;"Marek";"2007-04-16"
[SQL] pobierz, plaintext 

Fajne co?

EDIT łamanie wierszy... ale błędów robię idę spać

Ten post edytował Sedziwoj 16.04.2007, 00:50:47

[DeyV]

a sorki - mój błąd.

Przez to, że użyłem słowa kluczowego jako nazwy kolumny, export tabeli chyba jej nie wyłapał. Dziwne, ale cóż - może z jakiegoś innego powodu znikła.

Oczywiście, że zapytanie

[SQL] pobierz, plaintext 
SELECT DISTINCT ON ( imie ) imie, *
FROM test ORDER BY imie DESC
[SQL] pobierz, plaintext 

zwróci losowe wyniki. Bo i dlaczego nie?

Zrób tak

[SQL] pobierz, plaintext 
SELECT DISTINCT ON ( imie ) imie, *
FROM test ORDER BY imie, DATA DESC
[SQL] pobierz, plaintext 

i u mnie jest OK.

[Sedziwoj]

Sprawdzę, ale i tak nie użyję, bo to jest coś co jest niepewne, a ja nie chcę mieć czegoś co się może zmienić.
Więc jak nawet działa, to fajnie, ale do użytku się nie nadaje. Może mam zbyt wysokie wymaganie, ale nie będę używać czegoś, co może być nieprzewidywalne.

[DeyV]

Twoja sprawa, ale...

Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the "first row" of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)

Prawdę mówiąc - podaję tylko takie rozwiązanie, bo nie znam innego. Jeśli coś znajdziesz - pokaż - jestem bardzo ciekaw, jak załatwić to subzapytaniem, bez użycia tej klauzuli.

[Sedziwoj]

A co ja wyklepałem... właśnie nie do końca mi się podoba, dlatego pytałem. (jak już pytałem coś takiego miałem)

[SQL] pobierz, plaintext 
SELECT * FROM (SELECT max(date), user_id FROM druga GROUP BY user_id) 
AS s LEFT JOIN pierwsza ON (pierwsza.id=s.user_id)
[SQL] pobierz, plaintext 

Tak mniej więcej, no i robi co chciałem, ale czy to jest najlepsze rozwiązanie... wątpiem.

[osiris]

hmm, a nie prosciej tak:

[SQL] pobierz, plaintext 
SELECT t1.id, t1.user, t2.news, t2.date
FROM t1 LEFT JOIN t2 ON t2.user_id = t1.id
GROUP BY t2.user_id
HAVING t2.date = MAX(t2.date)
[SQL] pobierz, plaintext 

Ten post edytował osiris 19.07.2007, 13:56:01