【大数据面试题】38 说说 Hive 怎么行转列

一步一个脚印，一天一道大数据面试题

博主希望能够得到大家的点赞收藏支持！非常感谢
点赞，收藏是情分，不点是本分。祝你身体健康，事事顺心！

行转列

假设我们有一张名为 sales_data 的表，其中包含 product_id（产品 ID）、category（类别）和 sales_amount（销售金额）这几列的数据。

步骤：

1. group by id
2. 聚合函数sum/max/min，里面套一个 if / case when
3. as 列名

样例数据：

-- 样例 SQL SELECT * FROM students; +-----------+------------+-------------+ | stu_id    | subject    | score       | +-----------+------------+-------------+ | 1         | Chinese    | 80          | | 1         | Math       | 70          | | 1         | English    | 75          | | 2         | Chinese    | 77          | | 2         | Math       | 60          | | 2         | English    | 80          | +-----------+------------+-------------+ 

开始行转列：

SELECT stu_id,        SUM(IF(subject = 'Chinese', score, 0) AS chinese_score),        SUM(IF(subject = 'Math', score, 0) AS math_score),        SUM(IF(subject = 'English', score, 0) AS english_score) FROM students GROUP BY stu_id;  +-----------+--------------+-------------+---------------+ | stu_id    | chinese_score| math_score  | english_score | +-----------+--------------+-------------+---------------+ | 1         | 80           | 70          | 75            | | 2         | 77           | 60          | 80            | +-----------+--------------+-------------+---------------+ 

我是近未来，祝你变得更强！