postgresql - 使用 PostgreSQL 创建数据透视表

Question

我有一个这样的表:

type    code    desc    store  Sales/Day  Stock
-----------------------------------------------
1       AA1     abc     101        3        6
1       AA2     abd     101        4        0
1       AA3     abf     101        4        3
2       BA1     bba     101        5        1
2       BA2     bbc     101        2        1
1       AA1     abc     102        1        4
1       AA2     abd     102        2        0
2       BA1     bba     102        4        2
2       BA2     bbc     102        5        5
etc.

我怎样才能像这样显示结果表:

type    code    desc         Store 101             Store 102
                        Sales/Day | Stock    Sales/Day | Stock
--------------------------------------------------------------
1       AA1     abc        3          6         1          4
1       AA2     abd        4          0         2          0
1       AA3     abf        4          3         0          0
2       BA1     bba        5          1         4          2
2       BA2     bbc        2          1         5          5
etc.

注意:Colspan 只是显示。

Answer 1

demo:db<>fiddle

第一种方式:FILTER

SELECT
    type,
    code,
    "desc",
    COALESCE(SUM(sales_day) FILTER (WHERE store = 101)) as sales_day_101,
    COALESCE(SUM(stock) FILTER (WHERE store = 101), 0) as stock_101,
    COALESCE(SUM(sales_day) FILTER (WHERE store = 102), 0) as sales_day_102,
    COALESCE(SUM(stock) FILTER (WHERE store = 102), 0) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code

汇总您的值(value)观。我采用了 SUM 但在您的情况下，对于不同的行，许多其他聚合函数可以做到这一点。 FILTER 允许您仅聚合一个商店。

COALESCE 是为了避免在一个聚合中没有值时出现 NULL 值(例如商店 102 中的 AA3)。

---

第二种方式，CASE WHEN

SELECT
    type,
    code,
    "desc",
    SUM(CASE WHEN store = 101 THEN sales_day ELSE 0 END) as sales_day_101,
    SUM(CASE WHEN store = 101 THEN stock ELSE 0 END) as stock_101,
    SUM(CASE WHEN store = 102 THEN sales_day ELSE 0 END) as sales_day_102,
    SUM(CASE WHEN store = 102 THEN stock ELSE 0 END) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code

思路是一样的，但是更新的FILTER函数被更常见的CASE子句所取代。

---

注意“desc”是 Postgres 中的保留字。所以我强烈建议重命名您的专栏。