PostgreSQL crosstab函数|极客教程

PostgreSQL crosstab函数

1. 介绍

在 PostgreSQL 数据库中，有一个非常有用的函数叫做 crosstab。crosstab 函数用来将查询结果中的行数据转换为列数据，通常用于生成交叉表。本文将对 crosstab 函数进行详细介绍，并提供一些示例以帮助读者更好地理解它的使用方法。

2. 函数定义

在 PostgreSQL 中，crosstab 函数属于 tablefunc 模块，因此在使用之前需要先确保该模块已经被加载。可以使用 CREATE EXTENSION 命令进行加载：

CREATE EXTENSION tablefunc;

crosstab 函数的定义如下：

crosstab(crosstab_source_sql text, category_sql text)
RETURNS setof record

其中，crosstab_source_sql 是一个带有 row_name、category_name 和 data_value 这三个列的 SELECT 查询语句。row_name 一般代表行的标识，category_name 一般代表列的标识，data_value 则是所需填充到交叉表中的值。category_sql 是一个查询语句，用于生成所有可能的 category_name 的列表。

3. 使用示例

下面通过几个具体的示例来演示 crosstab 函数的使用方法。

示例1：基本用法

假设我们有一个名为 sales 的表，存储了每个员工在不同月份的销售额信息。表的结构如下：

CREATE TABLE sales (
    id serial primary key,
    employee_id integer,
    month date,
    amount numeric
);

数据如下：

INSERT INTO sales (employee_id, month, amount)
VALUES 
    (1, '2022-01-01', 1000),
    (2, '2022-01-01', 1500),
    (1, '2022-02-01', 1200),
    (2, '2022-02-01', 1800),
    (1, '2022-03-01', 1400),
    (2, '2022-03-01', 1600);

现在，我们想要生成一个交叉表，以员工 ID 作为行，以月份作为列，填充的值为销售额。

我们可以使用以下查询语句来实现：

SELECT *
FROM crosstab(
    'SELECT employee_id, to_char(month, ''YYYY-MM''), amount
     FROM sales
     ORDER BY 1, 2',
    'SELECT DISTINCT to_char(month, ''YYYY-MM'')
     FROM sales
     ORDER BY 1'
) AS ct (employee_id integer, "2022-01" numeric, "2022-02" numeric, "2022-03" numeric);

运行以上查询的结果如下：

employee_id	2022-01	2022-02	2022-03
1	1000	1200	1400
2	1500	1800	1600

示例2：动态列名

在上面的示例中，我们已经在查询语句中指定了列名。但在实际情况下，列名往往是不确定的，因此需要在查询中动态地生成列名。

考虑以下示例，我们有一个名为 scores 的表，存储了学生的考试成绩信息。表的结构如下：

CREATE TABLE scores (
    student_id integer,
    subject_name text,
    score integer
);

数据如下：

INSERT INTO scores (student_id, subject_name, score)
VALUES 
    (1, 'Math', 80),
    (1, 'Science', 90),
    (2, 'Math', 85),
    (2, 'Science', 95),
    (3, 'Math', 75),
    (3, 'Science', 85);

现在，我们想要生成一个交叉表，以学生 ID 作为行，以科目名称作为列，填充的值为分数。

由于科目名称是不确定的，我们需要在查询中动态地生成列名。可以使用以下查询语句来实现：

SELECT *
FROM crosstab(
    'SELECT student_id, subject_name, score
     FROM scores
     ORDER BY 1, 2',
    'SELECT DISTINCT subject_name
     FROM scores
     ORDER BY 1'
) AS ct (student_id integer, math numeric, science numeric);

运行以上查询的结果如下：