如何用SQL修正Google Analytics Event表中从0起始的Product List Position错误

实现思路 #

• 首先对每个独立商品列表做聚合判断，若列表的最小位置值为0，判定为部署错误的异常列表，需要统一修正
• 异常列表的所有位置值+1即可对齐正常从1开始的计数规则，正常列表的位置值保持不变

通用查询SQL示例 #

WITH abnormal_list AS (
    -- 筛选出所有起始计数为0的异常商品列表
    SELECT 
        product_list_id -- 替换为表中对应商品列表唯一标识的字段名
    FROM 
        ga_event_table -- 替换为实际的GA事件表名
    GROUP BY 
        product_list_id
    HAVING 
        MIN(product_list_position) = 0
)
SELECT
    t.* EXCEPT(product_list_position), -- 排除原位置字段，保留其他所有字段
    -- 异常列表的位置值+1，正常列表保持原值
    CASE WHEN a.product_list_id IS NOT NULL THEN t.product_list_position + 1 
         ELSE t.product_list_position END AS product_list_position
FROM 
    ga_event_table t
LEFT JOIN 
    abnormal_list a
ON 
    t.product_list_id = a.product_list_id

注意事项 #

• 若需要直接更新存储的表数据而非查询结果，可调整为UPDATE语句，MySQL语法示例：

UPDATE ga_event_table t
JOIN (
    SELECT product_list_id FROM ga_event_table GROUP BY product_list_id HAVING MIN(product_list_position) = 0
) a ON t.product_list_id = a.product_list_id
SET t.product_list_position = t.product_list_position + 1

• 不同业务部署场景下商品列表唯一标识字段可能为item_list_name、item_list_id等，需结合实际表结构调整
• 若表中没有独立的商品列表唯一标识，可结合会话ID（user_pseudo_id、ga_session_id）、事件触发时间等字段组合判定是否属于同一个商品列表，再做聚合判断即可

内容的提问来源于stack exchange，提问作者Ufuk AKÇAY