如何使用带条件的SQL LAG()函数为购买事件填充用户最近等级

原始表结构与数据 #

表定义SQL #

WITH 
my_table (user_id, date, event_name, value) AS (
    values 
    (1,'2021-10-01', 'level', 5),
    (1,'2021-10-03', 'purchase', null),
    (1,'2021-10-05', 'purchase', null),
    (2,'2021-10-02', 'level', 10),
    (2,'2021-10-03', 'purchase', null)
) 
SELECT *
FROM my_table

原始数据展示 #

需求说明 #

为每个user_id的每一条purchase事件添加level_achieved列，填充该用户在此次购买前最后达到的level值；level事件对应的该列留空。

期望结果 #

解决方案SQL #

WITH 
my_table (user_id, date, event_name, value) AS (
    values 
    (1,'2021-10-01', 'level', 5),
    (1,'2021-10-03', 'purchase', null),
    (1,'2021-10-05', 'purchase', null),
    (2,'2021-10-02', 'level', 10),
    (2,'2021-10-03', 'purchase', null)
) 
SELECT 
    user_id,
    date,
    event_name,
    CASE WHEN event_name = 'level' THEN value END AS level,
    CASE 
        WHEN event_name = 'purchase' THEN 
            LAST_VALUE(value) OVER (
                PARTITION BY user_id 
                ORDER BY date 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                IGNORE NULLS
            ) 
    END AS level_achieved
FROM my_table;

逻辑说明 #

• PARTITION BY user_id按用户分组，确保仅取当前用户的等级记录
• ORDER BY date按时间顺序处理事件，保证取到购买事件之前的最后一个等级
• LAST_VALUE(value) ... IGNORE NULLS忽略purchase事件的null值，仅保留level事件的有效等级，并取当前行之前的最后一个有效等级
• 通过CASE语句控制列显示：仅level事件展示level值，仅purchase事件展示level_achieved值

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