案例|使用 spark Pivot 处理复杂的数据统计需求

Pivot 算子是 spark 1.6 版本开始引入的，在 spark2.4版本中功能做了增强，还是比较强大的，做过数据清洗ETL工作的都知道，行列转换是一个常见的数据整理需求。spark 中的Pivot 可以根据枢轴点(Pivot Point) 把多行的值归并到一行数据的不同列，这个估计不太好理解，我们下面使用例子说明，看看pivot 这个算子在处理复杂数据时候的威力。

使用Pivot 来统计天气走势

下面是西雅图的天气数据表，每行代表一天的天气最高值：

Date	Temp (°F)
07-22-2018	86
07-23-2018	90
07-24-2018	91
07-25-2018	92
07-26-2018	92
07-27-2018	88
07-28-2018	85
07-29-2018	94
07-30-2018	89

如果我们想看下最近几年的天气走势，如果这样一天一行数据，是很难看出趋势来的，最直观的方式是 按照年来分行，然后每一列代表一个月的平均天气，这样一行数据，就可以看到这一年12个月的一个天气走势，下面我们使用 pivot 来构造这样一个查询结果：

结果如下图：

 

是不是很直观，第一行就代表 2018 年，从1月到12月的平均天气，能看出一年的天气走势。

关键词：spark pivot用法 spark dataframe转置 spark行转列 spark pivot scala spark列转行

这个SQL应该怎么理解

我们来看下这个 sql 是怎么玩的，首先是一个 子查询语句，我们最终关心的是 年份，月份，和最高天气值，所以先使用子查询对原始数据进行处理，从日期里面抽取出来年份和月份。

下面在子查询的结果上使用 pivot 语句，pivot 第一个参数是一个聚合语句，这个代表聚合出来一个月30天的一个平均气温，第二个参数是 FOR month，这个是指定以哪个列为枢轴列，第三个 In 子语句指定我们需要进行行列转换的具体的 枢轴点(Pivot Point)的值，上面的例子中 1到12月份都包含了，而且给了一个别名，如果只指定 1到6月份，结果就如下了：

上面sql语句里面有个特别的点需要注意， 就是聚合的时候有个隐含的维度字段，就是 年份，按理来讲，我们没有写  group-by year， 为啥结果表里面不同年份区分在了不同的行，原因是，FORM 子查询出来的每行有 3个列， year，month，tmp，如果一个列既不出现在进行聚合计算的列中（temp 是聚合计算的列）， 也不作为枢轴列 ， 就会作为聚合的时候一个隐含的维度。我们的例子中算平均值聚合操作的维度是 (year, month)，一个是隐含维度，一个是 枢轴列维度， 这一点一定要注意，如果不需要按照 year 来区分，FORM 查询的时候就不要加上这个列。

指定多个聚合语句

上文中只有一个聚合语句，就是计算平均天气，其实是可以加多个聚合语句的，比如我们需要看到 7，8，9 月份每个月的最大气温和平均气温，就可以用以下SQL语句

 

上文中指定了两个聚合语句，查询后， 枢轴点(Pivot Point)  和 聚合语句 的笛卡尔积作为结果的不同列，也就是  <value>_<aggExpr>， 看下图：

 

聚合列（Grouping Columns）和 枢轴列（Pivot Columns）的不同之处

现在假如我们有西雅图每天的最低温数据，我们需要把最高温和最低温放在同一张表里面看对比着看

Date	Temp (°F)
…	…
08-01-2018	59
08-02-2018	58
08-03-2018	59
08-04-2018	58
08-05-2018	59
08-06-2018	59
…	…

 

我们使用  UNION ALL 把两张表做一个合并：

 

现在使用 pivot 来进行处理：

我们统计了 4年中  7，8，9 月份最低温和最高温的平均值，这里要注意的是，我们把 year 和 一个最低最高的标记（H/L）都作为隐含维度，不然算出来的就是最低最高温度在一起的平均值了。结果如下图：

上面的查询中，我们把最低最高的标记（H/L）也作为了一个隐含维度，group-by 的维度就变成了 （year， H/L, month）, 但是year 和 H/L 体现在了不同行上面，month 体现在了不同的列上面，这就是  聚合列（Grouping Columns）和 枢轴列（Pivot Columns）的不同之处。

再接再厉，我们把 H/L 作为  枢轴列（Pivot Columns） 进行查询：

 

结果的展现方式就和上面的不同了，虽然每个单元格值都是相同的，但是把  H/L 和 month 笛卡尔积作为列，H/L 维度体现在了列上面了：

大家都在看

▼

spark sql源码系列:

是时候学习真正的spark技术了丨从0到1认识 spark sql丨spark sql 源码剖析 PushDownPredicate丨spark sql 源码剖析 OptimizeIn 篇

structured streaming 系列:

structured streaming 原理剖析丨structured streaming 碰上kafka丨structured streaming 是如何搞定乱序时间的

spark streaming 系列:

spark streaming 读取kafka各种姿势详解丨spark streaming流式计算中的困境与解决之道

spark core 系列:

彻底搞懂spark shuffle过程（1）丨彻底搞懂spark shuffle过程（2）丨spark内存管理-Tungsten框架探秘

spark 机器学习系列

关注【spark技术分享】

一起撸spark源码，一起玩spark最佳实践

原文始发于微信公众号（spark技术分享）：案例|使用 spark Pivot 处理复杂的数据统计需求