sql - GROUP BY 或 COUNT Like 字段值 - UNPIVOT？

Question

我有一个包含测试字段的表，示例

id         | test1    | test2    | test3    | test4    | test5
+----------+----------+----------+----------+----------+----------+
12345      | P        | P        | F        | I        | P

所以对于每条记录，我想知道有多少通过、失败或不完整(P、F 或 I)

有没有办法对值进行 GROUP BY？

伪:

SELECT ('P' IN (fields)) AS pass
WHERE id = 12345

我有大约 40 个测试字段需要以某种方式组合在一起，我真的不想写这个 super 丑陋的长查询。是的，我知道我应该将表格重写为两个或三个单独的表格，但这是另一个问题。

预期结果:

passed     | failed   | incomplete
+----------+----------+----------+
3          | 1        | 1

建议？

注意:我正在运行 PostgreSQL 7.4，是的，我们正在升级

Answer 1

我可能想出了一个解决方案:

SELECT id
      ,l - length(replace(t, 'P', '')) AS nr_p
      ,l - length(replace(t, 'F', '')) AS nr_f
      ,l - length(replace(t, 'I', '')) AS nr_i
FROM   (SELECT id, test::text AS t, length(test::text) AS l  FROM test) t

这个技巧是这样的:

• 将行类型转换为其文本表示形式。
• 测量字符长度。
• 替换您要计数的字符并测量长度的变化。
• 计算子选择中原始行的长度以供重复使用。

这要求 P, F, I 不出现在该行的其他地方。使用子选择排除可能干扰的任何其他列。

在 8.4 - 9.1 中测试。现在没有人再使用 PostgreSQL 7.4，你必须自己测试一下。我只使用基本函数，但我不确定在 7.4 中是否可以将行类型转换为文本。如果这不起作用，您将不得不手动连接所有测试列一次:

SELECT id
      ,length(t) - length(replace(t, 'P', '')) AS nr_p
      ,length(t) - length(replace(t, 'F', '')) AS nr_f
      ,length(t) - length(replace(t, 'I', '')) AS nr_i
FROM   (SELECT id, test1||test2||test3||test4 AS t FROM test) t

这要求所有列都为 NOT NULL。