MariaDB 10.2.15- supporto a Window_Function presente.
Recupero da un dispositivo, con base tempi di circa 5 secondi, un dato di consumo energetico (totalizzatore consumo) e salvo il tutto in una tabella di MariaDB.
Nel record è presente un campo chiave (IdTbl - autoincrement - direi che è inutile, credo che lo sopprimerò) un campo (UTCDate - tipo DateTime) con indice NON univoco (sto valutando di renderlo chiave primaria) e un campo (EN_genkwh - tipo Decimal).
Il campo EN_genkwh contiene un numero che può solo aumentare con il passare del tempo TRANNE il caso, in verità abbastanza eccezionale, di un suo riazzeramento.
Ora vorrei raggruppare i circa 180 records presenti in 15 minuti di acquisizioni in un unico record contenente la DataOra del periodo di raggruppamento, il valore di EN_genkwh del primo record del periodo e il consumo avvenuto nel periodo.
Il calcolo del consumo nel periodo di raggruppamento sarebbe semplice se non ci fosse di mezzo il possibile riazzeramento del totalizzatore : MAX(EN_genkwh) - MIN(EN_genkwh).
Volendo invece contemplare anche la gestione del riazzeramento mi servirebbe avere oltre al MIN e al MAX anche il FIRST e il LAST e se il MIN è uguale a zero calcolo il consumo come MAX - FIRST + LAST
Sembra facile .... ecco i tristi risultati che ottengo (quello che non va è sempre il LAST_VALUE)
Il 900 nel 'where' e nel 'group by' indica 15 minuti (15*60 secondi)
'Interval 900 * 3' nel 'where' serve per avere 3 records da 15 minuti
Prova n° 1 :
SELECT UTCDate,
CEIL(TIMESTAMPDIFF(MINUTE,UTCdate,SRVdate) / 60) AS UTCOffset, COUNT(UTCDate) AS Totsamples,
MIN(UTCDate) AS UTCDate_MIN, MIN(EN_genkwh) AS EN_MIN,
COALESCE(FIRST_VALUE(EN_genkwh) OVER (),0) AS EN_FIRST,
MAX(UTCDate) AS UTCDate_MAX, MAX(EN_genkwh) AS EN_MAX,
COALESCE(LAST_VALUE(EN_genkwh) OVER(),0) AS EN_LAST
FROM `rawenergy`
WHERE UTCDate BETWEEN "2018-10-17 20:30:00" AND DATE_ADD("2018-10-17 20:30:00", INTERVAL (900 * 3) - 1 SECOND)
GROUP BY UNIX_TIMESTAMP(UTCDate) DIV 900
Risultato :
UTCDate UTCOffset Totsamples UTCDate_MIN EN_MIN EN_FIRST UTCDate_MAX EN_MAX EN_LAST
2018-10-17 20:30:03 2 178 2018-10-17 20:30:03 2186.52 2186.52 2018-10-17 20:44:54 2186.71 2186.85
2018-10-17 20:45:00 2 181 2018-10-17 20:45:00 2186.71 2186.52 2018-10-17 20:59:59 2186.85 2186.85
2018-10-17 21:00:04 2 81 2018-10-17 21:00:04 2186.85 2186.52 2018-10-17 21:06:44 2186.93 2186.85
Le funzioni FIRST_VALUE e LAST_VALUE vengono eseguite senza PARTITION BY e ORDER BY.
La funzione FIRST_VALUE restituisce il risultato corretto (2186.52) uguale al MIN del primo record.
La funzione LAST_VALUE non restituisce il valore atteso (2186.93 -> MAX dell'ultimo record) ma 2186.85
Prova n° 2 :
SELECT UTCDate,
CEIL(TIMESTAMPDIFF(MINUTE,UTCdate,SRVdate) / 60) AS UTCOffset, COUNT(UTCDate) AS Totsamples,
MIN(UTCDate) AS UTCDate_MIN, MIN(EN_genkwh) AS EN_MIN,
COALESCE(FIRST_VALUE(EN_genkwh) OVER (PARTITION BY UTCDate),0) AS EN_FIRST,
MAX(UTCDate) AS UTCDate_MAX, MAX(EN_genkwh) AS EN_MAX,
COALESCE(LAST_VALUE(EN_genkwh) OVER(PARTITION BY UTCDate),0) AS EN_LAST
FROM `rawenergy`
WHERE UTCDate BETWEEN "2018-10-17 20:30:00" AND DATE_ADD("2018-10-17 20:30:00", INTERVAL (900 * 3) - 1 SECOND)
GROUP BY UNIX_TIMESTAMP(UTCDate) DIV 900
Risultato :
UTCDate UTCOffset Totsamples UTCDate_MIN EN_MIN EN_FIRST UTCDate_MAX EN_MAX EN_LAST
2018-10-17 20:30:03 2 178 2018-10-17 20:30:03 2186.52 2186.52 2018-10-17 20:44:54 2186.71 2186.52
2018-10-17 20:45:00 2 181 2018-10-17 20:45:00 2186.71 2186.71 2018-10-17 20:59:59 2186.85 2186.71
2018-10-17 21:00:04 2 81 2018-10-17 21:00:04 2186.85 2186.85 2018-10-17 21:06:44 2186.93 2186.85
Le funzioni FIRST_VALUE e LAST_VALUE vengono eseguite con PARTITION BY .
La funzione FIRST_VALUE restituisce il risultato corretto per ogni raggruppamento (uguale a MIN).
La funzione LAST_VALUE non restituisce il valore atteso ma sempre il MIN (o FIRST).
Ora, se non è un problema legato alla chiave primaria (campo IdTbl) presente nella tabella di partenza, comunque non esplicitamente richiesta nella query non so più che cosa inventarmi .... Certamente, vista la bassa probabilità di riazzeramento del totalizzatore, se non troverò una soluzione me ne farò una ragione e accetterò il risultato errato (quando MIN = 0).
Continuo a pensare che sia un mio errore, certo che, la tentazione di gridare al BUG sta crescendo.
P.S. - motivo della modifica -> tabulazione dei risultati