sql - 计算包括数据缺失日期在内的月平均值

Question

我想使用 SQL 查询来计算某些数据的月平均值，其中数据驻留在 redshift DB 中。
数据以下列格式出现在表中。

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

数据可能不存在于一个月中的所有日期。 如果一天中没有数据，则应将其视为 0。
以下查询使用 AVG() 函数“group by”月份作为基于可用日期数据的平均值。

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

但是，它不会将缺失日期的数据视为 0 .按预期计算月平均值的正确查询应该是什么？
还有一个期望是，对于当月，应该根据截至今天的数据计算平均值。所以它不应该考虑整个月(比如 30 或 31 天)。
问候，
保罗

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 中生成一个中间表，其中包含数据集中每个数据的一条记录，以及该日期的总销售额。然后，我们按年/月汇总，并报告平均销售额。