计算月平均值，包括数据丢失的日期

Question

我想使用SQL查询来计算一些数据的月平均值，其中数据位于红移数据库中。数据以以下格式显示在表中。

s_date   | sales 
------------+-------
2020-08-04 |    10
2020-08-05 |    20
----     |    --
----     |    --

数据可能不存在于一个月中的所有日期如果数据在一天内不存在，则应将其视为0。
使用AVG((函数进行以下查询"分组依据"；月as根据可用日期的数据给出的平均值。

select trunc(date_trunc('MONTH', s_date)::timestamp) as month, avg(sales) from sales group by month;

但是，它不认为缺少日期的数据为0。按照预期计算月平均值的正确查询应该是什么？

还有一个期望是，对于本月，平均值应该根据截至今天的数据计算。所以它不应该考虑整个月(比如30或31天(。

问候，
Paul

Answer 1

使用日历表可能是最简单的方法：

WITH dates AS (
SELECT date_trunc('day', t)::date AS dt
FROM generate_series('2020-01-01'::timestamp, '2020-12-31'::timestamp, '1 day'::interval) t
),
cte AS (
SELECT t.dt, COALESCE(SUM(s.sales), 0) AS sales
FROM dates t
LEFT JOIN sales s ON t.dt = s.s_date
GROUP BY t.dt
)
SELECT
LEFT(dt::text, 7) AS ym,
AVG(sales) AS avg_sales
FROM cte
GROUP BY
LEFT(dt::text, 7);

这里的逻辑是首先在第二个CTE中生成一个中间表，该表为数据集中的每个数据都有一条记录，以及该日期的总销售额。然后，我们按年/月进行汇总，并报告平均销售额。