SQL编程-组队学习打卡task06-秋招秘籍ABC

PS：

• 目录为考点 - 题目形式，方便查看
• 点击标题可以跳转至原题链接
• 点击建表语句进行建表

Section A

1.子查询 - 184. 部门工资最高的员工

思路：
先在Employee表中，对DepartmentId 字段分组查询最大值，得到不同DepartmentId 下的最大值

select departmentid,max(salary) 
from Employee 
group by DepartmentId;

departmentid|max(salary)|
------------+-----------+
           1|      90000|
           2|      80000|

再根据DepartmentId字段连接Department表，根据Salary 和DepartmentId 查找department.name 字段

• in 谓词，子查询

select
	d.name as Department,
	e.name as Employee,
	Salary
from
	department d,employee e
where
	e.departmentid = d.id
	and (e.departmentid, Salary)
	in (select departmentid, max(Salary)
		from employee
		group by departmentid);

Department|Employee|Salary|
----------+--------+------+
Sales     |Henry   | 80000|
IT        |Max     | 90000|

• inner join

select 
	d.name as Department,
	e.name as Employee,
	res.Salary
from (select departmentid,max(salary) as Salary 
	  from Employee 
	  group by DepartmentId) as res
inner join department d
	on res.departmentid = d.id 
inner join employee e 
    on res.departmentid = e.departmentid
	and res.Salary = e.salary;

Department|Employee|Salary|
----------+--------+------+
Sales     |Henry   | 80000|
IT        |Max     | 90000|

• 窗口函数

select
	name,departmentId,salary,
	rank() over(partition by departmentId order by salary desc) as rk 
from employee;
name |departmentId|salary|rk|
-----+------------+------+--+
Max  |           1| 90000| 1|
Joe  |           1| 70000| 2|
Henry|           2| 80000| 1|
Sam  |           2| 60000| 2|

select
	d.name as Department,
	res.name as Employee,
	res.Salary
from (select
		name,departmentId,salary,
		rank() over(partition by departmentId order by salary desc) as rk 
	  from Employee) as res
inner join department d 
on res.departmentid =d.id
where res.rk=1;

Department|Employee|Salary|
----------+--------+------+
IT        |Max     | 90000|
Sales     |Henry   | 80000|

2.case when - 626. 换座位

思路：
若id为偶数，则id减一，若id为奇数，则id加一，并单独处理奇数情况下的最后一个id。
只是查询结果改变 并不是真的改变了原表。

select 
	(case 
		when mod(id,2)=0 then id-1
		when id = (select count(*) from seat) then id
		else id+1
	 end) as ID,
	student
from seat
order by id;

ID|student|
--+-------+
 1|Doris  |
 2|Abbot  |
 3|Green  |
 4|Emerson|
 5|Jeames |

3.排序专用窗口函数 - 178. 分数排名

# -- 不指定 partition by 相当于所有行数据一个 partition, 数据进行区内排序
# -- dense_rank() 相当于每一行数据一个窗口, 对数据进行比较
# -- rank   按照成绩排名，纯排名 
# -- dense_rank   --按照成绩排名，相同成绩排名一致
# -- row_number   --按照成绩依次排名
# -- ntile         --按照分数划分成绩梯队
select
    Score,
    dense_rank() over(order by score desc) as 'Rank'
from
    scores;

• 组队学习题目：
  假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91，请问可以实现几种排名结果？分别使用了什么函数？排序结果是怎样的？（只考虑降序）
  
  思路：
  考察排序用的专用窗口函数：RANK、DENSE_RANK、ROW_NUMBER

select class,score_avg,
		rank() over (order by score_avg DESC) as ranking,
		dense_rank() over (order by score_avg DESC) as dense_ranking,
		row_number() over (order by score_avg DESC) as row_numbering
from score;

class|score_avg|ranking|dense_ranking|row_numbering|
-----+---------+-------+-------------+-------------+
    1|       93|      1|            1|            1|
    2|       93|      1|            1|            2|
    3|       93|      1|            1|            3|
    4|       91|      4|            2|            4|

