PostgreSQL中如何动态地将行数据转换为列数据？

PostgreSQL 中动态将行数据转换为列数据的替代方案：CASE 和 GROUP BY vs. crosstab 函数

在 PostgreSQL 中，许多用户需要将行数据表转换为列数据表以进行分析和报告。虽然 CASE 和 GROUP BY 是常用的方法，但存在更动态和高效的替代方案。

问题陈述

例如，考虑以下表格：

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

期望的输出将此数据转换为以下格式：

bar	val1	val2	val3
A	10	20	NULL
B	3	4	NULL
C	5	NULL	NULL
D	6	7	8

使用 CASE 和 GROUP BY 的解决方案

一种实现此目标的方法是使用 CASE 和 GROUP BY 技术：

<code class="language-sql">SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar </code>

虽然此方法有效，但在灵活性和性能方面存在潜在的局限性。

crosstab 函数：一种动态解决方案

PostgreSQL 中 tablefunc 模块引入的 crosstab() 函数是一种更动态、更高效的替代方案。使用 crosstab() 函数，无需显式定义输出列即可实现所需的转换。

基本 crosstab 解决方案

对于示例问题，基本的 crosstab 解决方案如下：

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);</code>

在此查询中，SELECT bar, 1 AS cat, feh 提供与 crosstab() 兼容的格式的数据。cat 列用作虚拟占位符，因为表中没有类别信息。

高级 crosstab 解决方案

如果存在实际的类别列，则可以使用它来控制结果中值的顺序。以下查询演示了这一点：

<code class="language-sql">SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         
) AS ct (bar text, val1 int, val2 int, val3 int);</code>

动态 crosstab

虽然上述解决方案提供了动态列生成，但在 PostgreSQL 中动态返回不同的列更具挑战性。但是，在某些限制下，可以使用 tablefunc 模块的内置功能来实现。

结论

crosstab() 函数为 CASE 和 GROUP BY 方法提供了一种强大而动态的替代方案。它允许灵活的数据转换，使其成为 PostgreSQL 中数据操作的宝贵工具。

以上是PostgreSQL中如何动态地将行数据转换为列数据？的详细内容。更多信息请关注PHP中文网其他相关文章！