leetcode

569. 员工薪水中位数

 

 解法1:

--解法1
--注意：如何在窗口函数order by两个字段，需要写两次asc
--用row_nunber()正向排序一次，反向排序一次，
--当为奇数的时候就是正反序号相等的时候，当为偶数的时候，就是正反序号相差一的时候。
--还有个小细节就是在使用SQL窗口函数按照大小顺序编号的时候，相等的两个数会存在以升序序列来标注问题
--此时可以利用SQL表的主键id，在待求序列相等时，正向编号按id升序排序编号，反向编号按id降序排序编号，这样就保证了两列编号的走向处处相反，从而使得判断条件生效。
select Id,Company,Salary
from
(select Id,Company,Salary,row_number()over(partition by Company order by Salary asc,Id asc) rk1,
row_number()over(partition by Company order by Salary desc,Id desc) rk2
from Employee) a 
where abs(cast(rk1 as signed)-cast(rk2 as signed))=1 or rk1=rk2
order by Company,Salary asc

--解法2
select Id,Company,Salary
from
(
    select Id,Company,Salary,
        row_number()over(partition by Company order by Salary asc) as rk,
        count(*)over(partition by Company) as num 
    from Employee
) t
-- where rk between num/2 and num/2+1
where rk in (num/2,num/2+1,num/2+0.5)

570. 至少有5名直接下属的经理

  

select Name 
from Employee a join
(select ManagerId,count(distinct Name) as cnt 
from Employee
where ManagerId is not null
group by ManagerId
having cnt>=5) b
on a.Id = b.ManagerId

571. 给定数字的频率查询中位数

 

--1
select avg(Number) as median
from
(select Number,Frequency,
    sum(Frequency)over(order by Number desc) as cnt1,
    sum(Frequency)over(order by Number asc) as cnt2
from Numbers
order by Number asc) a 
where cnt1>=(select sum(Frequency)/2 from Numbers)
and cnt2>=(select sum(Frequency)/2 from Numbers)

--2
select avg(number) as median
from
(select number, sum(frequency) over(order by number asc) as asc_amount, 
                sum(frequency) over(order by number desc) as desc_amount,
                sum(frequency) over() as total_num
from numbers) a
where asc_amount >= total_num/2 and desc_amount >= total_num / 2 

579. 查询员工的累计薪水

--不要求每个月近三个月连续

SELECT Id, Month, Salary
FROM (SELECT Id, Month, SUM(Salary) OVER (PARTITION BY Id ORDER BY Month ROWS 2 PRECEDING) AS Salary, rank() OVER (PARTITION BY Id ORDER BY Month DESC) AS r
      FROM Employee) t
WHERE r > 1
ORDER BY Id, Month DESC;

/*输入：
{"headers":{"Employee":["Id","Month","Salary"]},
"rows":{"Employee":[[1,1,20],[2,1,20],[1,2,30],[2,2,30],[3,2,40],[1,3,40],[3,3,60],[1,4,60],[3,4,70],[1,7,90],[1,8,90]]}}
输出：
{"headers": ["Id", "Month", "Salary"], 
"values": [[1, 7, 190], [1, 4, 130], [1, 3, 90], [1, 2, 50], [1, 1, 20], [2, 1, 20], [3, 3, 100], [3, 2, 40]]}*/

--要求连续
--range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内
--rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与
--排序后的行号相关）

select id,
        month,
        sum(salary) over(partition by id order by month range 2 preceding) as 'salary' 
from Employee
where ( id, month) not in 
    (select id, max(month)  from Employee as e group by id)
order by id, month desc

/*输出：
{"headers": ["id", "month", "Salary"], 
"values": [[1, 7, 90], [1, 4, 130], [1, 3, 90], [1, 2, 50], [1, 1, 20], [2, 1, 20], [3, 3, 100], [3, 2, 40]]}*/

615. 平均工资：部门与公司比较

select pay_month,department_id,
    case when avg>total then 'higher'
    when avg=total then 'same'
    else 'lower' end as comparison
