select product_id,
avg(ctr) ctr,
round(avg(cnt_cart/cnt_click),3) cart_rate,
round(avg(cnt_payment/cnt_cart),3) payment_rate,
round(avg(cnt_refund/cnt_payment),3) refund_rate
from
(
select product_id,
sum(if_payment) over(partition by product_id) cnt_payment,
sum(if_cart) over(partition by product_id) cnt_cart,
sum(if_click) over(partition by product_id) cnt_click,
sum(if_refund) over(partition by product_id) cnt_refund,
round(avg(if_click) over(partition by product_id),3) ctr,
from tb_user_event
where date_format(event_time,'%Y%m')='2021-10'
) t1
where refund_rate<=0.5
group by product_id
```sql
SELECT product_id,
round(sum(if_click)/count(id),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')='2021-10'
GROUP BY product_id
HAVING refund_rate <= 0.500
ORDER BY product_id
```sql
能问下，你为什么用窗口函数，想实现什么效果？
select product_id,
avg(ctr) ctr,
round(avg(cnt_cart/cnt_click),3) cart_rate,
round(avg(cnt_payment/cnt_cart),3) payment_rate,
round(avg(cnt_refund/cnt_payment),3) refund_rate
from
(
select product_id,
sum(if_payment) over(partition by product_id) cnt_payment,
sum(if_cart) over(partition by product_id) cnt_cart,
sum(if_click) over(partition by product_id) cnt_click,
sum(if_refund) over(partition by product_id) cnt_refund,
round(avg(if_click) over(partition by product_id),3) ctr,
from tb_user_event
where date_format(event_time,'%Y%m')='2021-10'
) t1
where round(avg(cnt_refund/cnt_payment),3) <= 0.5
group by product_id