一、典型问题
1、求最大
思路:排序 / 子查询
select * from employees
order by hire_date desc
limit 0,1;
子查询查最大最小
select * from employees
where hire_date = (select max(hire_date) from employees);
推荐优先使用子查询,因为如果最大的同时有N个,子查询可以全部查询到,但排序只能排一个
口试:
-
如果我只需要知道该字段的最值的具体value,那我直接select max from table即可,当然order by XX desc 接limit 0,1也可以
-
但是实际上我在业务中会用子查询,比如我想知道某一天被购买次数最多的商品名称,这个时候有可能当天有2个商品正好销量都是1000,都是第一 这时候在用排序肯定不行
我会用 select productname from table 接where sales=select maxsales from table -
求第N名也是同样的道理,如果只关注一个字段的value(比如今天的销量第三商品的具体销售值),直接select distinct sales as Distinctsales from table 后面接order by Dis…limit就好了
2、求第n名
第一个坑:重复问题
- 对主键排序 VS 对其他属性排序
1、对主键员工排序 查找入职员工时间排名倒数第三的员工所有信息
select * from employees
order by hire_date desc
limit 2,1;
2、对入职时间排序 比如排第一的有3个人,但入职时间一致,即重复。
就只能子查询
select * from employees
where hire_date=(
select distinct hire_date from employees//关于distinct问题见下
order by hire_date DESC limit 2,1);
执行过程中distinct是优先处理的,即如果我只关注倒数第三的日子进来的所有员工就用distinct
但如果我关注的是员工数量倒数第三的,就不要用distinct
******/
第二个坑:正确返回NULL问题
查第2高的工资,可实际上大家的工资都是一样的,不存在第二名
- 此时,如果直接排序会直接报错,什么都不返回
- 但用两次select,若有值第二次的Select会返回正确值,若无,第二次的select会返回NULL.
第二高的薪水:
-- 解决方案 (1) 双Select 法
SELECT (
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1,1)
AS SecondHighestSalary
******注意在上面这个子查询临时表方法中最后是没有From 的
******一般此时我们会用dual作为规范代替,dual是最小单位临时表
常用语特殊语句的查询和计算:eg "select 22*33 from dual"
from Employee; ×
From dual; √
-- 解决方案 (2) ifnull函数
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1)
, NULL)
AS SecondHighestSalary
→综上:查询第N名问题用子查询最优!!
另外别忘了select后面跟子查询一定要记得起别名,否则会报错???
3、分组 (窗口函数)
举例而言:有两个人拿到了苹果
①只能告诉你重复值是“苹果”,最多能看到所有重复中的第一条数据
②不仅告诉你苹果,还能告诉所有的拿到苹果的人的名字性别等其他信息`
①、只关注分组后聚类数据(sum avg count):group by和having
select Email from Person
group by Email
having count(*)>1; #此答案可以拓展成重复数量大于N的数据
☆☆☆每组占比问题:对上面的聚类数据进一步计算可以使用窗口函数
☆☆☆over() 和 group by 联用的情况
最常见的我需要算每组占比
一般也可以用子查询——
SELECT admin_id,
COUNT(*) AS total_count_for_admin,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM review_detail), 2) AS percentage_of_total
FROM
review_detail
GROUP BY
admin_id;
但是我们这里介绍更好的方法
SELECT admin_id,
COUNT(*), #这列是每组的个数
SUM(COUNT(*)) OVER(), #这列是每组个数求和 也就是总个数
max(COUNT(*)) OVER()
FROM
review_detail
GROUP BY
admin_id
②、需要分组后的每一条数据:窗口函数
窗口函数=原表加一列组内排序
窗口函数=原表加一列组内排序
窗口函数=原表加一列组内排序
窗口函数是不去重分组,每一个组就叫一个窗口。
其实窗口函数本质上是更灵活的分组,因为如果只用group by ,只能seletc出一些聚合函数max(),min(),count(),即无法查到组内的某条数据的其他字段。
如果要达到group这种去重分组,也只需要利用rank函数,具体见例子中
SELECT * from
(select *,
row_number() over(partition by Email) as '组内序号'
from
leetcod196) as r1 #给这个窗口表取名窗口1 r1
where '组内序号'>1;
总体思路:
- 先得到若干窗口表格,其中 rn 是组内的序号
select *,
row_number() over(partition by Email order by Id) as 组内序号
from
leetcod196
得到下结果相当于多了一列组内排序
2. 再进行子查询,在后面跟 “where ‘组内序号’>1” 判断即可
SELECT * from
(select *,
row_number() over(partition by Email order by Id) as '组内序号'
from leetcod196
) as r1 ###一定要记得给这个窗口表取名窗口1 r1
where '组内序号'>1
如果没有这步嵌套后续无法跟Where语句进行判断
这个思路很重要:相当于先查出来一个临时表(这个表并不真实存在),当需要对这个临时表进行2次处理,就把它再select *取出来
核心原因是:窗口函数的执行顺序是在where之后的select,因此如果直接where rn>1,一定报错,此时表中根本没有rn这一列
**同时请注意,窗口函数形成的 【表格】 一定要进行命名!!!**
③、快手抽查/另类去重:分组后显示每组n条数据
(静态筛选条件)例子1:找出每个大学(商品)的第一条数据
SELECT * FROM
( SELECT *, row_number() over ( PARTITION BY university ) AS rk
FROM user_profile ) tmp
WHERE rk = 1
上面加上ORDER BY gpa DESC 就可以找到组内某一个最大值的数据,但是请注意
存在并列第一的时候
如果需要都出现用rank
如果希望就只要取一个用row_number
此时上面的1是静态的,即每个组都是取1个数据
(动态筛选条件)例子2:(+with as )分组抽查题 快手
- 从后往前的思路:
- 最后的表应该是:【原来的大表+一列分组窗口序号+一列每组需要取多少个数量Xn】→【根据序号筛选where】。。。其中第三个需要取多少这个数字是动态的,根据分组有所不同的(每个组要抽的数据不同),因此一定是需要一个join操作,
- 表tmp:需要with as 一个临时表tmp,这个表包含了分组Key+每个组需要的动态个数x 这个Xn=(X/总个数)*每组个数
表tmp2:原来的大表要加上一列分组窗口序号列
with tmp as (
SELECT
admin_id,
COUNT(*) AS total_count,
ROUND(4 / SUM(COUNT(*)) OVER() * COUNT(*),1) AS target_count
FROM
review_detail
GROUP BY
admin_id ) ,
tmp2 as (
SELECT * ,
ROW_NUMBER() over (PARTITION by admin_id) as seq
FROM review_detail)
SELECT *
FROM tmp a
LEFT JOIN tmp2 b USING (admin_id)
where seq<=target_count
注1: 不同窗口函数区别
-
rank函数:如果有并列名次的行,会占用下一名次的位置。
比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。 -
dense_rank函数:如果有并列名次的行,不占用下一名次的位置。
比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。 -
row_number函数:也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
4、分组问题
①分组后计数count
- 不分组 取count,整个结果只有一行
- 分组 取count,会计算每组个数 结果有多行
- 分一次组,算组内一种:工资是17000的人的个数
SELECT COUNT(salary) as '数量',salary from employees
GROUP BY salary
having salary=17000
- 第一点:无论如何优先用 count(字段) 而不是count(*)
- 第二点:group by后面只能跟 having
- 分一次组,算组内所有:求不同工资的人的个数
SELECT COUNT(salary) as '数量',salary from employees
GROUP BY salary
ORDER BY salary DESC
可以直接进行排序
- 分两次组
请仔细思考:依据先A再B分组 和先B再A的区别
没有区别,本质上相当于把A\B两个字段拼成一个字段C,并对这个C字段分一次组
也就说如果计数,只有当某两行的A、B完全相同,才会组在一起
并且分组次数越多,组合难度越大,数据行数越多
②表连接后的分组
精简笔记 该块见下文 连接问题中的3、三表连接+分组计算
③☆☆ group的依据是原表中没有的字段☆☆
结论:在正常数据库中,逻辑顺序先进行Group分组,再select取数的。
因此如果group的依据是一个原表中没有的字段,语句是无法被执行的,此时需要子查询嵌套
具体场景见此题
最常见的情况就是根据 if生成的新字段分组
select age_cut,
count(*) number
from
(select *,
if(age<25 or age is null,'25岁以下','25岁及以上') age_cut
from user_profile ) a
GROUP BY age_cut
5、查找连续出现N次的数据
场景:找出被连续购买的商品
①窗口函数lead(优先学习)
select distinct num as ConsecutiveNums
from (select
num,
lead(num,1) over()as num1, #返回num下面第1个数据
lead(num,2)over()as num2 #返回num下面第2个数据
from logs
) as c
where c.num = c.num1 and c.num1 = c.num2
然后出现三列相等即:第一个数字=第二个数字=第三个数字
这就是连续出现三次的数字
缺点是:如果老板问你连续出现100次的数据就很麻烦,需要用下面的变量法
②变量赋值法(没看懂)
#初始化两个变量,一个为pre,记录上一个数字;
#一个为count,记录上一个数字已经连续出现的次
#然后调用if()函数,如果pre和当前行数字相同,count加1极为连续出现的次数;如果不同,意味着重新开始一个数字,count重新从1开始。
#最后,将当前的Num数字赋值给pre,开始下一行扫描。
select
Num, #当前的Num 数字
if(@pre=Num,@count := @count+1,@count := 1) as count_times,
@pre:=Num is not null#将当前Num赋值给pre
from Logs as a;
select @pre:= null,@count:=1 as pc #这里需要别名
#上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。
#②将上面表的结果中,重复次数大于等于3的数字选出,再去重即为连续至少出现三次的数字。
select
distinct Num as ConsecutiveNums
from
(select
Num,
if(@pre=Num,@count := @count+1,@count := 1) as nums,
@pre:=Num
from Logs as l ,
(select @pre:= null,@count:=1) as pc
) as n
where nums >=3;
#注意:pre初始值最好不要赋值为一个数字,因为不确定赋值的数字是否会出现在测试表中
二、连接问题
*总结连接的效果:
①A inner join B:取交集
②A left join B:取A全部,B没有对应的值,则为null
③A right join B:取B全部,A没有对应的值,则为null
④A full outer join B:取并集,彼此没有对应的值为null
-
Where 和 On 连接主键的区别:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
因此:在大部分情况下只推荐用on连接,如果必须要保证链接后的表不能有Null数据,则直接用内连接即可,因此不推荐Where
1、自连接(员工薪资大于经理)
在同一个表内,有两个字段的含义是重复的情况的处理
比如下表中ManagerId和 Id其实是有重复的
此时就要用到自连接,自连接的关键是把一张表复制成两张用。
Id | Name | Salary | ManagerId |
---|---|---|---|
1 | Joe | 70000 | 3 |
2 | Henry | 80000 | 4 |
3 | Sam | 60000 | NULL |
4 | Max | 90000 | NULL |
select * from employee as A
left join employee as B
on A.ManagerId=B.Id
这个自连接的效果如下
完整回答
select A.Name as 'Employee' from
employee as A
left join employee as B
on A.ManagerId=B.Id
where A.Salary>B.Salary;
2、datadiff函数作为连接条件
查找与之前(昨天的)日期相比温度更高的所有日期的 id 。**
SELECT a.Id from weather a
LEFT JOIN weather b
on DATEDIFF(a.RecordDate,b.RecordDate)=1
where a.Temperature>b.Temperature;
3、三表连接+分组计算
业务场景:分析每个大学不同难度的情况,计算平均做题数量(即北京大学困难题,做了几道,几人(设备)做,平均每人几道?)
- a :用户信息表(含设备、大学、性别)
- b :用户做题表(含设备、题号)
- c :题目细节表(含题号、难度)
第一步:想象联表。分清楚主表是b,然后加上其他分表需要的字段, 就可以看到合并后的表。
对应代码↓
SELECT a.university ,c.difficult_level, b.*
FROM user_profile a,
question_practice_detail b,
question_detail c
where a.device_id=b.device_id
and b.question_id=c.question_id
第二步:这个时候就可以只看此联表,并且分析的是每个大学 每个难度的情况 所以是group两次
SELECT a.university ,c.difficult_level,b.device_id,
COUNT( b.question_id) '问题',
COUNT(b.device_id) '设备',
COUNT(DISTINCT b.device_id) '去重设备',
COUNT(b.device_id) /COUNT(DISTINCT b.device_id) 平均答题数
FROM user_profile a,question_practice_detail b,question_detail c
where a.device_id=b.device_id and b.question_id=c.question_id
GROUP BY a.university,c.difficult_level
浙江大学简单题中,设备2315 1个人做了5道题,因此平均答题数是5
4、json数组并表问题连接问题FIND_IN_SET
当两个连接键中有一列的元素存在list的时候用FIND_IN_SET这个函数
https://blog.csdn.net/windowsxp2018/article/details/108688861
- FIND_IN_SET( str1 , str2 ) 返回的是str1在str2的位置
SELECT FIND_IN_SET('b', 'a,b,c,d'); 返回结果为2
TRIM( ']' FROM A.account_id) 是对A.account_id这一列两边都去除‘]’
注意trim( 一定要紧跟括号,否则报错
SELECT account_name ,COUNT(*) as 量级 FROM
(SELECT A.*,B.account_id as key_id, B.account_name
FROM review_detail A
JOIN account_detail B ON FIND_IN_SET(B.account_id, TRIM( '[' FROM TRIM( ']' FROM A.account_id))) > 0)c
GROUP BY account_name
ORDER BY 量级 DESC
三、电商题目
常见指标
- GMV 商品交易总额(Gross Merchandise Volume,简称GMV)
1、计算GMV、仅计算每个商品(每组)的聚合指标
- date_format (字段 , ‘%Y-%m-%d %H:%i:%s’);
- 结果:2020-12-07 22:18:58
- 注意点是参数别忘了加引号
SELECT
date_format( event_time, '%Y-%m' ) MONTH,
sum( total_amount ) GMV
FROM
tb_order_overall
WHERE STATUS <>2 and year(event_time)=2021
GROUP BY
MONTH
HAVING GMV >100000
order by GMV
##第二题
SELECT
product_id,
ROUND(sum(if_click)/COUNT(product_id),3) AS ctr ,
ROUND((sum(if_cart)/sum( if_click )) ,3)as cart_rate,
ROUND((SUM(if_payment)/sum(if_cart)) ,3)as payment_rate,
ROUND((SUM(if_refund)/SUM(if_payment)),3)as refund_rate
FROM
tb_user_event
WHERE DATE_FORMAT(event_time,'%Y-%m')= '2021-10'
GROUP BY
product_id
HAVING
refund_rate <= 0.5
ORDER BY product_id
可以看到这里如果只要观察分组后的,每一组的“聚合情况”如
- 每组计数count
- 求和、最大最小等 sum max min
都不需要用上面的子查询
2、计算复购人数(固定节点往前90天) 条件计数 count(case when)
思路详解:用窗口函数的rank标记第二次购买的数据
中分别对product_id,uid进行分窗口,然后计算rank
下表中rk>1的时候说明该uid对该商品购买了第二次
因此先三表联立得到下表,
然后再对这个表进行where筛选时间段
最后再通过一个新表对其进行group by product_id,并条件计算rk>1的个数即可
SELECT
product_id,
round( count( DISTINCT ( CASE WHEN ranking > 1 THEN uid ELSE NULL END ))/ count( DISTINCT uid ), 3 ) rate
FROM
(
SELECT
product_id,
uid,
event_time,
ROW_NUMBER() over ( PARTITION BY product_id, uid ORDER BY event_time ) ranking
FROM
tb_order_detail
LEFT JOIN tb_product_info USING ( product_id )
LEFT JOIN tb_order_overall USING ( order_id )
WHERE
tag = '零食'
AND STATUS = 1
AND datediff(( SELECT max( event_time ) FROM tb_order_overall ), event_time )< 90
) t
# 这里有一个关键的地方,就是用表中最大时间为当前节点,向前计算90天
# 而不是每个商品都单独考虑一个时间节点
# 但现实中上面的max(event_time)也可以换成Now(),或者其他任意一个固定点
GROUP BY
product_id
ORDER BY
rate DESC,
product_id ASC
LIMIT 3
四、替换
1、替换表中数据(update)
题目:将男女互换
方法一:流程控制case 或 if
update salary
set sex = case sex
when 'm' then 'f'
when 'f' then 'm'
end;
方法二:replace 用空值去替换变量字符
update salary
set sex = replace('fm',sex,'');
#用空值去替换sex变量( sex=m/f )
2、删除重复数据
DELETE from leetcod196 where id in
(
SELECT id from
(select *,
row_number() over(partition by Email order by Id) as rn
from leetcod196) as r1 #给这个窗口表取名窗口1 r1
where rn>1
);
五、建表规范性
1、注意标点
Create table If Not Exists Logs (id int, num int);
Truncate table Logs;
insert into Logs (id, num) values ('1', '1');
insert into Logs (id, num) values ('2', '1');
insert into Logs (id, num) values ('3', '1');
insert into Logs (id, num) values ('4', '2');
insert into Logs (id, num) values ('5', '1');
insert into Logs (id, num) values ('6', '2');
insert into Logs (id, num) values ('7', '2');
2、基本例子
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
插入NULL值的时候不需要加单引号
insert into Employee (id, name) values (null, 'Henry');
insert into Employee (id, name) values ('3', 'Sam');