如何在SQL中有效率地執行交叉表查詢？

使用CASE和GROUP BY實現動態替代PIVOT

問題：

下表所示資料以行和列的形式組織。目標是將其轉換為具有動態列數的表，其中每列代表按指定類別分組的值。

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
B	3	4
C	5
D	6	7	8

原始查詢：

以下查詢使用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 

高效率的交叉表替代方案：

為了提高效率和可讀性，可以使用tablefunc模組中的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);  -- 更多列？

處理多值：

對於同一類別下有多個值的場景，可以將crosstab函數擴展為以下形式：

SELECT * FROM crosstab(
  'SELECT bar, val, feh
   FROM tbl_org
   ORDER BY 1, 2')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 更多列？

內建交叉表函數：

tablefunc模組也為特定列數提供了預先定義的crosstab函數：

SELECT * FROM crosstab3('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2');

這些函數使呼叫更簡單，並預設處理文字資料。

動態回傳型別：

雖然tablefunc簡化了流程，但在處理動態回傳類型方面有其限制。為了解決這個問題，可以考慮其他方法，例如使用PL/pgSQL函數或建立動態SQL語句。

以上是如何在SQL中有效率地執行交叉表查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！