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查询，这种不太好理解,层数太多了
