SQL查询:计算时间序列中的增量

Question

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

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

我有一个MS访问表-例如：

*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)

(在这里，我考虑到检查可能在不同的日期进行)

Answer 2

我认为最好的方法是使用相关子查询来获取之前的日期，然后连接回原始表。这可以确保您获得之前的记录，即使有或多或少1个月的延迟。

因此，正确的查询如下所示：

select t.*, tprev.date, tprev.meter
from (select t.*,
             (select top 1 date from t t2 where t2.date < t.date order by date desc
             ) prevDate
      from t
     ) join
     t tprev
     on tprev.date = t.prevdate

在您所描述的环境中，不要对仪表的读数频率做出假设，这一点非常重要。尽管它们可能平均每月阅读一次，但总会有例外。

Answer 3

使用以下数据进行测试：

HousholdID  Date        Meter
0           01/12/2012  100
1           01/12/2012  130
0           01/01/2013  120
1           01/01/2013  140
0           01/02/2013  120
1           01/02/2013  140

以下查询：

SELECT a.housholdid, 
   a.date, 
   b.date, 
   a.meter, 
   b.meter, 
   a.meter - b.meter AS Consumption
FROM   (SELECT * 
    FROM   water 
    WHERE  Month([date]) = Month(Date()) 
           AND Year([date])=year(Date())) a 
   LEFT JOIN (SELECT *
              FROM water
              WHERE DateSerial(Year([date]),Month([date]),Day([date]))
               =DateSerial(Year(Date()),Month(Date())-1,Day([date])) ) b 
   ON a.housholdid = b.housholdid 

上面的查询选择本月Month([date]) = Month(Date())的记录，并将它们与上个月([date]) = Month(Date()) - 1)的记录进行比较

请不要使用Date作为字段名。

返回以下结果。

housholdid  a.date      b.date      a.meter b.meter Consumption
0           01/02/2013  01/01/2013  120     100     20
1           01/02/2013  01/01/2013  140     130     10