künstlichen Zähler generieren

Mahlzeit! Anscheinend sterbe ich langsam den Hirntot - zumindest hatte ich eine Lösung bereits mal erstellt, finde sie aber nicht mehr (sowohl im Hirn als auch auf dem Rechner). Irgendjemand - glaube ich - hier im Forum hatte mich auf die unten stehende Lösung gebracht, nur finde ich den Eintrag nicht mehr…

Ich habe zwei Fakttabellen - mit neuen und alten Daten. Ich möchte sie über zwei Attribute verbinden und aggregieren, anhand eines Attributes sowie des Zählers absteigend sortieren und anhand des zweiten Attributes limitieren.

dev=# \d tmp_test_alt                                      
     Table "public.tmp_test_alt"                                       
  Column   |     Type     | Modifiers                                  
-----------+--------------+-----------                                 
 id        | integer      |                                            
 attribute | character(3) |                                            
 counter   | integer      |                                            

dev=# \d tmp_test_neu
     Table "public.tmp_test_neu" 
  Column   |     Type     | Modifiers 
-----------+--------------+-----------
 id        | integer      |           
 attribute | character(3) |           
 counter   | integer      |           

dev=# select * from tmp_test_alt;
 id | attribute | counter
----+-----------+---------
  4 | D         |      32
  1 | A         |     256
  1 | B         |      64
  1 | C         |     128
(4 rows)

dev=# select * from tmp_test_neu;
 id | attribute | counter
----+-----------+---------
  1 | A         |       2
  2 | B         |       4
  3 | C         |      16
(3 rows)

dev=# select * from (
select id, attribute, sum(counter) as counter from (
select * from tmp_test_neu
UNION
select * from tmp_test_alt) tmp1 group by id, attribute order by id, counter DESC) tmp;
 id | attribute | counter
----+-----------+---------
  1 | A         |     258
  1 | C         |     128
  1 | B         |      64
  2 | B         |       4
  3 | C         |      16
  4 | D         |      32
(6 rows)

Ich möchte aber nur folgendes Ergebnis:

id | attribute | counter
----+-----------+---------
  1 | A         |     258
  1 | C         |     128
  2 | B         |       4
  3 | C         |      16
  4 | D         |      32

Unter MySQL wäre die Lösung:

select @type := '' from dual;
select @num  := 1 from dual;
SELECT id, attribute, counter FROM (
select id, attribute, sum(counter) as counter, @num := IF(@type = id, @num + 1, 1) AS row_number, @type = id AS dummy from (
select * from tmp_test_neu
UNION
select * from tmp_test_alt) tmp1 
group by id, attribute order by id, counter DESC) tmp2 where row_number < 3;
  1. Wie würde die Syntax unter PostgreSQL lauten?
  2. Was mache ich vorr. komplett verkehrt, sprich diese Lösung ist langsam, welche geht schneller?

Achso: Ich habe nicht PostgreSQL 8.4 sondern 8.3…

Danke
anton-hirntot

Kann es sein, daß Dein Beispiel selbst einen Fehler enthält?

Du hast, von C abgesehen, sonst die Buchstaben nur einmal drin und dafür den geringsten Wert aus den Tabellen.

Falls dem so ist:

test=# create table anton1 (id int, value int);
CREATE TABLE
test=*# create table anton2 (id int, value int);
CREATE TABLE
test=*# copy anton1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    10
>> 2    20
>> 3    30
>> \.
test=*# copy anton2 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    100
>> 2    2
>> 3    33
>> \.
test=*# select distinct on (id) id, value from (select id, value from anton1 union all select id, value from anton2) foo order by id, value asc;
 id | value
----+-------
  1 |    10
  2 |     2
  3 |    30
(3 rows)

Andreas

Nein, das ist kein Fehler, Du könntest praktisch die IDs ausser 1 weglassen (sind nur drin um den UNION zu testen), es geht darum die Ergebnismenge pro ID auf 2 Attribute mit den höchsten Zählständen zu begrenzen.

Die Kombination aus ID und ATTRIBUTE ist praktisch unique - nur interessieren sich die Anwender nur pro ID für die höchsten Zählstände.