4.子查询 - 180. 连续出现的数字

思路：

-- 连续出现2次的数字
select *
from logs
where (id+1,num) in (select * from logs)
id|num|
--+---+
 1|  1|
 2|  1|
 6|  2|
 
-- 连续出现3次的数字
select *
from logs
where (id+1,num) in (select * from logs)
and (id+2,num) in (select * from logs)

-- 整理得
select distinct num as ConsecutiveNums 
from logs
where (id+1,num) in (select * from logs)
and (id+2,num) in (select * from logs)

5.case when - 608. 树节点

思路：

-- 40%
select id,
		(case 
			when p_id is null then 'Root'
			when p_id in (select id from tree) 
				and id in (select p_id from tree)
				then 'Inner'
			else 'Leaf'
		end) as 'Type'
from tree;

id|Type |
--+-----+
 1|Root |
 2|Inner|
 3|Leaf |
 4|Leaf |
 5|Leaf |

或者

select
    id,
    case when p_id is null then 'Root'
         when id in (select p_id from tree) then 'Inner'
         else 'Leaf'
    end as 'Type'
from
    tree;

--错误写法
--注意：不要用id not in (select p_id from tree) then 'Leaf'
-- 当你判断任意值a != null时, 官方说, "You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL", 任何与null值的对比都将返回null
-- 当询问 id not in (select p_id from tree)时, 因为p_id有null值, 返回结果全为false, 于是跳到else的结果, 返回值为inner. 
select
    id,
    case when p_id is null then 'Root'
         when id not in (select p_id from tree) then 'Leaf'
         else 'Inner'
    end as 'Type'
from tree;

6.子查询,分组过滤,联结 - 570. 至少有5名直接下属的经理

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：

注意：没有人向自己汇报。

思路1：分组过滤+inner join， e1.managerid =e2.id

--查询每个managerid的下属个数
select 
 	managerid,
 	count(managerid) as cnt
from 
 	employee2 
group by managerid
 
 managerid|cnt|
---------+---+
         |  1|
      101|  5|

--过滤处下属个数>=5的，分组过滤用having
select 
 	managerid,
 	count(managerid) as cnt
from 
 	employee2 
group by managerid 
having cnt>=5

managerid|cnt|
---------+---+
      101|  5|
      
--inner join
select 
	name
from employee2 e2 
inner join
(select 
 	managerid,
 	count(managerid) as cnt
 from 
 	employee2 
 group by managerid 
 having cnt>=5) e1
on e1.managerid =e2.id 

name|
----+
John|

思路2：对每一个员工，用子查询看是否有5个员工

select a.name 
from employee2 a
where 5<=(select count(*)
		  from employee2 b
		  where b.managerid =a.id)

其中第一次子查询执行：
select count(*)
from employee2 b
where b.managerid =101

7.case,sum - 578. 查询回答率最高的问题

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

写一条sql语句找出回答率（answer 出现次数 / show 出现次数）最高的 question_id。

说明：问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。
注意：最高回答率的意思是：同一个问题出现的次数中回答的比例。

思路1：

--通过 sum 和 case 计算出回答率 rate ，并且升序排列，作为临时表 a，然后查询 a 取第一条数据。
select question_id
from 
	(select 
		question_id,
		sum(case action when 'answer' then 1 else 0 end)/
		sum(case action when 'show' then 1 else 0 end) as rate
	from survey_log
	group by question_id
	order by rate desc) as a
limit 1;

question_id|
-----------+
        285|

思路2：

1. 先查出 action = answer 的数据存为一张临时表 s1；
2. 再查出 action = show 的数据作为一张临时表 s2；
3. 通过 question_id 连接两表；
4. 使用 order by对回答进行排列，取第一条数据。

select s1.question_id
from 
	(select question_id,count(*) as cnt_answer
	from survey_log 
	where action ='answer'
	group by question_id) as s1
join 
	(select question_id,count(*) as cnt_show
	from survey_log 
	where action ='show'
	group by question_id) as s2
on s1.question_id=s2.question_id
order by s1.cnt_answer/s2.cnt_show desc limit 1;

