SQL知识点与真题分析

知识点

SQL基本句型

SELECT column_name, aggregate_function(column_name) # 筛选字段
FROM table_name # 在哪个表中选择字段
WHERE column_name operator value # 筛选条件
GROUP BY column_name # 筛选完的数据可以进行聚合
HAVING aggregate_function(column_name) operator value; # 筛选聚合后的值
# 这个聚合后的值，他里面包含了1或多个数据
# having是考虑了每个不同的聚合值里面包含多个数据后
# 对每个不同的聚合值筛选

ORDER BY & ASC、DESC

ORDER BY column_name1 ASC,column_name2 DESC
# ORDER BY：排序
# ASC：升序
# DESC：降序
# 意思是：先将column_name1升序处理
#        如果column_name1中有连续相同的值
#        这些连续的值对应的column_name2作降序处理

LIMIT & OFFSET

LIMIT 3 OFFSET 4;
# LIMIT：选择多少行
# OFFSET：跳过多少行
# 意思是：从上向下选择3行，途中要跳过4行再进行选择
# 一般会和 ORDER BY 一起用
# 用where in 筛选时不可以用limit！！！
# 错误师范
where s_score in(   # 这里的in只能用=
    select distinct s_score from score
    order by s_score desc
    limit 1 offset 1
)

SELECT DISTINCT

SELECT DISTINCT column_name,column_name FROM table_name;
# DISTINCT：去重
# 顾名思义，DISTINCT用于SELECT后面对列进一步筛选
# 注意：DISTINCT只能放在开头！！！
# 并且distinct会对所有的字段生效，去重顺序从左往右

DISTINCT 和 GROUP BY 的区别

distinct直接将重复去掉，group by是将相同的数据聚合起来

JOIN

SELECT s.emp_no,s.salary,dm.dept_no FROM salaries s 
JOIN dept_manager dm ON s.emp_no=dm.emp_no
# 在select之后就可以直接写上所有我们需要的列(包括其他表的列)
# 记住这个 SELECT FROM JOIN ON 的格式
# 左连接右连接确定方向的位置是left join right join那里
# 并不是=号区别左右

通配符

SELECT * FROM Websites
WHERE url (not) LIKE 'https%';
# 记住要用like这个关键字

组合查询 UNION

• Websites表
  

• apps表
  

• 实例 UNION

  SELECT country FROM Websites
  UNION
  SELECT country FROM apps
  ORDER BY country;
  # 使用union组合查询时，只能使用一条order by字句
  # 他必须位于最后一条select语句之后
  # 因为对于结果集不存在对于一部分数据进行排序
  # 而另一部分用另一种排序规则的情况
  

  输出：
  
  他会选取所有不同的country（只有不同的值）

• 实例 UNION ALL

  SELECT country FROM Websites
  UNION ALL
  SELECT country FROM apps
  ORDER BY country;
  

  输出：
  
  他会选取所有的country（也有重复的值）

IF & CASE

• IF

  SELECT if(age>=25,'25岁及以上','25岁以下') as age_cut from user_profile
  group by age_cut
  

  输出：
  
• CASE

  SELECT case when age>=25 then '25岁及以上'
  			else '25岁以下' end as age_cut
  from user_profile
  group by age_cut
  

  输出：和上面的IF一样，如果有多个条件一定要将条件明确规定好

日期

day()、month()、year()分别是提取日月年

• DATEDIFF(date1,date2)

  返回两个日期之间的天数
  这东西没用了，用timestampdiff就行
  用timestampdiff可以求所有关于时间/日期间的距离
  SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
  # 结果为1
  SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate
  # 结果为-1
  

• DATE_SUB(datepart,number,date)

  # 我们想要向 "OrderDate" 减少 45 天，这样就可以找到付款日期
  SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
  FROM Orders
  

• DATE_ADD(date,INTERVAL expr type)

  # 我们想要向 "OrderDate" 添加 45 天，这样就可以找到付款日期
  SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
  FROM Orders
  

• date_format()

  # 在某个日期字段中取出年月
  select date_format(start_time,'%Y-%m') month from a
  # 年%y，月%m，日%d，周%w，时%h，分%i，秒%s
  

SUBSTRING ( expression, start, length )

