SQL面试题之按照指定顺序进行多行转一列

0 需求描述

user_name course score
user1 语文 89
user1 数学 92
user1 英语 87
user2 语文 96
user2 数学 84
user2 英语 99
1.将course, score多行转一列
用户 课程 分数
user1 语文，数学，英语 89，92，87
user2 语文，数学，英语 89，92，87

1 数据准备

create table test01 as 
select 'user1' as user_name,'语文' as course,89 as score
union all
select 'user1' as user_name,'数学' as course,92 as score
union all
select 'user1' as user_name,'英语' as course,87 as score
union all
select 'user2' as user_name,'语文' as course,96 as score
union all
select 'user2' as user_name,'数学' as course,84 as score
union all
select 'user2' as user_name,'英语' as course,99 as score

2 数据分析

这题初看，很简单，就是简单的多行转一行，用concat_ws(',',collect_list())就能快速解决，但是这样的答案面试官貌似不是很满意，事实上本题是有要求的，需要按照语文、数学、英语这三个顺序进行多行转一列，顺序是固定的。用过collect_list()函数的同学都知道，该函数，一般的结果是顺序不固定的，因此本题的问题点在于如何按照指定顺序进行多行转一行，并且字段对应的值还能对应相一致。

   本题的问题可以得到这样转化，collect_list()函数可以用于窗口函数中，如果我们在做转换的时候指定order by后的顺序，并指定窗口大小为分区全量窗口，那么问题是不是就能得到解决，同样由于语文、数学、英语是字符串，我们直接进行排序得不到想要的答案，所以在进行排序的时候我们可以通过case when进行转换。SQL如下

select user_name
     , collect_list(score) over (partition by user_name
    order by case
                 when course = '语文' then 1
                 when course = '数学' then 2
                 when course = '英语' then 3 end
    rows between unbounded preceding and unbounded following) a
     , collect_list(course) over (partition by user_name
    order by case
                 when course = '语文' then 1
                 when course = '数学' then 2
                 when course = '英语' then 3 end
    rows between unbounded preceding and unbounded following) b
from test01

   

 注意这里的rows between unbounded preceding and unbounded following就表示全量窗口。

根据上面的结果，我们进行去重就可以得到想要的答案，最终SQL如下：

select user_name
     , concat_ws(',', max(b)) as course
     , concat_ws(',', max(a)) as score
from (select user_name
           , collect_list(score) over (partition by user_name
        order by case
                     when course = '语文' then 1
                     when course = '数学' then 2
                     when course = '英语' then 3 end
        rows between unbounded preceding and unbounded following) a
           , collect_list(course) over (partition by user_name
        order by case
                     when course = '语文' then 1
                     when course = '数学' then 2
                     when course = '英语' then 3 end
        rows between unbounded preceding and unbounded following) b
      from test01
     ) t
group by user_name

 2 小结

 本文总结了一种按照指定顺序进行多行转一列的方法，本质上还是对窗口函数的应用。