hive列转行--行转列问题

列转行--行转列问题

 

问题：使用case when实现行转列

     解决：  www.2cto.com  

         1、测试准备：

              CREATE  TABLE StudentScores(

                      UserName NVARCHAR2(20), --学生姓名

                      Subject   NVARCHAR2(30), --科目

                          Score     FLOAT         --成绩

               );

         2、准备数据：

               INSERT INTO StudentScores values ('Nick', '语文', 80);

               INSERT INTO StudentScores values ('Nick', '数学', 90);

               INSERT INTO StudentScores values ('Nick', '英语', 70);

               INSERT INTO StudentScores values ('Nick', '生物', 85);

               INSERT INTO StudentScores values ('Kent', '语文', 80);

               INSERT INTO StudentScores values ('Kent', '数学', 90);

               INSERT INTO StudentScores values ('Kent', '英语', 70);

               INSERT INTO StudentScores values ('Kent', '生物', 85);

               commit;

            目前的数据形式为：  www.2cto.com  

                   

   

            目标的数据形式：

                       

                       

             3、实现方式：

                  SELECT

                             UserName,

                             MAX(CASE WHEN Subject='语文' THEN Score ELSE 0 END) AS "语文",

                             MAX(CASE WHEN Subject='数学' THEN Score ELSE 0 END) AS "数学",

                             MAX(CASE WHEN Subject='英语' THEN Score ELSE 0 END) AS "英语",

                             MAX(CASE WHEN Subject='生物' THEN Score ELSE 0 END) AS "生物"

                 FROM StudentScores

                 GROUP BY UserName;

     讨论：

         实现上述需求的关键在于，多个case when与group by的配合使用；下面我们来

         分析下sql的执行过程：

                 我们手下看下select子句，可以确定要查询的结果集中有5列，username、语文、数学、英  www.2cto.com  

                 语、生物；拿出表中第一条记录多数据处理得出的结果集：

                     Username          语文     数学   英语     生物

                            Nick              80            0          0            0           

                 80是由CASE WHEN Subject='语文' THEN Score ELSE 0 END得出，其他的0分别是由CASE WHEN

                    Subject='数学' THEN Score ELSE 0 END、CASE WHEN Subject='英语' THEN Score ELSE 0 END、

                    CASE WHEN Subject='生物' THEN Score ELSE 0 END得出，一次类推，得到的结果集为

                     Username          语文     数学   英语     生物

                          Nick               80       0       0         0

                          Nike               0        90      0         0

                           Nike               0        0       70        0

                           Nike                 0        0        0         85

                           Kent                 80      0        0         0

                           Kent                 0        90      0         0

                           Kent                  0        0       70        0

                           Kent                 0        0       0         85

         下一步，聚合分组，最终完成任务。

 

        令一种实现方式：

             SELECT

                   UserName,

                   sum(decode(subject,'语文',score,0)) AS "语文",

                   sum(decode(subject,'数学',score,0)) AS "数学",

                   sum(decode(subject,'英语',score,0)) AS "英语",

                   sum(decode(subject,'生物',score,0)) AS "生物"

             FROM StudentScores  www.2cto.com  

             GROUP BY UserName;

 

     问题：实现列转行

      解决：

         1、准备数据：

                 create table ScoresStudent as SELECT

                           UserName,

                           sum(decode(subject,'语文',score,0)) AS "语文",

                           sum(decode(subject,'数学',score,0)) AS "数学",

                            sum(decode(subject,'英语',score,0)) AS "英语",

                           sum(decode(subject,'生物',score,0)) AS "生物"

                 FROM StudentScores

                   GROUP BY UserName;

                 目前的数据形式：

                     

 

                  目标数据形式：

                       
                   

        

             2、实现方式：

                     select username,'语文' as subject,"语文" as score from ScoresStudent  www.2cto.com  

                     union

                     select username,'数学' as subject,"数学" as score from ScoresStudent

                     union

                     select username,'英语' as subject,"英语" as score from ScoresStudent

                     union

                     select username,'生物' as subject,"生物" as score from ScoresStudent;