Zeitstempel bei Aggregatfunktion übermitteln

PVbeiNacht

Benutzer
Beiträge
14
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

  • iobroker04.06.20.txt
    605,5 KB · Aufrufe: 2
Werbung:
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.
 
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.
 
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)
 
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

  • iobroker05.06.20.csv.txt
    129,3 KB · Aufrufe: 1
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 ...
 
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.
 
Werbung:
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
 
Zurück
Oben