首页 > 试题广场 >

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

[编程题]统计2021年10月每个退货率不大于0.5的商品各项指标
  • 热度指数：62907 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

现有用户对展示的商品行为表tb_user_event

id uid product_id event_time if_click if_cart if_payment if_refund
1 101 8001 2021-10-01 10:00:00 0 0 0 0
2 102 8001
 2021-10-01 10:00:00
 1 0 0 0
3 103 8001
 2021-10-01 10:00:00
 1 1 0 0
4 104 8001
 2021-10-02 10:00:00
 1 1 1 0
5 105 8001
 2021-10-02 10:00:00
 1 1 1 0
6 101 8002
 2021-10-03 10:00:00
 1 1 1 0
7 109 8001
 2021-10-04 10:00:00
 1 1 1 1
（uid-用户ID, product_id-商品ID, event_time-行为时间, if_click-是否点击, if_cart-是否加购物车, if_payment-是否付款, if_refund-是否退货退款）

问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，

注：
  • 商品点展比=点击数÷展示数；
  • 加购率=加购数÷点击数；
  • 成单率=付款数÷加购数；退货率=退款数÷付款数，
  • 当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

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

product_id ctr cart_rate payment_rate refund_rate
8001 0.833 0.800 0.750 0.333
8002 1.000 1.000 1.000 0.000

解释：
在2021年10月商品8001被展示了6次，点击了5次，加购了4次，付款了3次，退款了1次，因此点击率为5/6=0.833，加购率为4/5=0.800，
成单率为3/4=0.750，退货率为1/3=0.333（保留3位小数）；
示例1

输入

