pgsql crosstab

1. 介绍

在 PostgreSQL 数据库中，有许多强大的功能用于数据处理和查询。其中之一就是 crosstab 函数。crosstab 函数是一个用于透视（pivot）表格的函数，它能够将数据行转换为列，使数据更易于阅读和分析。本文将详细介绍 pgsql 中的 crosstab 函数的使用方法和示例。

2. 安装插件

在使用 crosstab 函数之前，需要先安装一个名为 tablefunc 的扩展插件。这个插件提供了实现 crosstab 函数所需的功能。可以使用以下命令安装该插件：

CREATE EXTENSION IF NOT EXISTS tablefunc;

3. 语法

crosstab 函数的基本语法如下：

crosstab(text source_sql)

其中，source_sql 是一个包含源数据的 SQL 查询语句。此语句必须返回三列的结果集，第一列是行的标识符，第二列是列的标识符，第三列是要填充到新表中的值。

4. 示例

为了更好地理解和使用 crosstab 函数，接下来将通过一些示例来说明。本文将使用一个示例表 sales 来演示。

4.1 创建示例表

首先，创建 sales 表并插入一些示例数据：

CREATE TABLE sales (
    product_id integer,
    month date,
    sales_amount numeric
);

INSERT INTO sales (product_id, month, sales_amount) VALUES
    (1, '2022-01-01', 1000),
    (1, '2022-02-01', 2000),
    (1, '2022-03-01', 3000),
    (2, '2022-01-01', 500),
    (2, '2022-02-01', 1000),
    (2, '2022-03-01', 1500);

4.2 基本用法

假设我们想要将 sales 表的数据按月份和产品 ID 进行透视，生成一个新的表格。下面的查询演示了如何使用 crosstab 函数：

SELECT *
FROM crosstab(
    'SELECT month, product_id, sales_amount
     FROM sales
     ORDER BY 1',
    'VALUES (1), (2), (3)'
) AS ct (month date, product1 numeric, product2 numeric, product3 numeric);

上述查询中的第一个参数是源数据查询语句，它按照月份和产品 ID 排序。第二个参数是一个值列表，用于指定生成表格的列名。上述查询将返回一个新表格，其中的列名为 month, product1, product2, product3，分别对应月份和产品 ID。

运行上述查询后，得到的结果如下所示：

月份	产品1销售额	产品2销售额	产品3销售额
2022-01-01	1000	500	NULL
2022-02-01	2000	1000	NULL
2022-03-01	3000	1500	NULL

其中，每一行表示某个月份下各个产品的销售额。

4.3 动态列名

有时候，我们希望动态生成列名，而不是手动输入列名。这可以通过将列名作为参数传递给 crosstab 函数来实现。下面的示例演示了如何使用动态列名：

SELECT *
FROM crosstab(
    'SELECT month, product_id, sales_amount
     FROM sales
     ORDER BY 1',
    'SELECT DISTINCT product_id
     FROM sales
     ORDER BY 1'
) AS ct (month date, product1 numeric, product2 numeric, product3 numeric);

在此示例中，我们使用 SELECT DISTINCT 子句来获取产品 ID 的唯一值，并用作生成表格的列名。运行上述查询后，得到的结果与前面示例中的结果相同。

4.4 引用其他表的值

有时候，在透视表格中我们可能需要引用其他表的值，并根据这些值进行计算。下面的示例演示了如何使用 crosstab 函数来引用其他表的值：

CREATE TABLE products (
    product_id integer,
    product_name text
);

INSERT INTO products (product_id, product_name) VALUES
    (1, 'Product A'),
    (2, 'Product B'),
    (3, 'Product C');

SELECT *
FROM crosstab(
    'SELECT month, sales.product_id, sales_amount * price
     FROM sales
     JOIN products ON sales.product_id = products.product_id
     ORDER BY 1',
    'SELECT DISTINCT product_id
     FROM sales
     ORDER BY 1'
) AS ct (month date, product1 numeric, product2 numeric, product3 numeric);

在此示例中，我们创建了一个 products 表，其包含产品 ID 和产品名称。然后，在 crosstab 函数的源数据查询语句中，我们将 sales_amount 乘以 price 列来计算销售额。最后，将产品 ID 作为列名生成透视表格。

4.5 空值处理

在某些情况下，源数据中可能存在空值。crosstab 函数提供了一个选项来处理这种情况。通过在源数据查询语句中使用 NULLIF 函数，我们可以将特定的值转换为 NULL。例如：

SELECT *
FROM crosstab(
    'SELECT month, product_id, NULLIF(sales_amount, 0)
     FROM sales
     ORDER BY 1',
    'VALUES (1), (2), (3)'
) AS ct (month date, product1 numeric, product2 numeric, product3 numeric);

在上述示例中，我们使用 NULLIF 函数将 sales_amount 为零的值转换为 NULL。这样，透视表格中将会显示 NULL 而不是零。

4.6 自定义缺失值填充

默认情况下，crosstab 函数在透视表格中使用 NULL 作为缺失值的填充。如果希望自定义缺失值的填充，可以在 crosstab 函数后添加 WITH NULL '<custom_value>' 来实现。例如：

SELECT *
FROM crosstab(
    'SELECT month, product_id, sales_amount
     FROM sales
     ORDER BY 1',
    'VALUES (1), (2), (3)'
) AS ct (month date, product1 numeric, product2 numeric, product3 numeric)
WITH NULL 'N/A';

在上述示例中，我们将缺失值的填充设置为 ‘N/A’。如果透视表格中存在缺失值，将会显示为 ‘N/A’。