MySQL例题:业务查询

文章访问量:

MySQL查询例题

大致回顾一下思路。

任务描述

本关任务:查询某网站每个日期新用户的次日留存率。

相关知识

某网站每天有很多人登录,请你统计一下该网站每个日期新用户的次日留存率。 有一个登录(login)记录表,简况如下:

,

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了该网站,因为是第1次登录,所以是新用户。

第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了该网站,因为是第2次登录,所以是老用户。

最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了该网站,因为是第2次登录,所以是老用户。

请你写出一个sql语句查询每个日期新用户的次日留存率,结果保留小数点后面3位数(3位之后的四舍五入),并且查询结果按照日期升序排序,上面的例子查询结果如下:

,

查询结果表明: 2020-10-12登录了3个(user_id为2,3,1)新用户,2020-10-13,只有2个(id为2,1)登录,故2020-10-12新用户次日留存率为2/3=0.667; 2020-10-13没有新用户登录,输出0.000; 2020-10-14登录了1个(user_id为4)新用户,2020-10-15,user_id为4的用户登录,故2020-10-14新用户次日留存率为1/1=1.000; 2020-10-15没有新用户登录,输出0.000; 提示: 1、本题的新用户不是真正的新用户定义,是本题给出的新用户定义。

2、MYSQL里计算日期t2与日期t1差的函数为:datediff(t2,t1)。

3、round(x,y):按y位小数,对x四舍五入。

4、ifnull函数的语法:

  1. ifnull(expression_1,expression_2);

如果expression_1不为NULL,则ifnull函数返回expression_1; 否则返回expression_2的结果。ifnull函数根据使用的上下文返回字符串或数字。如果要返回基于true或false条件的值,而不是null,则应使用if函数。

5、MySQL语句with是 MySQL 8.0中的一个新特性,用于帮助简化复杂查询以及提高查询效率。它是一种临时表的方式,得到的结果集可以作为查询的结果集。在with语句中,可以定义多个别名,然后嵌套使用。

  1. with t1 as (
  2. select col1 from table1
  3. ),
  4. t2 as (
  5. select col1 from table2
  6. )
  7. --使用
  8. select * from t1 inner join t2 on t1.col1 = t2.col1;

在这个例子中,我们定义了两张表,t1和t2,然后使用了这两张表来进行查询,这样我们就可以避免在查询语句中多次嵌套使用相同的子查询,并且也不需要为每个查询定义一个临时表。

注意2:本次实验切换到了mysql8.0,要注意以下问题: 1、order by的关键字段,要来自select关键字段; 2、group by的关键字段,要和select关键字段一致。

示例1 输入: drop table if exists login; create table login ( id int(4) not null, user_id int(4) not null, client_id int(4) not null, date date not null, primary key (id));

insert into login values (1,2,1,’2020-10-12′), (2,3,2,’2020-10-12′), (3,1,2,’2020-10-12′), (4,2,2,’2020-10-13′), (5,1,2,’2020-10-13′), (6,3,1,’2020-10-14′), (7,4,1,’2020-10-14′), (8,4,1,’2020-10-15′);

输出: date p 2020-10-12 0.667 2020-10-13 0.000 2020-10-14 1.000 2020-10-15 0.000

很明显,关键点在于查找新用户和次日。次日很明显了,直接left join+DATE_ADD就行。

写者注

为什么不用datediff?主要是之前写leetcode的时候发现datediff无法处理2016-02-29和2016-03-01这种情况。会导致datediff=NULL而非等于1。所以我个人习惯用DATE_ADD来判断日期。

新用户比较麻烦。简单来说,先对login表按用户分组,再查找每个用户最早登录的日期(即MIN(date))。再用求出来的最小值去匹配来进行筛选,最后就能得到每个用户最早登录的日期。

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT A.date,IFNULL(ROUND(COUNT(DISTINCT E.user_id)/COUNT(DISTINCT C.user_id),3),0) AS p
FROM login AS A
LEFT JOIN(
SELECT *
FROM login AS B  
WHERE B.date=(
    SELECT MIN(D.date)
    FROM login AS D 
    WHERE B.user_id=D.user_id
    GROUP BY D.user_id
    LIMIT 1
)) AS C
ON A.date=C.date AND A.user_id=C.user_id
LEFT JOIN login AS E 
ON A.user_id=E.user_id AND DATE_ADD(A.date,INTERVAL 1 DAY)=E.date
GROUP BY A.date
ORDER BY A.date ASC
########## End ##########

