ORDER BY - 5 Minuten Intervall

barracuda317

Dabei seit: 03.10.2014

Beiträge: 2
#1

ORDER BY - 5 Minuten Intervall

03.10.2014, 09:18

Hallo,

ich habe hier eine Tabelle (Größe 16 GB) mit Verkehrsdaten. Jede Zeile in dieser Tabelle gibt den Wert der Messung für eine Minute an.

Zur Normalisierung benötige ich nun den Maximalwert. Klar - erster Ansatz mit MAX(..) geht auch. Das Problem ist jedoch, dass ich dabei genau die Peeks erwische und die Daten somit über einen Zeitraum von 5 Minuten mitteln möchte.

Erster Ansatz: SELECT * FROM Daten GROUP BY UNIX_TIMESTAMP(DATETIME) DIV 300

Auf den ersten Blick scheint dies auch zu gehen und baut Gruppe, welche von Minuten 25 bis 30, 30 bis 35, 35 bis 40 ... gehen.

Das ist zwar besser als die 1-Minuten Päckchen, aber noch nicht das was ich brauche.

Hat jemand eine Idee wie ich das Zeitfenster über die Daten wandern lassen kann. Also Päckchen von Minute 25 bis 30, 26 bis 31, 27 bis 32.

Mir ist bewusst, dass das bei 16 Gig sehr lang dauert, es wird aber nur einmal alle paar Wochen abgefragt werden.
Stichworte: -

akretschmer

Dabei seit: 13.01.2012
Beiträge: 4836

03.10.2014, 09:40

Zitat von barracuda317 Beitrag anzeigen

Hallo,

ich habe hier eine Tabelle (Größe 16 GB) mit Verkehrsdaten. Jede Zeile in dieser Tabelle gibt den Wert der Messung für eine Minute an.

Zur Normalisierung benötige ich nun den Maximalwert. Klar - erster Ansatz mit MAX(..) geht auch. Das Problem ist jedoch, dass ich dabei genau die Peeks erwische und die Daten somit über einen Zeitraum von 5 Minuten mitteln möchte.

Erster Ansatz: SELECT * FROM Daten GROUP BY UNIX_TIMESTAMP(DATETIME) DIV 300

Auf den ersten Blick scheint dies auch zu gehen und baut Gruppe, welche von Minuten 25 bis 30, 30 bis 35, 35 bis 40 ... gehen.

Das ist zwar besser als die 1-Minuten Päckchen, aber noch nicht das was ich brauche.

Hat jemand eine Idee wie ich das Zeitfenster über die Daten wandern lassen kann. Also Päckchen von Minute 25 bis 30, 26 bis 31, 27 bis 32.

Mir ist bewusst, dass das bei 16 Gig sehr lang dauert, es wird aber nur einmal alle paar Wochen abgefragt werden.

Code:

test=*# select * from messung ;
         ts          |       val        
---------------------+------------------
 2014-10-03 10:01:00 |  986.52125755325 
 2014-10-03 10:02:00 | 878.451874479651 
 2014-10-03 10:03:00 | 329.372962936759 
 2014-10-03 10:04:00 | 113.593352027237 
 2014-10-03 10:05:00 | 525.222308468074 
 2014-10-03 10:06:00 | 242.030848748982 
 2014-10-03 10:07:00 | 989.672433119267 
 2014-10-03 10:08:00 | 134.898131713271 
 2014-10-03 10:09:00 | 577.895030844957 
 2014-10-03 10:10:00 | 361.163215711713 
 2014-10-03 10:11:00 | 433.920940384269 
 2014-10-03 10:12:00 | 68.8369949348271 
 2014-10-03 10:13:00 | 932.680753059685 
 2014-10-03 10:14:00 | 365.012635476887 
 2014-10-03 10:15:00 | 772.338234353811 
 2014-10-03 10:16:00 | 894.559334497899 
 2014-10-03 10:17:00 | 885.283911600709 
 2014-10-03 10:18:00 | 317.721224855632 
 2014-10-03 10:19:00 | 986.092327162623 
 2014-10-03 10:20:00 | 173.613673076034 
 2014-10-03 10:21:00 | 397.622290533036 
 2014-10-03 10:22:00 |  771.48626325652 
 2014-10-03 10:23:00 | 72.5574619136751 
 2014-10-03 10:24:00 |  321.49510178715 
 2014-10-03 10:25:00 | 594.818733166903 
 2014-10-03 10:26:00 | 425.599286332726 
 2014-10-03 10:27:00 | 20.1816288754344 
 2014-10-03 10:28:00 | 280.662123113871 
 2014-10-03 10:29:00 | 991.524972952902 
 2014-10-03 10:30:00 | 478.094316087663 
