如何用SQL统计用户点击商品后2小时内同商品再次曝光的次数及占比

实现SQL #

以下代码适配主流SQL引擎，若使用MySQL等方言，仅需调整时间计算函数即可：

WITH 
-- 提取全量用户+商品维度，保证无点击的组合也能输出
all_user_item AS (
    SELECT DISTINCT `user`, item
    FROM user_item_behavior
),
-- 统计每个用户对商品的总点击次数
click_total AS (
    SELECT 
        `user`,
        item,
        SUM(click) AS total_sample_cnt
    FROM user_item_behavior
    GROUP BY `user`, item
),
-- 提取所有点击事件记录
click_events AS (
    SELECT 
        `user`,
        item,
        time AS click_time
    FROM user_item_behavior
    WHERE click = 1
),
-- 提取所有曝光事件记录
exposure_events AS (
    SELECT 
        `user`,
        item,
        time AS exposure_time
    FROM user_item_behavior
    WHERE exposure = 1
),
-- 匹配点击后2小时内的二次曝光，统计次数
re_exposure_cal AS (
    SELECT 
        c.`user`,
        c.item,
        COUNT(DISTINCT e.exposure_time) AS re_exposure_cnt
    FROM click_events c
    LEFT JOIN exposure_events e
        ON c.`user` = e.`user` 
        AND c.item = e.item
        AND e.exposure_time > c.click_time
        -- 若使用MySQL，替换为AND e.exposure_time <= DATE_ADD(c.click_time, INTERVAL 2 HOUR)
        AND e.exposure_time <= c.click_time + INTERVAL '2' HOUR
    GROUP BY c.`user`, c.item
)
-- 关联所有维度输出最终结果
SELECT 
    a.`user`,
    a.item,
    COALESCE(r.re_exposure_cnt, 0) AS re_exposure_cnt,
    CASE 
        WHEN COALESCE(c.total_sample_cnt, 0) = 0 THEN 0
        ELSE ROUND(COALESCE(r.re_exposure_cnt, 0) / c.total_sample_cnt, 3)
    END AS re_exposure_rate,
    COALESCE(c.total_sample_cnt, 0) AS total_sample_cnt
FROM all_user_item a
LEFT JOIN click_total c
    ON a.`user` = c.`user` AND a.item = c.item
LEFT JOIN re_exposure_cal r
    ON a.`user` = r.`user` AND a.item = r.item
ORDER BY a.`user`, a.item;

逻辑说明 #

1. 先提取全量的用户+商品唯一组合，保证无点击、无符合条件曝光的组合也能正常输出，避免结果缺失。
2. 分别统计总点击次数、匹配点击后2小时内的二次曝光次数，关联后计算曝光率即可。

内容的提问来源于stack exchange，提问作者Bowen Peng