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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
