如何使用 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 better readability.

以上是如何使用 crosstab() 在 SQL 中动态透视数据？的详细内容。更多信息请关注PHP中文网其他相关文章！