行列转换（一）• MySQL版

1.准备操作

先建一个表

create table table_grade(

id int,

user_name varchar(20),

course varchar(10),

score decimal(5,2)

);

练习 三种插入

insert into table_grade(id,user_name,course,score) values (1,'张龙','语文','78'),(2,'张龙','数学','95'),(3,'张龙','英语','81');

insert into table_grade values(4,'赵虎','语文','97'),(5,'赵虎','数学','78'),(6,'赵虎','英语','91');

insert into table_grade set id = 7,user_name = '王五',course = '语文',score = '81';

insert into table_grade set id = 8,user_name = '王五',course = '数学',score = '55';

insert into table_grade set id = 9,user_name = '王五',course = '英语',score = '75';

insert into table_grade values(10,'马六','语文','87'),(11,'马六','数学','65'),(12,'马六','英语','75');

修改列类型

alter table table_grade modify score decimal; -- decimal默认10位整数

2.行转列

2.1为何进行行转列？

现实中用到的一些数据是明细数据/流水账

用户名 商品 购买时间

A 手机 4.20

A U盘 4.21

B …

B …

如上，展示的就是明细数据，每个用户有多条商品的购买记录，这种明细数据称为【多用户集】，往往我们想换一种展示的方式，因为每个用户有多个数据，想把这种多用户的数据变成单用户数据，即【单用户集】，如下

用户名 最近一次购买商品 最近两次购买商品 A U盘 手机

B … …

显然，单用户集展示的信息更多一点，对于上面的多用户集，如果一个用户拥有太多条信息，就会看着很乱，所以需要变成单用户集

单用户集有个问题，就是列需要不断的增加，存储方面的开销会大一点，因为需要给它分配很多列的空间

明细数据经常以如上所示的纵表的方式，所以又称为【纵表】，因为它的数据是以堆叠的方式显示的(一个用户有多条记录)，即stack方式显示；单用户集是以列的方式显示的，又称为【横表】，所以将多用户集变成单用户集的方式又称为【行转列】或【unstack】过程

行转列后的单用户集数据，更适合做数据分析，如上面的单用户集，可以很直观的看到最近一次或最近两次的购买数据，而如果是看上面的多用户集，还得根据时间去比较，才可以看到一些隐藏信息

列转行，就是将单用户集再转回多用户集

2.1 行转列有两个意思：1.表内的行转列 2.跨表的行转列

跨表就是假设除了购买商品的流水，还有其他的一些流水，也是与用户ID一起记录的，比如用户的积分表，也可以对上面的单用户集进行关联，使单用户集的列增多。经常将跨表的行转列，列变多的过程，称为拉宽的过程

称上面那种购物明细表为【事实表】，多个事实表通过键关联，就会拉宽字段(维度+指标)

update table_grade set id = 4 where user_name = '马六'

alter table table_grade add oid varchar(10)

alter table table_grade modify oid varchar(50)

update table_grade set oid = uuid();  -- 设置一个代理键

alter table table_grade rename column id to user_id -- 修改某列名字

alter table table_grade modify oid varchar(50) first -- 修改某列位置

show columns from table_grade;   -- 查看表各列

show keys from table_grade;  -- 查看索引

3.行转列的思路：行变少，列变多

3.1 如何进行行转列：增加字段，进行聚合(行变少)

通过聚合将多用户变成单用户,使用哪一个聚合函数与数据有关

使用max()则选取最大的一条

现在的表如下所示

张龙 语文 78

张龙 数学 95

张龙 英语 81

我们想显示成：

语文 数学 英语

张龙 78 95 81

这样的话,张龙语文只能有一个成绩,若有多个成绩

** 思路1：用列表把多个成绩放在一起 **

语文 数学 英语

张龙 78,88 95 81

** 思路2：取一个最近的考试成绩(这就需要在表中加一个时间列)**

** 思路3：如果两次成绩重复了,比如都是100,就可以用max或min**

语文 数学 英语

张龙 100 95 81

如果用sum的话,统计结果就变成了200

语文 数学 英语

张龙 200 95 81

- 增加一个时间限制

alter table table_grade add column exam_date date after score;

设置随机时间

update table_grade set exam_date = date_format(from_unixtime(  -- from_unixtime() 将时间戳转换为日期,date_format()设置一下日期格式

unix_timestamp('2023-01-01')    -- 将日期转为一个时间戳,此处是用1月1号加随机的天数(由于天数是1月1到4月28,所以最终的结果不会超过4月28)

+ floor( rand() *               -- rand() 随机数[0,1),floor() 向下取整

 (unix_timestamp('2023-04-28') - unix_timestamp('2023-01-01') + 1) -- 4月28减去1月1号的天数,因为rand的范围是[0,1),所以后面+1是为了保证可以取得4月28

)

),'%Y-%m-%d');

4.行转列的实操

前提： 假设张龙各科只考了一次,对此进行行转列

为了可以看出差异性，删除王五的英语，马六的语文

delete from table_grade where user_name = '王五' and course = '英语';

delete from table_grade where user_name = '马六' and course = '语文';

4.1 通用的行转列(Mysql和Oracle都能用)

select user_id '学生ID',

   max(case when course = '语文' then score else null end) '语文',  -- 因为只有一个成绩,max min 没区别; else null 可以省略,默认其他情况就是null

   max(case when course = '数学' then score end) '数学',

   max(case when course = '英语' then score end) '英语'

from table_grade

group by user_id  -- 用了聚合函数,那就用group by 去显示

4.1.1想在结果中加入学生名字

如果直接在group by 中加入user_name,是可以的执行的,这样group by的粒度会变细,细粒度的字段可以带粗粒度的字段(此处指select,因为select中只有user_id这一个字段)

如果把user_name加在select子句中,这样在mysql是可以执行的(我这里的mysql8出错了,可能8修改了这个),在orale中是不能执行的。即使mysql中可以执行,那意思就是按照user_id分组,然后带出一个比较细的划分user_id+user_name,而一个user_id可能对应多个user_name,那应该取哪一个呢？mysql会自动取分组中的第一个user_name

不要在group by中加入名字,因为可能会重名

4.1.1.1加入名字的方法：

select user_id '学生ID',

   (select max(user_name) from table_grade where user_id = t.user_id) user_name, -- 使用相关子查询(关联子查询)

   max(case when course = '语文' then score end) '语文',

   max(case when course = '数学' then score end) '数学',

   max(case when course = '英语' then score end) '英语'

from table_grade t

group by user_id;

相关子查询中的聚合函数与外面的聚合函数是由区别的,外面的聚合函数是同时按照所有的分组去求最大值的,相关子查询中的聚合函数是外层传入一个值,在内层进行一次查询。

平时的左关联右关联可能会产生笛卡尔积,但是关联子查询是不会产生笛卡尔积的,因为它每次外查询传入一个值,所以永远都是外对内是一对多,不会造成笛卡尔积

上面的相关子查询中加max()是为了防止出错，因为表中同一个ID对应多个名字,如果不加max()的话，就会报错:Subquery returns more than 1 row,即子查询返回了多行。所以此处需要加一个函数进行处理,至于决定加哪一个函数,根据需求决定,这后面讲

加入名字还有一种写法就是select … from in ——> 用in也最好是in(1,2) 这种小的组合,如果在in里面嵌套select查询，这种不太好理解,层数太多了