question_id|
-----------+
        285|

思路3：

--计算每道题的 action = ‘answer’ 的平均数。action = ‘answer’ 个数越多，回答率越高
select question_id 
from survey_log
group by question_id
order by avg(action='answer')desc limit 1

-- 一道题只有回答了才有 answer_id ，所以answer_id 数量最多的就是回答率最高的。
select question_id 
from survey_log
group by question_id
order by count(answer_id) desc limit 1

8.子查询,分组过滤 - 185. 部门工资前三高的所有员工

一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
思路：
找每个部门的工资前三名，
那么先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比我高】，
那么接下来按照having count(Salary) <= 2来筛选的原理是：
如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，

-- 部门相同但是工资比我高
select *
from employee e1
left join employee e2
on e1.departmentid =e2.departmentid 
and e1.salary < e2.salary ;
id|name |salary|departmentid|id|name |salary|departmentid|
--+-----+------+------------+--+-----+------+------------+
 1|Joe  | 85000|           1| 4|Max  | 90000|           1|
 2|Henry| 80000|           2|  |     |      |            |
 3|Sam  | 60000|           2| 2|Henry| 80000|           2|
 4|Max  | 90000|           1|  |     |      |            |
 5|Janet| 69000|           1| 1|Joe  | 85000|           1|
 5|Janet| 69000|           1| 4|Max  | 90000|           1|
 5|Janet| 69000|           1| 6|Randy| 85000|           1|
 5|Janet| 69000|           1| 7|Will | 70000|           1|
 6|Randy| 85000|           1| 4|Max  | 90000|           1|
 7|Will | 70000|           1| 1|Joe  | 85000|           1|
 7|Will | 70000|           1| 4|Max  | 90000|           1|
 7|Will | 70000|           1| 6|Randy| 85000|           1|
 
--按员工id分组，查询出比我工资高的人数
select *,count(distinct e2.salary)
from employee e1
left join employee e2
on e1.departmentid =e2.departmentid 
and e1.salary < e2.salary 
group by e1.id ;
id|name |salary|departmentid|id|name |salary|departmentid|count(distinct e2.salary)|
--+-----+------+------------+--+-----+------+------------+-------------------------+
 1|Joe  | 85000|           1| 4|Max  | 90000|           1|                        1|
 2|Henry| 80000|           2|  |     |      |            |                        0|
 3|Sam  | 60000|           2| 2|Henry| 80000|           2|                        1|
 4|Max  | 90000|           1|  |     |      |            |                        0|
 5|Janet| 69000|           1| 1|Joe  | 85000|           1|                        3|
 6|Randy| 85000|           1| 4|Max  | 90000|           1|                        1|
 7|Will | 70000|           1| 1|Joe  | 85000|           1|                        2|

--按员工id分组，过滤出比我工资高的人数不超过2的小组，即员工.这些员工都在各自部门的前3
select e1.id
from employee e1
left join employee e2
on e1.departmentid =e2.departmentid 
and e1.salary < e2.salary 
group by e1.id 
having count(distinct e2.salary) <= 2 ;
id|
--+
 1|
 2|
 3|
 4|
 6|
 7|

--外查询，按部门显示名字
select d.name as Department ,e3.name as Employee ,e3.salary as Salary 
from employee e3
inner join department d 
on e3.departmentid = d.id
where e3.id in (select e1.id
				from employee e1
				left join employee e2
				on e1.departmentid =e2.departmentid 
				and e1.salary < e2.salary 
				group by e1.id 
				having count(distinct e2.salary) <= 2)
order by d.id asc, e3.salary desc;
Department|Employee|Salary|
----------+--------+------+
IT        |Max     | 90000|
IT        |Joe     | 85000|
IT        |Randy   | 85000|
IT        |Will    | 70000|
Sales     |Henry   | 80000|
Sales     |Sam     | 60000|

此外，请考虑实现各部门前N高工资的员工功能。

