我有一个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 |
我希望能够按周汇总此数据总费用，我相信这是PIVOT吗？
因此，我会在给定的月份中选择它们：
"SELECT \"event_logs\".* FROM \"event_logs\" WHERE (event_logs.start_at BETWEEN '2013-10-01' AND '2013-10-31')"
然后以某种方式通过start_at（通常是一个月5周，通常是一个月？）按每周的总费用按不同的place_id和按周进行汇总。
place_id，第1周，第2周，…
但是我不确定该怎么做？
在CASE语句中使用周号
SQLFiddle演示
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