from
(select pay_month,department_id,avg(amount) as avg,max(total) as total
from
(select pay_month,department_id,
       amount,
       avg(amount)over(partition by pay_month) as total
from
(select a.employee_id as employee_id,
    a.amount as amount,
    substr(a.pay_date,1,7) as pay_month,
    b.department_id as department_id
from salary a left join employee b on a.employee_id=b.employee_id) a )b 
group by pay_month,department_id) c 
order by pay_month desc,department_id asc

select distinct pay_month,department_id,
    case when a1>a2 then 'higher' 
    when a1<a2 then 'lower' else 'same' end as comparison
from
(select 
    substr(a.pay_date,1,7) as pay_month,
    b.department_id as department_id,
    avg(amount)over(partition by pay_date,department_id) as a1,
    avg(amount)over(partition by pay_date) as a2
from salary a left join employee b on a.employee_id=b.employee_id) a
order by pay_month desc ,department_id asc

618. 学生地理信息报告

select 
    max(if(continent='America',name,null)) as America,
    max(if(continent='Asia',name,null)) as Asia,
    max(if(continent='Europe',name,null)) as Europe
from 
(select *,row_number()over(partition by continent order by name) as r
from student) a 
group by r

1045. 买下所有产品的客户

-- 表里有客户重复购买同种产品
-- 客户表里的产品有可能不源自于产品表 (测试中未发现此情况,但应谨慎)

select distinct customer_id
from customer 
where product_key in (select * from product)  # 防止客户表中某些产品不源于产品表
group by customer_id 
having count(distinct product_key) = (select count(distinct product_key) from product) #通过数数找购买了所有产品的客户


select distinct c.customer_id 
from product p 
left join customer c # 防止客户表中某些产品不源于产品表
on p.product_key = c.product_key
group by c.customer_id 
having count(distinct p.product_key) = (select count(distinct product_key) from product) #通过数数找购买了所有产品的客户

512. 游戏玩法分析 II

select player_id, device_id
from activity
where (player_id, event_date) in 
(select player_id, min(event_date)
from activity
group by player_id)

--或者窗口函数

 550. 游戏玩法分析 IV

select round(count(distinct player_id)/(select count(distinct player_id) from Activity),2) as fraction
from Activity
where (player_id,date_sub(event_date,interval 1 day)) in (
    select player_id,min(event_date)
    from Activity
    group by player_id
)

--mysql中date_sub要用interval 1 day

1097. 游戏玩法分析 V

select a.install_dt,count(a.player_id) as installs,
round(count(b.player_id)/count(a.player_id),2) as Day1_retention
from 
(select player_id,min(event_date) as install_dt
from Activity
group by player_id) a left join Activity b 
on a.player_id=b.player_id
and date_add(a.install_dt,interval 1 day)=b.event_date
group by a.install_dt

 1098. 小众书籍

 

--别人
--细节 
--1、join on 和 where 选择不同，on是连接时，where是连接后筛选 
--2、dispatch_date条件位置的放置很关键，它必须放在on后面，如果where后面，则会将没有购买的信息过滤掉 
--3、如果将available_from放到on后面，则对left join 无影响，无法按条件去除信息
select b.book_id, name
from books b left join orders o
on b.book_id = o.book_id and dispatch_date >= '2018-06-23'
where available_from < '2019-05-23'
group by b.book_id
having ifnull(sum(quantity), 0) < 10

--我
select book_id,name
from(
select b.book_id as book_id,b.name as name,
sum(quantity)over(partition by b.book_id) as num
from 
    (select * from Orders 
    where dispatch_date>='2018-06-23') a 
right join (
    select * from Books
    where datediff('2019-06-23',available_from)>30) b 
on a.book_id=b.book_id) c
where num<10 or num is null
group by book_id


--注意，left join条件写在on中，有一条不符合，保留左边，右边都为null
select *
from books b left join orders o
on b.book_id = o.book_id and dispatch_date >= '2018-06-23'

结果：
[1, "Kalila And Demna", "2010-01-01", 2, 1, 1, "2018-11-05"],
[1, "Kalila And Demna", "2010-01-01", 1, 1, 2, "2018-07-26"],
[2, "28 Letters", "2012-05-12", null, null, null, null],
[3, "The Hobbit", "2019-06-10", 3, 3, 8, "2019-06-11"],
[4, "13 Reasons Why", "2019-06-01", 5, 4, 5, "2019-06-20"],
[4, "13 Reasons Why", "2019-06-01", 4, 4, 6, "2019-06-05"],
[5, "The Hunger Games", "2008-09-21", null, null, null, null]