--将上面方法中2改为N-1，表示工资比我高的<= N-1 个，那么这些员工就在前N高里了
select d.name as Department ,e3.name as Employee ,e3.salary as Salary 
from employee e3
inner join department d 
on e3.departmentid = d.id
where e3.id in (select e1.id
				from employee e1
				left join employee e2
				on e1.departmentid =e2.departmentid 
				and e1.salary < e2.salary 
				group by e1.id 
				having count(distinct e2.salary) <= N-1)
order by d.id asc, e3.salary desc;

9.where - 612. 平面上的最近距离

point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。
写一条查询语句求出这些点中的最短距离并保留2位小数

最短距离是1，从点（-1，-1）到点（-1，-2）。所以输出结果为：
| shortest |
1.00
注意：所有点的最大距离小于10000。

思路：
1# 两表联查
2# 去重，where p1.x<>p2.x or p1.y<>p2.y
3# 距离计算公式sqrt(min(power((p1.x-p2.x),2)+power((p1.y-p2.y),2)))，我把取最小值放在根号里面

select round(sqrt(min(power((p1.x-p2.x),2)+power((p1.y-p2.y),2)))) as shortest
from point_2d p1,point_2d p2
where p1.x<>p2.x or p1.y<>p2.y;

shortest|
--------+
     1.0|

10.子查询,联结 - 262. 行程和用户

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

思路：
1.筛选出非禁止乘客，非禁止司机
2.筛选出符合日期
3.按日期分组，计算取消率

--筛选出非禁止用户
select *
from trips
where Client_Id in (select Users_Id from users where Banned ='No')
and Driver_Id  in (select Users_Id from users where Banned ='No');
Id|Client_Id|Driver_Id|City_Id|Status             |Request_at|
--+---------+---------+-------+-------------------+----------+
 1|        1|       10|      1|completed          |2013-10-01|
 3|        3|       12|      6|completed          |2013-10-01|
 4|        4|       13|      6|cancelled_by_client|2013-10-01|
 5|        1|       10|      1|completed          |2013-10-02|
 7|        3|       12|      6|completed          |2013-10-02|
 9|        3|       10|     12|completed          |2013-10-03|
10|        4|       13|     12|cancelled_by_driver|2013-10-03|

--分组，计算取消率。round四舍五入
select Request_at as 'Day',round(1-sum(Status='completed')/count(*),2) as 'Cancellation Rate'
from trips 
where Client_Id in (select Users_Id from users where Banned ='No')
and Driver_Id  in (select Users_Id from users where Banned ='No')
and Request_at between '2013-10-01' and '2013-10-03' 
group by Request_at;
Day       |Cancellation Rate|
----------+-----------------+
2013-10-01|             0.33|
2013-10-02|             0.00|
2013-10-03|             0.50|

--其他写法
select Request_at as 'Day',round(avg(Status!='completed'),2) as 'Cancellation Rate'
from trips 
where Client_Id in (select Users_Id from users where Banned ='No')
and Driver_Id  in (select Users_Id from users where Banned ='No')
and Request_at between '2013-10-01' and '2013-10-03' 
group by Request_at;

--时间75%
select Request_at as 'Day',round(avg(Status!='completed'),2) as 'Cancellation Rate'
from trips t
inner join Users u1
on t.Client_Id =u1.Users_Id and u1.Banned ='No'
inner join Users u2
on t.Driver_Id =u2.Users_Id and u2.Banned ='No'
where Request_at between '2013-10-01' and '2013-10-03' 
group by Request_at;

Section B

1. case when - 行转列

假设 A B C 三位小朋友期末考试成绩如下所示：

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

思路1：

select 
	name,
	case when subject ='chinese' then score else 0 end as 'chinese',
	case when subject ='math' then score else 0 end as 'math',
	case when subject ='english' then score else 0 end as 'english'
from score2;

name|chinese|math|english|
----+-------+----+-------+
A   |     99|   0|      0|
A   |      0|  98|      0|
A   |      0|   0|     97|
B   |     92|   0|      0|
B   |      0|  91|      0|
B   |      0|   0|     90|
C   |     88|   0|      0|
C   |      0|  87|      0|
C   |      0|   0|     86|