DROP TABLE IF EXISTS tb_user_event;
CREATE TABLE tb_user_event (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    product_id INT NOT NULL COMMENT '商品ID',
    event_time datetime COMMENT '行为时间',
    if_click TINYINT COMMENT '是否点击',
    if_cart TINYINT COMMENT '是否加购物车',
    if_payment TINYINT COMMENT '是否付款',
    if_refund TINYINT COMMENT '是否退货退款'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_event(uid, product_id, event_time, if_click, if_cart, if_payment, if_refund) VALUES
  (101, 8001, '2021-10-01 10:00:00', 0, 0, 0, 0),
  (102, 8001, '2021-10-01 10:00:00', 1, 0, 0, 0),
  (103, 8001, '2021-10-01 10:00:00', 1, 1, 0, 0),
  (104, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (105, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (101, 8002, '2021-10-03 10:00:00', 1, 1, 1, 0),
  (109, 8001, '2021-10-04 10:00:00', 1, 1, 1, 1);

输出

8001|0.833|0.800|0.750|0.333
8002|1.000|1.000|1.000|0.000
算法知识视频讲解
添加回答
phXu头像 phXu 
select
    product_id,
 ifnull(round(sum(if_click)/ count(id),3),0) ctr,
 ifnull(round(sum(if_cart)/ sum(if_click),3),0)  cart_rate,
 ifnull(round(sum(if_payment)/ sum(if_cart),3),0) payment_rate,
 ifnull(round(sum(if_refund)/ sum(if_payment),3),0) 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

# IFNULL(expression_1,expression_2);
# 如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

# ROUND(X,D)
# 此函数返回x舍入到最接近的整数。如果第二个参数，D有提供，则函数返回x四舍五入至第D位小数点

# DATE_FORMAT(date,format)函数接受两个参数：
# date：是要格式化的有效日期值format：
# format:是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。

发表于 2021-12-26 18:51:12 回复(3)
Mysql
SandyFlyHigh头像 SandyFlyHigh
如果有Iferror，会好很多。 
SELECT
    product_id,
    IF(COUNT(event_time) = 0, 0, ROUND(SUM(if_click) / COUNT(event_time), 3)) AS ctr,
    IF(SUM(if_click) = 0, 0, ROUND(SUM(if_cart) / SUM(if_click), 3)) AS cart_rate,
    IF(SUM(if_cart) = 0, 0, ROUND(SUM(if_payment) / SUM(if_cart), 3)) AS payment_rate,
    IF(SUM(if_payment) = 0, 0, 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;

发表于 2021-12-14 01:24:35 回复(1)
Mysql
牛客64151470号头像 牛客64151470号

虽然不考虑分母为0 也能过，但是题目要求了，还是加一下
SELECT product_id,
round(sum(if_click)/count(product_id),3) as ctr,
if(sum(if_click)=0,0,round(sum(if_cart)/sum(if_click),3)) as cart_rate,
if(sum(if_cart)=0,0,round(sum(if_payment)/sum(if_cart),3)) as payment_rate,
if(sum(if_payment)=0,0,round(sum(if_refund)/sum(if_payment),3)) as refund_rate
FROM tb_user_event
WHERE YEAR(event_time)=2021 and MONTH(event_time)=10
GROUP BY product_id
HAVING refund_rate <=0.5
ORDER BY product_id ASC

发表于 2021-12-05 13:13:36 回复(0)
Mysql
酸菜鱼土豆大侠头像 酸菜鱼土豆大侠

【场景】：分组求和

【分类】：分组查询

分析思路

select 查询结果 [产品ID;点击率;加购率;成单率;退货率]
from 从哪张表中查询数据[用户行为表]
where 查询条件 [2021年10月]
group by 分组条件 [产品ID]
order by 对查询结果排序 [产品ID升序];

求解代码

#统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标
select
    product_id,
    round(if(sum(if_click) = 0,0,sum(if_click)/count(product_id)),3) as ctr,
    round(if(sum(if_cart) = 0,0,sum(if_cart)/sum(if_click)),3) as cart_rate,
    round(if(sum(if_payment) = 0,0,sum(if_payment)/sum(if_cart)),3) as payment_rate,
    round(if(sum(if_refund) = 0,0,sum(if_refund)/sum(if_payment)),3) as refund_rate
from tb_user_event
where date_format(event_time,'%Y%m')='202110'
group by product_id
order by product_id
发表于 2022-11-06 23:14:22 回复(0)
Mysql
马老师的金轮头像 马老师的金轮 
SELECT product_id,round(sum(if_click)/ count(*),3) ctr,
#还需要用if(表达式1，true，false)判断分母是否为0
round(sum(if_cart)/ sum(if_click),3) cart_rate,
round(sum(if_payment)/ sum(if_cart),3) payment_rate,
round(sum(if_refund)/ sum(if_payment),3) 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

发表于 2022-02-22 18:48:03 回复(0)
Mysql
小螺号嘎嘎嘎头像 小螺号嘎嘎嘎 
with t as (
    select 
        product_id,
        count(*) as display_cnt,
        sum(if_click) as click_cnt,
        sum(if_cart) as cart_cnt,
        sum(if_payment) as pay_cnt,
        sum(if_refund) as refund_cnt
    from tb_user_event 
    where  date_format(event_time, '%Y-%m')='2021-10'
    group by product_id
)
select
    product_id,
    round(click_cnt/display_cnt, 3) as ctr,
    round(cart_cnt/click_cnt, 3) as cart_rate,
    round(pay_cnt/cart_cnt, 3) as payment_rate,
    round(refund_cnt/pay_cnt, 3) as refund_rate
from t
where refund_cnt/pay_cnt <= 0.5
order by product_id

发表于 2021-12-03 17:25:29 回复(2)
克克王头像 克克王
我看大家一般都是用的ifnull或者if语句来判断分母是0的情况
这里我根据以往的刷题经验，首先想到了用子链接 和 case when 语法
select
    product_id,
 (case when ct_id=0 then 0 else round(ck/ct_id,3) end) ctr,
 (case when ck=0 then 0 else round(ct/ck,3) end) cart_rate,
 (case when ct=0 then 0 else round(pt/ct,3) end) payment_rate,
 (case when pt=0 then 0 else round(rd/pt,3) end) refund_rate
from (select product_id
            ,sum(if_click) as ck
            ,count(id) as ct_id
            ,sum(if_cart) as ct
            ,sum(if_payment) as pt
            ,sum(if_refund) as rd
from tb_user_event
where date_format(event_time,'%Y-%m')= '2021-10'
group by product_id) as t
having refund_rate<=0.5
order by product_id

发表于 2025-02-23 09:38:11 回复(0)
说时依旧泪如倾头像 说时依旧泪如倾
select product_id,
       round(sum(if_click=1)/count(1),3) as ctr,
       round(sum(if_cart=1)/sum(if_click=1),3) as cart_rate,
       round(sum(if_payment=1)/sum(if_cart=1),3) as payment_rate,
       round(sum(if_refund=1)/sum(if_payment=1),3) as refund_rate
from tb_user_event where date_format(event_time,'%Y%m')=202110
group by product_id having sum(if_refund=1)/count(if_payment=1)<=0.5 order by product_id asc;

发表于 2022-10-03 19:22:15 回复(0)
Mysql
冷凡社长头像 冷凡社长 
select product_id,
round(sum(if_click)/count(*),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')='202110'
group by product_id
having refund_rate <=0.5
order by product_id
发表于 2022-08-26 14:08:20 回复(0)
Tiffany_tang头像 Tiffany_tang 
select product_id, 
round(sum(if_click)/count(uid),3) ctr, 
ifnull(round(sum(if_cart)/sum(if_click),3),0) cart_rate, 
ifnull(round(sum(if_payment)/sum(if_cart),3),0) payment_rate, 
ifnull(round(sum(if_refund)/sum(if_payment),3),0) refund_rate
from tb_user_event
where left(event_time, 7)='2021-10'
group by product_id
having sum(if_refund)/sum(if_payment)<=0.5
order by product_id

发表于 2021-12-04 19:13:39 回复(1)
迟缓的小饼干1头像 迟缓的小饼干1 
select product_id,
      round(sum(if_click)/count(product_id),3) as ctr,
      round(sum(if_cart)/count(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
要再套一个Ifnull  比较好
发表于 2025-07-02 15:30:42 回复(0)
浅浅的夏天_头像 浅浅的夏天_ 
 select product_id,
round(if_clicks/zhanshi,3) ctr,
round(if_carts/if_clicks,3) cart_rate,
round(if_payments/if_carts,3) payment_rate,
 round(if_refunds/if_payments,3) refund_rate
from
(
    select product_id,
    count(*) zhanshi,
    sum(if_click) if_clicks,
    sum(if_cart) if_carts,
    sum(if_payment) if_payments,
    sum(if_refund) if_refunds
    from tb_user_event
    where left(event_time,7)='2021-10'
    group by product_id
) a
where if_refunds/if_payments<=0.5
order by product_id

发表于 2025-06-25 13:35:51 回复(0)
hydhy头像 hydhy
# 把除变成乘
select product_id,
round(sum(if_click) / count(*),3) ctr,
round(sum(if_cart) * if(sum(if_click)=0,0,1/sum(if_click)),3) cart_rate,
round(sum(if_payment) * if(sum(if_cart)=0,0,1/sum(if_cart)),3) payment_rate,
round(sum(if_refund)  * if(sum(if_payment)=0,0,1/sum(if_payment)),3)  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;
发表于 2025-06-12 20:29:21 回复(0)
ailkid头像 ailkid 
select
    product_id,
    round(sum(if_click) / count(1), 3) ctr,
    round(sum(if_cart) / sum(if_click), 3) cart_rate,
    round(sum(if_payment) / sum(if_cart), 3) payment_rate,
    round(sum(if_refund) / sum(if_payment), 3) refund_rate
from
    tb_user_event
where
    left (event_time, 7) = '2021-10'
group by
    product_id
having
    round(sum(if_refund) / sum(if_payment), 3) <= 0.5
order by
    product_id

发表于 2025-06-12 14:27:02 回复(0)
kirua丶头像 kirua丶
select
product_id
,round(ctr,3)ctr
,round(cart_rate,3)cart_rate
,round(payment_rate,3)payment_rate
,round(refund_rate,3)refund_rate
from
(
select
distinct
product_id
,sum(case when if_click = 1 then 1 else 0 end)over(partition by product_id)/count(id)over(partition by product_id) ctr
,sum(case when if_cart = 1 then 1 else 0 end)over(partition by product_id)/sum(case when if_click = 1 then 1 else 0 end)over(partition by product_id) cart_rate
,sum(case when if_payment = 1 then 1 else 0 end)over(partition by product_id)/sum(case when if_cart = 1 then 1 else 0 end)over(partition by product_id) payment_rate
,sum(case when if_refund = 1 then 1 else 0 end)over(partition by product_id)/sum(case when if_payment = 1 then 1 else 0 end)over(partition by product_id) refund_rate
from tb_user_event
where event_time like '2021-10%'
) t

where refund_rate <= 0.5

发表于 2025-05-16 19:42:26 回复(0)
牛客924005724号头像 牛客924005724号
select product_id,round(sum(if_click)/count(*),3) as ctr
,round(if(sum(if_click)!=0,sum(if_cart)/sum(if_click),0),3) as cart_rate
,round(if(sum(if_cart)!=0,sum(if_payment)/sum(if_cart),0),3) as payment_rate
,round(if(sum(if_refund)!=0,sum(if_refund)/sum(if_payment),0),3) as refund_rate
from tb_user_event
where date_format(event_time,'%Y-%m') ='2021-10'
group by product_id
having round(sum(if_refund)/count(*),3) <=0.5
order by product_id
发表于 2025-04-10 10:04:38 回复(0)
牛客997640975号头像 牛客997640975号 
select
    product_id,
    ifnull(round(sum(if(if_click=1,1,0))/count(product_id),3),0) as ctr,
    ifnull(round(sum(if(if_cart=1,1,0))/sum(if(if_click=1,1,0)),3),0) as cart_rate,
    ifnull(round(sum(if(if_payment=1,1,0))/sum(if(if_cart=1,1,0)),3),0) as payment_rate,
    ifnull(round(sum(if(if_refund=1,1,0))/sum(if(if_payment=1,1,0)),3),0) as refund_rate
from tb_user_event
where year(event_time)=2021 and month(event_time)=10
group by product_id
having round(sum(if(if_refund=1,1,0))/sum(if(if_payment=1,1,0)),3)<=0.500
order by product_id

发表于 2025-04-03 17:31:52 回复(0)
想被叫靓仔的雪碧喜欢摸鱼头像 想被叫靓仔的雪碧喜欢摸鱼
  • 展示记录=product_id出现的次数
  • 退货率=退款数÷付款数
  • 商品点展比=点击数÷展示数；
  • 加购率=加购数÷点击数；
  • 成单率=付款数÷加购数；
发表于 2025-03-23 09:55:41 回复(0)
牛客775456359号头像 牛客775456359号 
select product_id,
round(ifnull(sum(if_click)/count(*),0),3) ctr,
round(ifnull(sum(if_cart)/sum(if_click),0),3) cart_rate,
round(ifnull(sum(if_payment)/sum(if_cart),0),3) paymen_rate,
round(ifnull(sum(if_refund)/sum(if_payment),0),3) refund_rate
from tb_user_event
where date_format(event_time,'%Y%m')='202110'
group by 1
having refund_rate<=0.5
order by 1;

发表于 2025-03-21 10:09:26 回复(0)
吧唧o1头像 吧唧o1
小白求解答，真实数据，是每个uid的每个行为都对应一个行为时间，也就是一个uid在不同时间里发生不同的行为，那么这题中一个uid是所有的行为融合一起了吗
发表于 2025-03-14 19:30:33 回复(0)
提交观点

问题信息

难度：
189条回答 1736浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号