PostgreSQL中如何统计每行多列非NULL元素数量？技术求助

方法1：逐列判断累加（最直观简洁） #

直接对role1到role5每一列判断是否不为NULL，将结果转为数值后求和。这种方法无需复杂函数，性能好且易读：

SELECT
  employee_id,
  role1,
  role2,
  role3,
  role4,
  role5,
  LEAST(role1, role2, role3, role4, role5) AS role_oldest,
  GREATEST(role1, role2, role3, role4, role5) AS role_newest,
  -- 统计非NULL角色列的数量
  (
    CASE WHEN role1 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN role2 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN role3 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN role4 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN role5 IS NOT NULL THEN 1 ELSE 0 END
  ) AS role_count
FROM employee_roles;

方法2：数组+UNNEST（适配列数较多的场景） #

如果后续角色列可能增加，用数组打包列后展开统计非NULL值的数量更易维护，适合PostgreSQL、BigQuery等支持数组函数的数据库：

SELECT
  employee_id,
  role1,
  role2,
  role3,
  role4,
  role5,
  LEAST(role1, role2, role3, role4, role5) AS role_oldest,
  GREATEST(role1, role2, role3, role4, role5) AS role_newest,
  (SELECT COUNT(*) FROM UNNEST(ARRAY[role1, role2, role3, role4, role5]) AS r WHERE r IS NOT NULL) AS role_count
FROM employee_roles;

方法3：利用布尔值转整数特性（简化写法） #

借助COALESCE将非NULL值转为布尔真，再转整数1，NULL转为0后求和，仅支持布尔类型转换的数据库可用：

SELECT
  employee_id,
  role1,
  role2,
  role3,
  role4,
  role5,
  LEAST(role1, role2, role3, role4, role5) AS role_oldest,
  GREATEST(role1, role2, role3, role4, role5) AS role_newest,
  COALESCE(role1, 0)::BOOLEAN::INT +
  COALESCE(role2, 0)::BOOLEAN::INT +
  COALESCE(role3, 0)::BOOLEAN::INT +
  COALESCE(role4, 0)::BOOLEAN::INT +
  COALESCE(role5, 0)::BOOLEAN::INT AS role_count
FROM employee_roles;

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