行转列典型应用

前言

行列转换在数仓里面比较常用，典型的用户是统计销售额度

数据准备

# 建表
create table sales (
`state` varchar(20) comment '国家',
`mouth` varchar(20) comment '月份',
`quantity` int  comment '数量'
);
# 插入测试数据
insert into sales(`state`, `mouth`, `quantity`) values ('CA', 'Jul', 45);
insert into sales(`state`, `mouth`, `quantity`) values ('CA', 'Aug', 50);
insert into sales(`state`, `mouth`, `quantity`) values ('CA', 'Sep', 38);
insert into sales(`state`, `mouth`, `quantity`) values ('OR', 'Jul', 33);
insert into sales(`state`, `mouth`, `quantity`) values ('OR', 'Aug', 36);
insert into sales(`state`, `mouth`, `quantity`) values ('OR', 'Sep', 31);
insert into sales(`state`, `mouth`, `quantity`) values ('WA', 'Jul', 30);
insert into sales(`state`, `mouth`, `quantity`) values ('WA', 'Aug', 42);
insert into sales(`state`, `mouth`, `quantity`) values ('WA', 'Sep', 40);

插入数据详情：

行转列

select s.mouth, 
cast(sum(case s.state when 'CA' then s.quantity else 0 end) as char) as 'CA',
cast(sum(case s.state when 'OR' then s.quantity else 0 end) as char) as 'OR',
cast(sum(case s.state when 'WA' then s.quantity else 0 end) as char) as 'WA'
from sales s group by mouth

转换详情：

列转行

方式一：使用union函数

with tmp as (select s.mouth, 
cast(sum(case s.state when 'CA' then s.quantity else 0 end) as char) as 'CA',
cast(sum(case s.state when 'OR' then s.quantity else 0 end) as char) as 'OR',
cast(sum(case s.state when 'WA' then s.quantity else 0 end) as char) as 'WA'
from sales s group by mouth)
select 'CA' state ,mouth, tmp.ca as quantiy from tmp
union select 'OR' state ,mouth, tmp.OR as quantiy from tmp
union select 'WA' state ,mouth, tmp.WA as quantiy from tmp
order by state, mouth

转换详情：

方式二： insert all into … select 数据插入新表
该方式未在mysql8.0调试通过

# 建表
create table sales2 (
`state` varchar(20) comment '国家',
`mouth` varchar(20) comment '月份',
`quantity` int  comment '数量'
);
# 数据
# mysql 不支持 insert all into
with tmp as (select s.mouth, 
cast(sum(case s.state when 'CA' then s.quantity else 0 end) as char) as 'CA',
cast(sum(case s.state when 'OR' then s.quantity else 0 end) as char) as 'OR',
cast(sum(case s.state when 'WA' then s.quantity else 0 end) as char) as 'WA'
from sales s group by mouth)
insert into sales2(state,mouth,quantity) values ('CA', tmp.mouth, tmp.CA) 
into sales2(state,mouth,quantity) values('OR', tmp.mouth, tmp.OR) 
into sales2(state,mouth,quantity) values('WA', tmp.mouth, tmp.WA) 
select tmp.mouth, tmp.CA, tmp.OR, tmp.WA from tmp
commit; 

典型应用

-多维度统计销售情况

with mytmp as (select s.mouth, 
cast(sum(case s.state when 'CA' then s.quantity else 0 end) as char) as 'CA',
cast(sum(case s.state when 'OR' then s.quantity else 0 end) as char) as 'OR',
cast(sum(case s.state when 'WA' then s.quantity else 0 end) as char) as 'WA'
from sales s group by mouth)
select * ,
(mytmp.CA + mytmp.OR + mytmp.WA) as total from mytmp
union
select 'total' as mouth,
sum(tmp.CA) as 'CA', 
sum(tmp.OR) as 'OR', 
sum(tmp.WA) as 'WA', 
sum(tmp.total) as 'total'
from 
(select *,
(mytmp.CA+ mytmp.OR+ mytmp.WA) as total 
from mytmp)tmp