PostgreSQL行内多列组统计优化咨询：如何高效计算NW前缀列的负值频率？

兄弟，我太懂你这种面对一堆同前缀列要写重复CASE的崩溃感了——一个个列出来不仅繁琐，以后加新年份列还得改SQL，确实够“丑陋”的！先给你拆解下问题，再给你几个更优雅的方案，顺便聊聊你纠结的表结构问题。

先给个临时救急的方案（不用改表结构） #

PostgreSQL有hstore或者jsonb这种键值对类型，可以帮你批量处理前缀匹配的列，不用手动罗列所有NW开头的字段：

用hstore实现的例子

SELECT 
  id,
  -- 统计当前行NW列中负值的数量
  (SELECT count(*) FROM each(hstore(a)) kv WHERE kv.key LIKE 'NW%' AND (kv.value::numeric < 0)) AS neg_count,
  -- 统计当前行非空的NW列数量
  (SELECT count(*) FROM each(hstore(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL) AS total_count,
  -- 计算负值频率（避免除以0的情况）
  CASE 
    WHEN (SELECT count(*) FROM each(hstore(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL) > 0
    THEN (SELECT count(*) FROM each(hstore(a)) kv WHERE kv.key LIKE 'NW%' AND (kv.value::numeric < 0))::double precision / 
         (SELECT count(*) FROM each(hstore(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL)
    ELSE 0
  END AS freq_negative
FROM a;

用jsonb实现的例子（逻辑和hstore一致，适合更复杂的结构）

SELECT 
  id,
  (SELECT count(*) FROM jsonb_each_text(to_jsonb(a)) kv WHERE kv.key LIKE 'NW%' AND (kv.value::numeric < 0)) AS neg_count,
  (SELECT count(*) FROM jsonb_each_text(to_jsonb(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL) AS total_count,
  CASE 
    WHEN (SELECT count(*) FROM jsonb_each_text(to_jsonb(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL) > 0
    THEN (SELECT count(*) FROM jsonb_each_text(to_jsonb(a)) kv WHERE kv.key LIKE 'NW%' AND (kv.value::numeric < 0))::double precision / 
         (SELECT count(*) FROM jsonb_each_text(to_jsonb(a)) kv WHERE kv.key LIKE 'NW%' AND kv.value IS NOT NULL)
    ELSE 0
  END AS freq_negative
FROM a;

这种方式的好处是一劳永逸，以后新增NW2025、NW2026这类列，SQL完全不用改，自动匹配前缀处理。

长期最优解：表结构规范化（你编辑里提到的Normalisation） #

说白了，你现在的表是宽表结构，把年份作为列名，这种结构在做跨列统计时天生麻烦。改成规范化的窄表才是关系型数据库的正确打开方式：

第一步：创建规范化的表

CREATE TABLE net_worth (
  id INT REFERENCES a(id), -- 和原表关联
  year INT, -- 单独存年份（比如2020、2021）
  value NUMERIC -- 对应年份的净值
);

第二步：把原表的数据导入新表

INSERT INTO net_worth (id, year, value)
SELECT 
  id,
  substring(kv.key, 3)::INT AS year, -- 从NW2020里提取年份数字
  kv.value::NUMERIC AS value
FROM a, each(hstore(a)) kv
WHERE kv.key LIKE 'NW%';

第三步：统计变得超简单！

现在要算每个id的负值频率，一行SQL搞定：

SELECT 
  id,
  COUNT(CASE WHEN value < 0 THEN 1 END)::double precision / COUNT(value) AS freq_negative
FROM net_worth
GROUP BY id;

以后要加新年份，直接往net_worth表里插数据就行，完全不用改表结构；甚至要统计每个年份的负值频率，直接GROUP BY year就可以，扩展性拉满。

最后聊聊你的表结构问题 #

你的表结构算不算“坏”？其实不是绝对的：如果你的业务主要是按id查询某条记录的所有年份数据，宽表可能更方便；但如果经常需要做这种跨列的统计分析，规范化的窄表才是更合理的选择——它符合关系型数据库的设计原则，后续维护和扩展成本低很多。

内容来源于stack exchange