在数据库里试试这个笔记本更新日期：2018年11月10日Pivot是apachespark1.6中首次引入的一种新的数据帧特性，它允许用户通过将唯一值从一列转换为单独的列来旋转表值表达式。apachespark2.4版本也将这种强大的数据透视功能扩展到了SQL用户。在这个博客中，使用西雅图的温度记录，我们将展示如何使用这个常见的sqlpivot特性来实现复杂的数据转换。用枢轴测量夏季温度今年夏天，西雅图的气温上升到令人不舒服的水平，在7月的9天里，最高温度达到80和90度。日期温度（°F）2018年7月22日862018年7月23日902018年7月24日912018年7月25日922018年7月26日922018年7月27日882018年7月28日852018年7月29日942018年7月30日89假设我们想探索或检查是否有汞含量上升的历史趋势。检查和呈现这些数字的一种直观方法是将月份列为列，然后在一行中显示每年的月平均高点。这样就很容易在水平方向、相邻月份之间以及垂直方向、不同年份之间比较温度。现在我们已经支持sparksql中的PIVOT语法，我们可以通过下面的SQL查询来实现这一点。选择*自(选择年（日期）年、月（日）月、临时来自高温其中日期介于"2015-01-01"和"2018-08-31"之间)支点(铸件（平均温度）十进制（4，1））月(1月1日、2月2日、3月3日、4月4日、5月5日、6月6日，7月7日、8月8日、9月9日、10月10日、11月11日、12月12日))按年份排序说明上述查询将产生如下结果：年简功能电子块宗教硕士四月五月六月七月AUG突击步枪九月十月11月十二月201849.745.85458.670.871.982.879.1无效的无效的无效的无效的201743.746.651.657.36772.178.381.573.861.151.345.6201649.153.656.465.968.873.17679.569.660.65641.9201550.354.557.959.96878.982.67968.563.649.447.1好吧，看来有好年景也有坏年景。2016年似乎是一个能源友好的年份。在SQL中旋转让我们仔细看看这个查询，以了解它是如何工作的。首先，我们需要指定FROM子句，它是pivot的输入，换句话说，将基于它执行透视的表或子查询。在我们的例子中，我们关心的是年份、月份和高温，因此这些是子查询中出现的字段。其次，让我们考虑查询的另一个重要部分PIVOT子句。PIVOT子句的第一个参数是聚合函数和要聚合的列。然后，我们将FOR子子句中的pivot列指定为第二个参数，后跟包含pivot列值的in运算符作为最后一个参数。pivot列是表将围绕其旋转的点，pivot列值将被转置到输出表中的列中。IN子句还允许您为每个pivot值指定别名，从而可以轻松生成更有意义的列名。关于pivot的一个重要思想是，它基于隐式groupbycolumns和pivot列的列表执行分组聚合。隐式group by列是from子句中不出现在任何聚合函数或透视列中的列。在pivot列上指定一对month（年），其中month的值将在pivot列上指定，其中month和year的值将在pivot列上指定。因此，这些聚合值中的每一个都将映射到相应的row year和column month的单元格中。值得注意的是，由于这种隐式groupby，我们需要确保不希望成为pivot输出一部分的任何列都不在from子句中，否则查询将产生不希望的结果。指定多个聚合表达式上面的示例只显示了PIVOT子句中使用的一个聚合表达式，而实际上，如果需要，用户可以指定多个聚合表达式。同样，根据上面的天气数据，我们可以列出6月至9月的最高气温和平均高温。选择*自(选择年（日期）年、月（日）月、临时来自高温其中日期介于"2015-01-01"和"2018-08-31"之间)支点(最大温度（4）平均铸造温度月份（6月6日、7月7日、8月8日、9月9日）)按年份排序说明在多个聚合表达式的情况下，列将是轴列值和聚合表达式的笛卡尔积，名称为。年均俊朱诺马克斯平均7月七月马克斯平均8月最大8月9月平均九月最大值201871.98882.89479.194无效的无效的201772.19678.38781.59473.890201673.193768979.59569.678201578.99282.695799268.581分组列与透视列现在假设我们想在我们的温度趋势探索中包括低温，从这个每日低温表：日期温度（°F）……2018年1月8日592018年2月8日582018年3月8日592018年4月8日582018年5月8日592018年6月8日59……为了将这张表与上一张日高温表合并，我们可以将这两张表合并到"日期"列中。但是，由于我们要使用pivot，它对日期执行分组，所以我们可以简单地使用UNION ALL将两个表连接起来。稍后您将看到，这种方法还为我们提供了更大的灵活性：选择日期、温度、"H"作为标志来自高温全部联合选择日期、温度、"L"作为标志从低温开始现在让我们用新的组合表来尝试透视查询：选择*自(选择年（日期）年、月（日）月、临时、标志`H/L`从(选择日期、温度、"H"作为标志来自高温全部联合选择日期、温度、"L"作为标志从低温开始)其中日期介于"2015-01-01"和"2018-08-31"之间)支点(铸件（平均温度）十进制（4，1））月份（6月6日、7月7日、8月8日、9月9日）)按年份排序说明，`H/L`ASC因此，我们在一个表格中得到了过去4年中每个月的平均最高值和平均最低值。注意，我们需要在pivot查询中包含column标志，否则表达式avg（temp）将基于高温和低温的混合。年H/L公司六月七月AUG突击步枪九月2018H71.982.879.1无效的2018我53.458.558.5无效的2017H72.178.381.573.82017我53.756.35955.62016H73.17679.569.92016我53.957.659.952.92015H78.982.67968.52015我56.459.958.552.5你可能已经注意到，现在我们每年有两排，一排是高温区，另一排是低温区。这是因为我们在pivot输入中又增加了一列flag，它反过来成为除了原始列year之外的另一个隐式分组列。或者，该标志也可以用作轴心列，而不是分组列。现在我们有两个轴列，月份和标志：选择*自(选择年（日期）年、月（日）月、临时、标志从(选择日期、温度、"H"作为标志来自高温全部联合选择日期、温度、"L"作为标志从低温开始)其中日期介于"2015-01-01"和"2018-08-31"之间)支点(铸件（平均温度）十进制（4，1））在（月，标志）中(（6，'H'）JUN\u嗨，（6，'L'）JUN\u lo，（7，"H"）七月嗨，（7，"L"）七月，（8，'H'）奥格嗨，（8，'L'）奥格卢，（9，'H'）九月嗨，（9，'L'）九月九日))按年份排序说明这个查询向我们展示了相同数据的不同布局，一年一行，每月两列。年俊俊嗨罗俊朱尔你好朱尔洛AUG\u嗨罗小月九月嗨9月10日201871.953.482.858.579.158.5无效的无效的201772.153.778.356.381.55973.855.6201673.153.97657.679.557.969.652.9201578.956.482.659.97958.568.552.5下一步是什么要运行此博客中使用的查询示例，请查看随附笔记本中的PivotSQL示例。感谢apachespark社区贡献者的贡献！免费试用Databricks。今天就开始吧