[大数据]LeetCode-MySQL

第二高成绩

select ifnull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary 

上升的温度

select t1.id 
from Weather t1
join Weather t2 on t1.temperature > t2.temperature and datediff(t1.RecordDate,t2.RecordDate) = 1

日期函数

select DATE_ADD(datetime,interval -1 month) from Employee

select date_sub(datetime,interval 2 month) from Employee

select datediff(datetime,DATE_SUB(datetime,interval 1 month)) from Employee

select timediff(datetime,DATE_SUB(datetime,interval 1 day)) from Employee

select DATE_FORMAT(datetime,'%Y:%m:%d') from Employee

select str_to_date('2008-01-01', '%Y-%d-%m'); 

select '2021-01-01'::timestamp + '1 year'
select '2021-01-01'::timestamp + '-1 year'
select '2021-01-01'::timestamp + interval '1 year'
select '2021-01-01'::timestamp - interval '1 year'
select date_part('day',cast('2022-03-01' as timestamp) - cast('2021-02-01' as timestamp)) 
select to_char('2021-01-01'::timestamp,'YYYY-MM-DD') 
select '2021-01-01'::timestamp

update做条件修改

# 1.
update salary
set sex = (
    case sex when 'm' then 'f' else 'm' end
);

# 2.
update salary set sex = if(sex='m','f','m');

MySQL实现dense_rank() over()

SELECT Score, 
    (SELECT count(DISTINCT Score) FROM Scores WHERE Score >= s.Score) AS 'Rank'
FROM Scores s
ORDER BY Score DESC;

SELECT Score,dense_rank() over(ORDER BY Score DESC)
FROM Scores;

互换座位

若为奇数，则最后一个不变

--表中包括ID和student
--互换座位的话，可分为两种，一是调整student的位置，二是将ID和student作为整体调整最后再排序
--调整student的话，可能不太方便，这里将ID和student作为一个整体进行调整
select 
--首先满足奇数的，并且id最大的一个是不需要调整的
(case when mod(id,2) = 1 and id = (select max(id) from Seat) then id
--除了最大的奇数，其余技术都ID增1，方便与其余的数据做交换
when mod(id,2) = 1 then id + 1
--其余的，也就是偶数的ID，需要减1，实现与奇数的增1做位置交换
else id -1 end ) as id , student
from Seat
--ID变动后，已经乱序，所以这里需要对其进行升序排序
order by id 

行程和用户

表：Trips

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| client_id   | int      |
| driver_id   | int      |
| city_id     | int      |
| status      | enum     |
| request_at  | date     |     
+-------------+----------+
id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

表：Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| users_id    | int      |
| banned      | enum     |
| role        | enum     |
+-------------+----------+
users_id 是这张表的主键。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

 

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

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

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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/trips-and-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

-- 分析
-- 1. 
-- 被司机或者乘客取消的非禁止用户生成的订单数量/非禁止用户生成的订单总数
-- user.banned = no and (Trips.status=cancelled_by_driver or Trips.status=cancelled_by_client)
-- user.banned = no
-- 2.
-- request_at between ('2013-10-01','2013-10-03')

-- 具体实现
select request_at as 'Day'
,round(avg(Status!='completed'), 2) as 'Cancellation Rate'
from Trips t1
inner join Users t2 on t1.client_id = t2.users_id  and t2.banned != 'YES'
inner join Users t3 on t1.driver_id = t3.users_id  and t3.banned != 'YES'
where request_at between '2013-10-01' and '2013-10-03' 
group by request_at
order by request_at

-- 值得吐槽的一点，在提交的时候因为别名不一致，一直提交运行失败