sql进阶day03

以下题目均来自leetcode官网 https://leetcode.cn/
本文仅用于记录学习进度

569. 员工薪水中位数

select id, company, salary
from (
         select *,
                cast(row_number() over (partition by company order by salary desc,id desc) as signed) rk,
                cast(row_number() over (partition by company order by salary ,id ) as signed)         rk2
         from employee
     ) t
where abs(rk - rk2) = 1
   or rk = rk2;
;

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

with recursive t as (
    select *
    from numbers
    union
    select num, frequency - 1
    from t
    where frequency > 1
)
select avg(num) median
from (select *, cast(row_number() over (order by num desc,rn desc ) as signed) rn2
      from (select *, cast(row_number() over (order by num) as signed) rn
            from t) t) t2
where abs(rn - rn2) = 1
   or rn = rn2
;

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

with t as (
    select *, cast(row_number() over (partition by status order by date) as signed) rn
    from (
             select fail_date as date, 'failed' as status
             from failed
             union all
             select *, 'succeeded' as status
             from succeeded
         ) tmp
    where substring(date, 1, 4) = '2019'
)
select status period_state, min(date) start_date, max(date) end_date
from (select date, status, rn, subdate(date, rn) new_date
      from t) a
group by status, new_date
order by start_date
;

1454. 活跃用户

select distinct t2.id, name
from (
         select distinct id, login_date, rk, subdate(login_date, interval rk day) new_date
         from (
                  select *, dense_rank() over (partition by id order by login_date) as rk
                  from logins
              ) t
     ) t2
         inner join accounts a on t2.id = a.id
group by t2.id, new_date
having count(*) >= 5
;

618. 学生地理信息报告

# 怎么把各个州变成列名 解决：起别名 第一列记录美洲的人 ....
# 怎么把人的信息 放到列名下 解决：使用if标签对student中的continent进行判断
# 经过上述操作出现问题：表中有很多为null的数据，怎么让他们紧密排列
# 解决：对排名进行分组，然后对每一列求最大值
# 为什么是对排名进行分组而不是其他列？为什么求最大值？
# 第一：只有靠rk分组才能够保证 每一个 name 都不会缺失；
# 第二：根据rk分组之后，只有使用聚合函数才能够让各个分组发生聚合，所以这里使用到了max()用min()也是可以的
# 收获：聚合函数是基于当前查询的列，根据分组情况来做聚合操作的。在没有执行到聚合函数的时候，数据都处于分散状态

select min(if(continent = 'America', name, null)) America,
       min(if(continent = 'Asia', name, null))    Asia,
       min(if(continent = 'Europe', name, null))  Europe
from (select name, continent, row_number() over (partition by continent order by name) rk from student) t
group by rk
;