Das Endergebnis wäre z.B. eine Begrenzung auf 5000 oder 10000.

Meine erste Frage ist also am wichtigsten - wie kann ich das MySQL Beispiel auf PostgreSQL portieren? -> variablen erzeugen und dynamisch den Zähler aufgrund der vorsortieren Menge (wenn neue ID Zähler zurücksetzen) verändern um diese dann zu limitieren. Ich bekomme es einfach nicht hin…peinlich

ich sehe gerade das MySQL Beispiel ist fehlerhaft portiert…

select @type := '' from dual;
select @num  := 1 from dual;
SELECT id, attribute, counter FROM (
select id, attribute, sum(counter) as counter, @num := IF([B]@type = id, [/B]@num + 1, 1) AS row_number,[B] @type = id [/B]AS dummy from (
select * from tmp_test_neu
UNION
select * from tmp_test_alt) tmp1 
group by id, attribute order by id, counter DESC) tmp2 where row_number < 3;

…sorry…

Oh man, ich habe jetzt eine Möglichkeit über einen left outer join hinbekommen - ob das nur bei 8 Millonen Datensätzen performt…

select id, attribute, counter from (
select l.id, l.attribute, l.counter, count(*) as num from
  (select id, attribute, sum(counter) as counter from (
  select * from tmp_test_neu
  UNION
  select * from tmp_test_alt) tmp1 group by id, attribute order by id, counter DESC) as l
  left outer join 
    (select id, attribute, sum(counter) as counter from (
    select * from tmp_test_neu
    UNION
    select * from tmp_test_alt) tmp1 group by id, attribute order by id, counter DESC) as r
    on l.id = r.id and l.counter <= r.counter
group by l.id, l.attribute, l.counter order by l.id, l.counter DESC) bla
group by id, attribute, counter, num
 having num < 3 order by id, counter DESC;

Natürlich könnte man den UNION vorher noch materialisieren lassen…

Der Code sieht komisch aus… falls irgendjemand von den Brains mir erklären könnte wie der left outer join funktioniert und welche Gefahren dadrin existieren wäre ich dankbar… allein das rumprobieren eben bracht erstaunliche Ergebnisse zu Tage - ich sage nur sortieren…

Was evtl. noch möglich wäre, eine temporäre sequence einzubinden und hoch zu zählen - funktioniert auch, nur wie bekomme ich die wieder zurückgesetzt? Da wären wir dann wieder bei der Ausgangssituation (oder?):

Erste Frage ist aber immer noch: Kann man nicht die MySQL Lösung über die Variablen im Select Statement adaptieren? Ich finde einfach nicht die richtige Syntax…

Wer mag mir ein zwei Löcher in mein Brett vorm Kopf bohren?

anton

Der Left outer join ist NICHT zu emfehlen!

Es scheint also wirklich nicht sooo einfach zu gehen… man müsste wohl mit “stored procedures” oder “functions” anfangen…

Da die Umgebung hier aus einer PostgreSQL DB und dem ETL Werkzeug kettle/Pentaho Data Integrator (V. 3.1.0) besteht, wollte ich nix neues noch in die DB stopfen und habe es mittels des ETL Tools gelöst.

Für die, die’s interessiert… transformation erstellen:

  • table input step
  • sort step entsprechend der gewünschten rownum - Partitionierung nicht vergessen!!! (bei mir halt “id” und “counter” absteigend)
  • “group by” step
    –> Häckchen bei “Add Line Number, restart in each group”
    –> “Line number field name” eingeben (z.B. “Rownumber_partition”)
    –> bei “group fields” das Attribut einhägen (in meinem fall die “id”)
  • Filter step bauen, auf “Rownumber_partition” eingrenzen (in meinem Fall <= 3)
  • mittels “select / rename values” das entstandene Feld rauskicken
  • table output zurück in die DB

Da der ETL Server eigenständig ist und im Grunde genommen alles in der DB ablaufen könnte - wenn ich denn wüsste wie es geht - ist das, mit etwas Entfernung betrachtet, einweniglich umständlich…

anton