Spark pivot数据透视详解

有数的编程笔记

已于 2025-02-07 18:33:13 修改

阅读量939

点赞数 3

分类专栏： Spark/Hive 文章标签： spark 大数据 hive

于 2024-04-24 20:12:39 首次发布

本文链接：https://blog.csdn.net/qq_33446500/article/details/138168523

版权

Spark/Hive 专栏收录该内容

19 篇文章

订阅专栏

1. 概念

描述
用于数据透视，根据特定的列获取聚合值，聚合值将转换为select子句中使用的多列。可以在表名或子查询后指定pivot子句。
使用场景
常见的使用场景之一，对数据进行行转列操作。

语法格式

select *
From Table
PIVOT ( 
    { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
    FOR column_list IN ( expression_list ) 
)

aggregate_expression：聚合计算表达式，和在sql中正常进行聚合计算使用方式一致。
column_list：指定进行旋转的列，用于生成新的列。必须是from子句中存在的列（如果From后为具体表则为表中存在的列，如果From后为子查询则为子查询中selece的列）。
expression_list：指定旋转列下具体的值。当指定别名后则使用别名作为新列名，否则将直接使用列中值作为新字段名。

简单说就是column_list用来指定根据那些列进行旋转，而expression_list用来指定旋转列下具体的值。最终结果中新列名由expression_list和aggregate_expression来决定，为二者的笛卡尔积。

官方文档

接下来通过具体示例理解pivot的具体用法。

2. 理解

测试数据

CREATE TABLE pivot1 (name STRING, subject string, score INT);
INSERT overwrite table pivot1
select inline(array(
struct('张三','语文',95),
struct('张三','英语',85),
struct('张三','数学',100),
struct('李四','语文',90),
struct('李四','英语',80),
struct('李四','数学',100),
struct('王五','语文',99),
struct('王五','数学',98)
));

2.1. 简单示例

select *
from pivot1 
pivot(max(score) as max for subject in('语文','英语','数学'));
-- 执行结果
name    语文    英语    数学
王五    99      NULL    98
李四    90      80      100
张三    95      85      100

== Physical Plan ==
HashAggregate(keys=[name#35], functions=[pivotfirst(subject#36, max(pivot1.`score`) AS `max`#39, 语文, 英语, 数学, 0, 0)])
+- Exchange hashpartitioning(name#35, 200)
   +- HashAggregate(keys=[name#35], functions=[partial_pivotfirst(subject#36, max(pivot1.`score`) AS `max`#39, 语文, 英语, 数学, 0, 0)])
      +- *(2) HashAggregate(keys=[name#35, subject#36], functions=[max(score#37)])
         +- Exchange hashpartitioning(name#35, subject#36, 200)
            +- *(1) HashAggregate(keys=[name#35, subject#36], functions=[partial_max(score#37)])
               +- *(1) FileScan orc pivot1[name#35,subject#36,score#37] Batched: true, Format: ORC, Location: 

select *
from pivot1 
pivot(max(score) as max for subject in('语文','英语'));
-- 执行结果
name    语文    英语
王五    99      NULL
李四    90      80
张三    95      85

上述sql中按照subject旋转并仅对score进行单次聚合。因此最终生成的新列完全由expression_list中值决定。

在scala api中pivot函数定义在RelationalGroupedDataset类中，该来表示一组用于DataFrame聚合的方法。该类实例由DataSet中groupBy、cube和rollup方法创建。
假如指定A、B、C三个字段进行分组：
cube方法将得到字段全部组合结果进行分组分组，分别得到(A,B,C),(A,B),(A,C),(B,C),(A),(B),©以及全表的分组。
rollup方法将按照从左到右的依次忽略最右侧字段结果进行分组，分别得到(A,B,C),(A,B),(A)以及全表的分组。

使用pivot的前提是先分组，在sql中使用pivot时自动选择为我们选了分组字段。那么自动选择依据是什么？在pivot中没有被选做聚合或旋转的字段将用作分组字段，如果全部字段都参与了pivot则按全局聚合，仅有一条结果。column_list部分即指定的旋转字段，而expression_list用来指定旋转字段中参与计算的值。

因此上述第一个sql中最终的分组字段为name。

2.2. 设置别名

当在expression_list中指定别名后，结果中新列名将为指定的别名。

select name,c,e,m
from pivot1 
pivot(max(score) as max for subject in('语文' as c,'英语' as e,'数学' as m));
-- 执行结果
name    c       e       m
王五    99      NULL    98
李四    90      80      100
张三    95      85      100

2.3. 按多列旋转

当column_list中指定多个字段时，须使用括号，并且expression_list中指定的字段值也需要使用括号，二者括号中内容顺序需要保持一致。

select *
from pivot1 
pivot(max(score) as max for (subject,name) in(('语文','张三'),('语文','李四'),('语文','王五')));
-- 执行结果
[语文, 张三]    [语文, 李四]    [语文, 王五]
95             90             99

== Physical Plan ==
HashAggregate(keys=[], functions=[pivotfirst(__pivot_col#129, max(pivot1.`score`) AS `max`#128, [语文,张三], [语文,李四], [语文,王五], 0, 0)])
+- Exchange SinglePartition
   +- HashAggregate(keys=[], functions=[partial_pivotfirst(__pivot_col#129, max(pivot1.`score`) AS `max`#128, [语文,张三], [语文,李四], [语文,王五], 0, 0)])
      +- *(2) HashAggregate(keys=[named_struct(subject, subject#125, name, name#124)#147], functions=[max(score#126)])
         +- Exchange hashpartitioning(named_struct(subject, subject#125, name, name#124)#147, 200)
            +- *(1) HashAggregate(keys=[named_struct(subject, subject#125, name, name#124) AS named_struct(subject, subject#125, name, name#124)#147], functions=[partial_max(score#126)])
               +- *(1) FileScan orc pivot1[name#124,subject#125,score#126] Batched: true, Format: ORC, Location:

for (subject,name) in(('语文','张三'),('语文','李四'),('语文','王五'))最终决定原表中只有以下数据参与计算

'张三','语文',95
'李四','语文',90
'王五','语文',99

上述sql中根据subject和name进行旋转，并对score字段聚合计算，表中全部字段都参与了旋转和聚合，相当于是对全表进行分组。因此最终结果最有一行。

2.4. 执行多次聚合

select *
from pivot1 
pivot(max(score) as max,avg(score) as avg for subject in('语文','英语','数学'));
-- 执行结果
name    语文_max    语文_avg    英语_max    英语_avg    数学_max    数学_avg
王五     99         99.0       NULL        NULL       98         98.0
李四     90         90.0       80          80.0       100        100.0
张三     95         95.0       85          85.0       100        100.0

== Physical Plan ==
HashAggregate(keys=[name#210], functions=[pivotfirst(subject#211, max(pivot1.`score`) AS `max`#215, 语文, 英语, 数学, 0, 0), pivotfirst(subject#211, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#216, 语文, 英语, 数学, 0, 0)])
+- Exchange hashpartitioning(name#210, 200)
   +- HashAggregate(keys=[name#210], functions=[partial_pivotfirst(subject#211, max(pivot1.`score`) AS `max`#215, 语文, 英语, 数学, 0, 0), partial_pivotfirst(subject#211, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#216, 语文, 英语, 数学, 0, 0)])
      +- *(2) HashAggregate(keys=[name#210, subject#211], functions=[max(score#212), avg(cast(score#212 as bigint))])
         +- Exchange hashpartitioning(name#210, subject#211, 200)
            +- *(1) HashAggregate(keys=[name#210, subject#211], functions=[partial_max(score#212), partial_avg(cast(score#212 as bigint))])
               +- *(1) FileScan orc pivot1[name#210,subject#211,score#212] Batched: true, Format: ORC, Location: 

select *
from pivot1 
pivot(max(score) as max,avg(score) as avg for subject in('语文','英语'));
-- 执行结果
name    语文_max        语文_avg        英语_max        英语_avg
王五     99             99.0           NULL            NULL
李四     90             90.0           80              80.0
张三     95             95.0           85              85.0

上述sql中指定按subject列进行旋转并对socre聚合，将按照原表中剩下的name字段进行分组。由于进行2次聚合，因此最终列为旋转值*聚合结果别名。

继续看如下两个sql的计算结果。

select *
from pivot1 
pivot(max(score) as max,avg(score) as avg,count(subject) as cnt for name in('张三','李四','王五'));
-- 执行结果
张三_max        张三_avg        张三_cnt        李四_max        李四_avg        李四_cnt        王五_max        王五_avg        王五_cnt
100             93.33          3              100             90.0           3              99              98.5           2

== Physical Plan ==
HashAggregate(keys=[], functions=[pivotfirst(name#386, max(pivot1.`score`) AS `max`#392, 张三, 李四, 王五, 0, 0), pivotfirst(name#386, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#393, 张三, 李四, 王五, 0, 0), pivotfirst(name#386, count(pivot1.`subject`) AS `cnt`#394L, 张三, 李四, 王五, 0, 0)])
+- Exchange SinglePartition
   +- HashAggregate(keys=[], functions=[partial_pivotfirst(name#386, max(pivot1.`score`) AS `max`#392, 张三, 李四, 王五, 0, 0), partial_pivotfirst(name#386, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#393, 张三, 李四, 王五, 0, 0), partial_pivotfirst(name#386, count(pivot1.`subject`) AS `cnt`#394L, 张三, 李四, 王五, 0, 0)])
      +- *(2) HashAggregate(keys=[name#386], functions=[max(score#388), avg(cast(score#388 as bigint)), count(subject#387)])
         +- Exchange hashpartitioning(name#386, 200)
            +- *(1) HashAggregate(keys=[name#386], functions=[partial_max(score#388), partial_avg(cast(score#388 as bigint)), partial_count(subject#387)])
               +- *(1) FileScan orc pivot1[name#386,subject#387,score#388] Batched: true, Format: ORC, Location:

表中全部字段参与旋转或聚合，因此按照全表分组，结果只有一条数据。最终列个数为(‘张三’,‘李四’,‘王五’)的长度3*聚合函数个数=9

select *
from pivot1 
pivot(max(score) as max,avg(score) as avg,count(score) as cnt for name in('张三','李四','王五'));
-- 执行结果
subject 张三_max        张三_avg        张三_cnt        李四_max        李四_avg        李四_cnt        王五_max        王五_avg        王五_cnt
英语     85             85.0           1               80             80.0            1              NULL           NULL            NULL
语文     95             95.0           1               90             90.0            1              99             99.0            1
数学     100            100.0          1               100            100.0           1              98             98.0            1

== Physical Plan ==
HashAggregate(keys=[subject#541], functions=[pivotfirst(name#540, max(pivot1.`score`) AS `max`#546, 张三, 李四, 王五, 0, 0), pivotfirst(name#540, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#547, 张三, 李四, 王五, 0, 0), pivotfirst(name#540, count(pivot1.`score`) AS `cnt`#548L, 张三, 李四, 王五, 0, 0)])
+- Exchange hashpartitioning(subject#541, 200)
   +- HashAggregate(keys=[subject#541], functions=[partial_pivotfirst(name#540, max(pivot1.`score`) AS `max`#546, 张三, 李四, 王五, 0, 0), partial_pivotfirst(name#540, avg(CAST(pivot1.`score` AS BIGINT)) AS `avg`#547, 张三, 李四, 王五, 0, 0), partial_pivotfirst(name#540, count(pivot1.`score`) AS `cnt`#548L, 张三, 李四, 王五, 0, 0)])
      +- *(2) HashAggregate(keys=[subject#541, name#540], functions=[max(score#542), avg(cast(score#542 as bigint)), count(score#542)])
         +- Exchange hashpartitioning(subject#541, name#540, 200)
            +- *(1) HashAggregate(keys=[subject#541, name#540], functions=[partial_max(score#542), partial_avg(cast(score#542 as bigint)), partial_count(score#542)])
               +- *(1) FileScan orc pivot1[name#540,subject#541,score#542] Batched: true, Format: ORC, Location: