DuckDB：Povit实现数据透视表

透视表（Pivot Table）是一种交互式的数据分析工具，主要用于对大量数据进行汇总、分析和展示。它可以快速地将原始数据按照用户指定的行、列和数据区域进行重新组织和统计，以呈现出数据的不同视角和汇总信息。本文介绍DuckDB如何生成透视表，以及如何还原透视表。

透视表（Pivot Table）介绍

透视表（Pivot Table）是一种交互式的数据分析工具，主要用于对大量数据进行汇总、分析和展示。它可以快速地将原始数据按照用户指定的行、列和数据区域进行重新组织和统计，以呈现出数据的不同视角和汇总信息。它主要包括三个要素：

• 行区域（Rows）

  用于放置一个或多个分类字段，这些字段决定了透视表中数据行的分组方式。例如，在销售数据透视表中，将 “产品类别” 字段放在行区域，那么透视表会按照不同的产品类别对数据进行分组显示，每个产品类别会占据一行。

• 列区域（Columns）

  同样用于放置分类字段，不过它决定了数据列的分组方式。比如，将 “销售地区” 字段放在列区域，就会在透视表中按照不同的销售地区划分列，这样就形成了一个以产品类别为行、销售地区为列的二维表格结构，便于查看不同产品类别在各个销售地区的数据情况。

• 数据区域（Values）

  这里放置需要进行汇总计算的数值字段，如销售额、销售量等。同时，用户可以指定汇总计算的方式，如求和、计数、平均值、最大值、最小值等。例如，在上述销售数据透视表中，将 “销售额” 字段放在数据区域，并选择求和计算方式，那么透视表的每个单元格（行和列的交叉点）就会显示相应产品类别在相应销售地区的销售额总和。

透视表意义

• 灵活的数据汇总与分析

  透视表比交叉表更加灵活，它允许用户快速地对数据进行重新排列和汇总。用户可以根据自己的需求将数据字段拖放到不同的位置（行、列、值区域）来生成各种汇总报表。以员工绩效数据为例，用户可以将部门字段拖到行区域，将绩效指标字段拖到列区域，将绩效得分拖到值区域，并选择合适的汇总方式（如求和、平均值等），这样就能方便地分析每个部门在各项绩效指标上的表现。

• 数据探索与洞察

  透视表是数据探索的有力工具。它能够帮助用户从不同的角度观察数据，发现数据中的异常点或趋势。例如，在财务数据分析中，通过透视表可以从时间（月份、季度）、费用类型（办公用品、差旅费等）、部门等多个维度对费用支出进行汇总分析。如果某个部门在某个月份的某项费用支出异常高，通过透视表可以很容易地发现这个情况，进而深入挖掘原因，可能是业务拓展活动导致的临时性支出增加等。

• 高效的报告生成

  在制作报告时，透视表可以大大提高效率。用户不需要编写复杂的公式或查询语句来汇总数据，只需要简单地操作透视表的字段和设置汇总方式，就可以生成专业的汇总表格。这些表格可以直接用于报告中，并且可以根据报告的受众和重点，快速调整透视表的布局和汇总方式，使报告更具针对性。

DuckDB透视表实践

数据准备

首先创建表：

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);

插入示例数据：

INSERT INTO cities
VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

PIVOT ON, USING

第一个查询以year列为中心。这意味着我们将分别获得国家、姓名和年份的列。

PIVOT cities
ON year
USING sum(population);

输出结果：

country|name         |2000|2010|2020|
-------+-------------+----+----+----+
US     |New York City|8015|8175|8772|
US     |Seattle      | 564| 608| 738|
NL     |Amsterdam    |1005|1065|1158|

PIVOT ON, USING, GROUP BY

下面示例，我们再次以年份为中心，但是按国家分组的：

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

输出结果：

country|2000|2010|2020|
-------+----+----+----+
NL     |1005|1065|1158|
US     |8579|8783|9510|

