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. I moved (I've been keeping this data for two decades ...
yes, I know...) to a new house, and in the new house, the utility
company has replaced the meter (one of those "smart" meters). So
the table has a foreign key reference to the primary key
identifying the meter:
CREATE TABLE electricity
(
electric_meter_pk integer,
current_reading_date date,
current_meter_reading integer
);
with sample data:
2 | 1997-04-14 | 0
2 | 1997-05-08 | 573
2 | 1997-06-12 | 1709
...
2 | 2009-09-14 |152941
3 | 2009-06-26 | 68502
3 | 2009-08-13 | 69738
...
3 | 2012-07-06 |118953
3 | 2012-07-18 |119185
4 | 2012-07-18 | 0
4 | 2012-08-06 | 887
4 | 2012-09-07 | 2158
4 | 2012-10-05 | 3018
Your suggestion almost worked as is for this, except that you
have to note that reading for meter #2 and meter #3 overlap (I
briefly owned two houses), and that seemed to confuse the lag()
function:
SELECT
electric_meter_pk,
lag(reading_date)
OVER(ORDER BY reading_date) as prev_date,
reading_date,
lag(meter_reading)
OVER(ORDER BY reading_date) AS prev_reading,
meter_reading,
meter_reading - lag(meter_reading)
OVER(ORDER BY reading_date) AS kWh_diff,
reading_date - lag(reading_date)
OVER(ORDER BY reading_date) as num_service_days
FROM electric
order by 1,3;
2 | 2009-04-09 | 2009-05-11 | 145595 | 146774 | 1179 |32
2 | 2009-05-11 | 2009-06-10 | 146774 | 148139 | 1365 |30
2 | 2009-06-26 | 2009-07-14 | 68502 | 149808 | 81306 |18
2 | 2009-07-14 | 2009-08-12 | 149808 | 151584 | 1776 |29
2 | 2009-09-12 | 2009-09-14 | 70934 | 152941 | 82007 | 2
3 | 2009-06-10 | 2009-06-26 | 148139 | 68502 |-79637 |16
3 | 2009-08-12 | 2009-08-13 | 151584 | 69738 |-81846 | 1
3 | 2009-08-13 | 2009-09-12 | 69738 | 70934 | 1196 |30
3 | 2009-09-14 | 2009-10-14 | 152941 | 71918 |-81023 |30
3 | 2009-10-14 | 2009-11-11 | 71918 | 72952 | 1034 |28