• select SUBSTRING(‘abcde’,1,2) 返回结果 ab
• select SUBSTRING(‘abcde’,2,3) 返回结果 bcd
• select SUBSTRING(‘abcde’,1,0) 返回结果为空
• select SUBSTRING(‘abcde’,0,8) 返回结果为abcde,注意后面没有空格了

SUBSTRING_INDEX
substring_index（“待截字符串”，“截取依据(字符)”，截取位置N）
注意：这个不是截取某个点，而是从左或右开始连续截取

窗口函数

• 格式

  <窗口函数> OVER (partition by <用于分组的列名> order by <用于排序的列名>)
  

  
• frame_clause
  格式

  <窗口函数> OVER (partition by <用于分组的列名> order by <用于排序的列名> rows (between) <某一行> and <某一行>)
  # 某一行的写法有以下几种
  # current row：当前行
  # unbounded preceding：某组窗口最前面一行
  # unbounded followuing：某组窗口最后面一行
  # m preceding：当前行的前m行
  # m following：当前行的后m行
  

  
• 注意
  窗口函数不能直接进行where！！需要运用窗口函数时，先用窗口函数创建一个包含所有数据的新表，再从新表上面提取需要的信息

row_number() 、 rank() 、 dense_rank()
where
如果select的字段中有用到别名的字段，这个字段的别名不能用于where

upper & concat

select cust_id,cust_name,
       upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) user_login
       from Customers
# upper：将字母变成大写
# concat：字符拼接

ROUND()

# ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM TABLE_NAME;
# column_name:必需。要舍入的字段。
# decimals:可选。规定要返回的小数位数。默认为0

TIMESTAMPDIFF()

# 求日期/时间之间的距离
# 以下示例将以月份值的形式返回2018-01-01和2018-06-01的差值
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01')
输出：正值
# 如果是求日期间的距离，一定要用date()将时间后缀去掉
# 前者时间早后者时间晚会得出一个正值，反之得到负值

TIMESTAMPADD()

# 在一个日期/时间上添加一个日期/时间间隔
select TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00')
输出 2012-08-30 12:00:00  

BETWEEN

# 在表 tb_students_info 中查询年龄在 20 到 23 之间的学生姓名和年龄
WHERE age BETWEEN 20 AND 23

真题

查找入职员工时间排名倒数第三的员工所有信息

• 入职时间相同的员工可能不止一人
• 题目信息
  
• 解题

  select * from employees
  where hire_date=(
  	select distinct hire_date from employees # 去重
  	order by hire_date desc # 降序
  	limit 1 offset 2 # 选择倒数第三的时间
  )
  # 首先同一个时期入职的员工可能不止一个，先对入职时间去重
  # 要选择入职时间排名倒数的，所以对 hire_date 降序
  # 倒数第三名：选择倒数第三的时间，跳过第一第二的时间
  # 整体思路：先将倒数第三的时间单独算出来
  #          再用 where 语句将这个时间的所有行显示出来
  

查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

• 题目信息
  
  
• 解题

  select emp_no,count(emp_no) t from salaries
  group by emp_no
  having count(emp_no)>15
  # 如果只有第一行：因为有 count(emp_no) t，他只会显示一行数据
  # 这一行数据分别是一个顶部的emp_no值、一个统计了emp_no个数的值
  # 如果有第一行和第二行：首先他会对emp_no进行聚合
  # 他有多少个聚合后的值就会有多少行数据
  # 然后count(emp_no)会对这些聚合值进行计数统计，结果体现在t字段上
  # 如果三行都有：体现在t字段上的数值不满足大于15就会被剔除
  

获取所有非manager的员工emp_no

• 题目信息
  
• 解题

  select emp_no from employees
  where emp_no not in (
  	select emp_no from dept_manager
  )
  # 这种题目其实不用表连接
  # 我们要的是非部门领导的员工 emp_no
  # 我们直接选出部门领导的员工，然后 not in 就行
  

获取所有员工当前的manager

• 题目信息
  
• 解题

  # 错误师范
  select de.emp_no,dm.emp_no manager from dept_emp de
  left join dept_manager dm on de.emp_no=dm.emp_no # 这里不能用员工来连接
  where de.emp_no not in (dm.emp_no)
  # 因为经理表员工少于员工表员工
  # 连接出来的数据并不会出现员工对应经理这样的数据
  # 只会出现员工对应的经理变成空值
  # 所以我们要用部门来作为连接
  
  # 正确示范
  select de.emp_no,dm.emp_no manager from dept_emp de  # 挑选需要的数据
  left join dept_manager dm on de.dept_no=dm.dept_no # 将两表连起来
  where de.emp_no not in (dm.emp_no)
  # 直接将两表连起来，挑选需要的数据，最后作员工筛选
  

