PostgreSQL统计多列distinct值并按指定格式输出的实现方法

首先将原表的col1、col2、col3进行行转列处理，再通过条件聚合统计各取值的出现次数即可，假设原始表名为your_table，具体SQL如下：

SELECT
  date,
  col,
  COUNT(*) FILTER (WHERE val = 'a') AS a,
  COUNT(*) FILTER (WHERE val = 'b') AS b,
  COUNT(*) FILTER (WHERE val = 'c') AS c,
  COUNT(*) FILTER (WHERE val = 'd') AS d
FROM (
  -- 并行展开列名数组和列值数组，实现列转行
  SELECT
    date,
    UNNEST(ARRAY['col1', 'col2', 'col3']) AS col,
    UNNEST(ARRAY[col1, col2, col3]) AS val
  FROM your_table
) t
GROUP BY date, col
ORDER BY date DESC, col;

如果使用的PostgreSQL版本不支持FILTER语法，可以用CASE WHEN替代实现相同逻辑：

SELECT
  date,
  col,
  SUM(CASE WHEN val = 'a' THEN 1 ELSE 0 END) AS a,
  SUM(CASE WHEN val = 'b' THEN 1 ELSE 0 END) AS b,
  SUM(CASE WHEN val = 'c' THEN 1 ELSE 0 END) AS c,
  SUM(CASE WHEN val = 'd' THEN 1 ELSE 0 END) AS d
FROM (
  SELECT
    date,
    UNNEST(ARRAY['col1', 'col2', 'col3']) AS col,
    UNNEST(ARRAY[col1, col2, col3]) AS val
  FROM your_table
) t
GROUP BY date, col
ORDER BY date DESC, col;

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