Ankündigung

Einklappen
Keine Ankündigung bisher.

ORDER BY - 5 Minuten Intervall

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • ORDER BY - 5 Minuten Intervall

    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.

  • #2
    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


    • #3
      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


      • #4
        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


        • #5
          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(messwertFROM Daten AS Daten2 WHERE Daten.zeitpunkt >= Daten2.zeitpunkt AND DATE_ADD(Daten.zeitpunktINTERVAL 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


          • #6
            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


            • #7
              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


              • #8
                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


                • #9
                  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


                  • #10
                    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

                    Lädt...
                    X