SQL:在一段时间内计算的总和之间的差额

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

我想要写一个查询，显示给定时期内的总wine_glasses_drunk、总whisky_shots_drunk和总beer_bottles_drunk与前一个时期的总和之间的差异。

听起来可能比现在复杂多了。如果我们使用的句点*为1 week == 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显然是一个黑客(特别是f1.data_entry_date - f2.data_entry_date between 6 and 9标准)。我在excel中检查了结果，上面查询的结果(毫不知情地)是错误的。

我如何编写这个查询--以及如何修改它，以便它能够处理数据库中的非连续日期？

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

Answer 1

我本来打算把这个作为我的另一个答案的编辑，但是它实际上是一种不同的方法，所以应该是一个单独的答案。

我想我更喜欢我给出的另一个答案，但即使数据有缺口，这个答案也应该有效。

若要设置查询的参数，请更改with子句的period_start_date部分和period_days部分中的query_params值。

with query_params as (
  select 
    date '2011-01-01' as period_start_date,
    7 as period_days
),
summary_data as (
select
  user_id,
  (data_entry_date - period_start_date)/period_days as period_number,
  sum(wine_glasses_drunk) as wine_glasses_drunk,
  sum(whisky_shots_drunk) as whisky_shots_drunk,
  sum(beer_bottle_drunk) as beer_bottle_drunk
from foobar
  cross join query_params
group by user_id,
  (data_entry_date - period_start_date)/period_days
)
select
  user_id,
  period_number,
  period_start_date + period_number * period_days as period_start_date,
  sum(wine_glasses_drunk) as wine_glasses_drunk,
  sum(whisky_shots_drunk) as whisky_shots_drunk,
  sum(beer_bottle_drunk) as beer_bottle_drunk
from (
  -- this weeks data
  select 
    user_id,
    period_number,
    wine_glasses_drunk,
    whisky_shots_drunk,
    beer_bottle_drunk
  from summary_data
  union all
  -- last weeks data
  select 
    user_id,
    period_number + 1 as period_number,
    -wine_glasses_drunk as wine_glasses_drunk,
    -whisky_shots_drunk as whisky_shots_drunk,
    -beer_bottle_drunk as beer_bottle_drunk
  from summary_data
) a
cross join query_params
where period_number <= (select max(period_number) from summary_data)
group by 
  user_id,
  period_number,
  period_start_date + period_number * period_days
order by 1, 2

同样，SQL Fiddle也是可用的。

Answer 2

从您给出的描述中，我不完全确定我是否会这样做，但是我会使用两个不同的函数来获得您想要的结果。

首先，看看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

给你看的可以使用SQL花弦。。

顺便说一句，我来自甲骨文背景，并使用PostgreSQL文档和SQL对此进行了黑客攻击。希望这就是你所需要的。

Answer 3

一种稍微不同的方法(我将让您填写日期参数)：

Declare @StartDate1, @EndDate1, @StartDate2, @EndDate2 AS Date
Set @StartDate1='6/1/2012'
Set @EndDate1='6/15/2012'
Set @StartDate2='6/16/2012'
Set @EndDate2='6/30/2012'

SELECT SUM(U.WineP1)-SUM(U.WineP2) AS WineDiff, SUM(U.WhiskeyP1)-SUM(U.WhiskeyP2) AS WhiskeyDiff, SUM(U.BeerP1)-SUM(U.BeerP2) AS BeerDiff
FROM
(
SELECT SUM(wine_glasses_drunk) AS WineP1, SUM(whisky_shots_drunk) AS WhiskeyP1, SUM(beer_bottle_drunk) AS BeerP1, 0 AS WineP2, 0 AS WhiskeyP2, 0 AS BeerP2
FROM foobar
WHERE data_entry_date BETWEEN @StartDate1 AND @EndDate1

UNION ALL

SELECT 0 AS WineP1, 0 AS WhiskeyP1, 0 AS BeerP1, SUM(wine_glasses_drunk) AS WineP2, SUM(whisky_shots_drunk) AS WhiskeyP2, SUM(beer_bottle_drunk) AS BeerP2
FROM foobar
WHERE data_entry_date BETWEEN @StartDate2 AND @EndDate2
) AS U