获取每个部门中当前员工薪水最高的相关信息

• 题目信息
  
• 解题

  # 答案
  select t1.dept_no,t1.emp_no,t1.salary maxSalary from(
  	select de1.emp_no,de1.dept_no,s1.salary from dept_emp de1
  	join salaries s1 on de1.emp_no=s1.emp_no
  )t1,( # 构建的第一个表t1，描述了每个员工的部门和薪资
  	select de2.dept_no,max(s2.salary) maxSalary from dept_emp de2
  	join salaries s2 on de2.emp_no=s2.emp_no
  	group by de2.dept_no
  )t2 # 构建的第二个表t2，描述了不同部门的最大薪资
  where (t1.dept_no=t2.dept_no and t1.salary=t2.maxSalary)
  order by t1.dept_no
  # 遇到复杂的题目时，想一想可不可以自己构建两个或多个表
  # 根据这两个相等的地方选出自己想要的数据
  
  # 注意：如果要用groupby的话
  # select中的非聚合字段只能有一个
  # 并且groubyby的字段一致，如下：
  select from_date,count(emp_no),count(to_date) from dept_emp # select中的非聚合字段为from_date
  group by from_date # groubyby的字段也为from_date
  

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目信息
  
  
• 解题

  select e.emp_no,s.salary,e.last_name,e.first_name from employees e 
  join salaries s on e.emp_no=s.emp_no
  where salary =(
  	select max(salary) from salaries
  	where salary not in (select max(salary) from salaries)
  )
  # 不能用order by，那就只能用嵌套max()
  # 先选出一个最大值，将这个最大值剔除后再选一个最大值
  

查找在职员工自入职以来的薪水涨幅情况

• 题目信息
  
• 解题

  select t2.emp_no,t3.salary-t2.salary salary from(
  	select e.emp_no,s.salary from employees e 
  	left join salaries s on (e.emp_no=s.emp_no and e.hire_date=s.from_date)
  )t2 # 入职工资表
  join (select t.emp_no,s2.salary from(
   	select emp_no,max(to_date) now_day from salaries
   	group by emp_no
   )t
  left join salaries s2 on (t.now_day=s2.to_date and t.emp_no=s2.emp_no)
  )t3 # 当前工资表
  on t2.emp_no=t3.emp_no
  order by growth
  # 遇见这样复杂的题目，首先要审好题目
  # 我们算的是涨幅，只要找出一张入职工资表和一张当前工资表
  # 就可以计算出涨幅
  

用where过滤空值练习

• 题目信息
  
• 解题

  select device_id,gender,age,university from user_profile
  where age is not null
  # 记住is not null这个格式
  

分组计算练习题

• 题目信息
  
  
• 解题

  select gender,university,count(gender) user_num,avg(active_days_within_30),avg(question_cnt) from user_profile
  group by gender,university # 多重groupby
  # 题目很长，但很简单
  # 重要的是记住这个多重group的用法
  

分组过滤练习题

• 题目信息
  
  
• 解题

  select university,avg(question_cnt) avg_question_cnt,avg(answer_cnt) avg_answer_cnt from user_profile
  group by university
  having (avg_question_cnt<5 or avg_answer_cnt<20)
  # 一定一定记住：where是groupby之前进行筛选，having是之后
  

统计每个学校的答过题的用户的平均答题数

• 题目信息
  
  
  
• 解题

  select u.university,count(u.device_id)/count(distinct u.device_id) avg_answer_cnt from question_practice_detail q
  left join user_profile u on q.device_id=u.device_id
  group by u.university
  # 审好题目，理清思路，将question_practice_detail左连接user_profile
  # 再稍微计算以下就可以了
  

计算用户的平均次日留存率

• 题目信息
  
