PostgreSQL基于二值月份列创建客户活跃交叉聚合表SQL实现

现有宽表Table1，字段包含customerid（客户ID）、jan21/feb21/mar21/apr21（2021年1-4月客户活跃二值标识，1代表当月活跃，0代表当月不活跃），样例数据如下：
Table1

需基于Table1生成聚合结果表Table2，结构如下：
Table2

统计规则：以行维度的月份为基准月，统计该月活跃的客户群体中，在各列对应月份也活跃的客户总数；对角线左下侧因数据对称可留空，也可填充完整对称值。

目前靠写大量子查询做统计，每次算单个基准月数据都要手动改WHERE条件（比如替换成jan21 = 1、feb21 = 1这类条件）逐次计算，操作麻烦、效率低，需要一个可以一次性跑完所有统计的PostgreSQL SQL实现。

当前在用的SQL如下（已经包含Table1的生成逻辑）：

select 
sum(jan21) as jan21,
sum(feb21) as feb21,
sum(mar21) as mar21,
sum(apr21) as apr21 

from(
select customerid, 
sum(jan21) as jan21,
sum(feb21) as feb21,
sum(mar21) as mar21,
sum(apr21) as apr21 
from (
with orders as (
select customerid, to_char(orderdate, 'YYYYMM') as orderdate
from ordertable
group by customerid, orderdate
    )
select distinct customerid,
    case 
    when orderdate = '202101' then 1 else 0 end as jan21,
    case 
    when orderdate = '202102' then 1 else 0 end as feb21,
    case 
    when orderdate = '202103' then 1 else 0 end as mar21,
    case 
    when orderdate = '202104' then 1 else 0 end as apr21        
from orders
) t1 group by customerid
) t25
where jan21 = 1 

直接用UNION ALL把四个基准月的统计逻辑合并成一条SQL，一次执行就能出全部结果，不需要反复改WHERE条件多次运行。左下侧留空位置直接填'-'即可，完整SQL如下：

WITH orders AS (
    SELECT customerid, to_char(orderdate, 'YYYYMM') as orderdate
    FROM ordertable
    GROUP BY customerid, orderdate
),
-- 生成宽表Table1
table1 AS (
    SELECT 
        customerid,
        MAX(CASE WHEN orderdate = '202101' THEN 1 ELSE 0 END) AS jan21,
        MAX(CASE WHEN orderdate = '202102' THEN 1 ELSE 0 END) AS feb21,
        MAX(CASE WHEN orderdate = '202103' THEN 1 ELSE 0 END) AS mar21,
        MAX(CASE WHEN orderdate = '202104' THEN 1 ELSE 0 END) AS apr21
    FROM orders
    GROUP BY customerid
)
-- 统计1月为基准月的结果
SELECT 
    'jan21' AS month,
    SUM(jan21) AS jan21,
    SUM(feb21) AS feb21,
    SUM(mar21) AS mar21,
    SUM(apr21) AS apr21
FROM table1
WHERE jan21 = 1

UNION ALL
-- 统计2月为基准月的结果
SELECT 
    'feb21' AS month,
    '-' AS jan21,
    SUM(feb21) AS feb21,
    SUM(mar21) AS mar21,
    SUM(apr21) AS apr21
FROM table1
WHERE feb21 = 1

UNION ALL
-- 统计3月为基准月的结果
SELECT 
    'mar21' AS month,
    '-' AS jan21,
    '-' AS feb21,
    SUM(mar21) AS mar21,
    SUM(apr21) AS apr21
FROM table1
WHERE mar21 = 1

UNION ALL
-- 统计4月为基准月的结果
SELECT 
    'apr21' AS month,
    '-' AS jan21,
    '-' AS feb21,
    '-' AS mar21,
    SUM(apr21) AS apr21
FROM table1
WHERE apr21 = 1;

后续如果要新增统计月份，只要在table1的CTE里加对应月份的活跃标识判断，再补一段对应UNION ALL的统计块就行。如果不需要左下位置留空，把对应位置的'-'换成和其他列一致的SUM统计逻辑，就能得到全量对称的统计结果。

内容的提问来源于stack exchange，提问作者aearslan