使用 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月的平均天气，能看出一年的天气走势。

这个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技术分享】

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