• 解题

  SELECT count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date)
  FROM question_practice_detail q1
  LEFT  JOIN question_practice_detail q2
  ON q1.device_id=q2.device_id and q2.date=date_add(q1.date,interval 1 day) # 今天与明天的匹配
  # 次日留存率 = 去重的数据表中符合次日留存的条目数目/去重的数据表中所有条目数目
  # 重点的今天与明天的匹配
  # 细节是一个设备可能在同一天回多个问题
  # 所以在设备和日期上要去重
  # 可以先将两张表的今天明天匹配好
  # 然后作去重，最后计算平均次日留存率
  

截取出年龄

• 题目信息
  
• 解题

  select substring_index(substring_index(profile,',',3),',','-1') age,count(device_id) number from user_submit
  group by age
  # 多重substring_index
  

找出每个学校GPA最低的同学

• 题目信息
  
• 解题

  select device_id,university,gpa from
  (select *,
  row_number() over (partition by university 
                     order by gpa) as rn from user_profile) t
  where t.rn = 1
  # 窗口函数不能直接进行where！！
  # 需要运用窗口函数时，先用窗口函数创建一个包含所有数据的新表
  # 再从新表上面提取需要的信息
  
  # 窗口函数格式
  row_number() over (partition by <课程名称c_name>
                      order by <课程成绩score>)
  

统计复旦用户8月练题情况

• 题目信息
  
  
• 解题

  select t1.device_id,t1.university,
  count(t2.result) question_cnt,
  sum(if(t2.result='right',1,0)) right_question_cnt
  from user_profile t1
  left join question_practice_detail t2
  on t1.device_id=t2.device_id and month(t2.date)=8
  where t1.university = '复旦大学'
  group by device_id
  # 思考sum(if)的用法和month(t2.date)=8的运用
  

计算总和

• 题目信息
  
• 解题

  select order_num,sum(item_price*quantity) total_price from OrderItems
  group by order_num
  having total_price>=1000
  order by order_num
  # 注意sum(item_price*quantity)
  # 注意having和where的区别
  

返回顾客名称和相关订单号以及每个订单的总价

• 题目信息
  
  
  
• 解题

  select c.cust_name,o3.order_num,sum(quantity*item_price) OrderTotal
  from OrderItems o3
  left join Orders o2 on o3.order_num=o2.order_num
  left join Customers c on o2.cust_id=c.cust_id
  group by c.cust_name,o3.order_num
  order by c.cust_name,o3.order_num
  # 当有多个非聚合字段和聚合字段同时出现在select中时
  # 就在groupby里加入所有非聚合字段
  

各个视频的平均完播率

• 题目信息
  
  
• 解题

  select u.video_id,
  round(sum(if(timestampdiff(second,u.start_time,u.end_time)>=v.duration,1,0))/count(u.video_id),3) avg_comp_play_rate
  from tb_user_video_log u
  left join tb_video_info v on u.video_id=v.video_id
  where year(u.start_time)=2021
  group by u.video_id
  order by avg_comp_play_rate desc
  # 这道题不难，但是细节很多
  

平均播放进度大于60%的视频类别

• 题目信息
  
  
• 解题

  select tag,
  concat(round(avg(if(timestampdiff(second,start_time,end_time)>duration,1,timestampdiff(second,start_time,end_time)/duration))*100,2),'%') avg_play_progress
  from tb_user_video_log a 
  left join tb_video_info b on a.video_id=b.video_id
  group by tag
  having avg_play_progress > 60
  order by avg_play_progress desc
  # 这道题很简单，但是细节很多
  # 注意用户开始观看和结束观看的时间差有可能会大于视频时长
  # 所以如果时间差大于视频时长，一律当1处理
  

每类视频近一个月的转发量/率

• 题目信息
  

• 解题

  select tag,sum(if_retweet) retweet_cut
  ,round(sum(if_retweet)/count(if_retweet),3) retweet_rate
  from tb_user_video_log a
  left join tb_video_info b on a.video_id=b.video_id
  where timestampdiff(day,date(start_time),date((select max(start_time) from tb_user_video_log)))<=29
  group by tag
  order by retweet_rate desc
  # 注意timestampdiff的运用
  

每个创作者每月的涨粉率及截止当前的总粉丝量

• 题目信息
  
  
  
