PostgreSQL技术问询：统计行中含指定值的col前缀列数量

嘿，这个需求我之前刚好处理过类似的，针对PostgreSQL里有50个以col开头的列，要每行统计值为指定整数（比如0）的列数，给你三个实用方案，完全适配大规模列的场景：

如果列的数量固定（比如就是50个col1-col50），直接构造每行的计算表达式就行。PostgreSQL里布尔值转整数时，TRUE会变成1，FALSE变成0，把每个列的判断结果加起来就是目标数量：

SELECT
  id,
  (
    (col1 = 0)::int +
    (col2 = 0)::int +
    (col3 = 0)::int +
    -- 依次写到col50即可
    (col50 = 0)::int
  ) AS count
FROM your_table;

优点：执行速度最快，没有额外函数调用开销；缺点：列数多的时候需要手动写（不过可以用Excel快速生成表达式，比如在单元格写=(col&ROW(A1)&" = 0)::int +"，下拉到50行就能批量生成）。

不想手动写50个列的判断？可以利用PostgreSQL的系统表自动生成完整的SQL语句，一步到位：

WITH col_list AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_name = 'your_table' -- 替换成你的实际表名
    AND column_name LIKE 'col%'
    AND table_schema = 'public' -- 替换成你的表所在schema，默认是public
  ORDER BY column_name -- 保证列顺序和表一致
)
SELECT format(
  'SELECT id, (%s) AS count FROM your_table;',
  string_agg(format('(%I = 0)::int', column_name), ' + ')
) AS dynamic_sql
FROM col_list;

执行这个查询后，会返回一个完整的可执行SQL语句，你直接复制执行就能得到结果。优点：完全不用手动写列，适合列数多的场景；缺点：需要额外执行一次生成SQL的步骤，适合列固定的情况。

如果后续可能增减col开头的列，不想每次都改SQL，用JSON转换的方法最省心：

SELECT
  id,
  (
    SELECT count(*)
    FROM jsonb_each_text(to_jsonb(t))
    WHERE key LIKE 'col%' AND value = '0'
  ) AS count
FROM your_table t;

原理是把每行数据转换成JSONB对象，遍历所有键（列名），筛选出以col开头且值为0的键值对，统计数量。如果你的值是整数类型，担心字符串匹配的问题，可以把value转成整数再判断：

SELECT
  id,
  (
    SELECT count(*)
    FROM jsonb_each(to_jsonb(t))
    WHERE key LIKE 'col%' AND value::int = 0
  ) AS count
FROM your_table t;

优点：完全不用关心列的数量和名称，列增减都不用改SQL；缺点：性能比前两个方案稍差（但50列的量级基本感知不到）。

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