本文已参与「新人创作礼」活动，一起开启掘金创作之路。
4.统计初次登录第二天还登录的人
select
round(
count(distinct player_id)/(select count(distinct player_id) from activity)
#要点1:要distinct,否则统计重复
,2)
as fraction
from activity
where (player_id,event_date)
in
(
select
player_id,date(min(event_date)+1) #要点2:做一个最小值的date+1,date妙用
from activity
group by player_id)
5.选取中位数妙用
select
id,company,salary
from
(
select
id,
company,
salary,
row_number() over(partition by company order by salary) as rnk, #把自己的排名搞出来,不重复,一行一行加
count(*) over(partition by company) as cnt #把所有人的人数搞出来,记得count(*)要带星
from employee
) t
where rnk in (cnt/2,cnt/2+1,cnt/2+0.5) #涵盖单位的数量是奇数偶数,得到的人数也会是一个或者两个
6.group by 压缩写法
select
name
from employee
where id in
(
select
managerId
from
employee
group by managerId having count(managerId)>=5
)t
7.大于所有:勿需为了比大小再造一个临时表:
select c.name from Candidate c
right join Vote v
on v.CandidateId = c.id
group by v.CandidateId
having count(*) >= all(select count(*) from vote group by CandidateId)
8.这种写法也可有 (count(*)可以写在order或者having里面)
select question_id as survey_log
from
surveylog
where action='answer'
group by question_id
order by count(*) desc ,question_id asc
limit 1
having count(*)=max(order_number) ##这样错错错!
9.Operand should contain 1 column(s)
如果用 in、all这种，注意在后面查询的临时表只能有一行
10.如果本来就要group by，那么何必开窗
SELECT Id, MAX(Month) FROM Employee GROUP BY Id
10.统计出null值：
-
区分count(*）和count(s.student_id)区别 前一个是有多少条不考虑值，没有的话会返回null；后一个只算s.student_id这一列的值不会计算null，会返回0
2.ifnull(****,0) ,如果是null就返回0的函数
11.两个一样的和两个不一样的
select
round(sum(kk.tiv_2016),2) as tiv_2016
from
(
select pid ,
tiv_2016,
count(tiv_2015) over (partition by tiv_2015) as t15, #至少跟一个其他投保人在 2015 年的投保额相同。
count(lat) over(partition by lat,lon) as lt #维度和经度不能跟其他任何一个投保人完全相同,两个变量一起
from insurance
) kk
where kk.lt=1 and t15>1
