【Hive】 九、 复杂数据类型：ARRAY / MAP / STRUCT 与嵌套展开

文章目录

• 一、三种复合类型总览
• 二、ARRAY
• • • 2.1 建表
    • 2.2 访问
  • 2.3 统计
• 三、Map
• • 3.1 建表
  • 3.2 访问
  • 3.3 统计
• 四、Struct
• • 4.1 建表
  • 4.2 访问
  • 4.3 统计
• 五、嵌套类型访问
• 六、常用辅助函数UDTF（配合复合类型）
• • 6.1 Array相关函数
  • • ① explode(array) 数组展开
    • ② posexplode(array) 带索引展开
    • ③ array_contains(arr, value) 数组包含判断
    • ④ sort_array(arr) 数组排序
  • 6.2 Map映射相关函数
  • • ① explode(map) Map展开
    • ②map_keys(map) map_values(map) 提取Map的键或值
  • 6.3 多个 LATERAL VIEW
  • • ① 基本语法回顾
    • ② 多个 LATERAL VIEW 的笛卡尔积问题
    • ③ 使用 posexplode 按索引对齐
    • • 对齐
      • 对不齐
    • ④ OUTER 关键字的作用
    • ⑤ 复杂场景示例
    • ⑥ 多个 LATERAL VIEW 与 OUTER 的组合
    • ⑦ 实际业务场景示例
    • ⑧ 性能优化建议
  • 6.4 stack
• 七、Spark 3.0+ 的新语法 CROSS JOIN UNNEST（推荐）
• • 7.1 基本概念对比
  • 7.2 基本数组展开
  • 7.3 保留空数组的行 (LEFT JOIN)
  • 7.4 展开多个数组 (按元素对齐)
  • • • 🆚CROSS JOIN UNNEST()与LATERAL VIEW posexplode()对比
      • 处理数组长度不匹配
  • 7.5 带位置索引展开 (WITH ORDINALITY)
  • 7.6 展开 Map 类型
  • 7.7 🔄 高级用法
  • • ① 多层嵌套展开
    • ② 过滤展开后的数据
    • ③ 聚合与展开组合

我的网站原文：
https://eleanora-lyh.github.io/MyLearningNotes/
csdn处的文章会尽快同步更新，欢迎大家来访问！

Hive 除了支持基础数据类型（INT、STRING、DOUBLE 等）之外，还提供了 4 种复合（Complex）数据类型，用于在单个字段中存储结构化/半结构化数据。这在数仓建模中非常常见——比如用户标签、嵌套 JSON、商品多属性等场景。

一、三种复合类型总览

结配合 get_json_object / json_tuple 解析

💡 本质：复合类型让 Hive 能直接承接 JSON、Parquet/ORC 这类嵌套结构的数据，避免拆表带来的 Join 开销。

二、ARRAY

Hive 的一种复杂数据类型，表示“数组/列表”，常见用途是把多个值放到一个字段里，后续用 explode 展开

array(1, 2, 3)表示一个数组[1, 2, 3],它本身还是 一行里的一个字段值，结果是 1 行 1 列：

2.1 建表

CREATE TABLE user_scores (
user_id INT,
scores ARRAY<INT> — 成绩数组
);

2.2 访问

下标从 0 开始，array 常和 explode 搭配

— 1. 访问特定元素（下标从0开始）
SELECT scores[0] AS first_score FROM user_scores;

— 2. 展开数组（行转列）
SELECT user_id, score
FROM user_scores
LATERAL VIEW explode(scores) exploded AS score;

2.3 统计

⚠️ 常见误区：length() 是字符串函数，不能用于 ARRAY/MAP；反之 size() 也不能用于 STRING（会报错或返回意外结果）。

统计数组长度
SELECT size(scores) AS score_count FROM user_scores;

三、Map

3.1 建表

CREATE TABLE user_info (
user_id INT,
info MAP<STRING, STRING> — 用户属性映射
);

3.2 访问

— 1. 访问特定key的值
SELECT info['name'] AS user_name FROM user_info;

— 3. 获取所有keys或values
SELECT map_keys(info) AS all_keys,
map_values(info) AS all_values
FROM user_info;

3.3 统计

⚠️ 常见误区：length() 是字符串函数，不能用于 ARRAY/MAP；反之 size() 也不能用于 STRING（会报错或返回意外结果）。

