Einzelne Messwerte in Auswertungstabelle zusammenfassen

Code: 
test=*# select * from (select messort, nr, datum as "DatumVon", lead(datum) over (partition by messort,nr order by datum) as "DatumBis", lead(messung) over (partition by messort,nr order by datum) - messung as "Delta" from nomos order by messort desc,nr desc, datum) foo where "Delta" is not null;
 messort |  nr  |  DatumVon  |  DatumBis  | Delta
---------+--------+------------+------------+-------
 azz  | Z45678 | 2010-01-01 | 2010-06-01 |  143
 azz  | Z45678 | 2010-06-01 | 2010-09-01 |  46
 azz  | Z45678 | 2010-09-01 | 2010-10-12 |  43
 azz  | Y95492 | 2010-10-12 | 2011-01-01 |  245
 azz  | Y95492 | 2011-01-01 | 2012-01-01 |  634
 ayy  | Z54679 | 2010-01-01 | 2011-01-01 |  327
 ayy  | Z54679 | 2011-01-01 | 2012-01-01 |  627
(7 Zeilen)

test=*# select * from (select messort, nr, datum as "DatumVon", lead(datum) over (partition by messort,nr order by datum) as "DatumBis", lead(messung) over (partition by messort,nr order by datum) - messung as "Delta" from nomos order by messort desc,nr desc, datum) foo ;
 messort |  nr  |  DatumVon  |  DatumBis  | Delta
---------+--------+------------+------------+-------
 azz  | Z45678 | 2010-01-01 | 2010-06-01 |  143
 azz  | Z45678 | 2010-06-01 | 2010-09-01 |  46
 azz  | Z45678 | 2010-09-01 | 2010-10-12 |  43
 azz  | Z45678 | 2010-10-12 |  |   
 azz  | Y95492 | 2010-10-12 | 2011-01-01 |  245
 azz  | Y95492 | 2011-01-01 | 2012-01-01 |  634
 azz  | Y95492 | 2012-01-01 |  |   
 ayy  | Z54679 | 2010-01-01 | 2011-01-01 |  327
 ayy  | Z54679 | 2011-01-01 | 2012-01-01 |  627
 ayy  | Z54679 | 2012-01-01 |  |   
(10 Zeilen)

test=*#

PS.: auch wenn MySQL nun Window-Funktionen (Window ohne s) kann, bleiben vermutlich reichlich Dinge, die es weiterhin nicht kann.
 
Werbung:
Zurück
Oben