Limit in Join Abfrage

V

vodanet

Benutzer
Beiträge
18
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).
SQL: 
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
 
Werbung:
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:

Code: 
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.
 
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.
 
Werbung:
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.
 

Ähnliche Themen

L
Unterschiedlich strukturierte Daten kombinieren
Antworten
6
Aufrufe
265
ukulele
ukulele
Kampfgummibaerlie
Network Address Types
Antworten
3
Aufrufe
566
akretschmer
akretschmer
B
INSERT INTO mit vorheriger Abfrage
Antworten
2
Aufrufe
458
dgsoft
D
I
In Where Bedingung "not like" & "and" kombinieren
Antworten
4
Aufrufe
127
IchHH
I
L
CTE Abfrageergebnis kombinieren
Antworten
4
Aufrufe
485
dabadepdu
dabadepdu
Zurück
Oben