— 统计Map键值对数量
SELECT size(info) AS info_count FROM user_info;

四、Struct

4.1 建表

CREATE TABLE employees (
id INT,
details STRUCT<name:STRING, age:INT, dept:STRING>
);

4.2 访问

— 1. 访问字段（用点号）
SELECT details.name, details.age FROM employees;

4.3 统计

STRUCT 字段数是建表时固定的，没有运行时的 size 函数。如果一定要拿字段数，可以通过 DESCRIBE 或元数据查询，但实际工程中很少这么做

五、嵌套类型访问

复合类型可以嵌套，比如 ARRAY<STRUCT<…>> 或 MAP<STRING, ARRAY<INT>>：

— 假设有列 orders ARRAY<STRUCT<order_id:INT, amount:DOUBLE>>
SELECT orders[0].amount FROM t; — 取第一个订单的金额

— 假设有列 tag_scores MAP<STRING, ARRAY<INT>>
SELECT tag_scores['vip'][0] FROM t; — 取 vip 标签下数组的第一个元素

这类嵌套结构在解析 JSON / Parquet 时非常常见，掌握后能极大减少展开表的工作量。

六、常用辅助函数UDTF（配合复合类型）

UDTF​ = User-Defined Table-Generating Function（中文：用户定义表生成函数）

核心特性

• 一行输入 → 多行输出：将单行数据展开为多行

• 一对多转换：类似SQL中的LATERAL VIEW+ EXPLODE

• 内置常用UDTF：explode()、posexplode()、inline()等

lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

6.1 Array相关函数

① explode(array) 数组展开

— 基础用法：将数组元素展开为多行
CREATE TABLE user_scores (
user_id INT,
scores ARRAY<INT> — 成绩数组
);

— 插入测试数据
INSERT INTO user_hobbies VALUES
(1, ARRAY(85, 92, 78)),
(2, ARRAY(90, 88));

— 实际场景：分析用户浏览历史
SELECT user_id, score
FROM user_scores
LATERAL VIEW explode(scores) exploded AS score;

数据源

执行步骤：

为什么要用 LATERAL VIEW？

因为 explode()这样的UDTF，输入一行会输出多行，这破坏了SQL中“一行进，一行出”的常规函数规则。LATERAL VIEW就是Hive/Spark SQL为了解决“如何将这种一行变多行的结果，与上下文（原表其他列）正确关联”而引入的语法。

一个常见错误写法（无法运行）：

— 错误！SELECT子句中不能直接使用返回多行的UDTF
SELECT user_id, explode(scores) AS score FROM user_scores;

这个查询会报错，因为它不知道如何将 user_id(1行) 与 explode(scores)(3行) 对齐。LATERAL VIEW就是解决这个对齐问题的标准方式。

② posexplode(array) 带索引展开

和 explode(array) 相比，在炸开时增加了索引position，在合并的结果中可以体现出来，类似分组排序

— 用法：同时返回数组索引和元素
CREATE TABLE user_hobbies (
user_id INT,
hobbies ARRAY<STRING> — 爱好数组
);

— 插入测试数据
INSERT INTO user_hobbies VALUES
(1, ARRAY('reading', 'music', 'sports')),
(2, ARRAY('coding', 'gaming'));

— 查询
SELECT user_id, hobby, position
FROM user_hobbies
LATERAL VIEW posexplode(hobbies) exploded AS position, hobby;

数据源

执行步骤：

③ array_contains(arr, value) 数组包含判断

— 用法：判断数组是否包含特定值
CREATE TABLE products (
product_id INT,
tags ARRAY<STRING> — 商品标签
);

— 插入测试数据
INSERT INTO products VALUES
(1, ARRAY('electronics', 'discount', 'new')),
(2, ARRAY('clothing', 'summer')),
(3, ARRAY('electronics', 'home'));

— 查询：查找所有电子类商品
SELECT product_id, tags
FROM products
WHERE array_contains(tags, 'electronics');

数据源

执行步骤：

• 第二行 (product_id=2, tags=[‘clothing’,‘summer’])：

  • 计算 array_contains(['clothing','summer'], 'electronics')

  • 检查数组 ['clothing','summer']是否包含字符串 'electronics'

  • 结果为 FALSE（不包含）