其它的就没啥了,LEFT JOIN也是我个人习惯。主要是方便聚合函数计算非NULL值。

任务描述

有的同学会购买网上训练课程来学习,某教育网站对购买记录会产生订单存到数据库,有一个订单信息表(order_info),表的部分内容如下:

,

第1行表示user_id为557336的用户在2021-10-10的时候使用了client_id为1的客户端下了C++课程的订单,但是状态为没有购买成功。第2行为购买成功。

查询满足以下条件的用户: 在2021-10-15以后,如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程,那么输出这个用户的user_id,以及满足该条件的第一次购买成功的日期first_buy_date,以及满足前面条件的第二次购买成功的日期second_buy_date,以及购买成功的次数cnt,并且输出结果按照user_id升序排序。

函数提示 1、row_number() over(partition by某字段 order by 某字段):窗口函数按某字段分组,生成从1开始的顺序编号。 2、count(*) over(partition by 某字段):求分组后的总数。

注意,本次实验切换到了mysql8.0,要注意以下问题: 1、order by的关键字段,要来自select关键字段; 2、group by的关键字段,要和select关键字段一致。

示例1 输入: drop table if exists order_info; create table order_info( id int(4) not null, user_id int(4) not null, product_name char(20) not null, status char(20) not null, client_id int(4) not null, date date not null, primary key (id));

insert into order_info values (1,557336,’C++’,’no_completed’,1,’2021-10-10′), (2,230173543,’Python’,’completed’,2,’2021-10-12′), (3,230173543,’Python’,’completed’,2,’2021-10-15′), (4,557336,’Java’,’completed’,1,’2021-10-15′), (5,230173543,’Java’,’no_completed’,2,’2021-10-16′), (6,230173543,’Java’,’completed’,2,’2021-10-20′), (7,230173543,’C++’,’completed’,2,’2021-10-21′), (8,557336,’Java’,’completed’,1,’2021-10-21′), (9,557336,’离散数学’,’completed’,1,’2021-10-22′), (10,230173543,’离散数学’,’completed’,2,’2021-10-22′), (11,663466,’离散数学’,’completed’,3,’2021-10-22′), (12,663466,’C++’,’completed’,3,’2021-10-22′), (13,663466,’Python’,’no_completed’,3,’2021-10-23′), (14,663466,’Python’,’completed’,3,’2021-10-24′), (15,663466,’C++’,’completed’,3,’2021-10-25′), (16,8912311,’Java’,’completed’,1,’2021-10-25′), (17,3345600,’高等数学’,’completed’,1,’2021-10-27′), (18,3345600,’数学分析’,’completed’,1,’2021-10-28′), (19,3345600,’数据库系统’,’completed’,1,’2021-10-28′), (20,663466,’C++’,’no_completed’,3,’2021-10-29′), (21,557336,’Python’,’no_completed’,3,’2021-10-29′), (22,557336,’Python’,’completed’,2,’2021-10-29′), (23,230173543,’离散数学’,’no_completed’,2,’2021-10-29′), (24,663466,’离散数学’,’no_completed’,3,’2021-10-29′), (25,230173543,’Python’,’no_completed’,2,’2021-10-30′), (26,557336,’Python’,’completed’,2,’2021-10-30′), (27,823345,’Python’,’no_completed’,1,’2021-10-31′), (28,823345,’Python’,’no_completed’,1,’2021-10-31′), (29,823345,’Python’,’completed’,1,’2021-10-31′), (30,230173543,’Python’,’no_completed’,4,’2021-10-31′), (31,3345600,’数据库系统’,’no_completed’,1,’2021-11-01′);

