Berend Tober wrote:
> Raymond O'Donnell wrote:
>> On 20/10/2012 17:02, Berend Tober wrote:
>>> Thalis Kalfigkopoulos wrote:
>>> How would you get the previous reading (and perhaps the
>>> previous read
>>> date) to also appear ...
>>
>> Just include them in the SELECT:
>
> Well, that is surprisingly easy!
>
> How about this then: the table includes data for more than one
> meter....
Almost answering my own question. Adding the meter key to the lag:
SELECT
electric_meter_pk,
lag(reading_date)
OVER(ORDER BY electric_meter_pk,reading_date)
as prev_date,
reading_date,
lag(meter_reading)
OVER(ORDER BY electric_meter_pk,reading_date)
AS prev_reading,
meter_reading,
meter_reading - lag(meter_reading)
OVER(ORDER BY electric_meter_pk,reading_date)
AS kWh_diff,
reading_date - lag(reading_date)
OVER(ORDER BY electric_meter_pk,reading_date)
as num_service_days
FROM home.electric
order by 1,3;
Gives all good as far as lining up dates, except it does not
cross the new-meter boundary gracefully:
2 | 2009-07-14 | 2009-08-12 | 149808 | 151584 | 1776 | 29
2 | 2009-08-12 | 2009-09-14 | 151584 | 152941 | 1357 | 33
*3 | 2009-09-14 | 2009-06-26 | 152941 | 68502 | -84439 |-80
3 | 2009-06-26 | 2009-08-13 | 68502 | 69738 | 1236 | 48
3 | 2009-08-13 | 2009-09-12 | 69738 | 70934 | 1196 | 30
...
3 | 2012-05-04 | 2012-06-07 | 116091 | 117469 | 1378 | 34
3 | 2012-06-07 | 2012-07-06 | 117469 | 118953 | 1484 | 29
3 | 2012-07-06 | 2012-07-18 | 118953 | 119185 | 232 | 12
*4 | 2012-07-18 | 2012-07-18 | 119185 | 0 | -119185 | 0
4 | 2012-07-18 | 2012-08-06 | 0 | 887 | 887 | 19
4 | 2012-08-06 | 2012-09-07 | 887 | 2158 | 1271 | 32
4 | 2012-09-07 | 2012-10-05 | 2158 | 3018 | 860 | 28
The first-row-initialization problem is what lead me to consider
a recursive CTE. I have something that works and does not use
window functions, but I think it requires more detailed
explanation than I have prepared at this time.
