SQL中的透视表 (枢纽表、pivot table)
定义
枢纽表(英语:pivot table)也翻译成透视表,是用来汇总其它表的数据。首先把源表分组(grouping),然后对各组内数据做汇总操作如排序、平均、累加、计数或字符串连接等。透视表用于数据处理,在数据可视化程序如电子表格或商业智能软件中常见。
举例:
- 一张表包含一列数;透视表仅含一行一列为源表该列的均值。
- 源表有两列分别为 “性别“ 与 “身高” ,表的每行给出一个人的性别与高度;透视表有两行两列,在 “性别” 列分别写 “男性” 与 “女性” ,在 “身高” 列分别写对应性别的平均身高。
案例实现
案例1:
写一个查询语句实现对大洲(continent)列的 透视表 操作,使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。
-- 源数据
with student as (
select 'Jack' as name, 'America' as continent
union all
select 'Pascal' as name, 'Europe' as continent
union all
select 'Xi' as name, 'Asia' as continent
union all
select 'Jane' as name, 'America' as continent
)
-- 查询 源表数据
-- select * from student;
-- 输出结果
-- name continent
-- Jack America
-- Xi Asia
-- Pascal Europe
-- Jane America
-- 查询语句
-- 使用max() 是因为 group之后需要使用聚合函数
select max(if(continent = 'America', name, null)) America,
max(if(continent = 'Asia', name, null)) Asia,
max(if(continent = 'Europe', name, null)) Europe
from (select *, row_number() over (partition by continent order by name) rk
from student) t
group by rk;
-- 输出结果
-- america asia europe
-- Jack Xi Pascal
-- Jane NUll NULL
案例2:
-- 实际工作当中遇到这样一个问题,数据如下。我想通过SQL将数据转换成按列展示的形式,即常用的pivot透视操作。
-- # 原始数据
-- id item value
-- 2, b, 21
-- 1, b, 15
-- 2, a, 20
-- 1, a, 10
-- # 次级目标格式(SQL)
-- id item Value
-- 2, item-a, 20
-- 2, item-b, 21
-- 1, item-a, 10
-- 1, item-b, 15
-- # 目标格式
-- id item_a item_b
-- 1, 10, 15
-- 2, 20, 21
-- 查询 源表数据
-- select * from test;
-- 输出结果
-- id item value
-- 1 b 15
-- 2 b 21
-- 1 a 10
-- 2 a 20
-- 查询语句
-- 2)目标格式
select id,
max(if(item_col = 'item-a', value, null)) as item_a,
max(if(item_col = 'item-b', value, null)) as item_b
from (
-- 1) 次级目标格式(SQL)
select id,
concat('item-', item) as item_col,
value
from test
) t1
group by id;
学习链接
维基百科 - 透视表
hive-sql中透视表的实现文章来源:https://www.toymoban.com/news/detail-543636.html
Hive当中实现pivot操作文章来源地址https://www.toymoban.com/news/detail-543636.html
到了这里,关于2. SQL中的透视表 (枢纽表、pivot table)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!