在Postgres中的generate

在Postgres中的generate_series上加入提取（DATE的日期）(Joining extract (day from DATE) on generate_series in Postgres)

我正在尝试按月中的某天生成累计销售额（这样我可以在同一个图表上绘制多个月，在X轴上显示月份日期，在Y轴上显示累计金额总和），但是当我左键加入时到generate_series（1,31），我得到：

d sum 1 3746.5 2 5731.5 3 9258.42

而我想要的是：

d sum 1 3746.5 2 5731.5 3 9258.42 4 0 5 0 ... 0 31 0

这是我的查询：

SELECT d, SUM (SUM("Amount")) OVER (ORDER BY "CloseDate" ASC) FROM salesforce."Opportunity" LEFT JOIN generate_series (1, 31) d ON d = EXTRACT (DAY FROM "CloseDate") WHERE EXTRACT (MONTH FROM "CloseDate") = EXTRACT (MONTH FROM now()) AND EXTRACT (YEAR FROM "CloseDate") = EXTRACT (YEAR FROM now()) AND "StageName" = 'Closed Won' AND "Type" = 'Renewal' GROUP BY "CloseDate", d

我可以感觉到我做错了什么，但我不确定是什么。任何帮助将不胜感激 - 谢谢！

I'm trying to generate a cumulative sales amounts by day of the month (so that I can graph multiple months on the same chart, with day of month on X axis and cumulative sum of amount on Y axis), but when I LEFT join onto generate_series(1,31), I'm getting:

d sum 1 3746.5 2 5731.5 3 9258.42

Whereas what I want is:

d sum 1 3746.5 2 5731.5 3 9258.42 4 0 5 0 ... 0 31 0

Here is my query:

I can feel that I'm doing something wrong, but I'm not sure what. Any help would be appreciated - thanks!!

最满意答案

对。您需要将您的聚合放在子查询中，然后首先从generate_series中选择，然后左键连接：

WITH days as ( select d from generate_series (1, 31) d), aggregates as ( SELECT EXTRACT (DAY FROM "CloseDate") d, SUM (SUM("Amount")) OVER (ORDER BY "CloseDate" ASC) total FROM salesforce."Opportunity" WHERE EXTRACT (MONTH FROM "CloseDate") = EXTRACT (MONTH FROM now()) AND EXTRACT (YEAR FROM "CloseDate") = EXTRACT (YEAR FROM now()) AND "StageName" = 'Closed Won' AND "Type" = 'Renewal' GROUP BY "CloseDate" ) select days.d, aggregates.total from days left join aggregates on days.d = aggregates.d order by days.d;

如果将group by放在子查询中然后将该子查询连接到几天，那么它是最简单和最可读的。您还可以添加更多子查询并将它们连接起来，以便在同一个表中拥有更多聚合，甚至来自不同的源表。

Right. You need to put your aggregate in subquery and first select from generate_series then left join on that:

It's easiest and most readable if you put group by in a subquery and then left join that subquery to days. You can also add more subqueries and join them to have more aggregates, even from different source tables, in same table.

更多推荐