• 解题

  select author,date_format(start_time,'%Y-%m') month
  ,round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(if_follow),3) fans_growth_rate
  ,SUM(SUM(IF(if_follow = 2, -1, if_follow))) OVER (PARTITION BY author ORDER BY date_format(start_time,'%Y-%m')) total_fans
  from tb_video_info b
  left join tb_user_video_log a on b.video_id=a.video_id
  where year(start_time)='2021'
  group by month,author
  order by author,total_fans
  # 窗口函数的运用
  # PARTITION BY author：先把每个作者分开
  # ORDER BY date_format(start_time,'%Y-%m')：再以月为单位计算粉丝总数
  

国庆期间每类视频点赞量和转发量

• 题目信息
  
  
• 解题

  select * 
  from(
  	select tag,dt
  	,sum(sum_like_cnt) over (partition by tag order by dt rows between 6 preceding and current row) sum_like_cnt_7d
      ,max(sum_retweet_cnt) over (partition by tag order by dt rows between 6 preceding and current row) max_retweet_cnt_7d
      from(
    	  select tag,date(start_time) dt
     	  ,sum(if_like) sum_like_cnt
     	  ,sum(if_retweet) sum_retweet_cnt
     	  from tb_video_info b
        left join tb_user_video_log a on b.video_id=a.video_id
        where date(start_time) between date_sub('2021-10-01',interval 6 day) and '2021-10-03'
        group by tag,dt
        )t  # 求出10-01前6天到10-03每一天的点赞量和转发量
     )t2 # 求出近一周总点赞量和一周内最大单天转发量
  where dt between '2021-10-01' and '2021-10-03'
  order by tag desc,dt asc
  # 求到t2的时候不能直接where
  # 因为一where就相当于舍弃掉10-01前6天的数据
  # 这时窗口函数只会对10-01到10-03的数据进行计算
  # 所以我们要将t2得到的结果变成一个既定事实
  # 外嵌一个select筛选t2的数据
  

近一个月发布的视频中热度最高的top3视频

• 题目信息
  
  
  
• 解题

  select video_id,round((100*wbl+5*dzs+3*pls+2*zfs)*xxd,0) hot_index
  from(
  	select a.video_id
  	,sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(a.video_id) wbl # 完播率
      ,sum(if_like) dzs # 点赞数
  	,sum(if(comment_id is not null,1,0)) pls # 评论数
      ,sum(if_retweet) zfs # 转发数
  ,1/(timestampdiff(day,max(date(end_time)),date((select max(end_time) from tb_user_video_log)))+1) xxd # 新鲜度，最近无播放天数
      from tb_user_video_log a  
      left join tb_video_info b on a.video_id=b.video_id
      where timestampdiff(day,date(release_time),date((select max(end_time) from tb_user_video_log)))<=29 # 近一个月发布的视频
      group by a.video_id
      )t
  order by hot_index desc
  limit 3 
  # 先求视频完播率、点赞数、评论数、转发数、新鲜度
  # 最后再求热度
  # 记得limit在orderby后面
  

2021年11月每天的人均浏览文章时长

• 题目信息
  
  
• 题解

  select date(out_time) dt
  ,round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec
  from tb_user_log
  where year(out_time)='2021' and month(out_time)='11' and artical_id !=0  # 筛选年月可以用这种方法
  group by dt
  order by avg_viiew_len_sec
  # 认真审题
  

每篇文章同一时刻最大在看人数

• 题目信息
  
  
• 解题

  select artical_id,max(instant_viewer_cnt) max_uv from(
  SELECT artical_id,dt,
  SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt,diff desc) instant_viewer_cnt
  FROM(
      select artical_id,in_time dt,1 diff from tb_user_log
      where artical_id != 0
      union all
      select artical_id,out_time dt,-1 diff from tb_user_log
      where artical_id != 0
  	)t1    # 将所有日期连起来
  )t2  # 用窗口函数将不同文章的diff累加
  group by artical_id
  order by max_uv desc
  # 先用union将所有日期连起来，进入页面为1，退出为-1，将等于0的文章剔除
  # 用窗口函数将不同文章的diff累加
  # 按照要求先记录用户数增加再记录减少，所以diff desc
  # 记住：如果窗口函数没有ORDER BY，窗口函数返回的是一个总的累加值
  # 如果用了ORDER BY，就是分开累加
  # 最后简单的聚合，选出最大值即可
  

2021年11月每天新用户的次日留存率

