行列互轉(zhuǎn)問題

行轉(zhuǎn)列

從數(shù)據(jù)的行拆出數(shù)據(jù)的列，即把觀測的屬性（行）拆成一個變量（列）。
附注
1.其實我一開始覺得這是列轉(zhuǎn)行，因為觀測的屬性由列排布變成了行，但一般都稱這種問題為行轉(zhuǎn)列。
2.我對概念的理解可能有問題。

示例數(shù)據(jù)

https://leetcode.cn/problems/reformat-department-table/

輸出每個部門每個月的薪資，即結(jié)果表格結(jié)構(gòu)為：

允許空值。

錯誤解答

select id,case when month='Jan' then revenue end as Jan_Revenue,case when month='Feb' then revenue end as Feb_Revenue,case when month='Mar' then revenue end as Mar_Revenue,case when month='Apr' then revenue end as Apr_Revenue,case when month='May' then Revenue end as May_Revenue,case when month='Jun' then revenue end as Jun_Revenue,case when month='Jul' then revenue end as Jul_Revenue,case when month='Aug' then revenue end as Aug_Revenue,case when month='Sep' then revenue end as Sep_Revenue,case when month='Oct' then revenue end as Oct_Revenue,case when month='Nov' then revenue end as Nov_Revenue,case when month='Dec' then revenue end as Dec_Revenue
from department;

運行結(jié)果(部分)：

對于每個用戶分別輸出結(jié)果，不符合要求。
考慮合并結(jié)果，一個自然的想法是聚合函數(shù)（注意：sum(NULL)=0）：

select id,sum(case when month='Jan' then revenue end) as Jan_Revenue,
sum(case when month='Feb' then revenue end )as Feb_Revenue,
sum(case when month='Mar' then revenue end) as Mar_Revenue,
sum(case when month='Apr' then revenue end) as Apr_Revenue,
sum(case when month='May' then Revenue end) as May_Revenue,
sum(case when month='Jun' then revenue end) as Jun_Revenue,
sum(case when month='Jul' then revenue end) as Jul_Revenue,
sum(case when month='Aug' then revenue end) as Aug_Revenue,
sum(case when month='Sep' then revenue end) as Sep_Revenue,
sum(case when month='Oct' then revenue end) as Oct_Revenue,
sum(case when month='Nov' then revenue end) as Nov_Revenue,
sum(case when month='Dec' then revenue end) as Dec_Revenue
from department
group by id;

運行結(jié)果：

這一結(jié)果符合要求。
另外的寫法:

select id,sum(if(month='Jan',revenue,null)) as Jan_Revenue,
sum(if(month='Feb',revenue,null))as Feb_Revenue,
sum(if(month='Mar',revenue,null)) as Mar_Revenue,
sum(if(month='Apr',revenue,null)) as Apr_Revenue,
sum(if(month='May',revenue,null)) as May_Revenue,
sum(if(month='Jun',revenue,null)) as Jun_Revenue,
sum(if(month='Jul',revenue,null)) as Jul_Revenue,
sum(if(month='Aug',revenue,null)) as Aug_Revenue,
sum(if(month='Sep',revenue,null)) as Sep_Revenue,
sum(if(month='Oct',revenue,null)) as Oct_Revenue,
sum(if(month='Nov',revenue,null)) as Nov_Revenue,
sum(if(month='Dec',revenue,null)) as Dec_Revenue
from department
group by id;

與case when的寫法思路相同，稍微簡潔一些。

思路總結(jié)

Step1. 用case when或if篩選出將要轉(zhuǎn)為變量的屬性。
Step2.利用分組聚合函數(shù)sum匯總行。
語句框架：

select xx,sum(case when yy='屬性1'then zz end),...
from tb
group by xx;

select xx,sum(if(yy='屬性1',zz,null)),...
from tb
group by xx;

列轉(zhuǎn)行

列轉(zhuǎn)行是行轉(zhuǎn)列的逆過程。
把上述結(jié)果再轉(zhuǎn)回原表格：

