累积差异 [英] Cumulative Difference

问题描述

我有一张桌子

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

读数是一个累积值，在这里我需要计算与上个月和当月的差额.

The reading is a cumulative value where i would need to calculate the difference from the previous month and the current month.

您能帮我弄清楚如何生成一个视图，以便查看每个月的消耗量(前一个月的读数-本月的读数)吗?

Could you help me figure out how to generate a view where i can see the consumption (previous month reading - current month reading) for each month?

我试过了between函数:

I had tried the between function:

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;

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

but all i got was the readings for each month not the difference.

我该如何列出每月的消费量?

How could I make it list the monthly consumption?

推荐答案

您可以使用LAG函数获取上个月的读数.您发布的查询引用了三个表-ServiceAddress，Reading和Meter，这三个表都不是您发布其结构和数据的Meter_Reading表.由于我不确定这些表中的数据是什么样子，所以将忽略您发布的查询，而将重点放在为之发布数据的Meter_Reading表上

You can use the LAG function to get the reading for the prior month. The query you posted references three tables-- ServiceAddress, Reading, and Meter none of which are the Meter_Reading table you posted the structure and data for. I'll ignore the query you posted since I'm not sure what the data in those tables looks like and focus on the Meter_Reading table that you posted data for

SELECT MeterID, 
       DateRead,
       Reading,
       PriorReading,
       Reading - PriorReading AmountUsed
  FROM (SELECT MeterID,
               DateRead,
               Reading,
               nvl(lag(Reading) over (partition by MeterID 
                                          order by DateRead), 
                   0) PriorReading
          FROM meter_reading)

我假设，如果没有先前的读数，则要假设先前的读数为0

I assume that if there is no prior reading that you want to assume that the prior reading was 0

这篇关于累积差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！