--以name 为分组，对结果聚合用sum() 或max() 都可以
select 
	name,
	sum(case when subject ='chinese' then score else 0 end) as 'chinese',
	sum(case when subject ='math' then score else 0 end) as 'math',
	sum(case when subject ='english' then score else 0 end) as 'english'
from score2
group by name;
name|chinese|math|english|
----+-------+----+-------+
A   |     99|  98|     97|
B   |     92|  91|     90|
C   |     88|  87|     86|

思路2：

--还可以以窗口函数进行聚合
select distinct
	name,
	max(case when subject='chinese' then score else 0 end) over( partition by name) chinese,
	max(case when subject='math' then score else 0 end) over(partition by name) math,
	max(case when subject='english' then score else 0 end) over(partition by name) english 
from score2;

2. union - 列转行

上道题转过来。
思路：

select 
	name,
	'chinese' as 'subject',
	chinese as 'score'
from score22
union 
select
	name,
	'math' as subject,
	math as score
from score22
union
select
	name,
	'english' as subject,
	english as score
from score22
order by name;

3. union - 谁是明星带货主播

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

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

请使用 SQL 完成如下计算：
a. 2021年有多少个明星主播日？
b. 2021年有多少个明星主播？

思路：

-- 筛选出销售额占比当前90%以上的记录
select 
	*,a.sales/res.sum_today
from 
	anchor_sales a
inner join
	(select date,sum(sales) as sum_today
	from anchor_sales
	group by date) res
on a.date=res.date
where a.sales/res.sum_today >=0.9

anchor_name|date    |sales|date    |sum_today|a.sales/res.sum_today|
-----------+--------+-----+--------+---------+---------------------+
C          |20210102|90000|20210102|   100000|               0.9000|
C          |20210103|80000|20210103|    87500|               0.9143|

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

/*a. 2021年有多少个明星主播日？*/
select 
	count(distinct a.date)
from 
	anchor_sales a
inner join
	(select date,sum(sales) as sum_today
	from anchor_sales
	group by date) res
on a.date=res.date
where a.sales/res.sum_today >=0.9

count(distinct a.date)|
----------------------+
                     2|

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

/*b. 2021年有多少个明星主播？*/
select 
	count(distinct a.anchor_name)
from 
	anchor_sales a
inner join
	(select date,sum(sales) as sum_today
	from anchor_sales
	group by date) res
on a.date=res.date
where a.sales/res.sum_today >=0.9

count(distinct a.anchor_name)|
-----------------------------+
                            1|

4. MySQL 中如何查看sql语句的执行计划？可以看到哪些信息？

EXPLAIN SELECT 语句

举例：
如 Section A的第8题

explain 
select d.name as Department ,e3.name as Employee ,e3.salary as Salary 
from employee e3
inner join department d 
on e3.departmentid = d.id
where e3.id in (select e1.id
				from employee e1
				left join employee e2
				on e1.departmentid =e2.departmentid 
				and e1.salary < e2.salary 
				group by e1.id 
				having count(distinct e2.salary) <= 2)
order by d.id asc, e3.salary desc;

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                                     |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+------------------------------------------+
 1|PRIMARY    |d    |          |ALL |PRIMARY      |   |       |   |   2|   100.0|Using temporary; Using filesort           |
 1|PRIMARY    |e3   |          |ALL |             |   |       |   |   7|   14.29|Using where; Using join buffer (hash join)|
 2|SUBQUERY   |e1   |          |ALL |PRIMARY      |   |       |   |   7|   100.0|Using temporary; Using filesort           |
 2|SUBQUERY   |e2   |          |ALL |             |   |       |   |   7|   100.0|Using where; Using join buffer (hash join)|

• id
  id=1,两张表从上往下执行。
  from employee e3
inner join department d
  先 d 后 e3
  id= 2，id不同，如子查询，id的序号会递增
• select_type
  – PRIMARY ：查询中包含任何复杂的子部分，最外层查询则被标记为primary
  – SUBQUERY：在select 或 where列表中包含了子查询
• type=all
  Full Table Scan，遍历全表以找到匹配的行
  访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
