Snowflake如何获取分区内上一个不同的设备记录信息

我有一张表，通过ID_NO跟踪位置，同时记录设备的发放日期以及与设备关联的产品/配件信息。同一位置同一时间只能有一台设备，设备会不定期更换配件，每次关联新配件时都会创建一条新记录。我希望为每条记录添加上一个不同的设备信息（如果有的话）。

现有表结构 #

当前查询及问题 #

我使用以下查询：

select
id_no
,device_no
,device_date
,product_no
,product_date
,lag(device_no) over (partition by id_no order by device_date, product_date) prev_device_no
,lag(device_date) over (partition by id_no order by device_date, product_date) prev_device_date
from device_data
order by id_no,device_date,product_date

但得到的结果中，PREV_DEVICE_NO和PREV_DEVICE_DATE会关联上一条记录，即使设备编号相同。我需要获取上一个不同的设备编号和日期，预期结果如下：

预期结果 #

核心疑问：在进行分区处理时，是否有其他函数可以获取上一个不同的值？

不需要特殊函数，通过分组标识+常规窗口函数的组合就能实现需求，以下是两种可行方案：

方案1：分组后关联上一设备信息 #

先将同一ID_NO下的相同DEVICE_NO记录归为一组，再针对分组获取上一组的设备信息：

with device_groups as (
    select
        id_no,
        device_no,
        device_date,
        product_no,
        product_date,
        -- 生成设备分组编号：设备变化时编号递增
        sum(case when device_no = lag(device_no) over (partition by id_no order by device_date, product_date) then 0 else 1 end) 
            over (partition by id_no order by device_date, product_date) as device_group
    from device_data
),
prev_device_info as (
    select
        id_no,
        device_group,
        lag(device_no) over (partition by id_no order by device_group) as prev_device_no,
        lag(device_date) over (partition by id_no order by device_group) as prev_device_date
    from (
        -- 每个分组仅保留一条设备基准记录
        select distinct id_no, device_group, device_no, device_date
        from device_groups
    ) t
)
select
    dg.id_no,
    dg.device_no,
    dg.device_date,
    dg.product_no,
    dg.product_date,
    pdi.prev_device_no,
    pdi.prev_device_date
from device_groups dg
left join prev_device_info pdi 
    on dg.id_no = pdi.id_no and dg.device_group = pdi.device_group
order by dg.id_no, dg.device_date, dg.product_date;

方案2：用LAST_VALUE填充最近的不同设备值 #

如果使用PostgreSQL 11+、SQL Server 2012+或Oracle 12c+，可利用LAST_VALUE结合条件判断简化查询，自动保留最近一次出现的不同设备值：

select
    id_no,
    device_no,
    device_date,
    product_no,
    product_date,
    last_value(case when device_no != lag(device_no) over (partition by id_no order by device_date, product_date) then lag(device_no) over (partition by id_no order by device_date, product_date) end) 
        over (partition by id_no order by device_date, product_date rows between unbounded preceding and current row) as prev_device_no,
    last_value(case when device_no != lag(device_no) over (partition by id_no order by device_date, product_date) then lag(device_date) over (partition by id_no order by device_date, product_date) end) 
        over (partition by id_no order by device_date, product_date rows between unbounded preceding and current row) as prev_device_date
from device_data
order by id_no, device_date, product_date;

原理说明 #

• 方案1通过分组将同一设备的所有记录聚合，再针对分组取上一组的设备信息，确保同一设备的所有记录都关联到上一个不同的设备。
• 方案2利用LAST_VALUE的窗口范围特性，自动填充当前记录之前最近一次出现的不同设备值，无需额外分组。

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