GROUP BY或COUNT像字段值 - UNPIVOT？ [英] GROUP BY or COUNT Like Field Values - UNPIVOT?

问题描述

我有一个带有测试字段的表，例如

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

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

有没有一种方法来GROUP BY值？

伪：

  SELECT（'​​P'IN（fields） ）AS pass 
 WHERE id = 12345 
  

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

预期结果：

 传递|失败|不完整的
 + ---------- + ---------- + ---------- + 
 3 | 1 | 1 
  

建议？

'运行PostgreSQL 7.4并且我们正在升级

解决方案

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

  SELECT id 
，l  -  length（replace（t，'P'，''））AS nr_p 
，l  - （替换（t，'F'，''））AS nr_f 
，l  -  length（替换（t，'I'，''））AS nr_i 
 FROM（SELECT id，test： ：text AS t，length（test :: text）AS l FROM test）t 
  

如下所示：

• 将行类型转换为文本表示形式。


• 度量字符长度。 / li>
  
• 替换您要计数的字符并测量长度变化。
  
• 计算子选择中原始行的长度以便重复使用。 / li>
  

这要求 P，F，I 在行中的其他地方不存在。使用子选择来排除任何可能干扰的列。

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

  SELECT id 
，长度（t） - 长度（替换（t，'P'，''））AS nr_p 
，长度（t） - 长度（替换（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） 
  

这要求所有列为 NOT NULL 。

I have a table with test fields, Example

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

So for each record I want to know how many Pass, Failed or Incomplete (P,F or I)

Is there a way to GROUP BY value?

Pseudo:

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

I have about 40 test fields that I need to somehow group together and I really don't want to write this super ugly, long query. Yes I know I should rewrite the table into two or three separate tables but this is another problem.

Expected Results:

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

Suggestions?

Note: I'm running PostgreSQL 7.4 and yes we are upgrading

解决方案

I may have come up with a solution:

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

The trick works like this:

• Transform the rowtype into its text representation.
• Measure character-length.
• Replace the character you want to count and measure the change in length.
• Compute the length of the original row in the subselect for repeated use.

This requires that P, F, I are present nowhere else in the row. Use a sub-select to exclude any other columns that might interfere.

Tested in 8.4 - 9.1. Nobody uses PostgreSQL 7.4 anymore nowadays, you'll have to test yourself. I only use basic functions, but I am not sure if casting the rowtype to text is feasible in 7.4. If that doesn't work, you'll have to concatenate all test-columns once by hand:

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

This requires all columns to be NOT NULL.

这篇关于GROUP BY或COUNT像字段值 - UNPIVOT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！