Interview

【一】Hive表行列之间相互转换

总结 HiveSQL 中行转列和列转行的常见写法、原理、适用场景

全文共计3480字 次阅读

一、多行转多列（ Pivot / 透视转换）

假设原始表stu_score是这样：

stu_idsubjectscore
1math90
1english80
1chinese70
2math88
2english95

现在希望变成：

stu_idmath_scoreenglish_scorechinese_score
1908070
28895NULL

这就是 行转列。

本质是：

原来 subject 的不同取值是“行里的值”，现在把这些值变成“列名”。多行明细数据变成一行宽表数据

先按主键分组，再用条件判断把不同类别的数据筛到不同列里，最后用聚合函数把多行压成一行。

1.1 模板

1
2
3
4
5
6
7
8
9

GROUP BY 分组字段
+
聚合函数(
    CASE WHEN 条件 THEN 值 END
)
或
GROUP BY 分组字段
+
SUM(IF(条件, 值, 0))

Hive 中行转列通常使用 GROUP BY 配合条件聚合实现。

核心写法是 MAX/SUM/COUNT(CASE WHEN ... THEN ... ELSE ... END)。

  • 如果每个分组下某个类别只有一条记录，一般用 MAX 或 MIN；

  • 如果需要累计金额或次数，就用 SUM；

  • 如果需要统计条件次数，可以用 SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)。

例如按用户把不同事件类型转成行为指标列，可以写 SUM(CASE WHEN event_type='click' THEN 1 ELSE 0 END) AS click_cnt。

如果类别是动态的，Hive 本身不适合直接做完全动态 Pivot，通常通过脚本动态拼 SQL，或者用 map 类型保存动态键值。

1.2 原理

1、Hive 中最常见的行转列写法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

select stu_id,
	max(if(subject='math',score,NULL)) math_score,
    max(if(subject='english',score,null)) english_score,
    max(if(subject='chinese',score,NULL)) chinese_score
from stu_score
group by stu_id;
-- 或
select 
	stu_id,
	max(case when subject='math' then score end) math_score,
	max(case when subject='english' then score end) english_score,
	max(case when subject='chinese' then score end) chinese_score
from stu_score
group by stu_id;

1) 为什么需要 GROUP BY？

因为行转列通常是：同一个 user_id 的多行合并成一行

user_idsubjectscore
1math90
1english80
1chinese70

要变成同一个 user_id 的一行：

user_idmath_scoreenglish_scorechinese_score
1908070

所以必须：GROUP BY user_id

2）为什么需要 CASE WHEN / IF？

因为你要判断：

  • 如果 subject = ‘math’，那这一行的 score 就放到 math_score

  • 如果 subject = ’english’，那这一行的 score 就放到 english_score

  • 如果 subject = ‘chinese’，那这一行的 score 就放到 chinese_score

所以必须：CASE WHEN subject = 'math' THEN score END

1.3 题目

题目1_模板

将一个用户一天内多次消费转为列显示

user_idcategoryamount
1food20
1food30
1game100

即

user_idfood_amountgame_amount
150100
1
2
3
4
5
6

SELECT
    user_id,
    SUM(CASE WHEN category = 'food' THEN amount ELSE 0 END) AS food_amount,
    SUM(CASE WHEN category = 'game' THEN amount ELSE 0 END) AS game_amount
FROM user_consume
GROUP BY user_id;

题目2_加列

原表 user_event：

user_idevent_typeamount
1viewNULL
1clickNULL
1buy100
1buy200
2viewNULL
2clickNULL

要求输出每个用户每种行为的次数和金额：

user_idview_cntclick_cntbuy_cntbuy_amount
1112300
21100
1
2
3
4
5
6
7
8

SELECT
    user_id,
    SUM(CASE WHEevent_typey = 'view' THEN 1 ELSE 0 END) AS view_cnt,
    SUM(CASE WHEevent_typey = 'click' THEN 1 ELSE 0 END) AS click_cnt,
    SUM(CASE WHEevent_typey = 'buy' THEN 1 ELSE 0 END) AS buy_cnt,
    SUM(CASE WHEN event_type = 'buy' THEN amount ELSE 0 END) AS buy_amount
FROM user_consume
GROUP BY user_id;

题目3_汇总统计

原表 orders：

user_idcategoryorder_idamount
1foodo120
1foodo230
1gameo3100
2foodo450
2travelo5500

要求统计每个用户在不同品类下的订单数和消费金额：

user_idfood_order_cntfood_amountgame_order_cntgame_amounttravel_order_cnttravel_amount
1250110000
2150001500
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT 
    user_id,
    COUNT(IF(category='food',1,0)) food_order_cnt,
    SUM(IF(category='food',amount,0)) food_amount,

    COUNT(IF(category='game',1,0)) game_order_cnt,
    SUM(IF(category='game',amount,0)) game_amount,

    COUNT(IF(category='food',1,0)) travel_order_cnt,
    SUM(IF(category='travel',amount,0)) travel_amount
