GROUP BY 或 COUNT 类似字段值 - UNPIVOT？

Question

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

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

因此对于每条记录我想知道有多少通过，失败或不完整（P，F或I）

有没有办法按值进行分组？

Pseudo：

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

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

预期结果：

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。

Answer 2

本质上，您需要通过测试对数据进行逆透视：

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

...

- 以便您可以根据测试结果对其进行分组。

不幸的是，PostgreSQL没有内置的pivot/unpivot功能，所以最简单的方法是这样的：

select id, 'test1' test, test1 result from mytable union all
select id, 'test2' test, test2 result from mytable union all
select id, 'test3' test, test3 result from mytable union all
select id, 'test4' test, test4 result from mytable union all
select id, 'test5' test, test5 result from mytable union all

...

还有其他方法可以解决这个问题，但是对于40列数据，这将变得非常难看。

编辑：另一种方法-

select r.result, sum(char_length(replace(replace(test1||test2||test3||test4||test5,excl1,''),excl2,'')))
from   mytable m, 
       (select 'P' result, 'F' excl1, 'I' excl2 union all
        select 'F' result, 'P' excl1, 'I' excl2 union all
        select 'I' result, 'F' excl1, 'P' excl2) r
group by r.result

Answer 3

您可以使用辅助即时表将列转换为行，然后您就可以应用聚合函数，如下所示：

SELECT
  SUM(fields = 'P') AS passed,
  SUM(fields = 'F') AS failed,
  SUM(fields = 'I') AS incomplete
FROM (
  SELECT
    t.id,
    CASE x.idx
      WHEN 1 THEN t.test1
      WHEN 2 THEN t.test2
      WHEN 3 THEN t.test3
      WHEN 4 THEN t.test4
      WHEN 5 THEN t.test5
    END AS fields
  FROM atable t
    CROSS JOIN (
      SELECT 1 AS idx
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
    ) x
  WHERE t.id = 12345
) s

Answer 4

编辑：刚刚看到关于 7.4 的评论，我认为这不适用于那个古老的版本（unnest() 后来才出现）。如果有人认为这篇文章不值得保留，我会删除它。

采用 Erwin 的想法，使用“行表示”作为解决方案的基础，并自动动态“规范化”表格：

select id,
       sum(case when flag = 'F' then 1 else null end) as failed,
       sum(case when flag = 'P' then 1 else null end) as passed,
       sum(case when flag = 'I' then 1 else null end) as incomplete
from (
  select id, 
         unnest(string_to_array(trim(trailing ')' from substr(all_column_values,strpos(all_column_values, ',') + 1)), ',')) flag
  from (
    SELECT id,
           not_normalized::text AS all_column_values
    FROM not_normalized
  ) t1
) t2
group by id

该解决方案的核心是 Erwin 的技巧，即从完整的行中生成单个值使用强制转换 not_normalized::text。字符串函数用于去除前导 id 值及其周围的括号。

其结果将转换为数组，并使用 unnest() 函数将该数组转换为结果集。

要理解该部分，只需逐步运行内部选择即可。

然后对结果进行分组并统计相应的值。