xx率计算(1)

1.现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:

有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:

 

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;
下面是一个用户(user)表，id为主键（注意这里id代表用户编号），is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
...
第4行表示id为4的是正常用户

现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

 

结果表示:

2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500;

2020-01-12没有失败的情况，所以概率为0.000.

 

方法一：

select date,round(avg(case when type='no_completed' then 1 else 0 end),3) as p

from email e

join user u1

on e.send_id = u1.id AND u1.is_blacklist = 0

join user u2

on e.receive_id = u2.id AND u2.is_blacklist = 0

group by date

order by date

 

方法二：

select date, round(sum(type = "no_completed") / count(*), 3) as p

from email as t1

    join user as t2 on t1.send_id = t2.id

    join user as t3 on t1.receive_id = t3.id

where t2.is_blacklist = 0 and t3.is_blacklist = 0

group by date

order by date;

 

方法三：

select e.date

,round(sum(case type when 'no_completed' then 1 else 0 end)/count(id),3) as p

from email e

where send_id in(select id from user where is_blacklist=0)

and receive_id in(select id from user where is_blacklist=0)

group by e.date

order by e.date;

 

2.11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。

已知产品情况表product_tb如下（其中，item_id指某款号的具体货号，style_id指款号，tag_price表示标签价格，inventory指库存量）：

item_id	style_id	tag_price	inventory
A001	A	100	20
A002	A	120	30
A003	A	200	15
B001	B	130	18
B002	B	150	22
B003	B	125	10
B004	B	155	12
C001	C	260	25
C002	C	280	18

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_date	user_id	item_id	sales_num	sales_price
2021-11-01	1	A001	1	90
2021-11-01	2	A002	2	220
2021-11-01	2	B001	1	120
2021-11-02	3	C001	2	500
2021-11-02	4	B001	1	120
2021-11-03	5	C001	1	240
2021-11-03	6	C002	1	270
2021-11-04	7	A003	1	180
2021-11-04	8	B002	1	140
2021-11-04	9	B001	1	125
2021-11-05	10	B003	1	120
2021-11-05	10	B004	1	150
2021-11-05	10	A003	1	180
2021-11-06	11	B003	1	120
2021-11-06	10	B004	1	150

请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额），以上例子的输出结果如下（折扣率保留两位小数）：

discount_rate(%)

93.97

方法一：

select

    round(sum(sales_price)/sum(total_price)*100,2) as discount_rate

from

    (select

        tag_price*sales_num as total_price,sales_price as sales_price

    from

        product_tb pt

    join

        sales_tb st

    on

        pt.item_id = st.item_id

     ) t

 

 

 

方法二：

select

    round(sum(sales_price)*100/sum(sales_num*tag_price),2)

    as 'discount_rate(%)'

from sales_tb a

join product_tb b

on a.item_id = b.item_id

 

3.用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-10-01  10:00:00	2021-10-01  10:00:30	0	1	1	NULL
2	102	2001	2021-10-01  10:00:00	2021-10-01  10:00:24	0	0	1	NULL
3	103	2001	2021-10-01  11:00:00	2021-10-01  11:00:34	0	1	0	1732526
4	101	2002	2021-09-01  10:00:00	2021-9-01  10:00:42	1	0	1	NULL
5	102	2002	2021-10-01  11:00:00	2021-10-01  10:00:30	1	0	1	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	影视	30	2021-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2021-01-01 07:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间）

问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

 

示例数据的结果如下：

video_id	avg_comp_play_rate
2001	0.667
2002	0.000

解释：

视频2001在2021年10月有3次播放记录，观看时长分别为30秒、24秒、34秒，视频时长30秒，因此有两次是被认为完成播放了的，故完播率为0.667；

视频2002在2021年9月和10月共2次播放记录，观看时长分别为42秒、30秒，视频时长60秒，故完播率为0.000。

 workbench创建Table如下：

 

 

 

 

 

 

 

方法一：

select t1.video_id,

    round(avg(case when timestampdiff(second,start_time,end_time)>=duration then 1

            else 0 end),3) avg_comp_play_rate

from

    tb_user_video_log t1

join

    tb_video_info t2

on

    t1.video_id=t2.video_id

where

    year(start_time) = 2021

group by

    t2.video_id

order by

    avg_comp_play_rate desc

 

 

 

#方法二

#select t1.video_id as video_id,round(sum(if(end_time-start_time>=duration,1,0))/count(*),3)

#as avg_comp_play_rate

#from tb_user_video_log t1

#inner JOIN

#tb_video_info t2

#on t1.video_id=t2.video_id

#where year(start_time)=2021

#group by t1.video_id

#order by avg_comp_play_rate desc

 

#方法三

#select t1.video_id,round(avg(if(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration,1,0)),3)

#as avg_comp_play_rate

#from tb_user_video_log t1

#inner JOIN

#tb_video_info t2

#on t1.video_id=t2.video_id

#where year(start_time)=2021

#group by t1.video_id

#order by avg_comp_play_rate desc