IN 过滤子句

如果我们想过滤主列，我们可以使用on…IN子句。在本例中，我们只想包括2000年和2010年：

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;

输出结果：

country|2000|2010|
-------+----+----+
US     |8579|8783|
NL     |1005|1065|

ON 多列

可以让多个列作为主列：

PIVOT cities
ON country, name
USING sum(population);

输出结果：

year|NL_Amsterdam|NL_New York City|NL_Seattle|US_Amsterdam|US_New York City|US_Seattle|
----+------------+----------------+----------+------------+----------------+----------+
2000|        1005|                |          |            |            8015|       564|
2010|        1065|                |          |            |            8175|       608|
2020|        1158|                |          |            |            8772|       738|

我们看到有一些空列，这是country, name两列笛卡尔积产生的结果，这不一定符合实际业务场景，我们可以优化查询：

PIVOT cities
ON country || '_' || name
USING sum(population);

这里以实际数据进行组合产生主列，输出结果：

year|NL_Amsterdam|US_New York City|US_Seattle|
----+------------+----------------+----------+
2000|        1005|            8015|       564|
2010|        1065|            8175|       608|
2020|        1158|            8772|       738|

多个using表达式

接下来是多个USING表达式。我们将计算最大人口和按年份和按国家分组的人口总和：

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;

输出结果：

country|2000_total|2000_max|2010_total|2010_max|2020_total|2020_max|
-------+----------+--------+----------+--------+----------+--------+
NL     |      1005|    1005|      1065|    1065|      1158|    1158|
US     |      8579|    8015|      8783|    8175|      9510|    8772|

多个Group by 表达式

那么按多列分组呢？

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

输出结果：

country|name         |2000|2010|2020|
-------+-------------+----+----+----+
US     |New York City|8015|8175|8772|
NL     |Amsterdam    |1005|1065|1158|
US     |Seattle      | 564| 608| 738|

Select语句中使用povit

我们可以创建包括pivot的CTE查询，然后把它当作表来查询：

WITH pivot_alias AS (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
)
SELECT * FROM pivot_alias;

输出结果：

country|name         |2000|2010|2020|
-------+-------------+----+----+----+
US     |New York City|8015|8175|8772|
NL     |Amsterdam    |1005|1065|1158|
US     |Seattle      | 564| 608| 738|

多个pivot语句

我们还可以连接主语句，这很简洁：

SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);

输出结果：

country|2000|2010|2020|Amsterdam|New York City|Seattle|
-------+----+----+----+---------+-------------+-------+
NL     |1005|1065|1158|     3228|             |       |
US     |8579|8783|9510|         |        24962|   1910|

unpivot 应用

有时我们需要对透视表进行逆向操作，实现规范化，从而更好进行数据分析。首先我们准备数据：

create table country_data as 

> 这里是引用

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

select * from country_data;

输出结果如下：

country|name         |2000|2010|2020|
-------+-------------+----+----+----+
US     |New York City|8015|8175|8772|
NL     |Amsterdam    |1005|1065|1158|
US     |Seattle      | 564| 608| 738|

现在我们使用unpivot进行逆向查询：

UNPIVOT country_data
ON 2000, 2010, 2020
INTO
    NAME years
    VALUE population;

数据结果：

country|name         |years|population|
-------+-------------+-----+----------+
US     |New York City|2000 |      8015|
US     |New York City|2010 |      8175|
US     |New York City|2020 |      8772|
NL     |Amsterdam    |2000 |      1005|
NL     |Amsterdam    |2010 |      1065|
NL     |Amsterdam    |2020 |      1158|
US     |Seattle      |2000 |       564|
US     |Seattle      |2010 |       608|
US     |Seattle      |2020 |       738|

实际执行语句被翻译为：

SELECT
    country,
    name,
    unnest([2000, 2010, 2020]) AS years,
    unnest([2000, 2010, 2020]) AS population
FROM country_data;