• 第三行 (product_id=3, tags=[‘electronics’,‘home’])：

  • 计算 array_contains(['electronics','home'], 'electronics')

  • 检查数组 ['electronics','home']是否包含字符串 'electronics'

  • 结果为 TRUE（包含）

④ sort_array(arr) 数组排序

— 用法：对数组元素进行排序
CREATE TABLE student_results (
student_id INT,
scores ARRAY<INT>, — 多次考试成绩
subjects ARRAY<STRING> — 科目列表
);

— 插入测试数据
INSERT INTO student_results VALUES
(1, ARRAY(85, 92, 78, 90), ARRAY('physics', 'math', 'english', 'chemistry')),
(2, ARRAY(88, 75, 95), ARRAY('history', 'geography', 'biology'));

— 查询：对成绩数组进行排序
SELECT
student_id,
scores,
sort_array(scores) AS sorted_scores,
subjects,
sort_array(subjects) AS sorted_subjects
FROM student_results;

数据源

执行步骤：

• 第二行 (student_id=2)：

  • 原始数据：

    • scores = [88, 75, 95]

    • subjects = ['history','geography','biology']

    • 计算 sort_array([88, 75, 95])：

      • 对整数数组进行升序排序

      • 结果：[75, 88, 95]

    • 计算 sort_array(['history','geography','biology'])：

      • 对字符串数组按字典序排序

      • 结果：['biology','geography','history']

6.2 Map映射相关函数

① explode(map) Map展开

— 用法：将Map展开为key-value对
CREATE TABLE student_scores (
student_id INT,
scores MAP<STRING, INT> — 科目-分数映射
);

— 插入测试数据
INSERT INTO student_scores VALUES
(1, MAP('math', 90, 'english', 85, 'physics', 88)),
(2, MAP('math', 92, 'chemistry', 78));

— 查询
SELECT student_id, subject, score
FROM student_scores
LATERAL VIEW explode(scores) exploded AS subject, score;

数据源

执行步骤：

②map_keys(map) map_values(map) 提取Map的键或值

— 用法：提取Map中的所有键或所有值
CREATE TABLE employee_skills (
emp_id INT,
skills MAP<STRING, INT> — 技能:熟练度映射
);

— 插入测试数据
INSERT INTO employee_skills VALUES
(1, MAP('Java', 5, 'Python', 4, 'SQL', 3)),
(2, MAP('JavaScript', 4, 'React', 5));

— 查询1：提取所有技能名称
SELECT
emp_id,
map_keys(skills) AS skill_names
FROM employee_skills;

— 查询2：提取所有熟练度
SELECT
emp_id,
map_values(skills) AS skill_levels
FROM employee_skills;

数据源

执行步骤（查询1 – map_keys）：

• 第二行 (emp_id=2, skills={‘JavaScript’:4,‘React’:5})：

  • 计算 map_keys({'JavaScript':4,'React':5})

  • 提取Map中的所有键

  • 结果：数组 ['JavaScript','React']

执行步骤（查询2 – map_values）：

• 第二行 (emp_id=2, skills={‘JavaScript’:4,‘React’:5})：

  • 计算 map_values({'JavaScript':4,'React':5})

  • 提取Map中的所有值

  • 结果：数组 [4, 5]

6.3 多个 LATERAL VIEW

① 基本语法回顾

CREATE TABLE user_data (
user_id INT,
tags ARRAY<STRING>,
hobbies ARRAY<STRING>
);

— 基本用法
SELECT user_id, tag
FROM user_data
LATERAL VIEW explode(tags) t AS tag;

② 多个 LATERAL VIEW 的笛卡尔积问题

— 创建测试表
CREATE TABLE user_interests (
user_id INT,
tags ARRAY<STRING>, — 标签数组
skills ARRAY<STRING> — 技能数组
);

— 插入测试数据
INSERT INTO user_interests VALUES
(1, ARRAY('student', 'coder'), ARRAY('Java', 'SQL')),
(2, ARRAY('teacher'), ARRAY('Python', 'C++', 'Go'));

— 示例1：多个LATERAL VIEW（可能产生笛卡尔积）
SELECT
user_id,
tag,
skill
FROM user_interests
LATERAL VIEW explode(tags) t1 AS tag
LATERAL VIEW explode(skills) t2 AS skill;

