yatbfm's blog
牛客网SQL题
yatbfm
  2023-12-01 21:39   2025-01-22 11:27    7.4k 字  32 分钟  

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

描述

有一个员工表employees简况如下:

emp_no birth_date first_name last_name gender hire_date
10001 1953-09-02 Georgi Facello M 1986-06-26
10002 1964-06-02 Bezalel Simmel F 1985-11-21
10003 1959-12-03 Parto Bamford M 1986-08-28
10004 1954-05-01 Chirstian Koblick M 1986-12-01

有一个薪水表salaries简况如下:

emp_no salary from_date to_date
10001 88958 2002-06-26 9999-01-01
10002 72527 2001-08-02 9999-01-01
10003 43311 2001-12-01 9999-01-01
10004 74057 2001-11-27 9999-01-01

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

emp_no salary last_name first_name
10004 74057 Koblick Chirstian

解析

这道题不允许使用order by进行排序，因此需要采用另一种方法，可以使用自连接的方法。

1
2
3
4
5
6
7
8
9
10
select
    s1.salary
from
    salaries s1
    join salaries s2
    on s1.salary <= s2.salary
group by
    s1.emp_no
having
    count(distinct s2.salary) = 2

自连接，然后连接条件是s1.salary <= s2.salary，思考一下连接过程，左表是驱动表，左表s1从表中取出一行数据，然后依次和右表s2进行比对，如果s1.salary 小于等于 s2.salary，则满足条件，遍历完右表后，可以找到所有满足条件的数据，然后和s1表的这行数据存到虚拟表中，这个一行的匹配过程可以看作是后续的group by操作，当一遍遍历完右表s2后，如果右表匹配的不重复数据有一个，那说明左表数据是最大的，因为有等于条件，右表必定有一个数据满足，而其他数据都不满足，所以这个是最大的。如果右表匹配的有两个，那说明右表有两个数据大于等于当前数据，而有一个是他自己本身，所以当前数据是第二大的。因此对s1.salary进行分组后然后判断右边的不重复数据的个数即可找到排名。

SQL229 批量插入数据，不适用replace操作

描述

题目已经先执行了如下语句:

1
drop table if exists actor; CREATE TABLE actor (  actor_id smallint(5) NOT NULL PRIMARY KEY,  first_name varchar(45) NOT NULL,  last_name varchar(45) NOT NULL,  last_update DATETIME NOT NULL); insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

actor_id first_name last_name last_update
'3' 'ED' 'CHASE' '2006-02-15 12:34:33'

解析

1
2
3
4
5
6
# mysql中常用的三种插入数据的语句: 
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错； 
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； 
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

SQL230 创建一个actor_name表

描述

对于如下表actor，其对应的数据为:

actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

actor_name表结构如下，题目最后会查询actor_name表里面的数据来对比结果输出：

列表 类型 是否为NULL 含义
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏

解析

考察创建表的三种方式

1
2
3
4
5
6
7
8
9
10
11
12
1. 常规创建
create table if not exists 目标表
2. 复制表格
create 目标表 like 来源表
3. 将table1的部分拿来创建table2
create table if not exists actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
)
select first_name,last_name
from actor

SQL231 创建索引

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

1
2
3
4
5
CREATE TABLE actor  (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  datetime NOT NULL);

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

解析

  1. 添加主键
1
2
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  1. 添加唯一索引
1
2
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。
  1. 添加普通索引
1
2
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。
  1. 添加全文索引
1
2
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。
  1. 删除索引的语法：
1
2
3
4
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP PRIMARY KEY;

另一种创建索引的方法，使用create，但是这种方式不能创建主键

1
2
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON tbl_name (col_name);
//该语句指定了索引可以是唯一索引、全文索引、空间索引（一种基于空间对象的空间关系排列的数据结构，比如我们常见的地图定位应用，有兴趣的朋友可以自行谷歌一下）以及普通索引。

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

用户行为日志表tb_user_log

