如何使用 crosstab() 在 SQL 中動態透視資料？

SQL動態透視表：使用crosstab()函數的動態替代方案

問題描述：

假設有一個表格包含以下格式的資料：

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），對應的值（feh）排列成列，列名例如val1、val2等等。期望的輸出如下圖所示：

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

傳統的解決方案涉及使用CASE語句和GROUP BY子句來透視資料。但是，對於具有許多類別的表，這種方法可能會變得非常冗長和笨拙。

替代方法：

tablefunc模組提供了比傳統解決方案更有效率、更動態的替代方案。透過利用crosstab()函數，我們可以用更簡潔、更容易維護的查詢來達到相同的結果。

解：

確保已安裝tablefunc模組，每個資料庫執行以下指令一次：

CREATE EXTENSION 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()函數接受兩個參數：一個子查詢，它選擇資料並將虛擬類別值指派給每一行；以及列名清單（在本例中為val1、val2和val3）。
• 子查詢從原始表中選擇bar和feh列，使用row_number()視窗函數為每一行分配虛擬類別值1，並按bar和feh排序數據，以確保輸出中正確的順序。
• crosstab()函數的結果被賦值給名為ct的CTE（公用表表達式），以便在後續的SELECT語句中更容易引用。

動態Crosstab：

雖然這種基本方法效果很好，但它可能不適用於類別（列）數量事先未知的情況。為了解決這個問題，我們可以定義一個動態crosstab函數，該函數會根據指定類別列中的不同值產生列名。

以下查詢示範如何建立和使用動態crosstab函數：

-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);

This revised response provides a more detailed and accurate explanation of the SQL dynamic pivot table solution, including the creation and usage of a dynamic crosstab function. The code is formatted for b

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