秋招秘籍B

练习一：行转列

假设 A B C 三位小朋友期末考试成绩如下所示：
创建学生成绩表student_score:

create table student_score
(name varchar(4) not null,
 subject varchar(64) not null,
 score integer(4) not null,
 primary key(name,subject));
 
insert into student_score values('A','chinese',99);
insert into student_score values('A','math',98);
insert into student_score values('A','english',97);
insert into student_score values('B','chinese',92);
insert into student_score values('B','math',91);
insert into student_score values('B','english',90);
insert into student_score values('C','chinese',88);
insert into student_score values('C','math',87);
insert into student_score values('C','english',86);

请使用 SQL 代码将以上成绩转换为如下格式(行转为列）：

解题思路：
使用聚合函数sum+case语句

1. 通过group by 语句将表格按学生名称(name)进行分组
2. 通过case 语句分别查询学生chinese]、math、english三门课程成绩

select name, 
			 sum(case when subject = 'chinese' then score else null end)as chinese,
			 sum(case when subject = 'math' then score else null end)as math,
			 sum(case when subject = 'english' then score else null end)as english
from student_score
group by name

练习二：列转行

假设 A B C 三位小朋友期末考试成绩如下所示：
创建学生分数表格student_score2:

create table student_score2
(name varchar(4) not null,
 chinese integer(4) not null,
 math integer(4) not null,
 english integer(4) not null,
 primary key(name));

insert into student_score2 values('A',99,98,97);
insert into student_score2 values('B',92,91,90);
insert into student_score2 values('C',88,87,86);

请使用 SQL 代码将以上成绩转换为如下格式：

解题思路1：
直观想法模仿行转列，使用聚合函数加case语句：

1. 没有subject，故根据课程名称定义列subject，且分别查询chinese、math、english
2. 将查询的集合相并，并根据name排序得到列转行

解题思路2：
使用UNPIVOT，具体思路参考文章：SQL行转列，列转行
但自己并没有运行成功，后续有时间再整理什么原因吧

解题思路1 SQL语句：

select name,
			 'chinese' as subject,
			 chinese as score
from student_score2
group by name
union
select name,
			 'math' as subject,
			 math as score
from student_score2
group by name
union
select name,
			'english' as subject,
			english as score
from student_score2
group by name
order by name			 

练习三：带货主播

假设，某平台2021年主播带货销售额日统计数据如下：
创建销售额统计表anchor_sales:

create table anchor_sales
(anchor_name varchar(4) not null,
 date integer(32) not null,
 sales integer(64) not null,
 primary key(anchor_name,date));

insert into anchor_sales values('A',20210101,40000);
insert into anchor_sales values('B',20210101,80000);
insert into anchor_sales values('A',20210102,10000);
insert into anchor_sales values('C',20210102,90000);
insert into anchor_sales values('A',20210103,7500);
insert into anchor_sales values('C',20210103,80000);

定义：如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上，则称该主播为明星主播，当天也称为明星主播日。
请使用 SQL 完成如下计算：

a. 2021年有多少个明星主播日？

b. 2021年有多少个明星主播？

解题思路：

1. 要统计明星主播和日期，故以日期和主播名进行分组
2. 通过sum函数很容易统计在以日期和主播名分组情况下的sales
3. 通过聚合函数sum在窗口函数下的使用，统计平台每天的总销售额
4. 步骤2和3进行相除，得到每个主播销售总额占该平台当天销售总额百分比sales_rate
5. 使用where 选取出满足条件的明星主播和明星主播日
   计算平台每天各主播销售额占比，并排序SQL语句：

select date,anchor_name,
			(sum(sales)/sum(sales) over (partition by date))as sales_rate
from anchor_sales
group by date,anchor_name
order by date,sales_rate desc

运行结果：

选取销售额占比超过90%的明星主播和明星主播日的SQL语句：

select *
from(	select date,anchor_name,
			(sum(sales)/sum(sales) over (partition by date))as sales_rate
			from anchor_sales
			group by date,anchor_name
			order by date,sales_rate desc)as p1
where sales_rate >= 0.9

练习四：MySQL 中如何查看sql语句的执行计划？可以看到哪些信息？

MySQL 查看执行计划只需要在查询语句前加explain即可，已上题为例，查看执行计划的sql语句如下：

explain select date,anchor_name,
			(sum(sales)/sum(sales) over (partition by date))as sales_rate
from anchor_sales
group by date,anchor_name
order by date,sales_rate desc

查询到的信息结果如下：

详情解释参考文章：MySQL EXPLAIN 命令: 查看查询执行计划

练习五：解释一下 SQL 数据库中 ACID 是指什么

• 原子性： 语句要么都执行，要么都不是执行，是事务最核心的特性，事务本身来说就是以原子性历来定义的，实现主要是基于undo log
• 持久性： 保证事务提交之后，不会因为宕机等其他的原因而导致数据的丢失，主要是基于 redo log实现
• 隔离性： 保证事务与事务之间的执行是相互隔离的，事务的执行不会受到其他事务的影响。InnoDB存储引擎默认的数据库隔离级别是 RR，RR又主要是基于锁机制，数据的隐藏列，undo log类 以及 next-key lock机制
• 一致性： 事务追求的最终目标，一致性的实现即需要数据库层面的保障，也需要应用层面的保障。
  详情参考：MySQL事务之ACID实现原理（全方位解读）

本文参考：
MySQL EXPLAIN 命令: 查看查询执行计划
MySQL事务之ACID实现原理（全方位解读）

本文完整题目：
DataWhale 组队学习