Limit in Join Abfrage

[vodanet]

Hallo,
ich habe 2 Tabellen.
Tabelle 1 - hosts -> enthält Anzahl x von Geräten
- id *
- name
- ip
- ...
Tabelle 2 - statuses -> enthält die Anzahl aller Änderungen der Geräte aus Tabelle 1
- id *
- host_id
- values
-....

Ich möchte in einer Übersicht gern alle Geräte (aus Tabelle 1) und eine pro Gerät limitierte Anzahl Status (aus Tabelle 2).

SELECT id, updated_at, hostname, mac, ip, updated_at FROM (SELECT created_at, value , host_id from public.statuses limit 3) as statuses RIGHT OUTER JOIN (SELECT * from public.hosts ORDER BY updated_at) as hosts USING (host_id) ORDER BY ip limit 20;

Es wird limitiert, jedoch nur die ersten Einträge aus statuses.
Also für das erste Gerät werden mir korrekt 3 Einträge aus statuses angezeigt und dann aber für die anderen Geräte immer nur 1 Eintrag (obwohl mehr vorhanden sind).
Wo liegt mein Fehler? Oder was wäre eine bessere Lösung?
Vielen Dank für eure Hilfe und Viele Grüße

 

[castorp]

Du brauchst zuerst eine Möglichkeit 3 Einträge pro host zu identifizieren. Das geht am leichtesten via row_number(). Wenn man dann eine Restriktion auf diese Nummer in die JOIN Bedingung aufnimmt, bekommt man 3 Datensätze pro Host:

select h.*, st.created_at, st.value
from hosts h
  left join (
    select created_at,
           value,
           host_id,
           row_number() over (partition by host_id order by created_at desc) as rn
    from public.statuses
  ) st on st.host_id = h.host_id and rn <= 3
order by h.updated_at

Die Abfrage liefert die drei neuesten Einträge aus statuses weil absteigend nach created_at sortiert wird.

Um zu verstehen, was da genau passiert, lass am besten das innere SELECT mal alleine laufen.

 

[vodanet]

Habe vielen Dank!!
Dieses Vorgehen mit

 row_number()

war mir unbekannt. Danke nochmals!!!

 

[vodanet]

Um zu verstehen, was da genau passiert, lass am besten das innere SELECT mal alleine laufen.

Habe ich gemacht. Danke. Jetzt merke ich es mir bestimmt.

 

[castorp]

row_number() war mir unbekannt. Danke nochmals!!!

3.5. Window Functions

3.5. Window Functions A window function performs a calculation across a set of table rows that are somehow related to the …

www.postgresql.org

 

[akretschmer]

die Antwort von @castorp hat den minimalen Nachteil, daß aufgrund der row_number()-Funktion und NACHFOLGENDEM Limit u.U. ein sehr großes Zwischenresult anfällt, welchen dann später zum größten Teil wieder entsorgt wird. Das ist also relativ teuer.

Besserer Ansatz geht über LATERAL JOIN. Ich hab folgendes Beispiel mit Topics und Postings und Anzeige der N aktuellsten Postings je Topic bei meinen Kunden zur Demo gehabt:

test=# create table topics(id serial primary key, topic text);
CREATE TABLE
test=*# create table postings(id serial primary key, topic int references topics, posting text);
CREATE TABLE
test=*# insert into topics select t, format('topic %s', t) from generate_series(1, 5) t;
INSERT 0 5
test=*# insert into postings (topic, posting) select t, format('posting %s in topic %s',p,t) from generate_series(1, 10) p cross join generate_series(1,5) t;
INSERT 0 50

--
-- und dann
--

select t.*, p.* from topics t left join lateral (select * from postings p where topic=t.id order by p.id desc limit 3) p on true;


So etwa selbe Aufgabenstellung, oder? Wenn Du je Gerät sehr viele Statusmeldungen hast, aber nur einen kleinen Teil anzeigen willst, dürfte das die bessere Lösung sein.

 

[vodanet]

Ah - vielen Dank! Diesen Ansatz werde ich auch testen.
Ja - ich habe eine "Geräte" Tabelle mit momentan 35 - später evtl. hunderten / wenigen tausend Geräten und zu jedem Gerät wird der Status gespeichert und diese ist entsprechend groß. Es gib noch (momentan) zwei weitere Tabellen, die ich aber dann mit LEFT join einbinde.
Das größte Performance Problem war / ist die letzten/neuesten Status zu allen Geräten abzufragen.