【数据库与SQL】力扣刷题SQL篇（4）分组求中位数/新增/留存率/登录人数_分组求中位数向上累加

遇到中位数的题目，可以想到正向与反向

1.员工薪水中位数（分组求中位数）

此题，考察不用内置函数，利用SQL求中位数

窗口函数法 1

select Id,Company,Salary
from (
    select *, 
        row_number()over(partition by Company order by Salary)as ranking,
        count(Id) over(partition by Company)as cnt
    from Employee) as a
where ranking>=cnt/2 and ranking<=cnt/2+1
1
2
3
4
5
6
7

窗口函数法 2

SELECT ID, COMPANY, SALARY
FROM (
	SELECT *, 
		RANK() OVER(PARTITION BY COMPANY ORDER BY SALARY, ID) AS R1, 
		RANK() OVER(PARTITION BY COMPANY ORDER BY SALARY DESC, ID DESC) AS R2
    FROM EMPLOYEE) AS A
WHERE R1 BETWEEN R2 - 1 AND R2 + 1;
1
2
3
4
5
6
7

窗口函数法 3

参见：SQL复习笔记 —— 如何求中位数？

select id,company,salary
from(
	select *,
		cast(row_number() over(partition by company order by salary asc, id asc) as signed) as 'id1',
		cast(row_number() over(partition by company order by salary desc, id desc) as signed) as 'id2'
	from employee) as newtable
where abs(id1-id2)=1 or id1=id2;
1
2
3
4
5
6
7

说明：SQL中的cast()函数（参考：https://blog.csdn.net/qq_21101587/article/details/78642423）

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法：CAST (expression AS data_type)

转换类型

例如

SELECT CAST('9.0' AS decimal)  结果：9
SELECT CAST('9.5' AS decimal(10,2))  结果：9.50
SELECT CAST(NOW() AS   DATE) 结果：2017-11-27
1
2
3

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

此题一开始想到的就是这种思路

不过没想到向上累计与向下累计后，怎么求出中位数，参考大佬们的解答后，明白

参见：https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers/solution/sum-over-order-by-by-fugue-s/

select avg(number) median
from
    (select number,
        sum(frequency) over(order by number) asc_accumu,
        sum(frequency) over(order by number desc) desc_accumu
        from numbers) t1, 
    (select sum(frequency) total from numbers) t2
where asc_accumu >= total/2 and desc_accumu >=total/2
1
2
3
4
5
6
7
8

3.至少有5名直接下属的经理

select a2.Name  
from Employee as a1
left join Employee as a2
on a1.ManagerId = a2.Id 
group by a1.ManagerId 
having count(*)>=5;
1
2
3
4
5
6

4.当选者

select a.Name
from Candidate as a
inner join Vote as b
on a.id  = b.CandidateId 
group by b.CandidateId
order by count(*) desc
limit 1
1
2
3
4
5
6
7

5 用户留存分析

求每天新增用户数，以及他们第2天、30天的留存率

表1——用户登陆表user_log，字段有：

• user_id ‘用户编号’
• log_date ‘登陆时间’

参考：https://zhuanlan.zhihu.com/p/80835787

select date(t1.user_begin) as '日期',count(distinct t1.user_id) as '新增用户' ,count(distinct t2.user_id) as '第二日留存用户',
        count(distinct t3.user_id) as '第30日留存用户'
from (
    select user_id,min(log_time) as user_begin
    from user_log
    group by user_id
)t1 
left join 
(
    select user_id,log_time
    from user_log
)t2
on t1.user_id=t2.user_id
and date(t2.log_time)=date(t1.user_begin)+1
left join (
    select user_id,log_time
    from user_log
)t3
on t1.user_id=t3.user_id
and date(t3.log_time)=date(t1.user_begin)+29
group by date(t1.user_begin)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

找近90天,30天,7天的登录人数

参考：https://zhuanlan.zhihu.com/p/80835787

SELECT 
       CASE WHEN DATEDIFF(NOW(),log_date)<=90 THEN COUNT(DISTINCT user_id) ELSE NULL END AS '近90天登录人数',
       CASE WHEN DATEDIFF(NOW(),log_date)<=30 THEN COUNT(DISTINCT user_id) ELSE NULL END AS '近30天登录人数',
       CASE WHEN DATEDIFF(NOW(),log_date)<=7 THEN COUNT(DISTINCT user_id) ELSE NULL END AS '近7天登录人数'
FROM user_log
1
2
3
4
5

select 
	count(distinct case when datediff(now(),log_date )<=90 then user_id else null end) as 90_log_users, 
	count(distinct case when datediff(now(),log_date )<=30 then user_id else null end) as 30_log_users, 
	count(distinct case when datediff(now(),log_date )<=7 then user_id else null end) as 7_log_users 
from user_log
1
2
3
4
5

求用户近一个月平均登录时间间隔（按天）

select user_id, avg(diff)
from (
	select user_id,lead(log_date,1) over(partition user_id order by log_date) -log_date as diff 
	from user_log) as t 
where datediff(now(),log_date)<=30
group by user_id
1
2
3
4
5
6

更多题型

参见：https://zhuanlan.zhihu.com/p/151001421

学生成绩分析
中位数、众数、和四分位数的运用
求每个店铺订购商品的众数
求在不同分类中，店铺的信用分前100和top25%