Hive SQL经典面试题之行列转换

伯利恒教堂

已于 2023-01-07 15:33:45 修改

阅读量533

点赞数

分类专栏： hive sql 文章标签： hive sql 大数据面试

于 2023-01-05 11:11:31 首次发布

本文链接：https://blog.csdn.net/m0_46503302/article/details/128520164

版权

hive 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

sql

4 篇文章 0 订阅

订阅专栏

哈喽，小伙伴们，欢迎来到小张的频道，今天给大家讲解一道面试中常见的SQL问题------行列转换问题，希望能帮助到小伙伴们。还需要给大家说明的是这是一个系列性文章，如果您想了解更多面试题型，希望大家多多关注小张哦~

1. 多行转多列

1.1 原始数据r2c1.txt

a,c,1
a,d,2
a,e,3
b,c,4
b,d,5
b,e,6

1.2 建表语句

create table test.row2col1(
col1 string,
col2 string,
col3 int
)
row format delimited fields terminated by ',';

1.3 加载数据

load data local inpath '/opt/datas/hive/r2c1.txt' into table row2col1;

+----------------+----------------+----------------+
| row2col1.col1  | row2col1.col2  | row2col1.col3  |
+----------------+----------------+----------------+
| a              | c              | 1              |
| a              | d              | 2              |
| a              | e              | 3              |
| b              | c              | 4              |
| b              | d              | 5              |
| b              | e              | 6              |
+----------------+----------------+----------------+

1.4 期望结果

+-------+----+----+----+--+
| col1  | c  | d  | e  |
+-------+----+----+----+--+
| a     | 1  | 2  | 3  |
| b     | 4  | 5  | 6  |
+-------+----+----+----+--+

1.5 答案

SELECT 
    col1,
    MAX(CASE WHEN col2  ='c' THEN col3 ELSE 0  END ) c,
    MAX(CASE WHEN col2  ='d' THEN col3 ELSE 0  END ) d,
    MAX(CASE WHEN col2  ='e' THEN col3 ELSE 0  END ) e
FROM row2col1 
    GROUP BY col1;

1.6 执行结果

+-------+----+----+----+
| col1  | c  | d  | e  |
+-------+----+----+----+
| a     | 1  | 2  | 3  |
| b     | 4  | 5  | 6  |
+-------+----+----+----+

2. 多行转单列

2.1 原始数据r2c2.txt

a,b,1
a,b,2
a,b,3
c,d,4
c,d,5
c,d,6

2.2 建表语句

create table test.row2col2(
col1 string,
col2 string,
col3 int
)row format delimited fields terminated by ',';

2.3 加载数据

load data local inpath '/opt/datas/hive/r2c2.txt' into table row2col2;

+----------------+----------------+----------------+
| row2col2.col1  | row2col2.col2  | row2col2.col3  |
+----------------+----------------+----------------+
| a              | b              | 1              |
| a              | b              | 2              |
| a              | b              | 3              |
| c              | d              | 4              |
| c              | d              | 5              |
| c              | d              | 6              |
+----------------+----------------+----------------+

2.4 期望结果

+-------+-------+--------+--+
| col1  | col2  |  col3  |
+-------+-------+--------+--+
| a     | b     | 1,2,3  |
| c     | d     | 4,5,6  |
+-------+-------+--------+--+

2.5 答案

SELECT
    col1,
    col2,
    CONCAT_WS(',',COLLECT_SET(CAST(col3 as string))) col3
FROM 
    row2col2
GROUP BY 
    col1,col2;

2.6 执行结果

+-------+-------+--------+
| col1  | col2  |  col3  |
+-------+-------+--------+
| a     | b     | 1,2,3  |
| c     | d     | 4,5,6  |
+-------+-------+--------+

3. 多列转多行

3.1 原始数据c2r1.txt

a,1,2,3
b,4,5,6

3.2 建表语句

create table test.col2row1(
col1 string,
col2 int,
col3 int,
col4 int
)row format delimited fields terminated by ',';

3.3 加载数据

load data local inpath '/opt/datas/hive/c2r1.txt' into table test.col2row1;

+----------------+----------------+----------------+----------------+
| col2row1.col1  | col2row1.col2  | col2row1.col3  | col2row1.col4  |
+----------------+----------------+----------------+----------------+
| a              | 1              | 2              | 3              |
| b              | 4              | 5              | 6              |
+----------------+----------------+----------------+----------------+

3.4 期望结果

+-----------+-----------+-----------+--+
| col1      |    col2   |    col3   |
+-----------+-----------+-----------+--+
| a         | c         | 1         |
| b         | c         | 4         |
| a         | d         | 2         |
| b         | d         | 5         |
| a         | e         | 3         |
| b         | e         | 6         |
+-----------+-----------+-----------+--+

3.5 答案

SELECT 
    col1,
    col2,
    col3
FROM (
      SELECT col1,'c' as col2,col2 as col3 FROM col2row1 UNION 
      SELECT col1,'d' as col2,col3 as col3 FROM col2row1 UNION 
      SELECT col1,'e' as col2,col4 as col3 FROM col2row1
      ) t1
ORDER BY 
    col2,col1;

3.6 执行结果

+-------+-------+-------+
| col1  | col2  | col3  |
+-------+-------+-------+
| a     | c     | 1     |
| b     | c     | 4     |
| a     | d     | 2     |
| b     | d     | 5     |
| a     | e     | 3     |
| b     | e     | 6     |
+-------+-------+-------+

4. 单列转多行

4.1 原始数据c2r2.txt

a b 1,2,3
c d 4,5,6

4.2 建表语句

create table test.col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by ' ';

4.3 加载数据

load data local inpath '/opt/datas/hive/c2r2.txt' into table test.col2row2;

+----------------+----------------+----------------+
| col2row2.col1  | col2row2.col2  | col2row2.col3  |
+----------------+----------------+----------------+
| a              | b              | 1,2,3          |
| c              | d              | 4,5,6          |
+----------------+----------------+----------------+

4.4 期望结果

+-------+-------+-------+--+
| col1  | col2  | col3  |
+-------+-------+-------+--+
| a     | b     | 1     |
| a     | b     | 2     |
| a     | b     | 3     |
| c     | d     | 4     |
| c     | d     | 5     |
| c     | d     | 6     |
+-------+-------+-------+--+

4.5 答案

SELECT
    a.col1 col1,
    a.col2 col2,
    b.col3 col3
FROM 
    col2row2 a 
lateral view 
    explode(split(col3,',')) b as col3;

4.6 执行结果

+-------+-------+-------+
| col1  | col2  | col3  |
+-------+-------+-------+
| a     | b     | 1     |
| a     | b     | 2     |
| a     | b     | 3     |
| c     | d     | 4     |
| c     | d     | 5     |
| c     | d     | 6     |
+-------+-------+-------+