我有一个EventLogs表，它记录了给定事件的详细信息，比如事件的日期和费用。
+------+----------+---------------------------+-------------------+
| 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 |
我希望能够按周汇总此数据总费用，我相信这是一个支点吗？
所以我会在一个月内选择它们：
"SELECT \"event_logs\".* FROM \"event_logs\" WHERE (event_logs.start_at BETWEEN '2013-10-01' AND '2013-10-31')"
然后以某种方式将它们按不同的地点和周进行聚合，使用start at（通常是一个月内的5周？）每周的总费用。
地点，第一周，第二周。。。
但我不知道怎么做？
最佳答案
在这里您可以找到how to extract the week number。然后在CASE语句中使用周数
SQLFiddle demo
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 - SQL postgres按周汇总/汇总数据的总数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19709395/