数据源

执行步骤分析：

• 对第二行：

  • 虚拟表t1：tag=‘teacher’

• 对第一行的第二个tag（‘coder’）：

  • 展开skills数组：[‘Java’,‘SQL’]

  • 生成：(‘coder’, ‘Java’), (‘coder’, ‘SQL’)

• 对第二行的tag（‘teacher’）：

  • 展开skills数组：[‘Python’,‘C++’,‘Go’]

  • 生成：(‘teacher’, ‘Python’), (‘teacher’, ‘C++’), (‘teacher’, ‘Go’)

⚠️ 注意：这产生了笛卡尔积！user_id=1 的 2个tags × 2个skills = 4行。这可能不是你想要的效果。

③ 使用 posexplode 按索引对齐

对齐

— 创建表
CREATE TABLE student_subjects (
student_id INT,
subjects ARRAY<STRING>, — 科目
grades ARRAY<INT> — 对应成绩
);

— 插入测试数据
INSERT INTO student_subjects VALUES
(1, ARRAY('数学', '英语', '物理'), ARRAY(90, 85, 88)),
(2, ARRAY('化学', '生物'), ARRAY(92, 78));

— ✅ 正确做法：使用 posexplode 按位置对齐
SELECT
student_id,
subject,
grade
FROM student_subjects
LATERAL VIEW posexplode(subjects) s AS pos1, subject
LATERAL VIEW posexplode(grades) g AS pos2, grade
WHERE pos1 = pos2; — 关键：按相同位置连接

数据源

执行步骤：

1. 使用 posexplode 展开 subjects：

• 第一行：

  • (0, ‘数学’), (1, ‘英语’), (2, ‘物理’)

• 第二行：

  • (0, 90), (0, 85), (0, 88)

2. 使用 posexplode 展开 grades：

• 第一行：

  • (0, ‘化学’), (1, ‘生物’)

• 第二行：

  • (0, 92), (0, 78)

3. 按照笛卡尔积展开所有组合：

3. 按where指定的pos1=pos2进行连接：

• student_id=1，pos1=0 只能连接 pos2=0 → (‘数学’, 90)

• student_id=1，pos1=1 只能连接 pos2=1 → (‘英语’, 85)

• student_id=1，pos1=2 只能连接 pos2=2 → (‘物理’, 88)

• student_id=2，pos1=0 只能连接 pos2=0 → (‘化学’, 92)

• student_id=2，pos1=1 只能连接 pos2=1 → (‘物理’, 78)

最终结果：

✅ 正确：按位置对齐，避免了意外的笛卡尔积。

对不齐

— 使用 posexplode 按位置对齐
SELECT
user_id,
tag,
skill
FROM user_interests
LATERAL VIEW posexplode(tags) t1 AS pos1, tag
LATERAL VIEW posexplode(skills) t2 AS pos2, skill
WHERE pos1 = pos2; — 按相同位置对齐

数据源

执行步骤：

1. 使用 posexplode 展开 tags：

• 第一行：

  • (0, ‘student’), (1, ‘coder’)

• 第二行：

  • (0, ‘teacher’)

2. 使用 posexplode 展开 skills：

• 第一行：

  • (0, ‘Java’), (1, ‘SQL’)

• 第二行：

  • (0, ‘Python’), (1, ‘C++’), (2, ‘Go’)

3. 按位置连接：

• 第一行，pos1=0 只能连接 pos2=0 → (‘student’, ‘Java’)

• 第一行，pos1=1 只能连接 pos2=1 → (‘coder’, ‘SQL’)

• 第二行，pos1=0 只能连接 pos2=0 → (‘teacher’, ‘Python’)

最终结果：

✅ 正确：按位置对齐，避免了意外的笛卡尔积。

④ OUTER 关键字的作用

几乎没有额外性能开销，但会让结果集包含更多行（保留了空数组对应的行）。

— 创建包含空数组的数据
INSERT INTO user_interests VALUES
(3, ARRAY('engineer'), ARRAY()), — skills为空数组
(4, ARRAY(), ARRAY('Java')); — tags为空数组

— 没有OUTER关键字
SELECT
user_id,
tag
FROM user_interests
LATERAL VIEW explode(tags) t AS tag;

