Schwierigkeiten mit einem SQL-Befehl

[doerflia]

Hi,

ich habe eine DB-Tabelle 'overview' in folgendem Format:

group_id|person_id|value
-------------------------------
0|0|0
0|0|1
0|0|2
0|20|1
0|20|2
7|0|0
9|4|1
9|4|2
...

Das Werte-Paar (group_id, person_id) ist eindeutig und 100 Kombinationen/Gruppierungen kommen vor.
Der Wert von value liegt zwischen 0 und 4. Jede Kombination/Gruppierung hat mindestens einen Wert. Value darf in 'overview' nicht "null" sein.

Meine neue Tabelle 'first_value' grefit für jedes Wertepaar den ersten vorkommenden Wert ab und
besitzt folglich 100 Zeilen.

group_id|person_id|value
--------------------------------
0|0|0
0|20|1
7|0|0
9|4|1
...

Diese Tabelle habe ich mittels folgenden Code erzeugt:
CREATE TABLE first_value AS (
SELECT a.group_id,
a.person_id,
min(a.value_id) AS value_id
FROM public.overview a
GROUP BY a.group_id, a.person_id
ORDER BY a.group_id, a.person_id, value_id);

Nun möchte ich eine Tabelle 'second_value' erstellen, welche ebenfalls über alle Kombinationen/Gruppieren, also 100 Zeilen verfügt. Gibt es für eine Gruppieren keinen zweiten Wert, so soll 'null' in die Datenbankzeile geschrieben werden.

Was ich möchte, wäre also eine Tabelle in der Form:

group_id|person_id|value
--------------------------------
0|0|1
0|20|2
7|0|null
9|4|2
...

Informationstechnisch gesehen:
Wenn der Wert min(a.value_id)+1 existiert, dann ist value=min(a.value_id)+1, ansonsten 'null'.

Meine Versuche brachten falsche Lösungen oder Error-Meldungen.

Entweder verschwanden alle Zeilen mit value = null, also:
group_id|person_id|value
--------------------------------
0|0|1
0|20|2
9|4|2
...
--> Keine 100 Zeilen bzw. Kombinationen/Gruppierungen wie gewollt

oder ein falscher Value wird berechnet, z. B.
group_id|person_id|value
--------------------------------
0|0|1
0|20|2
7|0|1
9|4|2

oder falsche Gruppierungen, z. B. wieder die Ursprungstabelle
group_id|person_id|value
-------------------------------
0|0|0
0|0|1
0|0|2
0|20|1
0|20|2
7|0|0
9|4|1
9|4|2

Habt ihr eine Idee?
Vielen Dank schonmal für eure Hilfe!


Matthias

 

[akretschmer]

Mit Deinen Daten, bis auf (0,20) habe ich 20 als Value und nicht 2 (Fipptehler):

test=*# select * from doerflia ;
 g_id | p_id | value
------+------+-------
    0 |    0 |     0
    0 |    0 |     1
    0 |    0 |     2
    0 |   20 |     1
    0 |   20 |    20
    7 |    0 |     0
    9 |    4 |     1
    9 |    4 |     2
(8 rows)

test=*# with x as (select *, row_number() over (partition by g_id, p_id order by value) from doerflia), y as (select g_id, p_id,value from x where row_number = 2) select g_id,p_id,value from y union all select g_id,p_id,NULL from x where (g_id,p_id) not in (select g_id, p_id from y) order by g_id, p_id;
 g_id | p_id | value
------+------+-------
    0 |    0 |     1
    0 |   20 |    20
    7 |    0 |     
    9 |    4 |     2
(4 rows)

test=*#

 

[doerflia]

Also das Ergebnis sieht so zumindest viel versprechend aus.
Wie funktioniert den der Befehl row_number()???
Meine Tabelle 'overview' ist nämlich nicht sortiert...
Da sich Daten ändern können und Abhängigkeiten besteht, eine schwierege Situation.

Ich möchte die Tabellen 'first_value' und 'second_value' in Wirklichkeit gar nicht erzeugen.
Die Information muss ja nicht mehrfach vorhanden sein.
Ich mache das momentan noch als Zwischenschritt...

Die Abfrage dieser 'first_value's usw. geschieht später über PHP und
die Daten werden in Arrays geschrieben.

Da immer neue Personen in Gruppen hinzugefügt oder gelöscht werden können und sich der Value ändern kann,
müsste ich die Tabelle ständig sortieren, um immer die zweite Reihe herauszuziehen, oder?
Hast du eine Idee, wie ich das Sortieren sein lassen kann?
Sprich, einen anderen Ansatz über SQL?

Kann damit ansonsten arbeiten, nur wäre ein anderer Ansatz wohlmöglich weniger Arbeit.

 

[akretschmer]

row_number() ist etwas länger:

row_number() over (partition by g_id, p_id order by value)

es teilt die Rows auf (partition by g_id, p_id) und sortiert nach value (order by value). Diese gruppierte/soertierte Liste wird dann mit der laufenden Zeilennummer versehen.

Und ja: Du mußt nix sortieren in der Tabelle.

 

[doerflia]

Na dann ist die Lösung wieder einmal perfekt ;-) Danke! Ich teste es gleich einmal aus!