Ronny Seffner ronny@seffner.de wrote:
Jetzt braucht es wohl intelligente und performante SQL-Syntax das für mich zu erledigen - ein Wunder, dass es sowas offenbar noch nicht fertig gibt. Ich habe die Spalten ip_src, ip_dst, ports_src, port_dst, bytes sowie inserted (vereinfacht). Nun sollen alle Zeilen, deren 4 erste Spalten identisch sind und deren 'inserted' innerhalb einer Stunde liegen zu einer Zeile mit summierten 'bytes' zusammengefasst werden. Nur muss man dazu immer wieder iterativ über die gesamte DB rennen, was mir Sorgen bereitet.
Warum interaktiv? Das geht doch in einem Rutsch. Und sollen die Daten aus der Tabelle entfernt werden?
Angenommen, Deine Tabelle sieht so aus:
test=*# select * from ronny ; s1 | s2 | ts | val ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:01 | 10 1 | 2 | 2014-12-23 12:00:01 | 12 1 | 2 | 2014-12-23 13:00:03 | 22 1 | 2 | 2014-12-23 13:00:04 | 23 (4 rows)
Du kannst nun das rauslöschen und aggregieren und auch noch das Einfügen in eine aggregierte Tabelle in einem Rutsch machen:
test=*# with move as (delete from ronny returning *) select s1, s2, date_trunc('hour',ts), sum(val) from ronny group by s1, s2, date_trunc('hour',ts); s1 | s2 | date_trunc | sum ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:00 | 10 1 | 2 | 2014-12-23 12:00:00 | 12 1 | 2 | 2014-12-23 13:00:00 | 45 (3 rows)
Hier ist jetzt noch nicht das Einfügen mit drin - das Insert kannst Du aber vor ads Select machen. Deine Ursprungstabelle ist dann um die aggregierten Zeilen gekürzt - hier alle, da keine Where-Condition da war.
Das geht so ab 9.2, glaube ich. (nennt sich writeable Common Table Expression, also daß man mit WITH (...) so arbeiten kann, das kann man noch beliebig verschachteln. Am Ende ist das ein atomarer Befehl, eine einzige Transaktion.
Ich möchte da so wenig wie möglich keys drin haben, da die die INSERTS und UPDATES durch 'pmacct' sicher nur verlangsamen - kann man da welche entfernen?
Depends. Wenn der PK woanders als Foreign Key dienen sollte dann eher nicht. Hinreichend aktuelle Versionen zeigen dies an:
test=*# create table master (id int primary key); CREATE TABLE Time: 51,789 ms test=*# \d master Table "public.master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "master_pkey" PRIMARY KEY, btree (id)
test=*# create table slave( m_id int references master); CREATE TABLE Time: 41,206 ms test=*# \d master Table "public.master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "master_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "slave" CONSTRAINT "slave_m_id_fkey" FOREIGN KEY (m_id) REFERENCES master(id)
Wie man sieht, vor Erstellung der slave-Tabelle ist kein Referenced by, erst nach Erstellung der slave-Tabelle.
Das sind alles so Dinge, die bei PG halt cool sind ...
Fragen? Fragen!
Grüße ins Nachbardorf ;-)
Andreas