如何在每日用户操作日志表中添加用户最近非NULL操作列？

现有一张用户每日操作日志表，表结构包含date（日期）、user_id（用户ID）、action（操作）字段，具体数据如下：

需求为新增last_action列，展示每个用户在对应日期的最近一次非NULL操作，预期结果如下：

尝试了多种窗口函数均未得到预期结果，失败代码如下：

MAX(action) OVER (PARTITION BY user_id ORDER BY date ASC rows between unbounded preceding and current row) AS last_action

IF(
    action IS NULL,
    MAX(action) OVER (PARTITION BY user_id ORDER BY date ASC rows between unbounded preceding and current row) ,
    action 
  ) AS last_action

LAST_VALUE(action) OVER (PARTITION BY user_id ORDER BY date ASC rows between unbounded preceding and current row) AS last_action

之前的方法为啥不行？ #

• 用MAX(action)的逻辑错误：它返回的是窗口内的最大值，不是最近的操作。比如如果操作里有z和a，它会取z，完全不符合“最近一次”的要求，只是测试数据刚好没暴露这个问题。
• LAST_VALUE(action)的坑：当当前行action是NULL时，它会直接取当前行的NULL，而不是往前找最近的非NULL值——因为默认窗口包含当前行，NULL会被优先选中。

正确实现方式 #

你可以用分组填充的思路：先给每个用户的非NULL操作打上分组标记，NULL行继承上一个非NULL的分组ID，再在分组内填充最近的非NULL操作。SQL代码如下：

WITH grouped_data AS (
    SELECT 
        date,
        user_id,
        action,
        -- 遇到非NULL操作就加1生成分组ID，NULL行跟着上一个分组走
        SUM(CASE WHEN action IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY date ASC) AS action_group
    FROM 
        user_action_log
)
SELECT 
    date,
    user_id,
    action,
    -- 每个分组里取最早的非NULL操作（也就是最近的有效操作）
    FIRST_VALUE(action) OVER (PARTITION BY user_id, action_group ORDER BY date ASC) AS last_action
FROM 
    grouped_data
ORDER BY 
    user_id, date;

如果你的数据库（比如PostgreSQL、BigQuery）支持灵活的窗口框架，也可以直接用LAST_VALUE结合过滤逻辑：

SELECT
    date,
    user_id,
    action,
    -- 先找当前行之前最近的非NULL操作，找不到就用当前行的action（如果有的话）
    COALESCE(
        LAST_VALUE(action) OVER (
            PARTITION BY user_id 
            ORDER BY date ASC
            -- 只看当前行之前的记录
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            EXCLUDE CURRENT ROW
        ),
        action
    ) AS last_action
FROM user_action_log
ORDER BY user_id, date;

结果验证 #

执行上述SQL后，会得到与预期完全一致的结果：所有NULL行都会填充上最近的非NULL操作，用户首次出现的NULL行则保持NULL。

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