akretschmer
Datenbank-Guru
- Beiträge
- 10.333
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.