输出: user_id first_buy_date second_buy_date cnt 557336 2021-10-21 2021-10-29 3 663466 2021-10-22 2021-10-24 3 230173543 2021-10-20 2021-10-21 2

这题比另外两题简单些。主要是派生表。

我一开始想在最外层用COUNT去计算购买成功的次数。但由于SQL语句的执行顺序里,SELECT是最后执行的。所以如果你想要只返回前两个购买成功的日期的话(这样你才能用MIN和MAX得到第一次和第二次成功购买的日期),那必然会在最外层的WHERE中对rk进行筛选(比如rk≤2)。这么做就会导致最后COUNT的结果不全。

所以我选择在派生表的时候就用COUNT OVER对id分组再求和,最后直接外部SELECT即可。

主要还是COUNT(xxx)OVER的用法吧,我也是在刷leetcode题的时候发现聚合函数都能当窗口函数用的。

有的同学会购买网上训练课程来学习,某教育网站对购买记录会产生订单存到数据库,有一个订单信息表(order_info),表的部分内容如下:

,

第1行表示user_id为557336的用户在2021-10-10的时候使用了client_id为1的客户端下了C++课程的订单,但是状态为没有购买成功。第2行为购买成功。

查询满足以下条件的用户: 找出双11预热期间,(2021.10.16-2021.10.31),各product_name(要呈现)的用户未完成订单率(%后显示2位小数)排前三名(降序排序,不为0,且用dense_rank函数)的user_id、rnk(名次)、incomp_rate(未完成订单率),并先按product_name升序排序,再按rnk升序排序。

函数提示 1、round(x,y):按y位小数,对x四舍五入。

2、concat(s1,s2…sn):字符串s1,s2等多个字符串合并为一个字符串。

3、dense_rank() over(partition by a字段order by b字段):窗口函数按a字段分组,按b字段排序(缺省升序)生成从1开始数值编号,多值并列但排名仅+1。 (说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6)

4、if函数是MySQL中的条件函数之一,用于在满足条件时返回一个值,否则返回另一个值。IF函数的语法如下:

  1. if(condition, true_value, false_value)

其中,condition是一个布尔表达式,true_value是满足条件时返回的值,false_value是不满足条件时返回的值。

5、在MySQL中,我们可以使用if和sum函数的组合来实现多条件计算。假设我们有一个名为”orders”的表格,其中包含了订单信息,包括订单ID、客户ID、订单金额amount和订单状态status。现在我们要计算不同状态订单的总金额,可以使用如下的MySQL查询语句:

  1. select
  2. sum(if(status = 'pending', amount, 0)) as pending_amount,
  3. sum(if(status = 'completed', amount, 0)) as completed_amount,
  4. sum(if(status = 'canceled', amount, 0)) as canceled_amount
  5. from
  6. orders;

6、MySQL语句with是 MySQL 8.0中的一个新特性,用于帮助简化复杂查询以及提高查询效率。它是一种临时表的方式,得到的结果集可以作为查询的结果集。在with语句中,可以定义多个别名,然后嵌套使用。

  1. with t1 as (
  2. select col1 from table1
  3. ),
  4. t2 as (
  5. select col1 from table2
  6. )
  7. --使用
  8. select * from t1 inner join t2 on t1.col1 = t2.col1;

在这个例子中,我们定义了两张表,t1和t2,然后使用了这两张表来进行查询,这样我们就可以避免在查询语句中多次嵌套使用相同的子查询,并且也不需要为每个查询定义一个临时表。

注意,本次实验切换到了mysql8.0,要注意以下问题: 1、order by的关键字段,要来自select关键字段; 2、group by的关键字段,要和select关键字段一致.

示例1 输入: drop table if exists order_info; create table order_info( id int(4) not null, user_id int(4) not null, product_name char(20) not null, status char(20) not null, client_id int(4) not null, date date not null, primary key (id));

