PostgreSQL UNPIVOT|极客教程

PostgreSQL UNPIVOT

在关系型数据库中，UNPIVOT是一种将列转换为行的操作。在PostgreSQL中，我们可以使用函数和操作符来实现UNPIVOT操作。

什么是UNPIVOT？

UNPIVOT是一种将表格中的列转换为行的操作。它允许我们重新组织数据，以便更方便地进行分析和处理。通常，在UNPIVOT之前，我们的数据可能以一种宽格式表示，其中每个列都包含一个变量的不同值。但实际上，这些变量应该是行的一部分，而不是列的一部分。

示例数据

让我们考虑以下示例数据表，其中包含产品销售数据（sales）：

CREATE TABLE sales (
    product_id INT,
    jan_sales INT,
    feb_sales INT,
    mar_sales INT
);

INSERT INTO sales (product_id, jan_sales, feb_sales, mar_sales)
VALUES 
    (1, 100, 200, 150),
    (2, 50, 75, 100),
    (3, 300, 250, 200);

这个表格显示了每个产品在1月、2月和3月的销售数据。我们想要将它转换成一个更方便分析的格式。

使用UNPIVOT进行转换

在PostgreSQL中，我们可以使用UNION ALL操作符和LATERAL子查询来实现UNPIVOT功能。以下是一个示例查询：

SELECT product_id, month, sales
FROM sales
UNION ALL
SELECT product_id, 'jan', jan_sales
FROM sales
UNION ALL
SELECT product_id, 'feb', feb_sales
FROM sales
UNION ALL
SELECT product_id, 'mar', mar_sales
FROM sales;

此查询将原始表格拆分为四个部分。第一部分显示了产品ID、月份和销售额。剩下的三个部分将每个月的销售额作为单独的行显示。使用UNION ALL操作符可以将这些结果合并为一个结果集。

运行上述查询后，将得到以下结果：

 product_id | month | sales
------------+-------+-------
          1 | jan   |   100
          2 | jan   |    50
          3 | jan   |   300
          1 | feb   |   200
          2 | feb   |    75
          3 | feb   |   250
          1 | mar   |   150
          2 | mar   |   100
          3 | mar   |   200

使用CROSSTAB函数进行转换

除了使用UNION ALL操作符和LATERAL子查询，我们还可以使用CROSSTAB函数来实现UNPIVOT操作。以下是一个示例查询：

SELECT * FROM crosstab(
    'SELECT product_id, month, sales
    FROM sales
    ORDER BY 1, 2',
    'VALUES (''jan''), (''feb''), (''mar'')'
) AS t (product_id INT, jan_sales INT, feb_sales INT, mar_sales INT);