#為了方便，把行專列的結(jié)果存成一個臨時表
with tb as(
    select id,sum(if(month='Jan',revenue,null)) as Jan_Revenue,
sum(if(month='Feb',revenue,null))as Feb_Revenue,
sum(if(month='Mar',revenue,null)) as Mar_Revenue,
sum(if(month='Apr',revenue,null)) as Apr_Revenue,
sum(if(month='May',revenue,null)) as May_Revenue,
sum(if(month='Jun',revenue,null)) as Jun_Revenue,
sum(if(month='Jul',revenue,null)) as Jul_Revenue,
sum(if(month='Aug',revenue,null)) as Aug_Revenue,
sum(if(month='Sep',revenue,null)) as Sep_Revenue,
sum(if(month='Oct',revenue,null)) as Oct_Revenue,
sum(if(month='Nov',revenue,null)) as Nov_Revenue,
sum(if(month='Dec',revenue,null)) as Dec_Revenue
from department
group by id)
#列轉(zhuǎn)行
select id,Jan_Revenue as Revenue,'Jan' as month
from tb
union all
select id,Feb_Revenue as Revenue,'Feb' as month
from tb
union all
select id,Mar_Revenue as Revenue,'Mar' as month
from tb
union all
select id,Apr_Revenue as Revenue,'Apr' as month
from tb
union all
select id,May_Revenue as Revenue,'May' as month
from tb
union all
select id,Jun_Revenue as Revenue,'Jun' as month
from tb
union all
select id,Jul_Revenue as Revenue,'Jul' as month
from tb
union all
select id,Aug_Revenue as Revenue,'Aug' as month
from tb
union all
select id,Sep_Revenue as Revenue,'Sep' as month
from tb
union all
select id,Oct_Revenue as Revenue,'Oct' as month
from tb
union all
select id,Nov_Revenue as Revenue,'Nov' as month
from tb
union all
select id,Dec_Revenue as Revenue,'Dec' as month
from tb;

運行結(jié)果：

其中含有很多NULL，不是原來的表，考慮去除。

with tb as(
    select id,sum(if(month='Jan',revenue,null)) as Jan_Revenue,
sum(if(month='Feb',revenue,null))as Feb_Revenue,
sum(if(month='Mar',revenue,null)) as Mar_Revenue,
sum(if(month='Apr',revenue,null)) as Apr_Revenue,
sum(if(month='May',revenue,null)) as May_Revenue,
sum(if(month='Jun',revenue,null)) as Jun_Revenue,
sum(if(month='Jul',revenue,null)) as Jul_Revenue,
sum(if(month='Aug',revenue,null)) as Aug_Revenue,
sum(if(month='Sep',revenue,null)) as Sep_Revenue,
sum(if(month='Oct',revenue,null)) as Oct_Revenue,
sum(if(month='Nov',revenue,null)) as Nov_Revenue,
sum(if(month='Dec',revenue,null)) as Dec_Revenue
from department
group by id)
#列轉(zhuǎn)行
select *
from(
select id,Jan_Revenue as Revenue,'Jan' as month
from tb
union all
select id,Feb_Revenue as Revenue,'Feb' as month
from tb
union all
select id,Mar_Revenue as Revenue,'Mar' as month
from tb
union all
select id,Apr_Revenue as Revenue,'Apr' as month
from tb
union all
select id,May_Revenue as Revenue,'May' as month
from tb
union all
select id,Jun_Revenue as Revenue,'Jun' as month
from tb
union all
select id,Jul_Revenue as Revenue,'Jul' as month
from tb
union all
select id,Aug_Revenue as Revenue,'Aug' as month
from tb
union all
select id,Sep_Revenue as Revenue,'Sep' as month
from tb
union all
select id,Oct_Revenue as Revenue,'Oct' as month
from tb
union all
select id,Nov_Revenue as Revenue,'Nov' as month
from tb
union all
select id,Dec_Revenue as Revenue,'Dec' as month
from tb
)tb2
where revenue is not null;

運行結(jié)果：

返回了原表。

思路總結(jié)

Step1 分別取出各觀測各屬性的數(shù)據(jù)，生成屬性外其它列與屬性列（示例中的month）。
Step1 用union all合并觀測。
語句框架：

select xx,yy as 'name1',屬性1 as 'name2'
from tb
union all
select xx,yy as 'name1',屬性2 as 'name2' 
from tb
union all
...