SQL查询:计算时间序列中的增量 [英] SQL Query: Calculating the deltas in a time series

问题描述

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

For a development aid project I am helping a small town in Nicaragua improving their water-network-administration.

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

There are about 150 households and every month a person checks the meter and charges the houshold according to the consumed water (reading from this month minus reading from last month). Today all is done on paper and I would like to digitalize the administration to avoid calculation-errors.

我想到了一个MS Access表-例如:

I have an MS Access Table in mind - e.g.:

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

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

From this data I would like to create a query that calculates the consumed water (the meter-difference of one household between two months)

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

请，我将如何解决这个问题?

Please, how would I approach this problem?

推荐答案

即使缺少月份，此查询也会返回具有上一个日期的每个日期:

This query returns every date with previous date, even if there are missing months:

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

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

The query above will return every delta, for every households, for every month (or for every interval). If you are just interested in the last delta, you could use this query:

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

，并且(取决于您要执行的操作)，您只能过滤当前月份:

and (depending on what you are after) you could only filter current month:

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

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

this way if you miss a check for a particular household, it won't show. Or you might be interested in showing last month present in the table (which can be different than current month):

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

(这里我考虑的是支票可能在不同的日子)

(here I am taking in consideration the fact that checks might be on different days)

这篇关于SQL查询:计算时间序列中的增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！