首页 > 试题广场 >

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

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

热度指数：47488 时间限制：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

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

如果有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号

虽然不考虑分母为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)

说时依旧泪如倾

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

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)

Mysql

祖拜尔实习生

select
    product_id,
    round(avg(if_click),3) click_rate,
    round(
        count(case when if_cart = 1 then 1 else null end)
        /count(case when if_click = 1 then 1 else null end)
        ,3) cart_rate,
    round(
        count(case when if_payment = 1 then 1 else null end)
        /count(case when if_cart = 1 then 1 else null end)
        ,3) payment_rate,
    round(
        count(case when if_refund = 1 then 1 else null end)
        /count(case when if_payment = 1 then 1 else null end)
        ,3) 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 asc

编辑于 2024-04-08 10:41:11 回复(1)

友好的大师兄在泡池子

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 LEFT(event_time,7)='2021-10'
GROUP BY product_id
ORDER BY product_id
--上面没有考虑分母为0，所以应该是：
select product_id,	
round(if(count(*)=0,0,sum(if_click)/count(*)),3) as ctr, 
round(if(sum(if_click)=0,0,sum(if_cart)/sum(if_click)),3) as cart_rate,	
round(if(sum(if_cart)=0,0,sum(if_payment)/sum(if_cart)),3) as payment_rate,	
round(if(sum(if_payment)=0,0,sum(if_refund)/sum(if_payment)),3) as refund_rate
FROM tb_user_event
WHERE LEFT(event_time,7)='2021-10'
GROUP BY product_id
ORDER BY product_id

发表于 2024-03-09 14:00:56 回复(0)

牛客563312411号

if（a,b,c）

或where date_format(event_time,'%Y-%m')='2021-10'

或where year(event_time)=2021 and month(event_time)=10

select
product_id
,round(sum(if_click)/count(product_id),3) ctr 
,round(if(sum(if_click=0),0,sum(if_cart)/sum(if_click)),3) cart_rate
,round(if(sum(if_cart)=0,0,sum(if_payment)/sum(if_cart)),3) payment_rate
,round(if(sum(if_payment)=0,0,sum(if_refund)/sum(if_payment)),3) refund_rate
from tb_user_event
where event_time like '2021-10%'
group by 1
having refund_rate<=0.5
order by 1

ifnull(a,b)若a不为空，输出a；若a为空，则输出b

coalesce=ifnull

select
product_id
,round(ifnull(sum(if_click)/count(product_id),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) payment_rate
,round(ifnull(sum(if_refund)/sum(if_payment),0),3) refund_rate
from tb_user_event
where year(event_time)='2021' and month(event_time)='10'
group by 1
having refund_rate<=0.5
order by 1

编辑于 2024-03-07 18:08:54 回复(0)

崔睿哲

with a as (select product_id,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)
select a.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,a.refund_rate 
from  a left join tb_user_event t  on a.product_id	=t.product_id	
where date_format(event_time,'%Y-%m')='2021-10' 
group by product_id	
order by product_id

编辑于 2024-01-21 22:50:04 回复(0)

喜欢溜溜球的杨桃在抱佛脚

select product_id,
round(if(count(if_click)=0,0,sum(if_click)/count(if_click)),3) as ctr,
round(if(sum(if_click)=0,0,sum(if_cart)/sum(if_click)),3) as cart_rate,
round(if(sum(if_cart)=0,0,sum(if_payment)/sum(if_cart)),3) as payment_rate,
round(if(sum(if_payment)=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
having refund_rate<=0.5
order by product_id asc

编辑于 2024-01-16 16:19:29 回复(1)

Mysql

牛客535608285号

select product_id
    ,case when num = 0 then 0 else round(click/num,3) end ctr
    ,case when click = 0 then 0 else round(cart/click,3) end cart_rate
    ,case when cart = 0 then 0 else round(payment/cart,3) end payment_rate
    ,case when payment = 0 then 0 else round(refund/payment,3) end refund_rate
from(
    select product_id
        ,count(uid) num
        ,sum(if_click) click
        ,sum(if_cart) cart
        ,sum(if_payment) payment
        ,sum(if_refund) refund
    from tb_user_event
    where date(event_time) like '2021-10%'
    group by product_id
) a
group by product_id
having refund_rate <= 0.500
order by product_id

发表于 2023-11-24 11:33:38 回复(0)

呆毛儿本呆

select product_id,
round(sum(if_click)/count(*),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 date_format(event_time,'%Y-%m')='2021-10'
group by product_id
having refund_rate<=0.5
order by product_id

发表于 2023-11-21 11:33:38 回复(0)

Mysql

牛客962195832号

select product_id,round(if(cnt<>0,sum_click/cnt,0),3) as ctr,round(if(sum_click<>0,sum_cart/sum_click,0),3) as cart_rate,round(if(sum_cart<>0,sum_payment/sum_cart,0),3) as payment_rate,round(if(sum_payment<>0,sum_refund/sum_payment,0),3) as refund_rate

from (select product_id,sum(if_click) as sum_click,sum(if_cart) as sum_cart,sum(if_payment) as sum_payment,sum(if_refund) as sum_refund,count(product_id) as cnt

from (select product_id,if_click,if_cart,if_payment,if_refund

from tb_user_event

where date_format(event_time,"%Y-%m")="2021-10" ) as t

group by product_id) as t2

where round(if(sum_payment<>0,sum_refund/sum_payment,0),3)<=0.5

order by product_id

发表于 2023-10-31 19:50:39 回复(0)

Mysql

轩辕冥

select

product_id,

round(sum(if_click) / count(*), 3) ctr,

round(sum(if_cart) / sum(if_click <> 0), 3) cart_rate,

round(sum(if_payment) / sum(if_cart <> 0), 3) payment_rate,

round(sum(if_refund) / sum(if_payment <> 0), 3) refund_rate

from

tb_user_event

where

event_time like '2021-10%'

group by

product_id

having

refund_rate <= 0.5

order by

product_id

发表于 2023-09-15 08:47:07 回复(0)

Mysql

swbl_ww

select
    product_id,
    round(clickNum / showNum, 3) as ctr,
    round(cartNum / clickNum, 3) as cart_rate,
    round(paymentNum / cartNum, 3) as payment_rate,
    round(refundNum / paymentNum, 3) as refund_rate
from
    (
        select
            product_id,
            count(product_id) as showNum,
            sum(if_click) as clickNum,
            sum(if_cart) as cartNum,
            sum(if_payment) as paymentNum,
            sum(if_refund) as refundNum
        from
            tb_user_event
        where
            date_format(event_time,'%Y-%m') = '2021-10'
        group by
            product_id
    ) as t
order by 
    product_id asc

发表于 2023-09-14 14:31:26 回复(0)

后街小行家

中规中矩

select product_id,
round(sum(if_click)/count(*),3)                   as ctr,
round(ifnull(sum(if_cart)/sum(if_click),0),3)     as cart_rate,
round(ifnull(sum(if_payment)/sum(if_cart),0),3)   as payment_rate,
round(ifnull(sum(if_refund)/sum(if_payment),0),3) as refund_rate
from tb_user_event
where left(event_time,7)='2021-10'
group by product_id
having refund_rate<=0.5
order by product_id

发表于 2023-09-10 16:05:54 回复(0)