【大数据面试题】38 说说 Hive 怎么行转列
一步一个脚印，一天一道大数据面试题
博主希望能够得到大家的点赞收藏支持！非常感谢
点赞，收藏是情分，不点是本分。祝你身体健康，事事顺心！
行转列
假设我们有一张名为 sales_data 的表，其中包含 product_id（产品 ID）、category（类别）和 sales_amount（销售金额）这几列的数据。
步骤：
group by id- 聚合函数
sum/max/min，里面套一个if / case when 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 |
+-----------+--------------+-------------+---------------+
我是近未来，祝你变得更强！