• possible_keys
  查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
• rows
  根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
• Extra
  不适合在其他字段中显示，但是十分重要的额外信息
  – Using filesort ：
  mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”
  – Using temporary：
  使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by
  – Using where ：
  使用了where过滤
  – Using join buffer ：
  使用了链接缓存

Mysql查看执行计划

5. 解释一下 SQL 数据库中 ACID 是指什么

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)所应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）.这是可靠数据库所应具备的几个特性.下面针对这几个特性进行逐个讲解.

原子性(Atomicity)

整个事务是一个不可分割整体，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。
事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

每一条的T-SQL语句都是一个事务，如insert语句、update语句等。
用户也可以定义自己的事务，使用TYR-CATCH方法将多条语句合为一个事务，
比如银行转账，在A账户中减钱与在B账户中增钱是一个自定义的事务。

一致性(Consistency)

一致性,即在事务开始之前和事务结束以后，数据库的完整性约束（唯一约束，外键约束,Check约束等）没有被破坏。
业务的一致性可以转化为数据库的一致性。

隔离性（Isolation)

隔离执行事务，多个事务的执行互相不干扰。一个事务不可能获取到另一个事务执行的中间数据。
SQL Server利用加锁造成阻塞来保证事务之间不同等级的隔离性。

事务之间的互相影响的情况分为几种，分别为:脏读(Dirty Read)，不可重复读，幻读。

脏读表示一个事务获取了另一个事务的未提交数据，这个数据有可能被回滚。

不可重复度表示一个事务执行两次相同的查询，出现了不同的结果，
这是因为两次查询中间有另一事务对数据进行了修改。

幻读,是指当事务不是独立执行时发生的一种现象.
例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。
同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。
那么，第一个事务的用户发现表中还有 没有修改的数据行，就好象发生了幻觉一样。

为了避免上述几种事务之间的影响,SQL Server通过设置不同的隔离等级来进行不同程度的避免。
因为高的隔离等级意味着更多的锁，从而牺牲性能。
所以这个选项开放给了用户根据具体的需求进行设置。
不过默认的隔离等级Read Commited符合了99%的实际需求。

持久性（Durability)

持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中.

SQL SERVER通过write-ahead transaction log来保证持久性。
write-ahead transaction log的意思是，事务中对数据库的改变在写入到数据库之前，首先写入到事务日志中。
而事务日志是按照顺序排号的（LSN）。

当数据库崩溃或者服务器断点时，重启动SQL SERVER，SQL SERVER首先会检查日志顺序号，
将本应对数据库做更改而未做的部分持久化到数据库，从而保证了持久性.

Section C

1. case when - 行转列

假设有如下比赛结果：

请使用 SQL 将比赛结果转换为如下形式：

思路：

select 
	cdate as '比赛日期',
	case when result='胜' then 1 else 0 end as '胜',
	case when result='负' then 1 else 0 end as '负'
from row_col;

比赛日期  |胜|负|
----------+-+-+
2021-01-01|1|0|
2021-01-01|0|1|
2021-01-03|1|0|
2021-01-03|0|1|
2021-01-01|1|0|
2021-01-03|0|1|

select 
	cdate as '比赛日期',
	sum(case when result='胜' then 1 else 0 end) as '胜',
	sum(case when result='负' then 1 else 0 end) as '负'
from row_col
group by cdate

比赛日期  |胜|负|
----------+-+-+
2021-01-01|2|1|
2021-01-03|1|2|

2. union - 行转列

上一道题反转。
思路：

select cdate,result
from
(SELECT 比赛日期 AS cdate, '胜' AS result, 胜 AS times
FROM col_row
UNION
SELECT 比赛日期 AS cdate, '负' AS result, 负 AS times
FROM col_row
union
SELECT 比赛日期 AS cdate, '胜' AS result, 胜-1 AS times
FROM col_row
UNION
SELECT 比赛日期 AS cdate, '负' AS result, 负-1 AS times
FROM col_row) as a
where times >0
order by cdate,result;

