【Hive SQL 每日一题】行列转换

本文涉及的产品
实时计算 Flink 版,5000CU*H 3个月
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
大数据开发治理平台 DataWorks,不限时长
简介: 该文介绍了如何使用SQL进行数据的行列转换。首先展示了行转列的例子,通过创建一个学生成绩表,利用`IF`和`SUM`函数按学生ID分组,将每个学生的各科成绩转换为独立列。然后,文章讲述了列转行的需求,利用`LATERAL VIEW`和`POSEXPLODE`将已转换的表格恢复为原始行格式,通过索引匹配过滤笛卡尔积避免错误结果。此外,还提到了使用`UNION ALL`的另一种列转行方法。

@[toc]

行转列

测试数据:

DROP TABLE IF EXISTS student_scores;

CREATE TABLE student_scores (
    student_id INT,
    subject STRING,
    score INT
);


INSERT INTO student_scores (student_id, subject, score) VALUES
(1, 'Math', 85),
(1, 'English', 78),
(1, 'Science', 92),
(2, 'Math', 88),
(2, 'English', 76),
(2, 'Science', 81),
(3, 'Math', 90),
(3, 'English', 82),
(3, 'Science', 89);

表的结构以及数据展示如下:

student_id subject score
1 Math 85
1 English 78
1 Science 92
2 Math 88
2 English 76
2 Science 81
3 Math 90
3 English 82
3 Science 89

根据上面的学生成绩表,将其中的行转换成列进行展示,如下所示:

student_id math english science
1 85 78 92
2 88 76 81
... ... ... ...

这个需求主要从两个方面切入:

  • 因为是统计每名学生的成绩,所以按学生进行分组。

  • 行转列操作,其实就是将行数据通过列的方式进行查询展示而已,这里将行转为列的数据共有 3 列,分别代表每名同学各科的成绩,我们只需要在统计时加入判断条件即可,每列固定求某科的成绩,如果不是该科则用 0 或者空值替代,这样就可以轻松完成需求啦。

select
  student_id,
  sum(if(subject="Math",score,0)) math,
  sum(if(subject="English",score,0)) english,
  sum(if(subject="Science",score,0)) science
from
    student_scores
group by
    student_id;

输出结果如下:

image.png

列传行

现在变换一下需求,将学生成绩表中的数据列转换为行,测试数据:

DROP TABLE IF EXISTS student_scores_pivoted;
CREATE TABLE student_scores_pivoted (
    student_id INT,
    math INT,
    english INT,
    science INT
);

INSERT INTO student_scores_pivoted VALUES
(1, 85, 78, 92),
(2, 88, 76, 81),
(3, 90, 82, 89);

表的结构以及数据展示如下:

student_id Math English Science
1 85 78 92
2 88 76 81
3 90 82 89

我们需要将其转换为如下结构:

student_id subject score
1 Math 85
1 English 78
1 Science 92
2 Math 88
2 English 76
2 Science 81
3 Math 90
3 English 82
3 Science 89

这里使用到了 lateral view + posexplode 的方式,将表的一列扩展到多行,从而完成列转行的需求。

相较于传统的 lateral view + explode 扩展方式,posexplode 会返回两个参数,其中第一个参数表示索引,第二个参数才是其对应的值。

select
    student_id,
    subject_name_list,
    subject_list
from
    (select
        student_id,
        concat_ws(',',"Math","English","Science") subject_name,
        concat_ws(',',cast(math as string),cast(english as string),cast(science as string)) subject
    from
        student_scores_pivoted)t1 
    lateral view posexplode(split(subject,",")) tmp1 as pos1,subject_list
    lateral view posexplode(split(subject_name,",")) tmp2 as pos2,subject_name_list
where
    tmp1.pos1 = tmp2.pos2;

输出结果如下:

image.png

explodeposexplode 的区别:

-- explode 主要用于将一个包含多个元素的列转换为多行,每行对应一个元素。
SELECT explode(array(1, 2, 3));
-- 结果为:
1
2
3

-- posexplode 与 explode 类似,但它不仅返回数组中的值,还返回值在数组中的位置(索引)。
SELECT posexplode(array(1, 2, 3));
-- 结果为:
0  1
1  2
2  3

那么这里为什么使用 posexplode 而不是 explode 呢?

如果在这里使用 explode,那么会导致扩张多次(因为在这里使用了两次 explode3*3 最终会将每行扩张 9 次,形成笛卡尔积),变成如下所示的结果:

image.png

所以在这里并不使用 explode,推荐使用另一个函数 posexplode,虽然它也会导致笛卡尔积,但可以根据索引设置条件进行过滤:

