生成包含空月份的月份数据系列? [英] Generate month data series with null months included?

问题描述

我返回了一个简单的日历年每月数据集，但我知道我会缺少月份(即没有数据).

I have a simple calendar year monthly data set returned but I know I will have missing months (i.e. no data).

我在这里找到了这个答案:

I found this answer here:

对记录进行计数的最佳方法在Rails + Postgres中按任意时间间隔

已接近，但我不理解 JOIN USING 语法.

It's close but I do not understand the JOIN USING syntax.

大部分情况下，我都想尽办法:

I have my side figured out for the most part:

SELECT TO_CHAR(activity_tasks.start_date, 'MON') AS month,
EXTRACT(MONTH FROM activity_tasks.start_date) AS month_num,
SUM(cost_planned) FILTER (WHERE activity_tasks.aasm_state IN ('open', 'planned' ) )  AS planned,
SUM(cost_actual) FILTER (WHERE activity_tasks.aasm_state IN ('closed' ) ) AS actual
FROM "activity_tasks"
WHERE activity_tasks.start_date >= '2020-01-01' AND activity_tasks.start_date <= '2020-12-31'
GROUP BY month, month_num
ORDER BY month_num

哪个会吸引我:

+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| NOV   | 11        | NULL    | 123    |
| DEC   | 12        | 500     | NULL   |
+-------+-----------+---------+--------+

我只想加入:

SELECT *
FROM  (
   SELECT to_char(m, 'MON') AS mmm
   FROM generate_series('2020-01-01', '2020-12-31', interval '1 month') m
) m

输入一个简单表达式，并具有完整的JAN-DEC数据集，其中包括空白月份.

in to one simple expression and have a full JAN-DEC dataset including the empty months.

奖金附带问题:有没有更优雅的SQL来构建 generate_series('2020-01-01'，'2020-12-31'，间隔'1个月')数组，并设置我的 WHERE activity_tasks.start_date> ='2020-01-01'AND activity_tasks.start_date< ='2020-12-31'?在我的情况下，默认值为给定年份的1月1日，但有时会计年度为其他月份，并希望避免此处重复逻辑，也许只输入一个开始日期即可.

Bonus Side Question: is there a more elegant SQL to build the generate_series('2020-01-01', '2020-12-31', interval '1 month') array of months and set my WHERE activity_tasks.start_date >= '2020-01-01' AND activity_tasks.start_date <= '2020-12-31'? In my case the default is Jan 1 of a given year BUT there may be a case where the accounting fiscal year is some other month and looking to avoid duplicate logic here and perhaps just feed in a single start date.

推荐答案

您可以使用 generate_series()生成所有月份的开始，然后使用左连接:

You can generate all starts of months with generate_series(), then bring the table with a left join:

select 
    to_char(d.start_date, 'mon') as month,
    extract(month from d.start_date) as month_num,
    sum(cost_planned) filter (where t.aasm_state in ('open', 'planned' ) )  as planned,
    sum(cost_actual)  filter (where t.aasm_state = 'closed') as actual
from generate_series('2020-01-01'::date, '2020-12-01'::date, '1 month') d(start_date)
left join activity_tasks t
    on t.start_date >= d.start_date and t.start_date < d.start_date + '1 month'::interval
group by start_date
order by start_date

您可以轻松地更改 generate_series()的参数以适应不同的会计年度.

You can easily change the arguments of generate_series() to accomodate a different fiscal year.

这篇关于生成包含空月份的月份数据系列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！