PostgreSQL交叉表查询：将无数据列的NULL值转换为0

我懂你遇到的这个问题——你在内部查询里加了COALESCE想把NULL转成0，但发现某些月份还是显示NULL对吧？这是因为当某个客户在特定月份完全没有交易记录时，你的内部查询根本不会生成那一行数据，crosstab只能用NULL填充对应列，内层的COALESCE根本没机会处理这种情况。

给你两个可行的解决方案，按需选择：

方案一：快速修复——在外层对列应用COALESCE #

这是最简单的处理方式，直接在crosstab结果外层，对每个月份列单独用COALESCE把NULL转成0：

SELECT 
  id, 
  name, 
  COALESCE("Jan 2018", 0) AS "Jan 2018",
  COALESCE("Feb 2018", 0) AS "Feb 2018",
  COALESCE("Mar 2018", 0) AS "Mar 2018"
FROM crosstab( 
  $ct$ 
    SELECT sa.id, company.name, to_char(sat.transaction_date, 'YYYY-MM') AS my, sat.amount AS amnt 
    FROM sales_account_transactions sat 
    JOIN sales_account sa ON sa.id = sat.sales_account 
    JOIN company ON sa.company = company.id 
    WHERE sat.financial_company = 1 
      AND sat.transaction_date BETWEEN '2018-01-01' AND '2018-03-31' 
      AND sat.reversed_by = 0 
      AND sat.original_id = 0 
    GROUP BY sa.id, company.name, my, amnt 
    ORDER BY company.name, my; 
  $ct$, 
  $$VALUES ('2018-01'), ('2018-02'), ('2018-03') $$ 
) as ct(id int, name text, "Jan 2018" int, "Feb 2018" int, "Mar 2018" int);

方案二：更健壮的处理——确保每个客户都有所有月份的行 #

如果你的月份范围经常变动，或者希望从根源上避免NULL，可以先生成所有目标月份，再和客户列表做交叉连接，确保每个客户每个月份都有一行数据，最后左连接交易表并处理金额：

SELECT * FROM crosstab(
  $ct$
    SELECT 
      c.id, 
      c.name, 
      m.my, 
      COALESCE(SUM(sat.amount), 0) AS amnt
    FROM (
      -- 生成目标月份列表
      SELECT to_char(generate_series('2018-01-01'::date, '2018-03-31'::date, '1 month'), 'YYYY-MM') AS my
    ) m
    -- 交叉连接所有符合条件的客户
    CROSS JOIN (
      SELECT sa.id, company.name
      FROM sales_account sa
      JOIN company ON sa.company = company.id
      -- 筛选出有相关交易的客户（可根据需求调整）
      WHERE EXISTS (
        SELECT 1 FROM sales_account_transactions sat
        WHERE sat.sales_account = sa.id
          AND sat.financial_company = 1
          AND sat.transaction_date BETWEEN '2018-01-01' AND '2018-03-31'
          AND sat.reversed_by = 0
          AND sat.original_id = 0
      )
    ) c
    -- 左连接交易表，匹配客户和对应月份
    LEFT JOIN sales_account_transactions sat
      ON sat.sales_account = c.id
      AND to_char(sat.transaction_date, 'YYYY-MM') = m.my
      AND sat.financial_company = 1
      AND sat.reversed_by = 0
      AND sat.original_id = 0
    GROUP BY c.id, c.name, m.my
    ORDER BY c.name, m.my;
  $ct$,
  $$VALUES ('2018-01'), ('2018-02'), ('2018-03') $$
) as ct(id int, name text, "Jan 2018" int, "Feb 2018" int, "Mar 2018" int);

方案一适合固定月份的场景，修改成本低；方案二更灵活，后续调整月份范围时不需要改动外层的列处理逻辑。

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