cdate     |result|
----------+------+
2021-01-01|胜     |
2021-01-01|胜     |
2021-01-01|负     |
2021-01-03|胜     |
2021-01-03|负     |
2021-01-03|负     |

3. 窗口函数,group by - 连续登录

有用户表行为记录表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期）

1. 计算2021年每个月，每个用户连续登录的最多天数
2. 计算2021年每个月，连续2天都有登录的用户名单
3. 计算2021年每个月，连续5天都有登录的用户数

对于题1，思路：
关键在于连续日期统计。
1.计算相隔日期：datediff(imp_date,'2020-01-01')
选择参考日期2020-01-01，只要比表中所有日期小就可以。
对于用户u1004，
2020-01-01登录，相隔日期为366;
2020-01-02登录，相隔日期为367;
2020-01-03登录，相隔日期为368。

2.计算登录日期的排名；rank()over(partition by uid order by imp_date) as rank_
对于用户u1004，
2020-01-01登录，登录日期排名为1;
2020-01-02登录，登录日期排名为2;
2020-01-03登录，登录日期排名为3.
3.计算上面两者的差作为ranking，可以发现，同一用户的登录日期连续则ranking相同。
2020-01-01登录，ranking为366-1=365;
2020-01-02登录，ranking为367-2=365;
2020-01-03登录，ranking为368-3=365。
4.接下来就外查询，按uid ，imp_date ，ranking分组，count(*)就是连续登录天数

mysql 连续日期统计_MYSQL – 计算连续日期天数

select 
	uid,
	imp_date,
	rank()over(partition by uid order by imp_date) as rank_,
	(datediff(imp_date,'2020-01-01') - rank()over(partition by uid order by imp_date)) as ranking
from t_act_records

uid  |imp_date  |rank_|ranking|
-----+----------+-----+-------+
u1001|2021-01-01|    1|    365|
u1002|2021-01-01|    1|    365|
u1003|2021-01-01|    1|    365|
u1003|2021-01-02|    2|    365|
u1004|2021-01-01|    1|    365|
u1004|2021-01-02|    2|    365|
u1004|2021-01-03|    3|    365|
u1004|2021-01-04|    4|    365|
u1004|2021-01-05|    5|    365|

select month(imp_date) as '月份',
		uid,
		count(*) as '连续天数'
from 
	(select 
		uid,
		imp_date,
		rank()over(partition by uid order by imp_date) as rank_,
		(datediff(imp_date,'2020-01-01') - rank()over(partition by uid order by imp_date)) as ranking
	from t_act_records) as a
group by uid,month(imp_date),ranking
order by 连续天数 desc

月份|uid  |连续天数|
--+-----+----+
 1|u1004|   5|
 1|u1003|   2|
 1|u1001|   1|
 1|u1002|   1|

对于题2，思路：
即连续登录天数=2的用户，在题1上加where 连续天数=2

select *
from 
	(select month(imp_date) as '月份',
			uid,
			count(*) as '连续天数'
	from 
		(select 
			uid,
			imp_date,
			rank()over(partition by uid order by imp_date) as rank_,
			(datediff(imp_date,'2020-01-01') - rank()over(partition by uid order by imp_date)) as ranking
		from t_act_records) as a
	group by uid,month(imp_date),ranking
	order by 连续天数 desc) as b
where b.连续天数=2

月份|uid  |连续天数|
--+-----+----+
 1|u1003|   2|

对于题2，思路：
即连续登录天数=5的用户，在题1上加where 连续天数=5

select *
from 
	(select month(imp_date) as '月份',
			uid,
			count(*) as '连续天数'
	from 
		(select 
			uid,
			imp_date,
			rank()over(partition by uid order by imp_date) as rank_,
			(datediff(imp_date,'2020-01-01') - rank()over(partition by uid order by imp_date)) as ranking
		from t_act_records) as a
	group by uid,month(imp_date),ranking
	order by 连续天数 desc) as b
where b.连续天数=5

月份|uid  |连续天数|
--+-----+----+
 1|u1004|   5|

4. 子查询 - 用户购买商品推荐

