postgre如何从SQL中包含起始字段和结束字段的表中创建记录的每日总和

Question

我有下表

startdate   enddate activity
1/1/2015    1/3/2015    foo
1/2/2015    1/6/2015    bar
1/4/2015    1/7/2015    bar

如何查询以获得以下每日金额？

date    foo bar
1/1/2015    1   0
1/2/2015    1   1
1/3/2015    0   1
1/4/2015    0   2
1/5/2015    0   2
1/6/2015    0   1
1/7/2015    0   0

如果有帮助的话，我会使用PSQL。

我想这是一个简单的问题，但当我搜索时似乎找不到答案。也许我没有用正确的术语来描述。感谢您的帮助

Answer 1

您可以使用generate_series()和聚合。所以，类似于：

select dte,
       sum((activity = 'foo')::int) as foo,
       sum((activity = 'bar')::int) as bar
from (select generate_series(startdate, enddate, interval '1 day') as dte,
             activity
      from t
     ) t
group by dte
order by dte;

注意，generate_series()是高度特定于Postgres的。然而，这是非常方便的目的。

Answer 2

我们可以通过生成更多的活动案例来提高它的动态性。

参考Gordon Linoff's的答案，我创建了postgresql函数，它可以使它在活动案例中更具动态性。

检查以下功能：

create or replace function  sp_test()
returns void as
$$
declare cases character varying;
declare sql_statement text;
begin

select string_agg(distinct concat('sum((activity=','''',activity,'''',' )::int) as ',activity,' '),',') into cases from your_tbl;
drop table if exists temp_data;
sql_statement=concat('create temporary table temp_data as select t.dte, ',cases,'from 
(select generate_series(startdate, enddate, interval ','''1 day''',') as dte,
         activity
      from your_tbl) as t group by t.dte order by t.dte');
execute sql_statement;

end;
$$
language 'plpgsql';

函数使用临时表来存储动态列数据

以以下方式调用函数以获取数据：

select * from sp_test(); select * from temp_data;