SQL查询: Calculating the deltas in a time series

Question

在一个发展援助项目中，我正在帮助尼加拉瓜的一个小镇改善其供水网络管理。

大约有150户家庭，每个月都会有人查表，并根据用水量(本月读数减去上个月读数)向家庭收取费用。今天一切都是在纸上完成的，我希望将管理数字化以避免计算错误。

我心中有一个 MS Access 表 - 例如:

*HousholdID*  *Date*     *Meter*
0             1/1/2013   100
1             1/1/2013   130
0             1/2/2013   120
1             1/2/2013   140
...

根据这些数据，我想创建一个查询来计算消耗的水量(一个家庭两个月之间的水表差异)

*HouseholdID*  *Date*     *Consumption*
0              1/2/2013   20
1              1/2/2013   10
...

请问我该如何解决这个问题？

Answer 1

此查询返回每个日期及其前一个日期，即使缺少月份:

SELECT TabPrev.*, Tab.Meter as PrevMeter, TabPrev.Meter-Tab.Meter as Diff
FROM (
  SELECT
    Tab.HousholdID,
    Tab.Data,
    Max(Tab_1.Data) AS PrevData,
    Tab.Meter
  FROM
    Tab INNER JOIN Tab AS Tab_1 ON Tab.HousholdID = Tab_1.HousholdID
                                 AND Tab.Data > Tab_1.Data
  GROUP BY Tab.HousholdID, Tab.Data, Tab.Meter) As TabPrev
  INNER JOIN Tab
  ON TabPrev.HousholdID = Tab.HousholdID
     AND TabPrev.PrevData=Tab.Data

结果如下:

HousholdID  Data        PrevData    Meter  PrevMeter  Diff
----------------------------------------------------------
0           01/02/2013  01/01/2013  120    100        20
1           01/02/2013  01/01/2012  140    130        10

上面的查询将返回每个家庭、每个月(或每个时间间隔)的每个增量。如果您只对最后一个增量感兴趣，可以使用以下查询:

SELECT
  MaxTab.*,
  TabCurr.Meter as CurrMeter,
  TabPrev.Meter as PrevMeter,
  TabCurr.Meter-TabPrev.Meter as Diff
FROM ((
  SELECT
    Tab.HousholdID,
    Max(Tab.Data) AS CurrData,
    Max(Tab_1.Data) AS PrevData
  FROM
    Tab INNER JOIN Tab AS Tab_1
        ON Tab.HousholdID = Tab_1.HousholdID
           AND Tab.Data > Tab_1.Data
  GROUP BY Tab.HousholdID) As MaxTab
  INNER JOIN Tab TabPrev
  ON TabPrev.HousholdID = MaxTab.HousholdID
     AND TabPrev.Data=MaxTab.PrevData)
  INNER JOIN Tab TabCurr
  ON TabCurr.HousholdID = MaxTab.HousholdID
     AND TabCurr.Data=MaxTab.CurrData

并且(取决于您的目的)您只能过滤当前月份:

WHERE
  DateSerial(Year(CurrData), Month(CurrData), 1)=
  DateSerial(Year(DATE()), Month(DATE()), 1)

这样，如果您错过了某个特定家庭的支票，它就不会显示。或者您可能有兴趣在表中显示上个月(可能与当前月份不同):

WHERE
  DateSerial(Year(CurrData), Month(CurrData), 1)=
  (SELECT MAX(DateSerial(Year(Data), Month(Data), 1))
  FROM Tab)

(这里我考虑到支票可能在不同日期进行的事实)