力扣SQL刷题9

1107. 每日新用户统计-勉强

题型：每个日期中首次登录人数
解答：从原表中用按用户分组后min(日期)提取出首次登录的记录，然后对新表按日期分组计数
首次登录 — 按用户分组后min(日期)

SELECT 
    login_date,
    count(user_id) user_count 
FROM (  
    SELECT 
        min(activity_date) login_date, 
        user_id
    FROM Traffic
    WHERE activity= 'login'
    GROUP BY user_id
) t
WHERE DATEDIFF('2019-6-30', login_date) <= 90
GROUP BY login_date;

2019-06-30前90天内 — datediff(’ 2019-06-30’,日期)<=90

579. 查询员工的累计薪水 - 各种绕

题型：说实话，读题读的心累，更不用说总结题型了
解答：累计求和–窗口函数
筛选掉最近的一个月：where (Id,Month) not in
(select Id,max(Month)
from Employee
group by Id)

select *
from
(
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
        group by Id) 
) a
order by Id asc,Month desc

大佬的答案，少一层嵌套：

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 group by id)
order by id, month desc

易错点：range与rows区别

窗口函数中，range 2 PRECEDING与rows 2 PRECEDING的区别：

range是当前值减2，rows是当前行往下2行，这里的order by是月份，用range意味着7月后面必须是6月和5月才能加进来，用rows则是把7月后面实际跟着的4月、3月加进来。
在这题中，只有求连续3个月的累加，才能通过案例

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

题型：在每个月中，全部门的平均薪资和各部门平薪资比较
解答：整在一起太复杂----建临时表with，分开解决

虽然勉勉强强，但还是自己做出来的：
t1:每个工资发放日的公司平均工资
t2:每个工资发放日的各部门平均工资

with t1 as
(select date_format(pay_date,'%Y-%m') as d,avg(amount) avgm
from salary
group by date_format(pay_date,'%Y-%m'))

, t2 as
(select date_format(s.pay_date,'%Y-%m') as d,e.department_id,avg(amount) avgm
from salary s left join employee e on s.employee_id = e.employee_id
group by date_format(s.pay_date,'%Y-%m'),e.department_id)

select t2.d pay_month,t2.department_id,
    (case when t2.avgm>t1.avgm then "higher"
    when t2.avgm<t1.avgm then "lower"
    else "same" end ) as comparison
from t2 left join t1 on t2.d=t1.d
order by 1 desc,2

casewhen用法
case when 条件1 then 结果1
when 条件2 then 结果2
else 条件3 end

with建临时表注意点

只写成这样，不合规：

with t1 as (select * from salary)

写成这样，通过： with临时表后要使用

with t1 as (select * from salary)
select * from t1

with两个临时表时：
这样不合规：

with t1 as (select * from salary)
, t2 as (select * from salary)
select t1.* from t1 join t2

这样正确：

with t1 as (select * from salary)
, t2 as (select * from salary)
select t1.* from t1 join t2