数据源

执行结果（没有OUTER）：

user_id=4 的标签数组为空，explode([])不会生成任何行，所以这行数据完全丢失了。

— 使用OUTER关键字
SELECT
user_id,
tag
FROM user_interests
LATERAL VIEW OUTER explode(tags) t AS tag;

执行结果（有OUTER）：

✅ 关键区别：OUTER关键字会保留原始行，即使 UDTF 没有输出任何行，也会用 NULL 填充。

⑤ 复杂场景示例

— 创建电商订单表
CREATE TABLE orders (
order_id STRING,
order_date STRING,
items ARRAY<STRUCT< — 商品项
product_id STRING,
quantity INT,
price DECIMAL(10,2)
>>,
promotions ARRAY<STRING> — 优惠活动
);

— 插入测试数据
INSERT INTO orders VALUES
('O001', '2024-01-01',
ARRAY(
STRUCT('P001', 2, 50.00),
STRUCT('P002', 1, 100.00)
),
ARRAY('NEW_USER', 'FREE_SHIPPING')
),
('O002', '2024-01-01',
ARRAY(
STRUCT('P003', 3, 30.00)
),
ARRAY() — 没有促销
),
('O003', '2024-01-02',
ARRAY(), — 没有商品
ARRAY('DISCOUNT_10')
);

— 示例1：展开订单商品，保留没有商品的订单
SELECT
order_id,
order_date,
item.product_id,
item.quantity,
item.price
FROM orders
LATERAL VIEW OUTER explode(items) t AS item;

数据源：

执行结果：

注意：O003 订单没有商品，但使用了 OUTER，所以被保留下来，商品相关字段为 NULL。

⑥ 多个 LATERAL VIEW 与 OUTER 的组合

— 同时展开商品和促销
SELECT
order_id,
item.product_id,
promotion
FROM orders
LATERAL VIEW OUTER explode(items) t1 AS item
LATERAL VIEW OUTER explode(promotions) t2 AS promotion;

执行过程分析：

⑦ 实际业务场景示例

— 用户行为分析
CREATE TABLE user_events (
user_id INT,
date STRING,
page_views ARRAY<STRING>, — 访问页面
clicks ARRAY<STRUCT< — 点击事件
element_id STRING,
timestamp BIGINT
>>,
sessions ARRAY<STRUCT< — 会话信息
session_id STRING,
duration INT
>>
);

— 复杂的多维度分析
SELECT
user_id,
date,
page_view,
click.element_id AS clicked_element,
click.timestamp AS click_time,
session.session_id,
session.duration
FROM user_events
LATERAL VIEW OUTER explode(page_views) pv AS page_view
LATERAL VIEW OUTER explode(clicks) c AS click
LATERAL VIEW OUTER explode(sessions) s AS session
WHERE date = '2024-01-15';

⑧ 性能优化建议

— ❌ 不推荐的写法：在WHERE前做复杂的展开
SELECT
user_id,
page_view,
click.element_id
FROM user_events
LATERAL VIEW OUTER explode(page_views) pv AS page_view
LATERAL VIEW OUTER explode(clicks) c AS click
WHERE page_view LIKE '%product%'
AND click.timestamp > 1673712000;

— ✅ 推荐的写法：先过滤，再展开
WITH filtered_data AS (
SELECT
user_id,
page_views,
clicks
FROM user_events
WHERE EXISTS (
SELECT 1
FROM UNNEST(page_views) AS pv
WHERE pv LIKE '%product%'
)
)
SELECT
user_id,
page_view,
click.element_id
FROM filtered_data
LATERAL VIEW OUTER explode(page_views) pv AS page_view
LATERAL VIEW OUTER explode(clicks) c AS click
WHERE click.timestamp > 1673712000;

6.4 stack

stack(n, expr1, expr2, …, exprk) 是 Hive 的表生成函数。它的作用是：把后面的表达式按照指定列数切分，展开成 n 行。

示例：

SELECT stack(3, 'math', 90, 'english', 80, 'history', 70) AS (subject, score);

• AS (subject, score)定义了输出有两列。

• N=3表示要生成3行。

• 因此，需要的总参数数量为 3行 * 2列 = 6个。

