题解 | #202110退货率不大于0.5各项指标#窗口函数

SELECT 
    DISTINCT
    product_id,
    ROUND(
        COUNT(IF (if_click = 1,1,NULL)) OVER (
            PARTITION BY
                product_id
        ) / COUNT(*) OVER (
            PARTITION BY
                product_id
        ),
        3
    ) ctr,
    ROUND(
        COUNT(IF (if_cart = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ) / COUNT(IF (if_click = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ),
        3
    ) cart_rate,
    ROUND(
        COUNT(IF (if_payment = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ) / COUNT(IF (if_cart = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ),
        3
    ) payment_rate,
    ROUND(
        COUNT(IF (if_refund = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ) / COUNT(IF (if_payment = 1, 1, NULL)) OVER (
            PARTITION BY
                product_id
        ),
        3
    ) refund_rate
FROM
    tb_user_event
WHERE DATE(event_time) LIKE"2021-10%"

解题步骤：

1. 筛选数据：通过WHERE DATE(event_time) LIKE "2021-10%"筛选出2021年10月期间的所有事件数据。
2. 计算点击率：使用COUNT(IF(if_click = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的点击次数。使用COUNT(*) OVER (PARTITION BY product_id)计算每个产品的总事件次数。计算点击率：点击次数 / 总事件次数，并使用ROUND函数保留三位小数。
3. 计算购物车添加率：使用COUNT(IF(if_cart = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的添加购物车次数。使用COUNT(IF(if_click = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的点击次数。计算购物车添加率：添加购物车次数 / 点击次数，并使用ROUND函数保留三位小数。
4. 计算支付率：使用COUNT(IF(if_payment = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的支付次数。使用COUNT(IF(if_cart = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的添加购物车次数。计算支付率：支付次数 / 添加购物车次数，并使用ROUND函数保留三位小数。
5. 计算退款率：使用COUNT(IF(if_refund = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的退款次数。使用COUNT(IF(if_payment = 1, 1, NULL)) OVER (PARTITION BY product_id)计算每个产品的支付次数。计算退款率：退款次数 / 支付次数，并使用ROUND函数保留三位小数。
6. DISTINCT：确保每个product_id只出现一次。