SQL:一段时间内计算的 SUM 之间的差异

Question

我有一个看起来像这样的表:

CREATE TABLE foobar (
                     id                     SERIAL PRIMARY KEY,
                     data_entry_date        DATE NOT NULL,
                     user_id                INTEGER NOT NULL,
                     wine_glasses_drunk     INTEGER NOT NULL,
                     whisky_shots_drunk     INTEGER NOT NULL,
                     beer_bottle_drunk      INTEGER NOT NULL
                 );

insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-01', 1, 1,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-02', 1, 4,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-03', 1, 0,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-04', 1, 1,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-05', 1, 2,1,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-07', 1, 1,2,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-08', 1, 4,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-11', 1, 1,1,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-12', 1, 1,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-13', 1, 2,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-14', 1, 1,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-15', 1, 9,3,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-16', 1, 0,4,2);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-17', 1, 0,5,3);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-18', 1, 2,2,5);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-20', 1, 1,1,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-23', 1, 1,3,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-01-24', 1, 0,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-01', 1, 1,1,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-02', 1, 2,3,4);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-05', 1, 1,2,2);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-09', 1, 0,0,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-10', 1, 1,1,1);
insert into foobar (data_entry_date, user_id, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk) VALUES ('2011-02-11', 1, 3,6,3);

我想编写一个查询，显示给定时间段内 TOTAL wine_glasses_drunk、TOTAL whisky_shots_drunk 和 TOTAL beer_bottles_drunk 与上一时间段的 TOTAL 的差异。

这听起来可能比实际情况要复杂。如果我们使用的周期*为 1 周 == 7 天，则查询应返回本周消耗总量与消耗总量的差值上周。

有点复杂的是表中的日期不连续 - 即缺少一些日期，因此查询需要在确定期间计算日期时找到最相关的日期。

This is what I have so far:

-- using hard coded dates

SELECT (SUM(f1.wine_glasses_drunk) - SUM(f2.wine_glasses_drunk)) as wine_diff, 
(SUM(f1.whisky_shots_drunk) - SUM(f2.whisky_shots_drunk)) as whisky_diff, 
(SUM(f1.beer_bottle_drunk) - SUM(f2.beer_bottle_drunk)) as beer_diff 
FROM foobar f1 INNER JOIN foobar f2 ON f2.user_id=f1.user_id
WHERE f1.user_id=1 
AND f1.data_entry_date BETWEEN '2011-01-08' AND '2011-01-15'
AND f2.data_entry_date BETWEEN '2011-01-01' AND '2011-01-08'
AND f1.data_entry_date - f2.data_entry_date between 6 and 9;

上面的 SQL 显然是一个 hack(尤其是 f1.data_entry_date - f2.data_entry_date between 6 and 9 标准)。我在 excel 中检查了结果，上面查询的结果(不出所料)是错误的。

我如何编写此查询 - 我如何修改它以便它可以处理数据库中的非连续日期？

我正在使用 postgreSQl，但如果可能的话我更喜欢与数据库无关(即 ANSI)的 SQL。

Answer 1

根据您给出的描述，我不能完全确定我是否以正确的方式进行此操作，但我会使用两种不同的函数来获得您想要的结果。

首先，看一下date_trunc 函数。这可以获得一周第一天的日期，您可以对其进行分组以获得一周的总和。如果一周的第一天不是您想要的，您可以使用日期算法来解决这个问题。我想这个星期的第一天是星期一。

其次，您可以使用滞后窗口函数求出前一行的总和。请注意，如果您缺少一周，此函数将查看前一行，而不仅仅是前一周。我在查询中进行了检查，以确保数据库正在查看正确的行。

select 
  user_id,
  week_start_date,
  this_week_wine_glasses_drunk -
    case when is_consecutive_weeks = 'TRUE' 
      then last_week_wine_glasses_drunk else 0 end as wine_glasses_drunk,
  this_week_whisky_shots_drunk -
    case when is_consecutive_weeks = 'TRUE' 
      then last_week_whisky_shots_drunk else 0 end as whisky_shots_drunk,
  this_week_beer_bottle_drunk -
    case when is_consecutive_weeks = 'TRUE' 
      then last_week_beer_bottle_drunk else 0 end as beer_bottle_drunk
from (
select
  user_id,
  week_start_date,
  this_week_wine_glasses_drunk,
  this_week_whisky_shots_drunk,
  this_week_beer_bottle_drunk,
  case when (lag(week_start_date)
    over (partition by user_id order by week_start_date)  + interval '7' day)
      = week_start_date then 'TRUE' end as is_consecutive_weeks,
  lag(this_week_wine_glasses_drunk) 
    over (partition by user_id order by week_start_date) as last_week_wine_glasses_drunk,
  lag(this_week_whisky_shots_drunk) 
    over (partition by user_id order by week_start_date) as last_week_whisky_shots_drunk,
  lag(this_week_beer_bottle_drunk) 
    over (partition by user_id order by week_start_date) as last_week_beer_bottle_drunk
from (
  select
    user_id,
    date_trunc('week', data_entry_date) as week_start_date,
    sum(wine_glasses_drunk) as this_week_wine_glasses_drunk,
    sum(whisky_shots_drunk) as this_week_whisky_shots_drunk,
    sum(beer_bottle_drunk) as this_week_beer_bottle_drunk
  from foobar
  group by user_id,
    date_trunc('week', data_entry_date)
  ) a
) b

A SQL fiddle is available给你看看。

顺便说一句，我有 Oracle 背景，并且使用 PostgreSQL 文档和 SQL Fiddle 解决了这个问题。希望这就是您所需要的。