FROM orders
GROUP BY user_id;
-- 或
SELECT
    user_id,
    COUNT(CASE WHEN category = 'food' THEN order_id END) AS food_order_cnt,
    SUM(CASE WHEN category = 'food' THEN amount ELSE 0 END) AS food_amount,

    COUNT(CASE WHEN category = 'game' THEN order_id END) AS game_order_cnt,
    SUM(CASE WHEN category = 'game' THEN amount ELSE 0 END) AS game_amount,

    COUNT(CASE WHEN category = 'travel' THEN order_id END) AS travel_order_cnt,
    SUM(CASE WHEN category = 'travel' THEN amount ELSE 0 END) AS travel_amount
FROM orders
GROUP BY user_id;

题目4_合并统计(不汇总)

假设原始表stu_score是这样：系统记录出现问题，同一个科目出现了两个分数score

stu_idsubjectscore
1math90
1english80
1math70
2math88
2english95

现在希望变成：将相同科目的多个分数合并

stu_idmath_scoreenglish_score
190,7080
28895

思路：行转列 + 同一分组同一科目的多个值合并成一个字符串。也就是说： 不再用 MAX(score) 取一个分数，而是用 collect_list(score) 把多个分数收集起来，再用concat_ws(',', ...) 拼成 “90,70” 这种字符串

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
    stu_id,
    concat_ws(
        ',',
        collect_list(
            CASE WHEN subject = 'math' THEN cast(score AS string) END
        )
    ) AS math_score,
    concat_ws(
        ',',
        collect_list(
            CASE WHEN subject = 'english' THEN cast(score AS string) END
        )
    ) AS english_score
FROM stu_score
GROUP BY stu_id;

concat_ws 拼接字符串数组，所以最好显式写：cast(score AS string)

collect_list 收集出来的顺序在分布式执行中不一定绝对稳定。如果你想去重，用 collect_set

如果像保证顺序，可以加一个sort_arra，但如果转成字符串排序，会按字符串字典序排序，字符串排序可能不是你想要的数值顺序

1
2
3
4
5
6


sort_array(
            collect_list(
                CASE WHEN subject = 'math' THEN cast(score AS string) END
            )
        )

写法2：先聚合再行转列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

WITH subject_score AS (
    SELECT
        stu_id,
        subject,
        concat_ws(',', collect_list(cast(score AS string))) AS score_list
    FROM stu_score
    GROUP BY stu_id, subject
)
SELECT
    stu_id,
    MAX(CASE WHEN subject = 'math' THEN score_list END) AS math_score,
    MAX(CASE WHEN subject = 'english' THEN score_list END) AS english_score
FROM subject_score
GROUP BY stu_id;
对比项写法一：一步到位写法二：先聚合再行转列
聚合次数通常 1 次通常 2 次
Shuffle 次数通常较少可能更多
SQL 长度较短较长
小数据性能可能更好可能略慢
大数据重复多不一定好通常更稳
科目很多CASE WHEN 很多，较重更清晰
内存压力一个 stu_id 下维护多个 list按 stu_id, subject 拆分，通常更稳
可维护性一般更好
数仓生产推荐一般推荐

题目5_动态行转列思路

前面的例子都有一个特点：统计列的种类是提前知道的，比如math、english、chinese ，这种叫 静态行转列

但是如果类别很多，而且不固定，不可能手写几百个

  • 方案1：报表层的字段一般是确定的，不会无限动态变化，通常只取 Top N 类别

  • 方案2：可以不强行转成很多列，而是转成一个 map 字段。用 MAP 保存动态键值。例如：

    user_idtagscore
    1tag_a10
    1tag_b20
    2tag_c30

    变成：

    user_idtag_score_map
    1{“tag_a”:10,“tag_b”:20}
    2{“tag_c”:30}
    1
2
3
4
5
6
7
8
9

    SELECT
    user_id,
    map_from_entries(
        collect_list(
            named_struct('key', tag, 'value', score)
        )
    ) AS tag_score_map
FROM user_tag_score
GROUP BY user_id;

  • 方案3：用脚本动态生成 SQL

    • 先查出所有类别；

    • 用 Python / Shell / 调度系统生成 SQL；

    • 动态拼接 CASE WHEN；

    • 提交 Hive SQL 执行。

二、多列转多行（Unpivot / 反透视转换）

假设原始表 student_score_wide

user_idmath_scoreenglish_scorechinese_score
1908070
28895NULL

转成

user_idsubjectscore
1math90
1english80
1chinese70
2math88
2english95

现在希望变就是 列转行，跟上面讲的恰好相反

本质是：

一行宽表数据拆成多行明细数据

2.1 模板

Hive 中常见有三种写法：

写法特点推荐程度
UNION ALL最容易理解，适合新手高
LATERAL VIEW + explode更像 Hive 风格，适合数组/map 拆行高
stack专门用于列转行，写法简洁高

2.2 原理

1
2
3

math_score -> subject='math', score=90
english_score -> subject='english', score=80
chinese_score -> subject='chinese', score=70