insert into order_info values (1,557336,’C++’,’no_completed’,1,’2021-10-10′), (2,230173543,’Python’,’completed’,2,’2021-10-12′), (3,230173543,’Python’,’completed’,2,’2021-10-15′), (4,557336,’Java’,’completed’,1,’2021-10-15′), (5,230173543,’Java’,’no_completed’,2,’2021-10-16′), (6,230173543,’Java’,’completed’,2,’2021-10-20′), (7,230173543,’C++’,’completed’,2,’2021-10-21′), (8,557336,’Java’,’completed’,1,’2021-10-21′), (9,557336,’离散数学’,’completed’,1,’2021-10-22′), (10,230173543,’离散数学’,’completed’,2,’2021-10-22′), (11,663466,’离散数学’,’completed’,3,’2021-10-22′), (12,663466,’C++’,’completed’,3,’2021-10-22′), (13,663466,’Python’,’no_completed’,3,’2021-10-23′), (14,663466,’Python’,’completed’,3,’2021-10-24′), (15,663466,’C++’,’completed’,3,’2021-10-25′), (16,8912311,’Java’,’completed’,1,’2021-10-25′), (17,3345600,’高等数学’,’completed’,1,’2021-10-27′), (18,3345600,’数学分析’,’completed’,1,’2021-10-28′), (19,3345600,’数据库系统’,’completed’,1,’2021-10-28′), (20,663466,’C++’,’no_completed’,3,’2021-10-29′), (21,557336,’Python’,’no_completed’,3,’2021-10-29′), (22,557336,’Python’,’completed’,2,’2021-10-29′), (23,230173543,’离散数学’,’no_completed’,2,’2021-10-29′), (24,663466,’离散数学’,’no_completed’,3,’2021-10-29′), (25,230173543,’Python’,’no_completed’,2,’2021-10-30′), (26,557336,’Python’,’completed’,2,’2021-10-30′), (27,823345,’Python’,’no_completed’,1,’2021-10-31′), (28,823345,’Python’,’no_completed’,1,’2021-10-31′), (29,823345,’Python’,’completed’,1,’2021-10-31′), (30,230173543,’Python’,’no_completed’,4,’2021-10-31′), (31,3345600,’数据库系统’,’no_completed’,1,’2021-11-01′);

输出: product_name user_id rnk incomp_rate C++ 663466 1 33.33% Java 230173543 1 50.00% Python 230173543 1 100.00% Python 823345 2 66.67% Python 663466 3 50.00% 离散数学 230173543 1 50.00% 离散数学 663466 1 50.00%

其实就是两个派生表嵌套(不知道能不能这么叫)。

因为要给未完成率排序,那么你肯定得先算出未完成率才能给它排序。比较烦人的就是由于这该死的执行顺序,SELECT肯定是最后执行的,所以你在SELECT中定义的别名在当前结果集输出之前都是用不了的。

想用别名的话,必须要在外面再套一层SELECT。这个时候就能对未完成率用dense_rank对未完成率的结果集排序了。

总的来说就是一个一个结果集筛选,上一个结果集通过FROM作为下一个结果集的输入。再用SELECT作为输出给下一个结果集或者直接输出结果。

CONCAT可以最后SELECT输出结果的时候再用。

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT t2.product_name,t2.user_id,t2.rnk,CONCAT(ROUND(t2.incomp_rate,2),'%') AS incomp_rate
FROM(
    SELECT *,dense_rank()over(partition by t.product_name order by t.incomp_rate DESC) AS rnk
    FROM (
SELECT A.user_id,A.product_name,ROUND(COUNT(IF(A.status='no_completed',1,NULL))*100/COUNT(A.status),4) AS incomp_rate
FROM order_info AS A
WHERE A.date>='2021-10-16' AND A.date<='2021-10-31'
AND EXISTS(
    SELECT 1
    FROM order_info AS B 
    WHERE B.user_id=A.user_id AND B.product_name=A.product_name
    AND B.date>='2021-10-16' AND B.date<='2021-10-31'
    AND B.status='no_completed'
)
GROUP BY A.user_id,A.product_name
    )t
)AS t2
WHERE t2.rnk<=3
ORDER BY t2.product_name ASC,t2.rnk ASC
########## End ##########   

感觉还有很多地方可以优化。嘛,明年以后再说吧。

Subscribe
提醒
0 评论
Inline Feedbacks
View all comments
0
在此留下你的评论x