假设现在需要根据算法给每个 user_id推荐购买商品，推荐算法比较简单，推荐和他相似的用户购买过的 product 即可，说明如下：

• 排除用户自己购买过的商品
• 相似用户定义：曾经购买过 2 种或 2 种以上的相同的商品

思路：
为了方便，默认只有用户123和用户456。
子查询
对用户123找用户456买过的但是不是用户123买过的。
对用户456找用户123买过的但是不是用户456买过的。

select distinct o1.user_id,
	   (select product_id
		from orders 
		where product_id in (select product_id
							from orders
						   	where user_id <> o1.user_id)
		and product_id not in (select product_id
							from orders 
							where user_id =o1.user_id)) as product_id
from orders o1
where user_id in (select user_id
				 from orders 
				 group by user_id 
				 having count(distinct product_id)>=2);

user_id|product_id|
-------+----------+
    123|         4|
    456|         3|

如果对于：

应该返回：

user_id|product_id|
-------+----------+
    123|         4|
    123|         5|
    456|         3|
    456|         5|
    789|         3| 
    789|         4|               

相似用户数量>2时，还没想好怎么写。

5. hive 数据倾斜的产生原因及优化策略？

原因：
1.key分布不均匀
2.业务数据本身的特性
3.SQL语句造成数据倾斜

优化策略：
1.hive设置hive.map.aggr=true和hive.groupby.skewindata=true

2.有数据倾斜的时候进行负载均衡，当选项设定为true,生成的查询计划会有两个MR Job。 第一个MR Job中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同Group By
Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job在根据预处理的数据结果按照 Group By Key
分布到Reduce中(这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中)，最后完成最终的聚合操作。

3.SQL语句调整:
1.选用join key 分布最均匀的表作为驱动表。做好列裁剪和filter操作，以达到两表join的时候，数据量相对变小的效果。
2.大小表Join： 使用map join让小的维度表（1000条以下的记录条数）先进内存。在Map端完成Reduce。
3.count distinct大量相同特殊值：count distinct时，将值为空的情况单独处理， 如果是计算count distinct，可以不用处理，直接过滤，在做后结果中加1。如果还有其他计算，需要进行group
by，可以先将值为空的记录单独处理，再和其他计算结果进行union.
4.大表Join大表：把空值的Key变成一个字符串加上一个随机数， 把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终的结果。 ————————————————
版权声明：本文为CSDN博主「hxbnb」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hxbdc56/article/details/100764237

还没有用过Hive，对这个问题没有什么实际场景下的理解。后续学习Hive再来看这个问题。

6. LEFT JOIN 是否可能会出现多出的行？为什么？

假设 t1 表有6行（关联列 name 有2行为空），t2 表有6行（关联列 name 有3行为空）,

那么 SELECT * FROM t1 LEFT JOIN t2 on t1.name = t2.name 会返回多少行结果？

可以参考下图

SELECT * FROM A LEFT JOIN B ON A.name = B.name;

id|name|score|id|name|city     |
--+----+-----+--+----+---------+
1 |aaa |   90|1 |aaa |beijing  |
2 |bbb |   80|2 |bbb |tianjin  |
3 |ccc |   70|3 |ccc |chengdu  |
4 |ddd |   60|  |    |         |
5 |    |   90|4 |    |shenzhen |
5 |    |   90|5 |    |qingdao  |
5 |    |   90|6 |    |guangzhou|
6 |    |  100|4 |    |shenzhen |
6 |    |  100|5 |    |qingdao  |
6 |    |  100|6 |    |guangzhou|

结果发现多出了6行null，对照id分析可以得知左表的每一个null与右表的每一个nul都进行了配对。
因为 on 左表.null= on.右表.null 是符合条件的，每一个左表的null都可以和右表的null进行一 一连接，所以形成了类似笛卡尔积的结果。

---

感想🔈

个人收获：学习了SQL的基本语法。练习题中，task06的前10道题综合性很强，值得三刷。语法就只有那些，关键要理解问题如何转化为查询条件。

对课程的看法：
是不错的入门教材，助教们经验丰富且负责任。期待下一次组队学习。