所以列转行的关键是：

  1. 列名转成行值
  2. 列值转成统一字段值
  3. 一行拆多行

Hive 中列转行通常有三种做法：UNION ALL、LATERAL VIEW stack、LATERAL VIEW explode。

最常用的是 stack，它可以把一行中的多组列值拆成多行。

  • 如果列很多但逻辑固定，用 stack 更简洁；

  • 如果要处理数组、map、struct，通常用 explode；

  • 如果是新手或兼容性要求高，也可以用 UNION ALL。

方法1：UNION ALL

手动将所有非主键的列一一拆除，现在有3种枚举值，则写三个SELECT 拆解 english_score，最后将结果UNION ALL （UNION会做去重，可能额外引入开销，而且有时会错误去掉你想保留的重复行）

如果需要排序，可以再套一层SELECT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SELECT
    user_id,
    'math' AS subject,
    math_score AS score
FROM student_score_wide
WHERE math_score IS NOT NULL

UNION ALL

SELECT
    user_id,
    'english' AS subject,
    english_score AS score
FROM student_score_wide
WHERE english_score IS NOT NULL

UNION ALL

SELECT
    user_id,
    'chinese' AS subject,
    chinese_score AS score
FROM student_score_wide
WHERE chinese_score IS NOT NULL;

方法2：LATERAL VIEW stack

stack 行级构造，把一行数据拆成 n 行，每行输出一组 key/value（强类型）。

  • 每一“行”字段数一致

  • n 为key/value对的个数

  • stack 会在编译阶段统一列类型，数据类型必须完全一致，通常需要统一成 string（Hive 会尝试统一类型，失败就直接抛异常ClassCastException: LazyInteger cannot be cast to IntWritable）

基本语法

1

stack(n, key1, value1, key2, value2, ..., keyn, valuen)

示例：

stu_idmath_scoreenglish_scorechinese_score
1908070
28895NULL

这里的key是’math’，value是score，string+int 无法安全转换，触发 SerDe 层类型不匹配，所以是需要显式将score这个int转为string

因为 stack()会把 一行变成多行，属于“行生成函数”，Hive 规定这类函数必须通过 lateral view使用。这里的 stack 和 explode 类似，都是 UDTF，都会把一行拆成多行，都必须配合 lateral view

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    stu_id,
    subject,
    score
FROM interview.stu_score_wide
LATERAL VIEW stack(
    3,
    'math',    cast(math_score as string),
    'english', cast(english_score as string),
    'chinese', cast(chinese_score as string)
) t AS subject, score;

表示一行拆成 3 行：

stu_idsubjectscore
1math90
1english80
1chinese70
2math88
2english95
2chineseNULL

方法3：LATERAL VIEW explode

先把多个列构造成一个数组：

1
2
3
4
5

[
  {subject: 'math', score: math_score},
  {subject: 'english', score: english_score},
  {subject: 'chinese', score: chinese_score}
]

然后 explode 数组，把数组中的每个元素拆成一行。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    stu_id,
    subject_score.subject AS subject,
    subject_score.score AS score
FROM interview.stu_score_wide
LATERAL VIEW explode(array(
    named_struct('subject', 'math', 'score', math_score),
    named_struct('subject', 'english', 'score', english_score),
    named_struct('subject', 'chinese', 'score', chinese_score)
)) tmp AS subject_score;

执行过程对比

UNION ALL：多分支、多扫描、最后 Union

1
2
3
4
5

              ┌─ TableScan -> Filter math    -> Select ┐
              │                                        │
student table ├─ TableScan -> Filter english -> Select ├─ Union -> Output
              │                                        │
              └─ TableScan -> Filter chinese -> Select ┘

stack：单扫描、直接展开、不需要 Union

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

stu_score_wide table
    ↓
TableScan
    ↓
Select 原始列
    ↓
UDTF stack，一行变三行
    ↓
Lateral View Join，拼回 user_id
    ↓
Filter score IS NOT NULL
    ↓
Output

explode：单扫描、先构造复杂类型、再展开

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

student table
    ↓
TableScan
    ↓
Select 原始列
    ↓
构造 array<struct> （较stack多了这步）
    ↓
UDTF explode，一行变多行
    ↓
Lateral View Join，拼回 user_id
    ↓
Filter score IS NOT NULL
    ↓
Output
对比点UNION ALLstackexplode(array(named_struct))
扫描原表可能多次通常一次通常一次
核心算子Union OperatorUDTFOperator + Lateral ViewUDTFOperator + Lateral View
是否构造复杂对象否否/较少是，构造 array/struct
SQL 可读性新手最好懂简洁稍复杂
大表性能通常较差通常较好通常较好
列很多时SQL 很长，计划复杂更适合可用但对象构造更重
处理复杂类型不适合一般最适合
推荐场景学习、少量列、兼容性固定列转行首选array/map/struct 拆行
© 2026 My Learning Notes
使用 Hugo 构建
主题 Stack 由 Jimmy 设计
无法复制，本站文章内容受保护