hive sql面试题：行转列和列转行

1. 行转列

建表语句：

CREATE TABLE `student_x` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL
)

插入数据： 

INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (1, '张三', '数学', 78);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (2, '张三', '英语', 93);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (3, '张三', '语文', 65);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (4, '李四', '数学', 87);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (5, '李四', '英语', 90);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (6, '李四', '语文', 76);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (7, '李四', '历史', 69);

sql：

select
    name,
    sum(if(`class` = '语文', score, 0)) as chinese_score,
    sum(if(`class` = '数学', score, 0)) as math_score,
    sum(if(`class` = '英语', score, 0)) as engilsh_score,
    sum(if(`class` = '历史', score, 0)) as history_score
from 
    student_x
group by 
    name

2. 列转行

 建表语句：

CREATE TABLE `student_y` (
  `id` int(11) DEFAULT NULL, 
  `name` varchar(255) DEFAULT NULL,
  `math_score` bigint(255) DEFAULT NULL,
  `engilsh_score` bigint(255) DEFAULT NULL,
  `chinese_score` bigint(255) DEFAULT NULL,
  `history_score` bigint(255) DEFAULT NULL
)

插入数据： 

INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (1, '张三', 78, 93, 65, NULL);
INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (2, '李四', 87, 90, 76, 69);

sql：

select
    name,
    class,
    case
        when class = '语文' then chinese_score
        when class = '数学' then math_score
        when class = '英语' then engilsh_score
        when class = '历史' then history_score
    end as score
from student_y lateral view explode(Array('语文', '数学', '英语', '历史')) tbl as class

总结：

• 行转列：行数减少用 group by
• 列转行：行数增加用 lateral view explode

参考：MySQL中的行转列和列转行 - 墨天轮