PostgreSQL 分组列转行|极客教程

PostgreSQL 分组列转行

在 PostgreSQL 数据库中，有时候我们需要将一列以不同的分组方式转换成多行。这种转换通常称为列转行 (crosstab) 或者行透视 (row pivot)。在本文中，我们将学习如何使用 PostgreSQL 来实现这种数据转换。

准备工作

在开始之前，我们首先要创建一个测试表，然后向其中插入一些数据以便进行演示。

-- 创建测试表
CREATE TABLE sales (
    region VARCHAR(50),
    product VARCHAR(50),
    quantity INTEGER
);

-- 插入测试数据
INSERT INTO sales (region, product, quantity) VALUES 
    ('North', 'A', 10),
    ('North', 'B', 5),
    ('North', 'C', 15),
    ('South', 'A', 20),
    ('South', 'B', 12),
    ('South', 'C', 8);

现在我们已经准备好了测试数据，接下来我们将介绍两种常见的转换方式，分别是使用 CASE WHEN 和使用 crosstab 函数。

使用 CASE WHEN 实现列转行

使用 CASE WHEN 是一种基本的转换方法，它适用于分组列数量较少的情况。

SELECT 
    region,
    MAX(CASE WHEN product = 'A' THEN quantity END) AS A,
    MAX(CASE WHEN product = 'B' THEN quantity END) AS B,
    MAX(CASE WHEN product = 'C' THEN quantity END) AS C
FROM sales
GROUP BY region;

运行以上SQL语句，我们可以得到如下结果：

| region |  A  |  B |  C |
|--------|-----|----|----|
| North  |  10 |  5 | 15 |
| South  |  20 | 12 |  8 |

以上结果将产品列转换为了不同的行。但是，当产品种类较多时，以上方法会变得比较冗长。这时候我们可以使用 crosstab 函数来实现更灵活的转换。

使用 crosstab 函数实现列转行

在 PostgreSQL 中，有一个名为 tablefunc 的扩展，其中包含了 crosstab 函数。我们首先要确认 tablefunc 扩展已经被加载。

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后，我们可以使用 crosstab 函数来实现列转行的功能。

SELECT * FROM crosstab(
    'SELECT region, product, quantity FROM sales ORDER BY 1,2',
    'SELECT DISTINCT product FROM sales ORDER BY 1'
) AS result(region VARCHAR, A INTEGER, B INTEGER, C INTEGER);

运行以上SQL语句，我们将得到与前面相同的结果：

| region |  A  |  B |  C |
|--------|-----|----|----|
| North  |  10 |  5 | 15 |
| South  |  20 | 12 |  8 |

crosstab 函数会动态生成列，因此适用于产品种类较多时的情况。同时，可以通过修改第二个 SELECT 语句来适应不同数量的产品种类。

总结

在本文中，我们学习了如何使用 PostgreSQL 实现列转行的功能。我们介绍了两种方法，一种是使用 CASE WHEN，适用于分组列数量较少的情况；另一种是使用 crosstab 函数，适用于分组列数量较多的情况。通过这些方法，我们可以更灵活地转换数据，以便更好地进行分析和报告。

PostgreSQL 分组列转行