image.png

下面将来讲述这些笛卡尔积数据产生的原因,以及过滤条件该如何设置。

在只使用一个扩展函数时,并不会产生笛卡尔积,如下所示:

image.png

如果同时使用两个扩展函数,那么就会产生笛卡尔积,会随着后续每行的数据量成倍数增长,如下所示:

image.png

在使用 posexplode 函数形成笛卡尔积后,我们可以通过设置 where 条件来进行过滤,取到对应的数据。

通过观察可以发现,只有当两个索引列的值相同时,其扩展的数据行才是正确的,我们可以通过这一特性来对数据进行过滤,获取最终的结果:

image.png

其实,列转行还有其它的写法,这里提供另一种更容易理解的思路:

  • 先通过子查询获取单科的成绩;

  • 然后再进行合并。

如下所示:

select
    student_id,
    "math" subject_name,
    math score
from
    student_scores_pivoted
union all
select
    student_id,
    "english" subject_name,
    english score
from
    student_scores_pivoted
union all
select
    student_id,
    "science" subject_name,
    science score
from
    student_scores_pivoted;

输出结果如下:

image.png

解决问题的方式有许多种,但往往我们需要去注重学习解决问题的思路,希望本文对你有所帮助。

相关文章
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。
|
21天前
|
SQL HIVE
【Hive SQL 每日一题】统计用户连续下单的日期区间
该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。
|
22天前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
22天前
|
SQL 关系型数据库 HIVE
【Hive SQL 每日一题】统计最近1天/7天/30天商品的销量
这段内容是关于SQL查询的示例,目标是统计`sales`表中最近1天、7天和30天的商品销量和销售次数。表结构包含`id`、`product_id`、`quantity`和`sale_date`字段。初始查询方法通过三个独立的子查询完成,但效率较低。优化后的查询使用了`lateral view explode`将数据炸裂,通过一次查询同时获取所有所需时间段的数据,提高了效率。示例中展示了优化前后的SQL代码及结果对比。
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量
创建了一个名为`sales`的测试表,包含`user_id`、`product_id`、`quantity`和`sale_date`字段,插入了多条销售数据。需求是找出最近7天内连续下单3天的用户数量。SQL查询通过分组和窗口函数`row_number()`检查日期连续性,最终计算满足条件的唯一用户数。示例结果显示有3名用户符合条件。
|
22天前
|
SQL BI HIVE
【Hive SQL 每日一题】统计用户留存率
用户留存率是衡量产品成功的关键指标,表示用户在特定时间内持续使用产品的比例。计算公式为留存用户数除以初始用户数。例如,游戏发行后第一天有10000玩家,第七天剩5000人,第一周留存率为50%。提供的SQL代码展示了如何根据用户活动数据统计每天的留存率。需求包括计算系统上线后的每日留存率,以及从第一天开始的累计N日留存率。通过窗口函数`LAG`和`COUNT(DISTINCT user_id)`,可以有效地分析用户留存趋势。
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】分组排名取值
创建了一个名为`sales_data`的测试表,包含商品ID、销售额和销售日期。展示了部分示例数据。接着,提供了三个SQL查询:1) 查找每个商品销售额最高的记录;2) 获取每个商品最近和最远的销售记录;3) 求每个商品距今第二近的销售记录。每个查询都利用了窗口函数来处理数据,并给出了相应的查询结果图。
|
22天前
|
SQL 数据挖掘 HIVE
【Hive SQL 每日一题】在线课程学生行为数据分析
该数据分析师任务是分析在线学习平台的学生行为,以优化课程内容和学习体验。提供的数据包括`students`表(含学生ID、姓名、年龄和性别)和`course_activity`表(含活动ID、学生ID、课程ID、活动日期和学习时长)。分析涉及:1) 学生参加的课程数量,2) 课程总学习时长,3) 按性别分组的平均学习时长,4) 学生首次参加的课程及日期,5) 学生最近一次学习的时长,以及6) 参与学生最多的课程。所有查询都使用了SQL,部分涉及窗口函数和分组统计。数据集可在给定链接下载。
|
SQL 存储 Oracle
通过sql做数据透视表,数据库表行列转换(pivot和Unpivot用法)(一)
在mssql中大家都知道可以使用pivot来统计数据,实现像excel的透视表功能 一、MSsqlserver中我们通常的用法
339 0
|
SQL Go 数据库
SQL Server中行列转换 Pivot UnPivot
原文:SQL Server中行列转换 Pivot UnPivot PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法: table_s...
1617 0

热门文章

最新文章