Snowflake中使用Lag窗口函数正确获取用户上一角色

我想用窗口函数查看用户的上一角色。当出现新的USER_ID时序号会重新计数，但当前SQL中，新用户的第一条记录的Prev Role会继承上一个用户的角色，而非预期的NULL（第一条记录的上一角色应为NULL）。（注：记录按Account_Date_created排序）

原SQL尝试 #

SELECT
    USER_ID
   , ACCOUNT
   , row_number() over (partition by USER_ID order by account_date_created ) as seq
   , ROLE as CURRENT_ROW
   , lag(role) over (order by USER_ID, ACCOUNT, seq)  as prev_Role

from Table;

当前SQL输出 #

USERID  ACCOUNT   SEQ   Current Role  Prev ROLE 
222     12863r6     1   Owner         NULL
222     12871r9     2   Owner         Owner
222     14142rr1    3   Owner         Owner
333     2563r013    1   Owner         Owner
333     36998r64    2   Admin         Owner
333     37001r05    3   Owner         Admin
333     37016r10    4   Owner         Owner

期望输出 #

USERID  ACCOUNT SEQ Current Role    Prev Role
222     12863r6     1   Owner       NULL
222     12871r9     2   Owner        Owner
222     14142rr1    3   Owner        Owner
333     2563r013    1   Owner       NULL
333     36998r64    2   Admin        Owner
333     37001r05    3   Owner        Admin
333     37016r10    4   Owner        Owner

解决方案 #

问题出在lag()函数没有按USER_ID分区，导致跨用户读取了上一条记录的角色。只需给lag()的窗口子句加上partition by USER_ID，同时排序直接使用account_date_created（和生成seq的排序逻辑一致，更准确）。

修改后的SQL：

SELECT
    USER_ID
   , ACCOUNT
   , row_number() over (partition by USER_ID order by account_date_created ) as seq
   , ROLE as CURRENT_ROW
   , lag(role) over (partition by USER_ID order by account_date_created)  as prev_Role
from Table;

这样每个用户的记录会单独分组，lag()仅在当前用户的记录范围内取上一条角色，新用户的第一条记录自然返回NULL，符合预期。

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