文章目录
使用 CASE 表达式实现数据透视表
使用 FILTER 子句实现数据透视表
使用 CROSSTAB 函数创建数据透视表
总结
数据透视表(Pivot Table)是进行数据汇总、分析、浏览和展示的强大工具,可以帮助我们了解数据中的对比情况、模式和趋势,是数据分析师和运营人员必备技能之一。前文已经介绍了 MySQL/MariaDB、Oracle 以及 Microsoft SQL Server 中的数据透视表实现,今天我们来谈谈如何在 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') "五月",
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 函数将行转换成列,例如: