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

来自：网络

时间：2024-08-28

阅读：

使用 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') "五月",
       sum(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 order 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|44909.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|
苹果       |京东       | 38269| 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数据透视表的资料请关注其它相关文章！

SQL Server:SELECT TOP 10 * FROM T_USER ORDER BY NEWID()ORACLE:SELECT * FROM (SELECT * FROM T_USER ORDER BY DBMS_RANDOM.RANDOM()) WHERE RONUM <= 10MySQL:SELECT *

2024-10-21 20:47:50

一、limit用法在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。SELECT * FROM table LIMIT [off

2024-10-21 20:44:48

目录问题原因解决办法如何设置和查看使用Navicat Premium 12导入mysql的sql文件时，导入失败，出现如下错误：2006 - MySQL server has gone away。mysql出现ERROR : (2006, &

2024-10-21 20:43:22

今天早上启动了 phpstudy 软件中的 MySQL 8.0 数据库时，发现它启动后约过了 3 秒就自行关闭了。接着又自动启动然后再次关闭，似乎陷入了一个“开-闭-开”的循环。这导致我无

2024-10-18 23:15:41

目录

1. 每秒交易量

2. 每分钟交易量

3. 每5分钟交易量

4. 每10分钟交易量

5. 每30分钟交易量

结合 WHERE

2024-10-18 23:15:27

目录 Redis是什么？ Redis的特点依赖配置 redis的配置类（用于格式转换，处理乱码） String（字符串）特点业务场景代码使用案例 List（列表）特点业务场景代码使用案例 S

2024-10-18 23:15:18

目录 Oracle中BLOB、CLOB的读取和写入基于SQL的方式实现CLOB、BLOB的插入与读取 1. 插入大类型数据 2. 读取大类型数据并转换为字符串基于Java方式实现CLOB和B

2024-10-18 23:15:09

目录 dbeaver导出mysql数据库右键表-tools-Dump database 按默认设置总结 dbeaver导出mysql数据库右键表-tools-Dump database按默认设置只需修改输出文件夹我这里把

2024-10-18 23:15:01

目录 DBeaver如何导入excel中的大量数据步骤一：将Excel文件转换为CSV格式步骤二：在DBeaver中创建数据库表（如果尚未创建）步骤三：导入CSV文件到数据库表注意事项总结

2024-10-18 23:14:43

目录一、先清理归档日志使得数据库能够正常连接二、排查问题三、处理问题四、清理后效果最近客户单位的oracle数据库出了问题，经常出现无法连接,报错提示 ORA-00257: arc

2024-10-18 23:14:30

目录函数单行函数字符函数数学函数日期函数流程控制函数分组函数分组查询按单个字段分组在分组前进行条件筛选在分组之后进行条件筛选按多字段分

2024-10-18 23:14:18

目录 1、计算两个时间字符串之间的差值 2、字符串转换成日期/时间类型 3、计算两个日期和时间之间的差值在 SQL Server 中，计算两个时间字符串之间的差值首先将这些字符串转

2024-10-18 23:14:10

目录问题描述原因分析解决方案在Oracle数据库中，DECODE 函数是一个非常实用的条件处理函数，通常用于替代简单的 CASE WHEN 语句。它根据给定的值列表进行匹配，如果匹配成功

2024-10-18 23:14:02

目录描述实践描述删除表大批量数据，这是一个比较少的事件。但在实际的业务开发中或者数据测试也会遇到这种情况。比如定期从日志大表中删除几百万的数据记录；删除表数据的

2024-10-18 23:13:52

目录前言简介概述 DELETE 操作的基本概念常用的 DELETE 方法核心源码解读简单 DELETE 语句批量 DELETE 示例案例分析案例1：使用简单 DELETE 删除用户数据

2024-10-18 23:13:43

目录一、MySQL清空表数据三种方法 1.1 清空表数据：truncate 1.2 删除表：drop 1.3 删除/清空表数据：delete 二、使用原则三、truncate、drop、delete区别 1.操作类型

2024-10-14 19:50:11

目录一、问题描述二、解决方案 1.使用mysqldump命令备份数据库 2.创建目标数据库 3.导入数据到目标数据库三、补充知识 3.1 mysqldump命令介绍 3.2 常用备份命

2024-10-14 19:50:03

目录一、limit深分页为什么会变慢二、优化方案 2.1 通过子查询优化（覆盖索引）回顾B+树结构覆盖索引把条件转移到主键索引树 2.2 INNER JOIN 延迟关联

2024-10-14 19:49:55

目录 MySQL的TRUNCATE()函数规则如下函数示例当 X 为正数时当 X 为负数时总结 MySQL的TRUNCATE()函数TRUNCATE(X,D) 是MySQL自带的一个系统函数。其中，X是数值，D

2024-10-14 19:49:37

目录方法一：在电脑设置中查看电脑iP地址方法二：通过运行程序查看电脑IP地址方法三：通过浏览器查看电脑IP地址拓展：在自己电脑建立了MYSQL数据库，但是想在其它的电脑进行连接，

2024-10-14 19:49:29

目录概念 Pipeline 底层原理分析 Redis单个命令执行基本步骤 RTT 时间 Redis Pipeline Pipeline实际应用场景数据导入导出数据处理批量操作其他应用场

2024-10-14 19:49:13

修改配置文件1.打开Postgresql配置文件，如：C:\Program Files\PostgreSQL\14\data\postgresql.conf
2.修改如下，如果字段被#注释，则去掉#log_directory = 'log'
log_file

2024-10-14 19:49:02

目录创建分区删除分区分区性能总结说明：分区表，顾名思义，就是一张表根据规则，划分多个区，通过分区，实现一种“逻辑隔离”，这在Saas系统中是非常常见的。本文介绍如何

2024-10-14 19:48:52

目录 1.创建mysql基础配置 2.创建pvc挂载 3.创建工作负载 4.创建服务（创建服务后才能进行外部访问） 5.测试mysql是否能正常访问演示示例使用的是3.4.1，各版本有名字差异功能是

2024-10-14 19:48:36

目录安装步骤 1. 解压安装包 2. 移动解压后的文件夹 3. 创建MySQL用户 4. 设置权限 5. 配置MySQL 6. 设置环境变量 7. 初始化数据库 8. 设置系统服务 9. 设置roo

2024-10-14 19:48:11

目录解决方案 1. 检查是否输入正确的密码 2. 使用“跳过权限表”的方式重置 root 密码 2.1 停止 MySQL 服务 2.2 以跳过权限表模式启动 MySQL 2.3 重

2024-10-14 19:48:03

目录实现演示说明：实际开发中，我们在前端页面上点击了一个按钮，访问了一个接口，这时因为网络波动或者其他原因，页面上没有反应，用户可能会在短时间内再次点击一次或者用户以为没

2024-10-14 19:47:53

发生的错误好吧，当我在 laragon 应用程序中从mysql 5.1.72升级到mysql 8.0.32时，发生了这个错误。当我在 laragon 应用程序中 start all 时，出现的错误是这样的。laragon 中的

2024-09-17 21:32:55

介绍在本实验中，我们将学习和练习索引、视图、备份和恢复。这些概念对于数据库管理员来说非常重要。学习目标创建索引创建视图备份与恢复准备开始之前，我们需要准备好环境

2024-09-17 21:32:18

如何将 SQL 文件导入 MySQL 数据库导入 MySQL 数据库文件的方法：使用 MySQL 客户端打开 MySQL 客户端并连接到目标数据库。使用 SOURCE 命令，后跟要导入的 SQL 文件路径：SOUR

2024-09-17 21:32:00

2018-08-19

2020-03-22

2020-12-17

2020-03-22

2020-10-15

2021-01-28

2020-03-22

2021-01-21

2020-10-28

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

目录

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

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

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

总结

热点内容

免费资源网

在线工具

扫一扫随时看

本站下载频道