(30 rows)                               

Time: 0,301 ms
test=*# select *, avg(val) over (partition by extract(epoch from ts)::int / 300) from messung;
         ts          |       val        |       avg                                           
---------------------+------------------+------------------                                   
 2014-10-03 10:01:00 |  986.52125755325 | 576.984861749224                                    
 2014-10-03 10:02:00 | 878.451874479651 | 576.984861749224                                    
 2014-10-03 10:03:00 | 329.372962936759 | 576.984861749224                                    
 2014-10-03 10:04:00 | 113.593352027237 | 576.984861749224                                    
 2014-10-03 10:05:00 | 525.222308468074 |  493.94375057891                                    
 2014-10-03 10:06:00 | 242.030848748982 |  493.94375057891                                    
 2014-10-03 10:07:00 | 989.672433119267 |  493.94375057891                                    
 2014-10-03 10:08:00 | 134.898131713271 |  493.94375057891                                    
 2014-10-03 10:09:00 | 577.895030844957 |  493.94375057891                                    
 2014-10-03 10:10:00 | 361.163215711713 | 432.322907913476                                    
 2014-10-03 10:11:00 | 433.920940384269 | 432.322907913476                                    
 2014-10-03 10:12:00 | 68.8369949348271 | 432.322907913476                                    
 2014-10-03 10:13:00 | 932.680753059685 | 432.322907913476                                    
 2014-10-03 10:14:00 | 365.012635476887 | 432.322907913476                                    
 2014-10-03 10:15:00 | 772.338234353811 | 771.199006494135                                    
 2014-10-03 10:16:00 | 894.559334497899 | 771.199006494135                                    
 2014-10-03 10:17:00 | 885.283911600709 | 771.199006494135                                    
 2014-10-03 10:18:00 | 317.721224855632 | 771.199006494135                                    
 2014-10-03 10:19:00 | 986.092327162623 | 771.199006494135                                    
 2014-10-03 10:20:00 | 173.613673076034 | 347.354958113283                                    
 2014-10-03 10:21:00 | 397.622290533036 | 347.354958113283                                    
 2014-10-03 10:22:00 |  771.48626325652 | 347.354958113283                                    
 2014-10-03 10:23:00 | 72.5574619136751 | 347.354958113283                                    
 2014-10-03 10:24:00 |  321.49510178715 | 347.354958113283                                    
 2014-10-03 10:25:00 | 594.818733166903 | 462.557348888367                                    
 2014-10-03 10:26:00 | 425.599286332726 | 462.557348888367                                    
 2014-10-03 10:27:00 | 20.1816288754344 | 462.557348888367                                    
 2014-10-03 10:28:00 | 280.662123113871 | 462.557348888367                                    
 2014-10-03 10:29:00 | 991.524972952902 | 462.557348888367                                    
 2014-10-03 10:30:00 | 478.094316087663 | 478.094316087663                                    
(30 rows)

Time: 0,585 ms
test=*# select *, max(val) over (partition by extract(epoch from ts)::int / 300) from messung;
         ts          |       val        |       max
