sql进阶day04

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

2010. 职员招聘人数 II

# 目标：用7万元 招聘最多的高级员工，剩下的前 招最多的低级员工
# 思路：对各个级别的员工根据salary进行排序
# 问题：怎么用7万元 来招聘高级程序员，怎么记录招聘员工剩下的钱，怎么知道能不能招下一个高级员工 ：
# 创建变量来保存余额，如果余额大于员工的薪水 那么标记为1 并且余额 = 余额-薪水 否则 标记为0 并且余额不变
select employee_id
from (
         select employee_id,
                experience,
                salary,
                rn,
                if(@money > salary, 1, 0)                                        as tag,
                if(@money > salary, @money := @money - salary, @money := @money) as money
                 ,
                @money
         from (select *, row_number() over (partition by experience order by salary) rn
               from candidates c,
                    (select @money := 70000) a) t
     ) t2
where tag = 1
;

with s as (
    select employee_id, 70000 - sum(salary) over (order by salary) cum_sum
    from Candidates
    where experience = 'Senior'
),
     j as (
         select employee_id,
                ifnull((select min(cum_sum) from s where cum_sum >= 0), 70000)
                    - sum(salary) over (order by salary) cum_sum
         from Candidates
         where experience = 'Junior'
     )
select employee_id
from s
where cum_sum >= 0
union all
select employee_id
from j
where cum_sum >= 0;

262. 行程和用户

select
request_at                                                         Day,
round(sum(substring(status, 1, 9) = 'cancelled') / count(*), 2) as 'Cancellation Rate'
from trips
where client_id not in (select users_id from users where banned = 'Yes')
  and driver_id not in (select users_id from users where banned = 'Yes')
  and request_at between '2013-10-01' and '2013-10-03'
group by request_at
;

569. 员工薪水中位数

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

579. 查询员工的累计薪水

#     range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内
#     rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）
SELECT Id,
       Month,
       SUM(Salary) OVER (PARTITION BY Id ORDER BY Month range 2 PRECEDING) Salary
FROM Employee
WHERE (id, Month) NOT IN (SELECT id, MAX(Month) Month
                          FROM Employee
                          GROUP BY Id)

ORDER BY Id ASC, Month DESC
;
select e1.id, e1.month, sum(e2.salary) salary
from employee e1,
     employee e2
where e1.id = e2.id
  and e1.month >= e2.month
  and e1.month < e2.month + 3
  and (e1.id, e1.month) not in (select id, max(month) month from employee group by id)
group by e1.id, e1.month
order by id, month desc;

601. 体育馆的人流量

# 思路：找到人数大于100的行，再给id进行排名，因为此时id不连续，所以可以用id-排名来判断id是否连续，其实也就判断了日期是否连续
SELECT id,visit_date,people FROM(
    SELECT id,visit_date,people,COUNT(r) OVER(PARTITION BY r) c FROM (
        SELECT id,visit_date,people,id-rank() OVER(ORDER BY id) r FROM Stadium
        WHERE people>=100
    ) A
) B
WHERE B.c>=3