SQL Postgres按周汇总/汇总数据的总数 [英] SQL postgres aggregation/pivot of data by weeks with totals
问题描述
我有一个EventLogs表,该表记录了给定事件的详细信息,例如事件的日期和费用.
I have a table EventLogs which records a given Event's details such as the date of the event and the fee.
+------+----------+---------------------------+-------------------+
| id | place_id | start_at | total_fee_pennies |
+------+----------+---------------------------+-------------------+
| 4242 | 40 | 2013-10-20 19:00:00 +0100 | 8700 |
| 4288 | 48 | 2013-10-22 20:00:00 +0100 | 8000 |
| 4228 | 141 | 2013-10-17 19:30:00 +0100 | 20000 |
| 4232 | 19 | 2013-10-20 19:30:00 +0100 | 8000 |
| 4239 | 5 | 2013-10-20 19:30:00 +0100 | 6800 |
| 4269 | 6 | 2013-10-20 20:00:00 +0100 | 7000 |
| 4234 | 98 | 2013-10-20 20:00:00 +0100 | 6900 |
我希望能够按周汇总此数据总费用,我相信这是PIVOT吗?
I would like to be able to aggregate this data total fee by week, I believe this is a PIVOT?
所以我要在给定的月份中选择它们:
So I'd select them for a given month:
"SELECT \"event_logs\".* FROM \"event_logs\" WHERE (event_logs.start_at BETWEEN '2013-10-01' AND '2013-10-31')"
然后以某种方式通过start_at(通常是一个月5周,通常是一个月?)按每周的总费用按不同的place_id和每周进行汇总.
And then somehow aggregate them by distinct place_id and by week using start_at (5 weeks in a month, usually?) with the total fee for each week.
place_id,第1周,第2周,...
place_id, week 1, week2, ...
但是我不确定该怎么做?
But I'm not sure how to do this?
推荐答案
在这里您可以找到如何提取周数.然后在CASE语句中使用周号
Here you can find how to extract the week number. Then use a Week number in the CASE statement
WITH T AS
(
SELECT
EventLogs.*
,
extract(week from start_at) -
extract(week from date_trunc('month', start_at)) + 1 as WeekNo
FROM EventLogs
WHERE (start_at BETWEEN '2013-10-01' AND '2013-10-31')
)
SELECT
place_id,
SUM(CASE WHEN WeekNo=1 THEN total_fee_pennies ELSE 0 END) as Week_1,
SUM(CASE WHEN WeekNo=2 THEN total_fee_pennies ELSE 0 END) as Week_2,
SUM(CASE WHEN WeekNo=3 THEN total_fee_pennies ELSE 0 END) as Week_3,
SUM(CASE WHEN WeekNo=4 THEN total_fee_pennies ELSE 0 END) as Week_4,
SUM(CASE WHEN WeekNo=5 THEN total_fee_pennies ELSE 0 END) as Week_5
from T
GROUP BY place_id
这篇关于SQL Postgres按周汇总/汇总数据的总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!