2022.09.15 学习笔记

sql

all any some

All：对所有数据都满足条件，整个条件才成立 ，例如：5000大于所有返回的薪水
Any：只要有一条数据满足条件，整个条件成立，例如，5000大于薪水中的3000,10000,7000
Some的作用和Any一样 。

select order_id
from OrdersDetails
group by order_id
having max(quantity) > 
all(select avg(quantity) from OrdersDetails group by order_id)

avg

#统计date不为null的数据占比
avg(date is not null) 
等价于
sum(if(date is not null,1,0))/count(*)

select round(sum(if(a.event_date is not null, 1, 0))/count(*), 2) fraction
from 
    (select player_id, min(event_date) as login
    from activity
    group by player_id) p 
left join activity a 
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1

datediff()

这里注意！！DATEDIFF()两个参数与三个参数的时候startdate和enddate前后顺序是相反的

DATEDIFF(datepart,startdate,enddate)
DATEDIFF(enddate,startdate)