详解PostgreSQL中实现数据透视表的三种方法

目录

• 使用 CASE 表达式实现数据透视表
• 使用 FILTER 子句实现数据透视表
• 使用 CROSSTAB 函数创建数据透视表
• 总结

数据透视表（Pivot Table）是进行数据汇总、分析、浏览和展示的强大工具，可以帮助我们了解数据中的对比情况、模式和趋势，是数据分析师和运营人员必备技能之一。今天我们来谈谈如何在 PostgreSQL 中实现数据透视表的功能。

本文使用的示例数据可以点此下载。

使用 CASE 表达式实现数据透视表

实现数据行转列的一个通用方法就是利用 CASE 条件表达式和分组聚合操作。首先使用以下 GROUP BY 子句对销售数据进行分类汇总：

select coalesce(product, '【全部产品】') "产品",
       coalesce(channel, '【所有渠道】') "渠道",
       coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份",
       sum(amount) "销量"
from sales_data
group by rollup (product,channel, to_char(saledate, 'YYYYMM'))
order by product, channel, to_char(saledate, 'YYYYMM');

其中，group by 将数据按照产品、渠道以及月份进行汇总；rollup 选项用于生成不同层次的小计、合计以及总计；coalesce 函数用于将汇总行中的 NULL 值显示为相应的信息。该查询返回的结果如下：

产品       |渠道       |月份       |销量    |
----------|-----------|----------|-------|
桔子       |店面       |201901    |  41306|
桔子       |店面       |201902    |  37906|
桔子       |店面       |201903    |  48866|
桔子       |店面       |201904    |  48673|
桔子       |店面       |201905    |  58998|
桔子       |店面       |201906    |  58931|
桔子       |店面       |【所有月份】| 294680|
桔子       |京东       |201901    |  41289|
桔子       |京东       |201902    |  43913|
桔子       |京东       |201903    |  49803|
桔子       |京东       |201904    |  49256|
桔子       |京东       |201905    |  64889|
桔子       |京东       |201906    |  62649|
桔子       |京东       |【所有月份】| 311799|
桔子       |淘宝       |201901    |  43488|
桔子       |淘宝       |201902    |  37598|
桔子       |淘宝       |201903    |  48621|
桔子       |淘宝       |201904    |  49919|
桔子       |淘宝       |201905    |  58530|
桔子       |淘宝       |201906    |  64626|
桔子       |淘宝       |【所有月份】| 302782|
桔子       |【所有渠道】|【所有月份】| 909261|
...
香蕉       |【所有渠道】|【所有月份】| 925369|
【全部产品】|【所有渠道】|【所有月份】|2771682|

接下来我们将数据按照不同月份显示为不同的列，也就是将行转换为列，这个功能可以使用 CASE 表达式实现：

select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道", 
       sum(case to_char(saledate, 'YYYYMM') when '201901' then amount else 0 end) "一月",
       sum(case to_char(saledate, 'YYYYMM') when '201902' then amount else 0 end) "二月",
       sum(case to_char(saledate, 'YYYYMM') when '201903' then amount else 0 end) "三月",
       sum(case to_char(saledate, 'YYYYMM') when '201904' then amount else 0 end) "四月",
       sum(case to_char(saledate, 'YYYYMM') when '201905' then amount else 0 end) "五月",
       sum(case to_char(saledate, 'YYYYMM') when '201906' then amount else 0 end) "六月",
       sum(amount) "总计"
from sales_data
group by rollup (product, channel);

产品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |总计    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |店面       | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子       |京东       | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子       |淘宝       | 43488| 37598| 48621| 49919| 58530| 64626编程| 302782|
桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
苹果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
苹果       |京东       | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
苹果       |淘宝       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉       |店面       | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉       |京东       | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉       |淘宝       | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

第一个 SUM 函数中的 CASE 表达式只汇总 201901 月份的销量，其他月份销量设置为 0；后面的 SUM 函数依次类推，得到了每个月的销量汇总和所有月份的总计。

使用 CASE 条件表达式加上分组聚合的方法也适用于其他数据库。

使用 FILTER 子句实现数据透视表

除了在聚合函数中使用 CASE 表达式之外，PostgreSQL 还提供了一个更简单的方法，就是 FILTER 子句。

aggregate_function(<expression>) FILTER (WHERE <condition>)

其中，aggregate_function 可以是任意的聚合函数或者窗口函数； FILTER 子句用于指定一个额外的条件，只有满足该条件的数据行才会参与计算。

我们可以使用 SUM 函数的 FILTER 子句实现与上文 CASE 表达式相同的效果：