id uid artical_id in_time out_time sign_cin
1 101 9001 2021-11-01 10:00:00 2021-11-01 10:00:11 0
2 102 9001 2021-11-01 10:00:09 2021-11-01 10:00:38 0
3 103 9001 2021-11-01 10:00:28 2021-11-01 10:00:58 0
4 104 9002 2021-11-01 11:00:45 2021-11-01 11:01:11 0
5 105 9001 2021-11-01 10:00:51 2021-11-01 10:00:59 0
6 106 9002 2021-11-01 11:00:55 2021-11-01 11:01:24 0
7 107 9001 2021-11-01 10:00:01 2021-11-01 10:01:50 0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

artical_id max_uv
9001 3
9002 2

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

解析（计算瞬时的最大计数）

本题难点在于如何计算瞬时的最大计数（在看人数）。

通常想到的方法是编码+联立。首先对原表的in_time和out_time进行编码，如果是in_time则说明观看人数+1，如果是out_time则说明观看人数-1，分别进行编码，然后合并。

1
2
3
4
5
6
7
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

这三个字段表示artical_id文章在dt这个时刻的变化人数为diff。

然后对这个表使用窗口函数sum求和即可求得每一个时刻的观看人数。

1
2
3
4
5
6
7
8
9
10
11
select
    artical_id, sum(diff) over (partition by artical_id order by dt, diff desc) 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
) t

这里排序先是使用dt升序，按照时间戳的顺序，然后使用diff降序。因为题目中说如果同一时刻有进入和离开，则先增加再减少，也就是说如果排序时dt相同，则按照diff降序排列。

得到每个时刻的观看人数后，需要求观看人数最多的时刻的人数。因此最后再按照artical_id分组，求max(viewer_cnt)即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select artical_id, max(viewer_cnt) max_uv
from (
    select
        artical_id, sum(diff) over (partition by artical_id order by dt, diff desc) 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
    ) t
) tt
group by artical_id
order by max_uv desc

本题解参考了题解。

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

用户行为日志表tb_user_log

id uid artical_id in_time out_time sign_cin
1 101 0 2021-11-01 10:00:00 2021-11-01 10:00:42 1
2 102 9001 2021-11-01 10:00:00 2021-11-01 10:00:09 0
3 103 9001 2021-11-01 10:00:01 2021-11-01 10:01:50 0
4 101 9002 2021-11-02 10:00:09 2021-11-02 10:00:28 0
5 103 9002 2021-11-02 10:00:51 2021-11-02 10:00:59 0
6 104 9001 2021-11-02 11:00:28 2021-11-02 11:01:24 0
7 101 9003 2021-11-03 11:00:55 2021-11-03 11:01:24 0
8 104 9003 2021-11-03 11:00:45 2021-11-03 11:00:55 0
9 105 9003 2021-11-03 11:00:53 2021-11-03 11:00:59 0
10 101 9002 2021-11-04 11:00:55 2021-11-04 11:00:59 0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：

  • 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
  • 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

输出示例：

示例数据的输出结果如下

dt uv_left_rate
2021-11-01 0.67
2021-11-02 1.00
2021-11-03 0.00

解释：

11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；

11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；

11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；

11.04没有新用户，不输出。

解析

本题针对的是新用户的次日留存率。因此应该首先查出每个用户的最早登陆日期，也就是新用户的第一次登陆日期，这样可以找到每个日期的注册用户。

