从一个"时间条目"表中，我试图为每个用户创建一个每周总计的报告。
表格示例：
+-----+---------+-------------------------+--------------+
| id | user_id | start_time | hours_worked |
+-----+---------+-------------------------+--------------+
| 997 | 6 | 2018-01-01 03:05:00 UTC | 1.0 |
| 996 | 6 | 2017-12-01 05:05:00 UTC | 1.0 |
| 998 | 6 | 2017-12-01 05:05:00 UTC | 1.5 |
| 999 | 20 | 2017-11-15 19:00:00 UTC | 1.0 |
| 995 | 6 | 2017-11-11 20:47:42 UTC | 0.04 |
+-----+---------+-------------------------+--------------+
现在我可以运行以下程序，基本上得到我需要的
SELECT COALESCE(SUM(time_entries.hours_worked),0) AS total,
time_entries.user_id,
week::date
--Using generate_series here to account for weeks with no time entries when
--doing the join
FROM generate_series( (DATE_TRUNC('week', '2017-11-01 00:00:00'::date)),
(DATE_TRUNC('week', '2017-12-31 23:59:59.999999'::date)),
interval '7 day') as week LEFT JOIN time_entries
ON DATE_TRUNC('week', time_entries.start_time) = week
GROUP BY week, time_entries.user_id
ORDER BY week
这将返回
+-------+---------+------------+
| total | user_id | week |
+-------+---------+------------+
| 14.08 | 5 | 2017-10-30 |
| 21.92 | 6 | 2017-10-30 |
| 10.92 | 7 | 2017-10-30 |
| 14.26 | 8 | 2017-10-30 |
| 14.78 | 10 | 2017-10-30 |
| 14.08 | 13 | 2017-10-30 |
| 15.83 | 15 | 2017-10-30 |
| 8.75 | 5 | 2017-11-06 |
| 10.53 | 6 | 2017-11-06 |
| 13.73 | 7 | 2017-11-06 |
| 14.26 | 8 | 2017-11-06 |
| 19.45 | 10 | 2017-11-06 |
| 15.95 | 13 | 2017-11-06 |
| 14.16 | 15 | 2017-11-06 |
| 1.00 | 20 | 2017-11-13 |
| 0 | | 2017-11-20 |
| 2.50 | 6 | 2017-11-27 |
| 0 | | 2017-12-04 |
| 0 | | 2017-12-11 |
| 0 | | 2017-12-18 |
| 0 | | 2017-12-25 |
+-------+---------+------------+
然而，这很难解析，尤其是在一周内没有数据的情况下。我想要的是一个透视表或交叉表，其中周是列，行是用户。并包括每个的null(例如，如果用户在该周或一周内没有条目，而没有任何用户的条目)。
像这样的
+---------+---------------+--------------+--------------+
| user_id | 2017-10-30 | 2017-11-06 | 2017-11-13 |
+---------+---------------+--------------+--------------+
| 6 | 4.0 | 1.0 | 0 |
| 7 | 4.0 | 1.0 | 0 |
| 8 | 4.0 | 0 | 0 |
| 9 | 0 | 1.0 | 0 |
| 10 | 4.0 | 0.04 | 0 |
+---------+---------------+--------------+--------------+
我一直在网上四处寻找，似乎很难"动态"生成交叉表的列列表。我宁愿不硬编码，这似乎很奇怪的日期。或者使用类似于本案例的周数。
除了交叉表之外，我应该寻找其他解决方案吗？如果我能得到每个用户的一系列周，包括所有的null，我认为这就足够了。现在看来，我的加盟策略并没有回报。
就我个人而言，我会使用日期维度表，并将该表用作查询的基础。我发现在这些类型的计算中使用表格数据要容易得多，因为它使SQL更容易读取和维护。有一篇关于在PostgreSQL中创建日期维度表的好文章https://medium.com/@duffin/createing-a-date-dimension-table-in-postgresql-af3f8e2941ac，不过您可以使用该表的一个简单得多的版本。
最终，您要做的是使用Date表作为SELECT cols FROM table部分的基础，然后根据它进行连接，或者可能使用Common table Expression来创建计算。
如果您想演示如何创建这样的查询，我将为此编写一个解决方案。