PostgreSQL 如何实现数据透视表_postgresql pivot_weixin_44519342的博客

相关文章推荐

善良的高山 · PostgreSQL ...· 1 年前 ·

玩足球的大海 · 北京科技大学自动化学院-首页· 11 月前 ·

温暖的凉面 · Excel VBA 编程---语法下 - ...· 1 年前 ·

风度翩翩的伤疤 · 【css】如何为页面DOM添加分页属性，在打 ...· 1 年前 ·

睡不着的红茶 · 「sql存储过程批量写入数据」相关问答|文档 ...· 1 年前 ·

逆袭的黑框眼镜 · c++ - reference to ...· 1 年前 ·

使用 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);

📝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 函数将行转换成列，例如：

原文链接： http://www.mark-to-win.com/tutorial/51611.html oracle11g开始内置了 数据透视表 pivot table这一功能，可以用来实现行列转换的功能，但是在数据量较大的时候使用性能就会较差。 pivot 语法为： SELECT ... FROM ... PIVOT [XML] ( pivot _clause pivot _for_clause pivot _in_clause ) WH... 交叉表（Cross Tabulations）是一种常用的分类汇总表格。使用交叉表查询，显示源于表中某个字段的汇总值，并将它们分组，其中一组列在数据表的左侧，另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算，如：求和、平均值、记数、最大值、最小值等。使用交叉表查询数据非常直观明了，被广泛应用。交叉表查询也是 数据库 的一个特点。　　select 表1.组名, 　　(select 表1.成员姓名 from 表2 b where 表1.成员1id=表2.成员id) as 成员1id, 　　(select 表1.成员姓名 from 表2 b where 表1.成员2 在Oracle和 SQLServer 都有現成的 PIvot 樞紐分析函式可以用在MySQL中如果要使用樞紐分析怎麼辦...今天和大家分享如何在MySQL中使用樞紐分析案例樣本資料：CREATE TABLE T(userName VARCHAR(100),Price int,Dt DATE);INSERT INTO T VALUES ('Tom',100,'2017-01-01');INSERT... 做过数据清洗ETL工作的都知道，行列转换是一个常见的数据整理需求。在不同的编程语言中有不同的实现方法，比如SQL中使用case+group，或者Power BI的M语言中用拖放组件实现。今天正好需要在 PostgreSQL 中处理一个数据行列转换，就把这个方法记录下来。首先明确一下啥叫行列转换，因为这个叫法也不是很统一，有的地方叫转置，有的地方叫透视，不一而足。我们就以下表为例，定义如下： PostgreSQL 已经成为当下最流行的开源关系 数据库 系统之一。当客户从Oracle及微软SQL Server等商业 数据库 向外迁移时， PostgreSQL 已经成为一大首选替代方案。目前，AWS为大家提供两种 PostgreSQL 托管选项：Amazon RDS与Amazon Aurora。除了提供托管 PostgreSQL 服务之外，AWS还准备了一系列用于协助迁移的工具与资源。AWS Schema Co... 行转列，通用做法是使用 case when 。today，介绍一下新的方法 -- crosstab crosstab 函数被用来生成 pivot （扭曲，转动）展示，即通过横向而不是下拉展示（行转列）。如下面这个例子： row1 val11 row1 val12 row1 val13 row2 val21 row2 val22 row2 val23 期望的结果： row1 val11 val12 val13 ... PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UN PIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。测试用的数据及表结构： 1. CREATE TABLE ShoppingCart( 2. [Week] INT NOT NULL, 3. [TotalPrice] DE 文章目录 数据库 中通用的方法case..whenmysql postgresql oracle 在进行报表开发时，需要展示各种指标，会经常需要将数据表进行行列互转。 数据库 中通用的方法创建如下的表：行转列：展示每一用户的语文、数学、英语的成绩 case…when 使用case…when，需要和聚合函数一起使用。 select ,sum(case when subject='语文' th...