Aggregatfunktion mit sortierung

Ich habe eine Tabelle mit folgenden Struktur:

id
article_id
sort
key
value

zu einer article_id gibt es mehrere key/value Paare.
Ich möchte das ganze in folgender Form ausgeben:

article_id, key, value, key, value , …
wobei die key/value Tupel entspechend der spalte sort sortiert sind.

Ich habe eine einfache Aggregatfunktion erstellt, welche die key/value Paare in ein Array schreibt, dieses wird dann am Ende wieder in einen String umwandelt, aber sobald ich die sort Spalte mit in das select einbaue, muss es auch im group by stehen und dann habe ich immer nur ein Tupel im Array.

hat jemand eine Idee , wie man das hinbekommt?
rolf

Wie viele?

Ich möchte das ganze in folgender Form ausgeben:

article_id, key, value, key, value , …
wobei die key/value Tupel entspechend der spalte sort sortiert sind.

Wieviel kommt nach dem … ?
Schaut nach einer wilden Kreutztabelle aus.

Ich habe eine einfache Aggregatfunktion erstellt, welche die key/value Paare in ein Array schreibt, dieses wird dann am Ende wieder in einen String umwandelt, aber sobald ich die sort Spalte mit in das select einbaue, muss es auch im group by stehen und dann habe ich immer nur ein Tupel im Array.

hat jemand eine Idee , wie man das hinbekommt?
rolf

nicht wirklich.

Zeig mal ein Beispiel für die Daten und wie das Result aussehen soll. Mit plpgsql kann man viel machen…

Andreas

Wie wäre es damit:

SELECT article_id, 
       array_to_string(keys, ', ')
FROM (
  SELECT article_id, 
         array_agg(key||'='||value) OVER (PARTITION BY article_id ORDER BY sort) as keys,
         row_number() OVER (PARTITION BY article_id ORDER BY sort DESC) as row_number
  FROM articles
) t
WHERE row_number = 1

Theoretisch kann es beliebig viele dieser key/value Tupel geben,
allerdings sind es immer gleich viele.

Nein nein, es gibt zu einen Artikel (refernziert durch den Fk article_id) eine Reihe von Merkmalen, die über key/value Tupel spezifiziert werden. Die sort Spalte dient dazu, die Reienfolge im GUI zu bestimmen.

das ist nicht so einfach, da im wirklichen Fall noch ein paar Tabellen mehr involviert sind. Dann habe ich in der Doku nichts darüber gefunden, wie genau eine eigene sfunc in create aggreate aufgebaut sein muss, speziell was den Rückgabewert betrifft.
Ich versuche mal, das auf das Kernproblem zu reduzieren.
Rolf



Andreas

uhh was ist das Over? partition ?
Noch nie gesehen, Ist das neu am 8.4 ?
Ich habe hier noch 8.3 am Laufen.

Ja, ist 8.4
Nachdem Du keine Version erwähnt hast, bin ich davon ausgegangen, dass Du die aktuelle verwendest.

Zur Syntax:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

Ahh, Junge, das passt, das ist aber schon heftig in die Trickkiste gegriffen, so spontan fällt einem das nicht ein! Ich musste es noch etwas ändern damit es mit meinen Tabellen passt, aber das Ergebnis sieht gut aus. Jetzt muss ich noch die halbe Nacht Doku lesen um zu verstehen, warum das funktioniert …

Kommt drauf an :slight_smile:

Wenn man mal Geschmack an den Windowing Functions gefunden hat, dann ist das immer das erste woran man denkt…

Ich verwende die schon seit ewigen Zeiten gerne bei Oracle und war froh als meine Lieblings-DB das auch endlich konnte :wink:

Hey, geil, wenn Du da Erfahrung hast, ich hab da noch so meine Probleme dann und wann damit …

Sag mal, wie geht sowas:

test=*# select * from bla;
 id | val
----+-----
  1 |   3
  2 |   2
  3 |   1
  4 |   5
  5 |   5
(5 Zeilen)

Ich möchte da nun ein ‘gleitendes’ Fenster haben und die Summe bilden:

1 | 3
2 | 5
3 | 3
4 | 6
5 | 10

Also z.B. immer die Summe von val des aktuellen Record und dem davor. Oder Summe von val von Record davor, aktuell und danach.


Andreas

Das ist einfach :slight_smile:

SELECT id,
       val,
       val + lag(val,1,0) over (order by id) as summe_mit_vorher,
       val + lag(val,1,0) + over (order by id) + lead(val, 1, 0) over (order by id) as summe_vorher_und_nachher
FROM bla;

lag() greift auf die “vorherige” row zu. Der erste parameter definiert die Spalte aus der Zeile. Der zweite parameter definiert wie weit man zurückgehen will (2 bedeutet 2 Zeilen zurück usw), der dritte Parameter der default wert, falls es diese Zeile nicht gibt (bei der ersten gibt es ja keinen Vorgänger.

Erste/Vorherige usw. bezieht sich auf die Sortierung die in over() angegeben wurde.

Mit negativen Werten für den zweiten Parameter geht man “vorwärts”. Aber dafür kann man auch die lead() Funktion verwenden.

Na gott sei dank habe ich gestern die 8.4 version testweise installiert, jetzt kann ich mal dran gehen, diese neuen Features zu erkunden. Bisher dachte ich immer über “na ja, das habe ich bisher auch noch nie gebraucht”, aber das scheinen doch ganz mächtige neue Funktionen zu sein.

Hier ist eine sehr schöne Einführung von David Fetter:

http://www.pgexperts.com/document.html?id=29

Oh, Danke schön. Das eine '+*, was zuviel ist, habe ich mal entfernt und es funzt. Prima. Und Danke auch für den Link zum PDF von David. Sehr schön.

Andreas

hach ist das alles aufregend

Dem kann ich mich nur anschliessen :slight_smile:

CU Uwe