使用SQL窗口PARTITION函数计算product/search/details页后AddToCart累计值

核心思路 #

1. 先给每个会话计算首次访问/product/search/details页面的HitNumber值，会话内无该页面访问时该值为NULL
2. 累加时仅统计HitNumber大于等于首次详情页访问时间点的AddToCart数值，之前的数值忽略不计

完整SQL代码 #

WITH session_first_details AS (
    SELECT
        *,
        -- 计算当前会话首次访问详情页的命中序号
        MIN(CASE WHEN PagePath LIKE '%/product/search/details%' THEN HitNumber END)
            OVER (PARTITION BY SessionID) AS first_details_hit
    FROM 你的业务表名
)
SELECT
    *,
    -- 仅统计详情页访问后的AddToCart累计值
    SUM(CASE WHEN HitNumber >= first_details_hit THEN AddToCart ELSE 0 END)
        OVER (
            PARTITION BY SessionID
            ORDER BY HitNumber
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS desired_output
FROM session_first_details;

逻辑说明 #

你之前的写法错误原因是窗口函数的框架边界不支持直接传入动态匹配的行位置，上述写法兼容所有支持标准窗口函数的SQL引擎（PostgreSQL、BigQuery、Spark SQL等均可直接使用），如果业务规则要求访问详情页当日的AddToCart即使发生在访问前也需要统计，只需要把HitNumber的判断替换为日期维度的判断即可。 #

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