经典sql题（十三）炸裂对应学生的姓名和成绩

explode 和 posexplode 的区别

1. explode:

   • 用于将数组中的每个元素展开为单独的行。
   • 结果中只包含元素的值，不包含其索引。
   • 如果输入数组有 n 个元素，结果将返回 n 行。

2. posexplode:

   • 用于将数组中的每个元素展开为单独的行，同时提供每个元素的索引。
   • 结果包含两个列：一个是元素的索引，另一个是元素的值。
   • 如果输入数组有 n 个元素，结果将返回 n 行，但每行额外包含一个索引列。

示例对比

假设有一个数组 ['A', 'B', 'C']：

使用 explode

SELECT explode(array['A', 'B', 'C']) AS element;

结果：

element
A
B
C

输出行数为 3（与数组元素数量相同）。

使用 posexplode

SELECT posexplode(array['A', 'B', 'C']) AS (index, element);

结果：

index	element
0	A
1	B
2	C

输出行数仍为 3，但除了元素值外，还附加了索引列。

总结

• 行数: 两者在行数上是相同的（即每个数组元素生成一行）。
• 输出内容: explode 只返回元素，而 posexplode 返回元素及其对应的索引。

1.explode 炸裂

如果需要同时拆分两个不对应的列（例如 student 和 score），可以使用 explode 或 split 结合 LATERAL VIEW，但这种方法不会保持行之间的对应关系，结果将会是笛卡尔积，即每个学生的名字都会与每个分数组合在一起。

示例数据

假设表格如下：

class	student	score
1A	Alice,Bob,Charlie	90,85,80

使用 explode 同时拆分两个列

你可以使用以下 SQL 查询来拆分这两个列：

SELECT class, student_name, student_score
FROM test
LATERAL VIEW explode(split(student, ',')) AS student_name
LATERAL VIEW explode(split(score, ',')) AS student_score;

结果

由于直接使用 explode，结果将会是笛卡尔积，可能如下：

class	student_name	student_score
1A	Alice	90
1A	Alice	85
1A	Alice	80
1A	Bob	90
1A	Bob	85
1A	Bob	80
1A	Charlie	90
1A	Charlie	85
1A	Charlie	80

缺点

• 这种方法会导致所有学生与所有分数组合在一起，生成了一个完全的笛卡尔积。
• 每个学生的名字都会与每个分数配对，而没有保持对应关系。

2.posexplode 炸裂

如果想要通过炸裂函数将两个列（如学生和分数）拆分并对应起来，可以使用 posexplode 对两个列进行操作，确保它们的索引相匹配。以下是如何实现这一点的详细说明。

使用 posexplode 对应两个列

你可以使用以下 SQL 查询将 student 和 score 两列分别拆分，并确保它们的索引相对应。

SELECT class,
       student_name,
       student_score
FROM test
LATERAL VIEW posexplode(split(student, ',')) sn AS student_index, student_name
LATERAL VIEW posexplode(split(score, ',')) sc AS score_index, student_score
WHERE student_index = score_index;

解析

1. split(student, ','):

   • 将 student 列的字符串按 , 拆分为数组，例如 ['Alice', 'Bob', 'Charlie']。

2. posexplode(split(student, ',')):

   • 将拆分后的数组展开成多行，并为每个元素生成一个索引。例如：
     • student_index | student_name
     • 0 | Alice
     • 1 | Bob
     • 2 | Charlie

3. split(score, ','):

   • 类似地将 score 列拆分为数组，例如 ['90', '85', '80']。

4. posexplode(split(score, ',')):

   • 将拆分后的分数数组也展开成多行，并生成索引。例如：

• score_index | student_score
  • 0 | 90
  • 1 | 85
  • 2 | 80

5. WHERE student_index = score_index:
   • 通过匹配两个索引，只保留对应的学生和分数。

结果

根据上述查询，输出将会是：

class	student_name	student_score
1A	Alice	90
1A	Bob	85
1A	Charlie	80

通过这种方式，posexplode 可以有效地将两个列拆分并对应起来，确保每个学生的名字和分数在结果中正确匹配。