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 技術：

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 

雖然此方法有效，但在靈活性和性能方面存在潛在的限制。

crosstab 函數：一種動態解

PostgreSQL 中 tablefunc 模組引入的 crosstab() 函數是更動態、更有效率的替代方案。使用 crosstab() 函數，無需明確定義輸出列即可實現所需的轉換。

基本 crosstab 解

對於範例問題，基本的 crosstab 解如下：

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);

在此查詢中，SELECT bar, 1 AS cat, feh 提供與 crosstab() 相容的格式的資料。 cat 欄位用作虛擬佔位符，因為表中沒有類別資訊。

進階 crosstab 解

如果存在實際的類別列，則可以使用它來控制結果中值的順序。以下查詢示範了這一點：

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);

動態 crosstab

雖然上述解決方案提供了動態列生成，但在 PostgreSQL 中動態返回不同的列更具挑戰性。但是，在某些限制下，可以使用 tablefunc 模組的內建功能來實現。

結論

crosstab() 函數為 CASE 和 GROUP BY 方法提供了一個強大而動態的替代方案。它允許靈活的資料轉換，使其成為 PostgreSQL 中資料操作的寶貴工具。

以上是PostgreSQL中如何動態地將行資料轉換為列資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！