• 你提供的6个参数 ('math', 90, 'english', 80, 'history', 70)被依次填充：

  • 第1行: ('math', 90)

  • 第2行: ('english', 80)

  • 第3行: ('history', 70)

示例2

SELECT stack(10, 0,1,2,3,4,5,6,7,8,9) AS d;

• AS d定义了输出只有一列（列名为 d）。

• N=10表示要生成10行。

• 因此，需要的总参数数量为 10行 * 1列 = 10个。

• 你提供的10个参数 (0,1,2,3,4,5,6,7,8,9)被依次填充到这一列的10行中。

七、Spark 3.0+ 的新语法 CROSS JOIN UNNEST（推荐）

这是现代SQL（Spark 3.0+、Presto/Trino、BigQuery等）中处理数组的标准方法。

UNNEST 是 SQL（尤其在 Hive、Spark SQL、Flink SQL 等大数据框架中）用于将嵌套的集合类型数据“展开”成多行数据的核心操作。它主要用于处理数组（Array）和映射（Map）类型的数据。

7.1 基本概念对比

首先，我们对比两种语法：

7.2 基本数组展开

— 创建测试表
CREATE TABLE student_grades (
student_id INT,
name STRING,
grades ARRAY<INT>
);

— 插入数据
INSERT INTO student_grades VALUES
(1, '张三', ARRAY(85, 90, 92)),
(2, '李四', ARRAY(78, 82)),
(3, '王五', ARRAY()), — 空数组
(4, '赵六', NULL); — NULL数组

— 展开数组
SELECT
student_id,
name,
grade
FROM student_grades
CROSS JOIN UNNEST(grades) AS t(grade);

数据源

结果：

+———–+——+——-+
| student_id| name | grade |
+———–+——+——-+
| 1 | 张三 | 85 |
| 1 | 张三 | 90 |
| 1 | 张三 | 92 |
| 2 | 李四 | 78 |
| 2 | 李四 | 82 |
+———–+——+——-+

⚠️ 注意：王五和赵六的数据丢失了，因为CROSS JOIN不会保留空数组或NULL数组的行。

7.3 保留空数组的行 (LEFT JOIN)

— 使用 LEFT JOIN 保留空数组
SELECT
student_id,
name,
grade
FROM student_grades
LEFT JOIN UNNEST(grades) AS t(grade) ON true;

结果：

+———–+——+——-+
| student_id| name | grade |
+———–+——+——-+
| 1 | 张三 | 85 |
| 1 | 张三 | 90 |
| 1 | 张三 | 92 |
| 2 | 李四 | 78 |
| 2 | 李四 | 82 |
| 3 | 王五 | NULL | — 空数组，grade为NULL
| 4 | 赵六 | NULL | — NULL数组，grade为NULL
+———–+——+——-+

✅ 关键：LEFT JOIN UNNEST(…) ON true相当于 LATERAL VIEW OUTER explode(…)

7.4 展开多个数组 (按元素对齐)

— 创建表
CREATE TABLE student_subjects (
student_id INT,
subjects ARRAY<STRING>, — 科目
grades ARRAY<INT> — 对应成绩
);

— 插入测试数据
INSERT INTO student_subjects VALUES
(1, ARRAY('数学', '英语', '物理'), ARRAY(90, 85, 88)),
(2, ARRAY('化学', '生物'), ARRAY(92, 78));

— 同时展开多个数组（按位置对齐）
SELECT
student_id,
subject,
grade
FROM student_subjects
CROSS JOIN UNNEST(subjects, grades) AS t(subject, grade);

— ✅ 等价于 posexplode 按位置对齐
SELECT
student_id,
subject,
grade
FROM student_subjects
LATERAL VIEW posexplode(subjects) s AS pos1, subject
LATERAL VIEW posexplode(grades) g AS pos2, grade
WHERE pos1 = pos2; — 关键：按相同位置连接

数据源

执行过程：

UNNEST(subjects, grades) 按位置对齐展开：

student_id=1:
– 第一组：('数学', 90)
– 第二组：('英语', 85)
– 第三组：('物理', 88)

student_id=2:
– 第一组：('化学', 92)
– 第二组：('生物', 78)