---------------------+------------------+------------------
 2014-10-03 10:01:00 |  986.52125755325 |  986.52125755325
 2014-10-03 10:02:00 | 878.451874479651 |  986.52125755325
 2014-10-03 10:03:00 | 329.372962936759 |  986.52125755325
 2014-10-03 10:04:00 | 113.593352027237 |  986.52125755325
 2014-10-03 10:05:00 | 525.222308468074 | 989.672433119267
 2014-10-03 10:06:00 | 242.030848748982 | 989.672433119267
 2014-10-03 10:07:00 | 989.672433119267 | 989.672433119267
 2014-10-03 10:08:00 | 134.898131713271 | 989.672433119267
 2014-10-03 10:09:00 | 577.895030844957 | 989.672433119267
 2014-10-03 10:10:00 | 361.163215711713 | 932.680753059685
 2014-10-03 10:11:00 | 433.920940384269 | 932.680753059685
 2014-10-03 10:12:00 | 68.8369949348271 | 932.680753059685
 2014-10-03 10:13:00 | 932.680753059685 | 932.680753059685
 2014-10-03 10:14:00 | 365.012635476887 | 932.680753059685
 2014-10-03 10:15:00 | 772.338234353811 | 986.092327162623
 2014-10-03 10:16:00 | 894.559334497899 | 986.092327162623
 2014-10-03 10:17:00 | 885.283911600709 | 986.092327162623
 2014-10-03 10:18:00 | 317.721224855632 | 986.092327162623
 2014-10-03 10:19:00 | 986.092327162623 | 986.092327162623
 2014-10-03 10:20:00 | 173.613673076034 |  771.48626325652
 2014-10-03 10:21:00 | 397.622290533036 |  771.48626325652
 2014-10-03 10:22:00 |  771.48626325652 |  771.48626325652
 2014-10-03 10:23:00 | 72.5574619136751 |  771.48626325652
 2014-10-03 10:24:00 |  321.49510178715 |  771.48626325652
 2014-10-03 10:25:00 | 594.818733166903 | 991.524972952902
 2014-10-03 10:26:00 | 425.599286332726 | 991.524972952902
 2014-10-03 10:27:00 | 20.1816288754344 | 991.524972952902
 2014-10-03 10:28:00 | 280.662123113871 | 991.524972952902
 2014-10-03 10:29:00 | 991.524972952902 | 991.524972952902
 2014-10-03 10:30:00 | 478.094316087663 | 478.094316087663
(30 rows)

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Kommentar

nikosch

Dabei seit: 21.05.2008

Beiträge: 46002
#3

03.10.2014, 11:18

Das ist zwar besser als die 1-Minuten Päckchen, aber noch nicht das was ich brauche.

Vielleicht machst Du Dir die Mühe und schilderst dann mal das Problem dieser Lösung. An Deinem gegenwärtigen Plan kann ich das nämlich nicht ablesen. Der Scheint mir doch recht unsinnig.

[COLOR="#F5F5FF"]--[/COLOR]
[COLOR="Gray"][SIZE="6"][FONT="Georgia"][B]^^ O.O[/B][/FONT] [/SIZE]
„Emoticons machen einen Beitrag etwas freundlicher. Deine wirken zwar fachlich richtig sein, aber meist ziemlich uninteressant.
[URL="http://www.php.de/javascript-ajax-und-mehr/107400-draggable-sorttable-setattribute.html#post788799"][B]Wenn man nur Text sieht, haben viele junge Entwickler keine interesse, diese stumpfen Texte zu lesen.“[/B][/URL][/COLOR]
[COLOR="#F5F5FF"]
--[/COLOR]
Kommentar

G.Schuster

Dabei seit: 21.12.2009
Beiträge: 757

03.10.2014, 14:44

Ins Blaue geraten:

Code:

+---------------------+---+
| d                   | b |
+---------------------+---+
| 2014-10-03 14:30:00 | 1 |
| 2014-10-03 14:32:00 | 2 |
| 2014-10-03 14:34:00 | 3 |
| 2014-10-03 14:40:00 | 3 |
| 2014-10-03 14:35:00 | 3 |
| 2014-10-03 14:34:59 | 3 |
+---------------------+---+

Code:

SELECT `d`, SUM(`b`) / COUNT(`b`) FROM `a` GROUP BY UNIX_TIMESTAMP(`d`) DIV 300;

Code:

+---------------------+-------------------+
| d                   | sum(b) / count(b) |
+---------------------+-------------------+
| 2014-10-03 14:30:00 |            2.2500 |
| 2014-10-03 14:35:00 |            3.0000 |
| 2014-10-03 14:40:00 |            3.0000 |
+---------------------+-------------------+

VokeIT GmbH & Co. KG - VokeIT-oss @ github

Kommentar

erc

Dabei seit: 02.01.2009

Beiträge: 4426
#5

03.10.2014, 17:29

Dich zwingt keiner MAX() zu verwenden, du kannst auch den Druchschnitt nehmen AVG().

Also Päckchen von Minute 25 bis 30, 26 bis 31, 27 bis 32.

Das kannst du mit einem Subquery machen:

PHP-Code:

