如何查找给定日期范围之间的列总和,其中表只有开始日期和结束日期
我有一个PostgreSQL表userDistributions像这样: user_id,start_date,end_date,project_id,分布
我需要编写一个查询,其中给定的日期范围和用户ID输出应为该给定用户每天的所有分布之和。
输入的输出应为:'2-2-2012'-'2-4-2012',某些用户ID:日期总和(分配)
2012年2月2日12
2012年2月3日15
2-4 -2012 34
I have a postgresql table userDistributions like this : user_id, start_date, end_date, project_id, distribution
I need to write a query in which a given date range and user id the output should be the sum of all distributions for every day for that given user.
So the output should be like this for input : '2-2-2012' - '2-4-2012', some user id :
Date SUM(Distribution)
2-2-2012 12
2-3-2012 15
2-4-2012 34
用户在许多项目中都有分布,因此我需要每天汇总所有项目中的分布并输出当天的总和。
A user has distribution in many projects, so I need to sum the distributions in all projects for each day and output that sum against that day.
我的问题是我应该反对什么?如果我有一个字段作为日期(而不是start_date和end_date),那么我可以编写如下内容:选择日期,按日期从userDistributions组中选择SUM(distributions);
,但在这种情况下,我受其困扰。谢谢您的帮助。
My problem is what I should group by against ? If I had a field as date (instead of start_date and end_date), then I could just write something like select date, SUM(distributions) from userDistributions group by date;
but in this case I am stumped as what to do. Thanks for the help.
使用 generate_series
生成日期,例如:
Use generate_series
to produce your dates, something like this:
select dt.d::date, sum(u.distributions)
from userdistributions u
join generate_series('2012-02-02'::date, '2012-02-04'::date, '1 day') as dt(d)
on dt.d::date between u.start_date and u.end_date
group by dt.d::date
您的日期格式不明确,所以我猜将其转换为ISO 8601。
Your date format is ambiguous so I guess while converting it to ISO 8601.