结果：
+———–+——–+——-+
| student_id| subject| grade |
+———–+——–+——-+
| 1 | 数学 | 90 |
| 1 | 英语 | 85 |
| 1 | 物理 | 88 |
| 2 | 化学 | 92 |
| 2 | 生物 | 78 |
+———–+——–+——-+

🆚CROSS JOIN UNNEST()与LATERAL VIEW posexplode()对比

语法对比表

处理数组长度不匹配

数据源

使用 CROSS JOIN UNNEST（自动处理）：

— UNNEST 自动处理长度不匹配
INSERT INTO student_subjects VALUES
(3, ARRAY('历史', '地理', '政治'), ARRAY(80, 85)); — 长度不匹配

SELECT
student_id,
subject,
grade
FROM student_subjects
CROSS JOIN UNNEST(subjects, grades) AS t(subject, grade);

结果（自动用NULL填充）：

student_id | subject | grade
———–|———|——
3 | 历史 | 80
3 | 地理 | 85
3 | 政治 | NULL — 自动用NULL填充

使用 LATERAL VIEW posexplode（手动处理）：

— 使用 FULL OUTER JOIN 处理长度不匹配
SELECT
COALESCE(s.student_id, g.student_id) AS student_id,
s.subject,
g.grade
FROM (
SELECT student_id, pos, subject
FROM student_subjects
LATERAL VIEW posexplode(subjects) s AS pos, subject
) s
FULL OUTER JOIN (
SELECT student_id, pos, grade
FROM student_subjects
LATERAL VIEW posexplode(grades) g AS pos, grade
) g ON s.student_id = g.student_id AND s.pos = g.pos;

7.5 带位置索引展开 (WITH ORDINALITY)

— 使用 WITH ORDINALITY 获取元素位置
SELECT
student_id,
pos, — 位置索引（从1开始）
grade
FROM student_grades
CROSS JOIN UNNEST(grades) WITH ORDINALITY AS t(grade, pos);

数据源

结果：

+———–+—-+——-+
| student_id| pos| grade |
+———–+—-+——-+
| 1 | 1 | 85 |
| 1 | 2 | 90 |
| 1 | 3 | 92 |
| 2 | 1 | 78 |
| 2 | 2 | 82 |
+———–+—-+——-+

7.6 展开 Map 类型

— 创建包含Map的表
CREATE TABLE student_scores (
student_id INT,
scores MAP<STRING, INT> — 科目:分数
);

— 插入数据
INSERT INTO student_scores VALUES
(1, MAP('数学', 90, '英语', 85, '物理', 88)),
(2, MAP('化学', 92, '生物', 78));

— 展开Map
SELECT
student_id,
subject,
score
FROM student_scores
CROSS JOIN UNNEST(scores) AS t(subject, score);

数据源

结果：

+———–+——–+——-+
| student_id| subject| score |
+———–+——–+——-+
| 1 | 数学 | 90 |
| 1 | 英语 | 85 |
| 1 | 物理 | 88 |
| 2 | 化学 | 92 |
| 2 | 生物 | 78 |
+———–+——–+——-+

7.7 🔄 高级用法

① 多层嵌套展开

— 复杂数据结构
CREATE TABLE nested_data (
id INT,
data ARRAY<STRUCT<
name STRING,
values ARRAY<INT>
>>
);

— 插入数据
INSERT INTO nested_data VALUES
(1, ARRAY(
STRUCT('A', ARRAY(1, 2, 3)),
STRUCT('B', ARRAY(4, 5))
));

两层展开

SELECT
id,
outer_data.name,
inner_value
FROM nested_data
CROSS JOIN UNNEST(data) AS outer_data
CROSS JOIN UNNEST(outer_data.values) AS inner_value;

等价于：

SELECT
id,
outer_data.name,
inner_value
FROM nested_data,
UNNEST(data) AS outer_data,
UNNEST(outer_data.values) AS inner_value;

② 过滤展开后的数据

— 只展开特定条件的元素
SELECT
student_id,
grade
FROM student_grades
CROSS JOIN UNNEST(grades) AS t(grade)
WHERE grade > 85; — 只保留大于85的成绩

③ 聚合与展开组合

— 计算每个学生的平均分
SELECT
student_id,
name,
AVG(grade) AS avg_grade
FROM student_grades
CROSS JOIN UNNEST(grades) AS t(grade)
GROUP BY student_id, name;