Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Zeitstempel bei Aggregatfunktion übermitteln

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von PVbeiNacht, 4 Juni 2020.

  1. PVbeiNacht

    PVbeiNacht Benutzer

    Liebes Forum.

    Ich ermittle aus meiner MariaDB eine Summe aus täglichen Maximalwerten der erzeugten Energie von 3 unterschiedlichen PV-Anlagen (id=183,190,191).
    Nun möchte ich ermitteln an welchem Tag die Summe aller drei PV-Anlagen am höchsten war. Meine Abfrage liefert zwar - nachweislich - den höchsten Wert im betrachteten Zeitraum, das mitgelieferte Datum stimmt allerdings nicht.
    Wie kann ich das Datum an dem die Summe aller Erzeuger am höchsten war ausgeben?

    Danke für Eure Hilfe!

    P.S: Daten als csv mit .txt Endung im Anhang

    Code:
    SELECT max(sumvalue),time_sec
    FROM (
        SELECT sum(VALUE) as sumvalue, ts/1000 as time_sec
        FROM (
            SELECT
                ts,
                max(val) AS VALUE
            FROM ts_number n
            WHERE id=183
            GROUP BY from_unixtime(ts/1000,'%y'), from_unixtime(ts/1000,'%m'), from_unixtime(ts/1000,'%d')
    
            UNION ALL
    
            SELECT
                ts,
                max(val*1000) AS VALUE
            FROM ts_number m
            WHERE id=190
            GROUP BY from_unixtime(ts/1000,'%y'), from_unixtime(ts/1000,'%m'), from_unixtime(ts/1000,'%d')
    
            UNION ALL
    
            SELECT
                ts,
                max(val*1000) AS VALUE
            FROM ts_number o
            WHERE id=191
            GROUP BY from_unixtime(ts/1000,'%y'), from_unixtime(ts/1000,'%m'), from_unixtime(ts/1000,'%d')
        ) t
        GROUP BY from_unixtime(floor(ts/1000),'%y') DESC,from_unixtime(floor(ts/1000),'%m') DESC,from_unixtime(floor(ts/1000),'%d') DESC
    ) u
     

    Anhänge:

  2. akretschmer

    akretschmer Datenbank-Guru

    Ich glaube, auch Dir habe ich schon einmal erklärt, daß bei Aggregationen alle Spalten im Result aggregiert oder gruppiert sein müssen. MySQL erkennt NICHT den syntaktischen/logischen Fehler und liefert ein Zufallsergebniss.
     
  3. PVbeiNacht

    PVbeiNacht Benutzer

    Danke für den Hinweis, soweit bin ich auch schon gekommen. Da das Ergebnis zu einem ganz bestimmten Zeitpunkt eintritt muß der auch eruierbar sein - denke ich.
    Allerdings komm ich selber nicht drauf wie ich das anstellen müßte.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    PS.: der Anhang ist XML, nicht CSV.
     
  5. akretschmer

    akretschmer Datenbank-Guru

    ich bin jetzt zu faul Tabellendesign und Daten zu erraten, hier ein Schnellschuß:

    Code:
    test=*# \d pv
                                          Table "public.pv"
     Column  |            Type             | Collation | Nullable |           Default           
    ---------+-----------------------------+-----------+----------+------------------------------
     id      | integer                     |           | not null | generated always as identity
     station | integer                     |           |          |
     ts      | timestamp without time zone |           |          |
     value   | integer                     |           |          |
    
    
    test=*# select * from pv;
     id | station |         ts          | value
    ----+---------+---------------------+-------
      1 |     183 | 2020-06-01 10:00:00 |    10
      2 |     190 | 2020-06-01 10:00:00 |    12
      3 |     191 | 2020-06-01 10:00:00 |    13
      4 |     183 | 2020-06-01 11:00:00 |    22
      5 |     190 | 2020-06-01 11:00:00 |    33
      6 |     191 | 2020-06-01 11:00:00 |    44
      7 |     183 | 2020-06-02 10:00:00 |     2
      8 |     190 | 2020-06-02 10:00:00 |     1
      9 |     191 | 2020-06-02 10:00:00 |     3
    (9 rows)
    
    test=*# select ts::date, sum(value) from pv group by ts::date order by sum(value) desc limit 1;
         ts     | sum
    ------------+-----
     2020-06-01 | 134
    (1 row)
    
    
     
  6. PVbeiNacht

    PVbeiNacht Benutzer

    Danke für Deine Antwort, dabei ist mir aufgefallen, dass ich noch nicht alle Informationen zur Verfügung gestellt habe.
    Ich bitte um Entschuldigung, es ist mir nicht möglich .csv-Dateien hochzuladen, dabei ist mir beim Export der Daten ein Fehler unterlaufen. Hier nun ein Export als csv mit Endung .txt.
    Die Stationen 190 und 191 liefern den jeweiligen aggregierten Energiewert in kWh und das Gerät 183 den aggregierten Energiewert in Wh.
    Diese Werte sind innerhalb eines Tages gültig und werden um 00:00 Uhr zurückgesetzt.
    Da der Zeitstempel in ms seit 1970 angegeben wird, kann man nicht nach ts gruppieren, da es selten zwei gleiche Zeitstempel gibt. Daher habe ich in meiner Abfrage nach Tagen gruppiert um eine Übereinstimmung zwischen den Geräten herstellen zu können.
     

    Anhänge:

  7. akretschmer

    akretschmer Datenbank-Guru

    joa, dann paßt ja meine Lösung schon fast:

    Code:
    create table pv_csv(id int, ts bigint, val numeric);
    --
    -- deine csv nach /tmp kopiert, erste Zeile rausgenommen (hätte ich auch beim copy ignorieren können...)
    --
    test=*# copy pv_csv from '/tmp/iobroker05.06.20.csv' csv delimiter ',' NULL 'NULL';
    COPY 5000
    test=*# commit;
    COMMIT
    test=# select * from pv_csv limit 10;
     id  |      ts       |        val       
    -----+---------------+-------------------
     183 | 1591366368303 | 2402.800048828125
     183 | 1591366308234 | 2389.199951171875
     190 | 1591366282614 |               2.9
     191 | 1591366282614 |               2.5
     183 | 1591366248163 |            2362.5
     183 | 1591366188132 |              2337
     183 | 1591366128285 | 2299.900146484375
     190 | 1591366086559 |               2.8
     183 | 1591366068187 |            2275.5
     183 | 1591365888210 | 2216.400146484375
    (10 rows)
    
    test=*# select id, to_timestamp(ts/1000), val from pv_csv limit 10;
     id  |      to_timestamp      |        val       
    -----+------------------------+-------------------
     183 | 2020-06-05 16:12:48+02 | 2402.800048828125
     183 | 2020-06-05 16:11:48+02 | 2389.199951171875
     190 | 2020-06-05 16:11:22+02 |               2.9
     191 | 2020-06-05 16:11:22+02 |               2.5
     183 | 2020-06-05 16:10:48+02 |            2362.5
     183 | 2020-06-05 16:09:48+02 |              2337
     183 | 2020-06-05 16:08:48+02 | 2299.900146484375
     190 | 2020-06-05 16:08:06+02 |               2.8
     183 | 2020-06-05 16:07:48+02 |            2275.5
     183 | 2020-06-05 16:04:48+02 | 2216.400146484375
    (10 rows)
    
    test=# alter table pv_csv alter COLUMN ts type timestamp using (to_timestamp(ts/1000));
    ALTER TABLE
    test=*# select min(ts), max(ts) from pv_csv ;
             min         |         max         
    ---------------------+---------------------
     2020-06-01 10:15:44 | 2020-06-05 16:12:48
    (1 row)
    
    test=*# select * from pv_csv limit 10;
     id  |         ts          |        val       
    -----+---------------------+-------------------
     183 | 2020-06-05 16:12:48 | 2402.800048828125
     183 | 2020-06-05 16:11:48 | 2389.199951171875
     190 | 2020-06-05 16:11:22 |               2.9
     191 | 2020-06-05 16:11:22 |               2.5
     183 | 2020-06-05 16:10:48 |            2362.5
     183 | 2020-06-05 16:09:48 |              2337
     183 | 2020-06-05 16:08:48 | 2299.900146484375
     190 | 2020-06-05 16:08:06 |               2.8
     183 | 2020-06-05 16:07:48 |            2275.5
     183 | 2020-06-05 16:04:48 | 2216.400146484375
    (10 rows)
    
    --
    -- id=183 muß durch 1000 gerechnet werden, damit es in kwh ist
    --
    test=*# select ts::date, sum(case when id=183 then val/1000 else val end) from pv_csv group by ts::date order by 2 desc limit 1;
         ts     |            sum           
    ------------+---------------------------
     2020-06-03 | 5829.22570556628704156300
    (1 row)
    
    okay soweit?

    Ich sollte mir eine Preisliste für Auftragsdatenverarbeitung anlegen ...
     
    Kampfgummibaerlie gefällt das.
  8. PVbeiNacht

    PVbeiNacht Benutzer

    Der Ansatz ist spannend allerdings muss das Ergebnis zwischen 0 und 40kWh sein.
    Die Werte in "val" sind schon aggegierte Summen, d.h. sie noch einmal zu summieren hat keinen Sinn.
    Tut mir Leid, dass das aus meiner Beschreibung nicht klar geworden ist.

    Das Projekt ist übrigens nicht gewerblich sondern für meine PV-Anlage zu Hause.
     
  9. akretschmer

    akretschmer Datenbank-Guru

    Gut. Ich kann nicht erraten, was da wie zu berechnen ist. Ich denke, Du hast nun einen Ansatz, den "Rest" bekommst sicher selber hin. Gute Nacht.
     
  10. PVbeiNacht

    PVbeiNacht Benutzer

    Habe mit Hilfe eines Freundes und dem Ansatz von akretschmer eine Lösung gefunden die folgende Aufgabenstellung abdeckt:
    Drei Energieerzeuger die über den Tag bereits aufsummierte Energiewerte liefern. Nun soll über den betrachteten Zeitraum der Tag ermittelt werden an dem die Summe aller drei Geräte den Maximalwert hat.

    Daher muss der Maximalwert jedes Gerätes für einen Tag ermittelt werden, daraus die Summe gebildet werden und dann den Maximalwert dieser Summe samt zugehörigem Datum ausgegeben werden:
    Code:
    SELECT
        from_unixtime(ts/1000,'%Y-%m-%d') AS Zeit,
        max(CASE WHEN id=183 THEN val/1000  END) +
            max(CASE WHEN id=190 THEN val END) +
            max(CASE WHEN id=191 THEN val END) AS grand_total
    FROM ts_number
    WHERE id IN(183,190,191)
    GROUP BY Zeit
    ORDER BY grand_total DESC LIMIT 1
    Meine Datenbank läuft auf einer MariaDB (Ver. 10.3.21), der Host ist eine Synology 218+, die Tabelle hat >12Mio Zeilen und die Abfrage liefert ein Ergebnis in <2 Sek.
    Code:
    2020-05-27    37.677
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden