PostgreSQL 数据透视(Pivot)|极客教程

PostgreSQL 数据透视(Pivot)

简介

数据透视是一种常用的数据处理技术，可以将原始数据表中的行数据转化为列，以便更好地进行分析和处理。PostgreSQL是一个强大的关系型数据库管理系统，支持灵活的查询和数据操作。本文将介绍如何在PostgreSQL中使用透视技术来处理数据，并提供相关的示例代码。

什么是数据透视？

数据透视是指将原始的行数据转换为列数据的过程。在数据透视中，我们可以选择一个或多个列作为”行标签”，另外选择一个或多个列作为”列标签”，并将需要透视的列作为”值”。通过透视操作，我们可以更方便地对数据进行汇总、计算和分析。

例如，假设有一张销售数据表，包含以下列：销售日期、产品名称、销售金额。原始数据表如下所示：

销售日期	产品名称	销售金额
2019-01-01	产品A	1000
2019-01-01	产品B	2000
2019-01-02	产品A	1500
2019-01-02	产品B	1800

如果我们希望将上述表格按照产品名称进行透视，并计算每个产品在不同销售日期的销售金额，透视后的结果如下所示：

产品名称	2019-01-01	2019-01-02
产品A	1000	1500
产品B	2000	1800

通过透视操作，我们可以更清晰地看到不同产品在不同销售日期上的销售情况，方便分析和比较。

PostgreSQL实现数据透视

在PostgreSQL中，我们可以使用crosstab函数来实现数据透视。crosstab函数是PostgreSQL的一个扩展函数，需要通过tablefunc扩展来支持。首先需要安装tablefunc扩展，在终端中执行以下命令：

CREATE EXTENSION tablefunc;

安装成功后，即可使用crosstab函数进行数据透视操作。

准备工作

我们首先需要创建一个示例表格，并插入一些数据，以便后续的数据透视操作。执行以下SQL语句：

CREATE TABLE sales (
    sales_date DATE,
    product_name TEXT,
    sales_amount INTEGER
);

INSERT INTO sales (sales_date, product_name, sales_amount)
VALUES
    ('2019-01-01', '产品A', 1000),
    ('2019-01-01', '产品B', 2000),
    ('2019-01-02', '产品A', 1500),
    ('2019-01-02', '产品B', 1800);

创建好表格和数据后，我们可以开始进行数据透视操作。

执行透视操作

下面的示例代码将执行一个简单的数据透视操作，将上述示例表格按照产品名称进行透视，并计算每个产品在不同销售日期的销售金额。

SELECT *
FROM crosstab(
    'SELECT product_name, sales_date, sales_amount FROM sales ORDER BY 1, 2',
    'SELECT DISTINCT sales_date FROM sales ORDER BY 1'
) AS sales_pivot (product_name TEXT, "2019-01-01" INTEGER, "2019-01-02" INTEGER);

上述代码中的crosstab函数是用于执行数据透视操作的关键，它接收两个参数。第一个参数是用于获取原始数据的查询语句，我们可以在查询语句中指定需要透视的列，并通过ORDER BY对结果进行排序。第二个参数是用于指定需要在透视结果中包含哪些列的查询语句，同样可以通过ORDER BY对结果进行排序。

执行上述代码后，我们将得到透视后的结果，如下所示：

 product_name | 2019-01-01 | 2019-01-02
--------------+------------+------------
 产品A        |       1000 |       1500
 产品B        |       2000 |       1800

通过透视后的结果，我们可以更方便地对不同产品在不同销售日期上的销售金额进行分析和比较。

使用`crosstab`函数

除了上述简单的用法，crosstab函数还支持一些其他的参数和选项，可以进一步定制透视操作的结果。下面是crosstab函数的完整语法：

crosstab(text source_sql, text value_sql)
    RETURNS SETOF record
    LANGUAGE 'internal'
    IMMUTABLE
    PARALLEL SAFE
    STRICT

source_sql：用于获取原始数据的查询语句，可以包含任意有效的SELECT语句。
value_sql：用于指定需要在透视结果中包含哪些列的查询语句，可以包含任意有效的SELECT语句。

crosstab函数的结果是一个记录集，每一条记录包含透视后的一行数据。我们可以通过AS关键字来为返回的记录集中的列指定名称，从而更好地理解结果。

透视结果中的空值

在透视结果中，有时候会出现空值。例如，某个产品在某个销售日期上没有销售记录，那么在透视结果中对应的单元格就会显示为空值。

为了处理透视结果中的空值，可以使用NVL函数来将空值替换为指定的值。例如，我们可以将空值替换为0。示例代码如下：

SELECT *
FROM crosstab(
    'SELECT product_name, sales_date, sales_amount FROM sales ORDER BY 1, 2',
    'SELECT DISTINCT sales_date FROM sales ORDER BY 1'
) AS sales_pivot (product_name TEXT, "2019-01-01" INTEGER, "2019-01-02" INTEGER)
NVL(0);

动态列名

在透视操作中，有时候我们无法提前知道透视后的列名。例如，销售数据中的销售日期是动态变化的，无法预先确定。此时，可以使用generate_series函数和动态SQL来动态生成列名，并执行透视操作。示例代码如下：

-- 查询销售日期的范围
WITH date_range AS (
    SELECT MIN(sales_date) AS min_date, MAX(sales_date) AS max_date
    FROM sales
)
-- 生成日期序列
SELECT *
FROM crosstab(
    'SELECT product_name, sales_date, sales_amount FROM sales ORDER BY 1, 2',
    'SELECT DISTINCT sales_date FROM sales ORDER BY 1'
) AS sales_pivot (
    product_name TEXT,
    '2019-01-01' INTEGER,
    '2019-01-02' INTEGER,
    -- 动态生成剩余的日期列
    '2019-01-03' INTEGER,
    '2019-01-04' INTEGER
)
-- 使用COALESCE函数将空值替换为0
NVL(0);

上述代码通过WITH子句和date_range公共表表达式获取销售日期的范围，然后使用generate_series函数生成日期序列，并动态生成透视结果中的列名。最后，使用COALESCE函数将透视结果中的空值替换为0。

通过上述代码，我们可以处理动态列名的情况，使得透视操作更加灵活。