• 题目信息
  
  
• 解题

  select t1.dt,round(count(t2.uid)/count(t1.uid),2) uv_rate
  from (select uid
    ,min(date(in_time)) dt
    from tb_user_log 
    group by uid) as t1  -- 每天新用户表
  left join (select uid , date(in_time) dt
         from tb_user_log
         union
         select uid , date(out_time)
         from tb_user_log) as t2 -- 用户活跃表
  on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
  where date_format(t1.dt,'%Y-%m') = '2021-11'
  group by t1.dt
  order by t1.dt
  # 先找出每天新用户表，再找出用户活跃表，考虑到跨天活跃，要用union，顺带去重数据
  # 每天新用户表 左连接 用户活跃表
  # 如果每天新用户表找不到同一个用户且下一天有活跃的话，t2.dt就为空
  

统计活跃间隔对用户分级结果

• 题目信息
  
  

• 解题

  select user_grade,round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio # 这里的小嵌套表计数也是细节
  from(
      select uid, 
             (case when datediff("2021-11-04",max(in_time)) <= 6
                       and datediff("2021-11-04",min(in_time)) > 6 then "忠实用户"
                   when datediff("2021-11-04",min(in_time)) <= 6 then "新晋用户"
                   when datediff("2021-11-04",max(in_time)) > 6
                       and datediff("2021-11-04",min(in_time)) <= 29 then "沉睡用户"
                   else "流失用户" end) user_grade
      from tb_user_log
      group by uid   # 细节：casewhen要对uid进行聚合分析，所以要group by
  )t1
  group by user_grade
  order by ratio desc
  # 合理运用case when
  

连续签到领金币

• 题目信息
  
  
• 解题

  select uid,date_format(dt,"%Y%m") month,sum(coin) coin
  from(
      select uid,
      timestampadd(day,-diff+1,dt) start_date,dt,
      case dense_rank() over(partition by uid,timestampadd(day,-diff+1,dt) order by dt)%7
      when 3 then 3
      when 0 then 7
      else 1 end coin
      from(
          select uid,date(in_time) dt,
          dense_rank() over(partition by uid order by date(in_time)) diff      
          from tb_user_log
          where artical_id = 0 and sign_in = 1 
          and date(in_time) between "2021-07-07" and "2021-10-31" # 这个where条件限制一定要记得
      ) t1
  ) t2
  group by uid,month
  order by month,uid
  # 先按照限制条件，筛选出满足条件的用户，并用窗口函数为每个用户的日期进行排序
  # 用日期-(-排序序号+1)可以得到一个日期值
  # 如果用户是连续签到的话，这个日期值将会保持一致
  # 如果没有连续签到，这个日期值就会断开
  # 再以用户和新的日期值为窗口进行排序
  # 这时的排序值就可以用于计算得到的分数
  

统计2021年10月每个退货率不大于0.5的商品各项指标

• 题目信息
  
  
• 解题

  select product_id,
  if(round(sum(if_click)/count(if_click),3)>0,round(sum(if_click)/count(if_click),3),0.000) ctr,
  if(round(sum(if_cart)/sum(if_click),3)>0,round(sum(if_cart)/sum(if_click),3),0.000) cart_rate,
  if(round(sum(if_payment)/sum(if_cart),3)>0,round(sum(if_payment)/sum(if_cart),3),0.000) payment_rate,
  if(round(sum(if_refund)/sum(if_payment),3)>0,round(sum(if_refund)/sum(if_payment),3),0.000) refund_rate
  from tb_user_event
  where date_format(event_time,"%Y-%m") = "2021-10"
  group by product_id
  having sum(if_refund)/sum(if_payment) <= 0.5
  order by product_id
  # 细节多
  

零食类商品中复购率top3高的商品

• 题目信息
  
  
  
  
  
• 解题

  select product_id,round(sum(fre)/count(uid),3) repurchase_rate 
  from(
      select c.product_id,uid,if(count(uid)>1,1,0) fre
      from tb_order_detail c
      left join tb_order_overall b using(order_id)
      left join tb_product_info a using(product_id)
      where timestampdiff(day,date(event_time),(select date(max(event_time)) from tb_order_overall)) < 90
      and tag = '零食' and status = 1
      group by c.product_id,uid
  )t1
  group by product_id
  order by repurchase_rate desc,product_id 
  limit 3
  # 体会一下这个语句if(count(uid)>1,1,0)