内容简介:下面是西雅图的天气数据表,每行代表一天的天气最高值:如果我们想看下最近几年的天气走势,如果这样一天一行数据,是很难看出趋势来的,最直观的方式是 按照年来分行,然后每一列代表一个月的平均天气,这样一行数据,就可以看到这一年12个月的一个天气走势,下面我们使用 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) 和 聚合语句 的笛卡尔积作为结果的不同列,也就是
聚合列(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最佳实践
以上所述就是小编给大家介绍的《使用 Spark Pivot 处理复杂的数据统计需求》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 微信小程序数据统计和错误统计的实现
- Enovy proxy中的数据统计
- OLAP 分析引擎,基于 Druid 组件数据统计分析
- WebRTC视频数据统计之延时、抖动与丢包
- 使用Spark进行数据统计并将结果转存至MSSQL
- [译]使用 Pandas 对 Kaggle 数据集进行统计数据分析
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Java常用算法手册
2012-5 / 59.00元
《Java常用算法手册》分三篇,共13章,分别介绍了算法基础、算法应用和算法面试题。首先介绍了算法概述,然后重点分析了数据结构和基本算法思想;接着,详细讲解了算法在排序、查找、数学计算、数论、历史趣题、游戏、密码学等领域中的应用;最后,列举了算法的一些常见面试题。书中知识点覆盖全面,结构安排紧凑,讲解详细,实例丰富。全书对每一个知识点都给出了相应的算法及应用实例,虽然这些例子都是以Java语言来编......一起来看看 《Java常用算法手册》 这本书的介绍吧!