然后再查询出每天都有哪些用户登录，将这两个表连接，连接条件是相同用户和用户最早登录日期的下一天仍然登录。如果下一天仍然登录，则该值不为null否则为null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select t1.dt, round(count(t2.dt) / count(t1.dt), 2) uv_left_rate
from (
    select uid, min(date(in_time)) dt
    from tb_user_log
    group by uid
) t1  -- 查询新用户和新用户的最早登录日期
left join (
    select uid, date(in_time) dt
    from tb_user_log
    union
    select uid, date(out_time) dt
    from tb_user_log
) t2  -- 查询每天登录的用户，如果跨日期，则认为两天都登录了，所以需要查询两次然后合并
on t1.uid = t2.uid
and datediff(t2.dt, t1.dt) = 1
where date_format(t1.dt, '%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt

SQL167 连续签到领金币

用户行为日志表tb_user_log

id uid artical_id in_time out_time sign_in
1 101 0 2021-07-07 10:00:00 2021-07-07 10:00:09 1
2 101 0 2021-07-08 10:00:00 2021-07-08 10:00:09 1
3 101 0 2021-07-09 10:00:00 2021-07-09 10:00:42 1
4 101 0 2021-07-10 10:00:00 2021-07-10 10:00:09 1
5 101 0 2021-07-11 23:59:55 2021-07-11 23:59:59 1
6 101 0 2021-07-12 10:00:28 2021-07-12 10:00:50 1
7 101 0 2021-07-13 10:00:28 2021-07-13 10:00:50 1
8 102 0 2021-10-01 10:00:28 2021-10-01 10:00:50 1
9 102 0 2021-10-02 10:00:01 2021-10-02 10:01:50 1
10 102 0 2021-10-03 10:00:55 2021-10-03 11:00:59 1
11 102 0 2021-10-04 10:00:45 2021-10-04 11:00:55 0
12 102 0 2021-10-05 10:00:53 2021-10-05 11:00:59 1
13 102 0 2021-10-06 10:00:45 2021-10-06 11:00:55 1

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：

  • artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
  • 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
  • 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

输出示例：

示例数据的输出结果如下：

uid month coin
101 202107 15
102 202110 7

解释：

101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；

102在10.01~10.03连续签到3天获得5金币

10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

解析

本题最主要的问题是如何判断签到日期是否连续，因此可以联想到连续问题。连续问题通常解决方法是新建一个连续的列，相减得到一个常数值，通过这个相同的值判断连续情况。也就是连续n问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
with t_sign_info as (
    select 
        distinct uid
        , date(in_time) sign_dt
        , date(in_time) - row_number() over (partition by uid order by date(in_time)) row_num
    from tb_user_log
    where artical_id = 0
    and sign_in = 1
    and datediff(in_time, '2021-07-07') >= 0
    and datediff(in_time, '2021-10-31') <= 0
)
select uid, date_format(sign_dt, '%Y%m') month, sum(coin) coin
from (
    select 
        uid
        , sign_dt
        , case row_id % 7
            when 3 then 3
            when 0 then 7
            else 1
        end coin
    from (
        select *, row_number() over (partition by row_num, uid order by sign_dt) row_id
        from t_sign_info
    ) t
) tt
group by uid, month
order by month, uid

首先找到每个用户的合法的签到日期，然后按照用户号分组使用窗口函数分配序号。然后使用签到日期和分配的序号相减，如果签到日期是连续的，那么和序号相减得到的结果的值是固定的，如果不同了，说明日期发生了断连。

然后再次使用窗口函数分配序号，这样可以看到用户签到的天数情况。

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

商品信息表tb_product_info

id product_id shop_id tag int_ quantity release_time
1 8001 901 零食 60 1000 2020-01-01 10:00:00
2 8002 901 零食 140 500 2020-01-01 10:00:00
3 8003 901 零食 160 500 2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id order_id uid event_time total_amount total_cnt status
1 301001 101 2021-09-30 10:00:00 140 1 1
2 301002 102 2021-10-01 11:00:00 235 2 1
3 301011 102 2021-10-31 11:00:00 250 2 1
4 301003 101 2021-10-02 10:00:00 300 2 1
5 301013 105 2021-10-02 10:00:00 300 2 1
6 301005 104 2021-10-03 10:00:00 170 1 1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id order_id product_id price cnt
1 301001 8002 150 1
2 301011 8003 200 1
3 301011 8001 80 1
4 301002 8001 85 1
5 301002 8003 180 1
6 301003 8002 140 1
7 301003 8003 180 1
8 301013 8002 140 2
9 301005 8003 180 1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态- 订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态- 订单状态为1表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。

问题：请统计零食类商品中复购率top3高的商品。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

输出示例：

示例数据的输出结果如下：

product_id repurchase_rate
8001 1.000
8002 0.500
8003 0.333

解释：

商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；

商品8002被101购买了两次，被105购买了1次，复购率0.500；

商品8003被102购买两次，被101和105各购买1次，复购率为0.333。

解析

本题难点在于找到复购的商品的人数。

首先看复购的定义，指的是一个用户在某段时间内对一个商品重复购买。也就是说在表中的记录里，用户复购一个商品时的uid和product_id是相同的，因为用户相同、商品相同。因此可以首先使用uid, product_id进行分组，找到某用户对对某商品的购买次数，可以通过event_time判断购买了多少次。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
    product_id
    , round(avg(if_repurchase), 3) repurchase_rate
from (
    select
        info.product_id
        , if(count(overall.event_time) > 1, 1, 0) if_repurchase
    from tb_product_info info
    inner join tb_order_detail detail
    on info.product_id = detail.product_id
    inner join tb_order_overall overall
    on detail.order_id = overall.order_id
    where info.tag = '零食'
    and datediff((select max(event_time) from tb_order_overall), overall.event_time) < 90
    group by info.product_id, overall.uid
) t
group by product_id
order by repurchase_rate desc, product_id asc
limit 3

SQL173 店铺901国庆期间的7日动销率和滞销率

商品信息表tb_product_info

id product_id shop_id tag int_ quantity release_time
1 8001 901 日用 60 1000 2020-01-01 10:00:00
2 8002 901 零食 140 500 2020-01-01 10:00:00
3 8003 901 零食 160 500 2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id order_id uid event_time total_amount total_cnt status
1 301004 102 2021-09-30 10:00:00 170 1 1
2 301005 104 2021-10-01 10:00:00 160 1 1
3 301003 101 2021-10-02 10:00:00 300 2 1
4 301002 102 2021-10-03 11:00:00 235 2 1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id order_id product_id price cnt
1 301004 8002 180 1
2 301005 8002 170 1
3 301002 8001 85 1
4 301002 8003 180 1
5 301003 8002 150 1
6 301003 8003 180 1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

注：

  • 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
  • 滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
  • 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

输出示例：

示例数据的输出结果如下：

dt sale_rate unsale_rate
2021-10-01 0.333 0.667
2021-10-02 0.667 0.333
2021-10-03 1.000 0.000

解释：

10月1日的近7日（9月25日---10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；

10月2日的近7日（9月26日---10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；

10月3日的近7日（9月27日---10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，

滞销率为0.000；

解析

总体思路先求这三天每天的上架商品总数，然后再求七天内的售出商品总数。

要计算过去七天的，首先想到使用窗口函数，但是因为售出日期不是连续的，使用窗口函数不能解决问题，因此可以采用连接方式，在连接条件上判断。

首先求出要求的三天的日期，题目要求只要当天有商品卖出就要展示当天的结果，另一个意思是如果一天没有任何商品售出就不展示该天。所以先根据订单求出需要展示哪些天。

1
2
3
SELECT DATE(event_time) dt
FROM tb_order_overall
WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'

然后求出这几天的每天的上架的商品数量，题目给的条件里没有说明下架日期，默认商品不会下架，这里根据上架日期小于题目中要求的三天即可，使用连接的方法即可求出。

1
2
3
4
5
6
7
-- t_date为上一次求的三天日期
SELECT dt, COUNT(DISTINCT product_id) cnt
FROM tb_product_info
INNER JOIN t_date
ON DATEDIFF(dt, release_time) >= 0
WHERE shop_id = 901
GROUP BY dt

接下来求出七天内的出售的商品，对三个表进行连接，根据要求的店铺id和日期，即可求得每天出售的商品id。注：这里不能求每天出售的商品数，题目中要求的是七天内售出的不同的商品数，如果两天售出同样的商品，正确的结果为1，而如果求每天出售的商品数，结果则大于1了

1
2
3
4
5
6
7
8
SELECT DATE(oo.event_time) dt, od.product_id 
FROM tb_order_detail od
INNER JOIN tb_order_overall oo
ON od.order_id = oo.order_id
INNER JOIN tb_product_info pi
ON od.product_id = pi.product_id
WHERE pi.shop_id = 901
AND DATE(oo.event_time) BETWEEN '2021-09-25' AND '2021-10-03'

最后对上架商品表和出售商品表连接，求出结果。

1
2
3
4
5
6
7
8
9
10

SELECT
    t_onsale.dt,
    ROUND(COUNT(DISTINCT t_sold.product_id) / t_onsale.cnt, 3) sale_rate,
    ROUND(1 - COUNT(DISTINCT t_sold.product_id) / t_onsale.cnt, 3) unsale_rate
FROM t_onsale
LEFT JOIN t_sold
ON DATEDIFF(t_onsale.dt, t_sold.dt) BETWEEN 0 AND 6
GROUP BY t_onsale.dt
ORDER BY t_onsale.dt

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

用户行为日志表tb_user_log

id uid artical_id in_time out_time sign_cin
1 109 9001 2021-08-31 10:00:00 2021-08-31 10:00:09 0
2 109 9002 2021-11-04 11:00:55 2021-11-04 11:00:59 0
3 108 9001 2021-09-01 10:00:01 2021-09-01 10:01:50 0
4 108 9001 2021-11-03 10:00:01 2021-11-03 10:01:50 0
5 104 9001 2021-11-02 10:00:28 2021-11-02 10:00:50 0
6 104 9003 2021-09-03 11:00:45 2021-09-03 11:00:55 0
7 105 9003 2021-11-03 11:00:53 2021-11-03 11:00:59 0
8 102 9001 2021-10-30 10:00:00 2021-10-30 10:00:09 0
9 103 9001 2021-10-21 10:00:00 2021-10-21 10:00:09 0
10 101 0 2021-10-01 10:00:00 2021-10-01 10:00:42 1

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：

  • 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
  • 假设今天就是数据中所有日期的最大值。
  • 近7天表示包含当天T的近7天，即闭区间[T-6, T]。

输出示例：

示例数据的输出结果如下

user_grade ratio
忠实用户 0.43
新晋用户 0.29
沉睡用户 0.14
流失用户 0.14

解释：

今天日期为2021.11.04，根据用户分级标准，用户行为日志表tb_user_log中忠实用户有：109、108、104；新晋用户有105、102；沉睡用户有103；流失用户有101；共7个用户，因此他们的比例分别为0.43、0.29、0.14、0.14。

解析

本题主要判断的是用户的最后一次活跃、第一次活跃和所有活跃日期的最大值的比较。

所以首先应该查找出所有用户的首次活跃和最后一次活跃日期，这里可以采用分组的方法，每一组里的最大值和最小值即是结果。

所有日期的最大值可以通过再次查找表取所有数据的最大值，但是这样会重复查找。可以在刚刚查找每个用户的活跃日期时顺便查找出所有用户的最后活跃日期。即在分组后使用一次窗口函数，求分组后的结果的最大值，即为全局的最大值。

1
2
3
4
SELECT
    uid, MAX(out_time) last_dt, MIN(out_time) first_dt, MAX(MAX(out_time)) OVER () today
FROM tb_user_log
GROUP BY uid

这里提供了一个思路：如果想在分组后求全局的聚合结果，可以再次使用窗口函数进行求解。

所以本题解法为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH t_all AS (
    SELECT
        uid, MAX(out_time) last_dt, MIN(out_time) first_dt, MAX(MAX(out_time)) OVER () today
    FROM tb_user_log
    GROUP BY uid
),
t_grade AS (
    SELECT
        uid,
        CASE
            WHEN DATEDIFF(today, last_dt) <= 6 AND DATEDIFF(today, first_dt) > 6 THEN '忠实用户'
            WHEN DATEDIFF(today, first_dt) <= 6 THEN '新晋用户'
            WHEN DATEDIFF(today, last_dt) > 29 THEN '流失用户'
            ELSE '沉睡用户'
        END user_grade
    FROM t_all
)

SELECT
    user_grade,
    ROUND(COUNT(uid) / SUM(COUNT(user_grade)) OVER (), 2) ratio
FROM t_grade
GROUP BY user_grade
ORDER BY ratio DESC

同样的思路，最后需要判断所占比率，每类用户的数量除以所有用户的数量，已经按照用户等级分组了，对分组后的结果使用窗口函数对每个组的行数进行求和，就得到了总数。

SQL 170 某店铺的各商品毛利率及店铺整体毛利率

描述

商品信息表tb_product_info

id product_id shop_id tag in_price quantity release_time
1 8001 901 家电 6000 100 2020-01-01 10:00:00
2 8002 902 家电 12000 50 2020-01-01 10:00:00
3 8003 901 3C数码 12000 50 2020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

id order_id uid event_time total_amount total_cnt status
1 301001 101 2021-10-01 10:00:00 30000 3 1
2 301002 102 2021-10-01 11:00:00 23900 2 1
3 301003 103 2021-10-02 10:00:00 31000 2 1

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

id order_id product_id price cnt
1 301001 8001 8500 2
2 301001 8002 15000 1
3 301002 8001 8500 1
4 301002 8002 16000 1
5 301003 8002 14000 1
6 301003 8003 18000 1

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

场景逻辑说明：

  • 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；
  • 当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；
  • 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

注：商品毛利率=(1-进价/平均单件售价)*100%；

店铺毛利率=(1-总进价成本/总销售收入)*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

输出示例：

示例数据的输出结果如下：

product_id profit_rate
店铺汇总 31.0%
8001 29.4%
8003 33.3%

解释：

店铺901有两件商品8001和8003；8001售出了3件，销售总额为25500，进价总额为18000，毛利率为1-18000/25500=29.4%，8003售出了1件，售价为18000，进价为12000，毛利率为33.3%；

店铺卖出的这4件商品总销售额为43500，总进价为30000，毛利率为1-30000/43500=31.0%

解析（WITH ROLLUP）

题目理解不难，难点在于怎么同时计算出每个商品的毛利率和店铺整体毛利率。

第一种可以采用分别计算的方法，先计算出店铺的毛利率然后再计算每个商品的毛利率，最后进行合并即可。

第二种是采用WITH ROLLUP方法，因为整体毛利率和某个商品的毛利率计算方法相似。

这里需要对商品毛利率的计算方法做一个转换。进价平均单件售价，上下同时乘出售的总数量，就变成了总进价成本该商品总销售收入，所以计算方法和计算整体毛利率相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    product_id, CONCAT(profit_rate, '%') profit_rate
FROM (
    SELECT
        COALESCE(product_id, '店铺汇总') product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) profit_rate
    FROM (
        SELECT info.product_id, info.in_price, detail.price, detail.cnt
        FROM tb_order_detail detail
        INNER JOIN tb_product_info info
        ON detail.product_id = info.product_id
        INNER JOIN tb_order_overall overall
        ON detail.order_id = overall.order_id
        WHERE info.shop_id = 901
        AND DATE(overall.event_time) >= '2021-10-01'
    ) t1
    GROUP BY product_id WITH ROLLUP
    HAVING profit_rate >= 24.9
    OR product_id IS NULL
    ORDER BY product_id
) t2
牛客网SQL题
2023/12/01/SQL题/牛客/Sql题/
作者
yatbfm
发布于
2023-12-01 21:39
许可
  1. SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
    1. 解析
  2. SQL229 批量插入数据，不适用replace操作
    1. 解析
  3. SQL230 创建一个actor_name表
    1. 解析
  4. SQL231 创建索引
    1. 解析
  5. SQL163 每篇文章同一时刻最大在看人数
    1. 解析（计算瞬时的最大计数）
  6. SQL164 2021年11月每天新用户的次日留存率
    1. 解析
  7. SQL167 连续签到领金币
    1. 解析
  8. SQL171 零食类商品中复购率top3高的商品
    1. 解析
  9. SQL173 店铺901国庆期间的7日动销率和滞销率
    1. 解析
  10. SQL 165 统计活跃间隔对用户分级结果
    1. 解析
  11. SQL 170 某店铺的各商品毛利率及店铺整体毛利率
    1. 描述
    2. 解析（WITH ROLLUP）
© 2020 - 2025    yatbfm
由 Hexo 驱动 & 主题 Keep
访客数 访问量
  1. SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
    1. 解析
  2. SQL229 批量插入数据，不适用replace操作
    1. 解析
  3. SQL230 创建一个actor_name表
    1. 解析
  4. SQL231 创建索引
    1. 解析
  5. SQL163 每篇文章同一时刻最大在看人数
    1. 解析（计算瞬时的最大计数）
  6. SQL164 2021年11月每天新用户的次日留存率
    1. 解析
  7. SQL167 连续签到领金币
    1. 解析
  8. SQL171 零食类商品中复购率top3高的商品
    1. 解析
  9. SQL173 店铺901国庆期间的7日动销率和滞销率
    1. 解析
  10. SQL 165 统计活跃间隔对用户分级结果
    1. 解析
  11. SQL 170 某店铺的各商品毛利率及店铺整体毛利率
    1. 描述
    2. 解析（WITH ROLLUP）