sql - 累计差异

Question

我有一张 table

Meter_Reading

MeterID    |    Reading     |     DateRead     |
1               10                1-Jan-2012
1               20                2-Feb-2012
1               30                1-Mar-2012
1               60                2-Apr-2012
1               80                1-May-2012

读数是一个累积值，我需要计算上个月和当月的差异。

你能帮我弄清楚如何生成一个 View ，让我可以看到每个月的消耗情况(上个月的读数 - 当月的读数)吗？

我尝试过 Between 函数:

select address, reading as Consumption, dateread
from ServiceAddress, reading, meter
where address like '53 Drip Drive%'
and dateread 
between (to_date('01-JAN-2012','DD-MON-YYYY')) and (to_date('30-SEP-2012', 'DD-MON-YYYY'))
and serviceaddress.serviceaddid = meter.serviceaddid and meter.meterid = reading.meterid;

但我得到的只是每个月的读数，而不是差异。

如何让它列出每月的消费量？

Answer 1

尝试使用analytic functions 。像这样的事情应该可以解决问题:

SELECT meterid, dateread, 
    reading - LAG(reading, 1, 0) OVER(PARTITION BY meterid ORDER BY dateread)
FROM meter_reading