计算 PostgreSQL 中按月聚合的年份之间的销售额差异

Question

我在PostgreSQL中有一个销售表，有两列：销售和日期。例如，使用以下数据：

Date         Sales
2017-02-05   600
2017-03-01   800
2018-01-10   300
2018-02-02   500
2018-02-03   300
2018-03-01   800

我需要计算按月汇总的年份之间的销售额差异。

结果应该是这样的：

Month  sales_2017   sales_2018   variance
Jan    null         300          300
Feb    600          800          200
Mar    800          800          0

应该是什么查询才能获得上面的结果？

Answer 1

demo：db<>fiddle

SELECT
    to_char(to_date(date_part::text, 'MM'), 'Mon') as month,                           -- 3
    sales_2017,
    sales_2018,
    COALESCE(sales_2018, 0) - COALESCE(sales_2017, 0) as variance                      -- 4
FROM (
    SELECT
        date_part('month', sales_date),                                                -- 1
        SUM(sales) FILTER (WHERE date_part('year', sales_date) = 2017) as sales_2017,  -- 2
        SUM(sales) FILTER (WHERE date_part('year', sales_date) = 2018) as sales_2018
    FROM
        sales
    GROUP BY 1                                                                         -- 1
)s
ORDER BY date_part

1. date_part给出日期的月份编号。这允许按月份分组，而无需日期的年份
2. 这是一个简单的透视函数：使用某个过滤器SUM值（在本例中，过滤器是年份）。这允许仅聚合单个年份的值
3. 根据月份编号计算月份名称（使用 to_date 重新创建 date 类型，然后用 to_char 格式化）。'Mon'格式化程序给出了三个字母的缩写。
计算
4. 前面计算列中的variance。 COALESCE 将 NULL 值转换为数字0

Postgres date 函数