官方题解 | #某东618展示到浏览、浏览到加购、加购到购买的转化率#

step1:计算每个用户if_snd, if_vw, if_cart, if_buy的均值，当均值不为0时代表该用户进行了相应的操作；

step2:计算各种转换率，如展示的个数除以浏览的个数即为展示到浏览的转化率；

avg()用于返回所有列的平均值，

mysql中用"<>"或"!="表示不等于。

SELECT

ROUND(COUNT(IF(vw_avg<>0, TRUE, NULL)) / COUNT(IF(snd_avg<>0, TRUE, NULL)), 2) as '展示到浏览',

ROUND(COUNT(IF(cart_avg<>0, TRUE, NULL)) / COUNT(IF(vw_avg<>0, TRUE, NULL)), 2) as '浏览到加购',

ROUND(COUNT(IF(buy_avg<>0, TRUE, NULL)) / COUNT(IF(cart_avg<>0, TRUE, NULL)), 2) as '加购到购买'

FROM(

SELECT

cust_uid,

AVG(if_snd) as snd_avg,

AVG(if_vw) as vw_avg,

AVG(if_cart) as cart_avg,

AVG(if_buy) as buy_avg

FROM tb_clk_rcd

group by cust_uid

)t;