select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道", 
       sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "一月",
       sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "二月",
       sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "三月",
       sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "四月",
       sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "五月",
       www.devze.comsum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "六月",
       sum(amount) "总计"
from sales_data
group by rollup (product, channel);

产品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |总计    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |店面       | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子       |京东       | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子       |淘宝       | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
苹果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
苹果       |京东       | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
苹果       |淘宝       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉       |店面       | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉       |京东       | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉       |淘宝       | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

PostgreSQL 支持在 filter 子句中使用子查询，例如 exists。

使用 CROSSTAB 函数创建数据透视表

PostgreSQL 的扩展模块 tablefunc 提供了许多返回结果为数据表的函数，其中 crosstab 函数可以用于实现数据的行列转换。这是一个扩展模块，所以我们需要先安装插件：

create extension if not exists tablefunc;
ERROR:  could not open extension control file "/usr/pgsql-12/share/extension/tablefunc.control": No such file or directory

以上错误表示没有安装 postgresql-contrib 包，我们通过操作系统命令进行安装（Centos）：

sudo yum install postgresql12-contrib

然后再次执行上面的 create extension 命令安装 tablefunc 模块。

接下来就可以通过 crosstab 函数将行转换成列，例如：

select *
from crosstab(
$$select product||'-'||channel pc,product,channel, to_char(saledate, 'YYYYMM'), sum(amount)
from sales_data
group by product||'-'||channel, product, channel, to_char(saledate, 'YYYYMM')
order by 1$$,
$$select distinct to_char(saledate, 'YYYYMM') from sales_data ord编程客栈er by 1$$
)
as ct(pc text, product text, channel text,
      "201901" numeric, "201902" numeric, "201903" numeric,
      "201904" numeric, "201905" numeric, "201906" numeric);

crosstab 函数包含 2 个字符串类型的参数，都是查询语句；第一个 select 语句用于构造源数据，crosstab 的源数据需要指定一个标识每个结果行的字段，示例中使用 pc；第二个 select 语句的结果用于构造转换之后的字段；as 子句用于定义返回结果的字段类型。

pc      |product |channel |201901  |201902  |201903  |201904  |201905  |201906  |
--------|--------|--------|--------|--------|--------|--------|--------|--------|
桔子-京东|桔子     |京东     |41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|
桔子-店面|桔子     |店面     |41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|
桔子-淘宝|桔子     |淘宝     |43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|
苹果-京东|苹果     |京东     |38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
苹果-店面|苹果     |店面     |43845.00|40539.00|4490www.devze.com9.00|55646.00|56771.00|64933.00|
苹果-淘宝|苹果     |淘宝     |42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|
香蕉-京东|香蕉     |京东     |36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|
香蕉-店面|香蕉     |店面     |41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|
香蕉-淘宝|香蕉     |淘宝     |42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|

接下来还需要增加一个总计行和总计列：

select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",
       sum("201901") "一月", sum("201902") "二月", sum("201903") "三月",
       sum("201904") "四月", sum("201905") "五月", sum("201906") "六月",
       sum("201901"+"201902"+"201903"+"201904"+"201905"+"201906") "总计"
from crosstab(
$$select product||'-'||channel pc,product,channel, to_char(saledate, 'YYYYMM'), sum(amount)
from sales_data
group by product||'-'||channel, product, channel, to_char(saledate, 'YYYYMM')
order by 1$$,
$$select distinct to_char(saledate, 'YYYYMM') from sales_data order by 1$$
)
as ct(pc text, product text, channel text,
      "201901" numeric, "201902" numeric, "201903" numeric,
      "201904" numeric, "201905" numeric, "201906" numeric)
group by rollup (product, channel);

我们基于 crosstab 函数的结果增加了一些总计数据并且修改了返回字段的名称，让结果更加接近 EXCEL 数据透视表：

产品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |总计    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |京东       | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子       |店面       | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子       |淘宝       | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
苹果       |京东       | 38269javascript| 40593| 56552| 56662| 64493| 62045| 318614|
苹果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
苹果       |淘宝       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉       |京东       | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉       |店面       | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉       |淘宝       | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

总结

数据透视表是一个非常实用的数据分析功能，可以用于实现复杂的数据分类汇总和对比分析。本文介绍了在 PostgreSQL 中实现数据透视表的三种方式，包括使用 CASE 条件表达式和分组聚合相结合、聚合函数的 FILTER 子句以及扩展模块 tablefunc 中的 crosstab 函数生成数据透视表。

以上就是详解PostgreSQL中实现数据透视表的三种方法的详细内容，更多关于PostgreSQL数据透视表的资料请关注编程客栈(www.devze.com)其它相关文章！