如何使用動態 SQL 技術在 SQL 中有效率地透視資料？

使用動態SQL高效實現SQL資料透視

本文探討如何運用動態SQL技術將表格資料​​轉換為透視表格式，重點在於提升資料轉換的效率與彈性。

初始查詢

以下查詢使用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()函數能提供更有效率、更動態的解決方案。

安裝tablefunc模組

在使用crosstab()之前，需要確保tablefunc模組已安裝到PostgreSQL資料庫中。每個資料庫只需執行一次以下命令：

CREATE EXTENSION tablefunc;

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);  -- 可根据需要增加列

說明：

• tbl_org 是輸入表。
• crosstab()中的子查詢選擇bar、一個虛擬類別cat和feh。 cat列僅作為佔位符，會被忽略。
• ORDER BY子句確保值依適當順序排列。

此查詢將以所需的透視表格式傳回資料。

動態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()函數為SQL中的資料透視提供了更有效率、更靈活的解決方案，簡化了查詢並允許根據需求進行靈活自訂。

以上是如何使用動態 SQL 技術在 SQL 中有效率地透視資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！