SELECT zeitpunkt, messwert, (SELECT AVG(messwert) FROM Daten AS Daten2 WHERE Daten.zeitpunkt >= Daten2.zeitpunkt AND DATE_ADD(Daten.zeitpunkt, INTERVAL 5 MINUTE)) < Daten2.zeitpunkt) AS avg_messwert FROM Daten

Das wird aber bei entsprechnender Datenmenge (die ausgelesen wird) ziemlich langsam. Ich würde das eher Clientseitig machen.
Kommentar
ChristianK

Dabei seit: 29.06.2011

Beiträge: 3400
#6

04.10.2014, 06:50

Bist du sicher, dass das Clientseitig schneller ist? Ich behaupte nein.

[URL="https://github.com/chrisandchris"]GitHub.com - ChrisAndChris[/URL] - [URL="https://github.com/chrisandchris/symfony-rowmapper"]RowMapper und QueryBuilder für MySQL-Datenbanken[/URL]
Kommentar
Durin

Dabei seit: 09.08.2014

Beiträge: 79
#7

04.10.2014, 07:34

Wenn du ein optimiertes Sortierprogramm und 16 GB RAM hast

Das Zeitfenster sollte sich so verschieben lassen, auch wenn es dann natürlich keine ganze fünf Minuten am Anfang sind:

PHP-Code:

$offset = $anzahl_minuten*60; $db->query("SELECT * FROM Daten GROUP BY (UNIX_TIMESTAMP(DATETIME)+".$offset.") DIV 300");
Kommentar
akretschmer

Dabei seit: 13.01.2012

Beiträge: 4836
#8

04.10.2014, 08:11

Zitat von erc Beitrag anzeigen

Dich zwingt keiner MAX() zu verwenden, du kannst auch den Druchschnitt nehmen AVG().

welchen Unterschied macht das für die Abfrage?

Das kannst du mit einem Subquery machen:

Das wird aber bei entsprechnender Datenmenge (die ausgelesen wird) ziemlich langsam.

Genau.

Ich würde das eher Clientseitig machen.

Ich nicht. Eine Lösung hab ich ja gezeigt.

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Kommentar
ChristianK

Dabei seit: 29.06.2011

Beiträge: 3400
#9

04.10.2014, 09:34

ORDER BY - 5 Minuten Intervall

Zitat von Durin Beitrag anzeigen

Wenn du ein optimiertes Sortierprogramm und 16 GB RAM hast

Das Zeitfenster sollte sich so verschieben lassen, auch wenn es dann natürlich keine ganze fünf Minuten am Anfang sind:

PHP-Code:

$offset = $anzahl_minuten*60; $db->query("SELECT * FROM Daten GROUP BY (UNIX_TIMESTAMP(DATETIME)+".$offset.") DIV 300");

Ich bin der Meinung, die Daten so gut wie nur möglich in der Datenbank aufzubereiten und dann erst zu transferieren. Sortieren und filtern und rechnen Client-/Applikationsseitig sollte man vermeiden, wenn es denn so einfach geht.

[URL="https://github.com/chrisandchris"]GitHub.com - ChrisAndChris[/URL] - [URL="https://github.com/chrisandchris/symfony-rowmapper"]RowMapper und QueryBuilder für MySQL-Datenbanken[/URL]
Kommentar
barracuda317

Dabei seit: 03.10.2014

Beiträge: 2
#10

06.10.2014, 08:25

Zunächst erstmal vielen Dank für die Tips. Der Grund, warum ich erst jetzt antworte, ist die Tatsache, dass die Datenbank vorübergehend nicht erreichbar war und ich somit nichts testen konnte.

Das Problem mit den 5-Minuten-Päckchen von 25 bis 30, 30 bis 35 etc ist aus der Datenbasis begründet.

Dort liegen Messerwerte, welche für eine Minuten gemittelt sind. Zudem gibt es einen Messdurchlauf, welche etwa 5 Minuten beträgt. Nun kann es passieren, dass der Durchlauf so unglücklich liegt, dass ich durch die 5-Minuten-Kästchen die Peeks verpasse und dann im nächsten Päckchen mehre Peeks zu finden sind.

das heißt, die beste Lösung wären diese verschiebbaren Päckchen gewesen. Aber vermutlich habt ihr recht, und es ist den Aufwand nicht wert, der dafür betrieben werden muss.

Die Datenbasis ist etwa 200 GB groß.
Kommentar

Ankündigung

ORDER BY - 5 Minuten Intervall

Neue Werbung 2019