​​​​​​1107. 每日新用户统计

select login_date,count(*) as user_count
from(
select user_id,min(activity_date) as login_date
from Traffic
where activity = 'login'
group by user_id) a 
where login_date<='2019-06-30'
and login_date>=date_sub('2019-06-30',interval 90 day)
group by login_date
order by login_date

--5对应6.21，先过滤再查询
select user_id,min(activity_date) as login_date
from Traffic
where activity = 'login' and activity_date>'2019-03-01'
group by user_id

[1, "2019-05-01"],
[2, "2019-06-21"],
[4, "2019-06-21"],
[5, "2019-06-21"]

--5没有了，先查询再过滤
select user_id,min(activity_date) as login_date
from Traffic
where activity = 'login'
group by user_id
having login_date>'2019-03-01'

[1, "2019-05-01"],
[2, "2019-06-21"],
[4, "2019-06-21"]

1159、市场分析

 

  

select a.user_id as seller_id,
case when a.favorite_brand =d.item_brand then 'yes' else 'no' end as 2nd_item_fav_brand 
from Users a left join 
(select seller_id,item_brand
from(
select a.seller_id,b.item_brand,row_number()over(partition by a.seller_id order by a.order_date) as rk
from Orders a join Items b 
on a.item_id=b.item_id) c 
where rk=2
group by 1,2) d 
on a.user_id=d.seller_id

 1212、查询球队积分

Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
此表的主键是 team_id，表中的每一行都代表一支独立足球队。

Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
此表的主键是 match_id，表中的每一行都代表一场已结束的比赛，比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。

积分规则如下：

• 赢一场得三分；
• 平一场得一分；
• 输一场不得分。

写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id  升序排序。

查询结果格式如下：

Teams table:
+-----------+--------------+
| team_id   | team_name    |
+-----------+--------------+
| 10        | Leetcode FC  |
| 20        | NewYork FC   |
| 30        | Atlanta FC   |
| 40        | Chicago FC   |
| 50        | Toronto FC   |
+-----------+--------------+

Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
+------------+--------------+---------------+-------------+--------------+
| 1          | 10           | 20            | 3           | 0            |
| 2          | 30           | 10            | 2           | 2            |
| 3          | 10           | 50            | 5           | 1            |
| 4          | 20           | 30            | 1           | 0            |
| 5          | 50           | 30            | 1           | 0            |
+------------+--------------+---------------+-------------+--------------+

Result table:
+------------+--------------+---------------+
| team_id    | team_name    | num_points    |
+------------+--------------+---------------+
| 10         | Leetcode FC  | 7             |
| 20         | NewYork FC   | 3             |
| 50         | Toronto FC   | 3             |
| 30         | Atlanta FC   | 1             |
| 40         | Chicago FC   | 0             |
+------------+--------------+---------------+

select a.team_id,a.team_name,
    sum(if(team_id=host_team,
    (if(host_goals>guest_goals,3,(if(host_goals=guest_goals,1,0)))),
    (if(guest_goals>host_goals,3,(if(guest_goals=host_goals,1,0)))))) as num_points
from Teams a left join Matches b 
on a.team_id = b.host_team or a.team_id = b.guest_team
group by 1,2
order by num_points desc,team_id asc

1225、报告系统状态的连续日期

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.
Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.
 

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+

Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。

select * from(
select period_state,min(fail_date) as start_date,max(fail_date) as end_date
from(
select 'failed' as period_state,fail_date,row_number()over(order by fail_date asc) as rk
from Failed 
where fail_date between '2019-01-01' and '2019-12-31') a 
group by date_sub(fail_date,interval rk day)
union all
select period_state,min(success_date) as start_date,max(success_date) as end_date
from(
select 'succeeded' as period_state,success_date,row_number()over(order by success_date asc) as rk
from Succeeded 
where success_date between '2019-01-01' and '2019-12-31') a 
group by date_sub(success_date,interval rk day)) b 
order by start_date asc