Hi Andreas,
irgendwann musste mir Dein pgsql-Wissen mal nützlich werden. Die Fähigkeiten hier (im RDBMS) sind ja der Wahnsinn ;-)
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);
Habe ich mal überführt zu:
mac_src | mac_dst | ip_src | ip_dst | port_src | port_dst | ip_proto | packets | bytes | stamp_inserted | stamp_updated -------------------+-------------------+---------+---------+----------+----- -----+----------+-----+-----+---------------------+--------------------- 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 1 | 100 | 2014-12-23 17:01:00 | 2014-12-23 17:07:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 3 | 0 | 2 | 200 | 2014-12-23 16:02:00 | 2014-12-23 16:12:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 3 | 300 | 2014-12-23 16:03:00 | 2014-12-23 16:22:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 7 | 700 | 2014-12-23 17:04:00 | 2014-12-23 17:13:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 3 | 300 | 2014-12-23 16:05:00 | 2014-12-23 16:05:08
with move as (delete from acct returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Das führt zu:
mac_src | mac_dst | ip_src | ip_dst | port_src | port_dst | ip_proto | sum | sum | date_trunc -------------------+-------------------+---------+---------+----------+----- -----+----------+-----+-----+--------------------- 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 6 | 600 | 2014-12-23 16:00:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 3 | 0 | 2 | 200 | 2014-12-23 16:00:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 8 | 800 | 2014-12-23 17:00:00
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.
Zuerst das WHERE. Wenn mich nur die 16 Uhr Einträge interessieren:
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Und nun das INSERT dazu, weil ich die Daten ja zusammengeführt weiter nutzen will. Bis hier her hab ich die vorgekaute Syntax sogar verstanden, aber jetzt ist mir die Platzierung des INSERT nicht so ganz klar? Und die Spalte stamp_updated kann leer bleiben.? Das Ergebnis hat in den manipulierten Spalten (siehe oben) nun andere Bezeichner, das macht dem korrekten INSERT ja aber sicher nichts. Auf jeden Fall gibts einen Syntaxfehler bei "moved" ;-(
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) moved as (insert into acct (select * from move)) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Die wiederholten date_trunc() und WHERE kostet nicht unnötig Ressourcen?
Depends. Wenn der PK woanders als Foreign Key dienen sollte dann eher nicht. Hinreichend aktuelle Versionen zeigen dies an:
v9.1.14 und kein "Referenced by". Was meinst Du zu den Indizes? Geschrieben in die DB wird alle 5min, den Job zum Zusammenführen lass ich 1x stündlich laufen, wirklich reingucken in die Daten wird man zwei drei Mal im Monat.
Das sind alles so Dinge, die bei PG halt cool sind ...
Offenbar.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen www.seffner.de | ronny@seffner.de | +49 35245 72950 7EA62E22D9CC4F0B74DCBCEA864623A568694DB8