SQL/BigQuery中使用LAG()函数获取上一行值异常问题修复

问题 #

我通过以下查询获取目标数据表：

with
PairingActivityInPairing AS (
SELECT  DISTINCT
    P.Id AS PairingId,
    PD.Id AS PairingDutyId,
    PA.Id AS PairingActivityId,
    E.Id AS EmployeeId,
    PEV.PairingEventTypeId,
    PEV.StartDate AS PEStartDate,
    FF.ArrivalActual AS FFArrival,
    TZ.ZuluOffset
FROM
   <my_table>
ORDER BY
   P.Id, PD.Id, PA.Id

原始查询结果：

我希望按PairingId、PairingDutyId分区，获取每行对应的上一行FFArrival值，于是添加了LAG()函数：

with
PairingActivityInPairing AS (
SELECT  DISTINCT
    P.Id AS PairingId,
    PD.Id AS PairingDutyId,
    PA.Id AS PairingActivityId,
    E.Id AS EmployeeId,
    PEV.PairingEventTypeId,
    PEV.StartDate AS PEStartDate,
    FF.ArrivalActual AS FFArrival,
    LAG(FF.ArrivalActual) OVER (PARTITION BY P.Id, PD.Id ORDER BY PA.Id) AS LagFFArrival,
    TZ.ZuluOffset
FROM
   <my_table>
ORDER BY
   P.Id, PD.Id, PA.Id

但结果不符合预期：

期望结果：

解决方案 #

问题核心是**DISTINCT与窗口函数的执行顺序冲突**：窗口函数会先于DISTINCT执行，导致LAG计算时可能处理重复行，后续去重时出现异常；同时当前行FFArrival为null时，默认LAG不会跳过null，但我们需要按行顺序取上一行的值。

修正方案是先对原始数据去重，再在去重后的数据集上计算LAG：

with
-- 先获取去重后的原始数据集
DistinctData AS (
SELECT  DISTINCT
    P.Id AS PairingId,
    PD.Id AS PairingDutyId,
    PA.Id AS PairingActivityId,
    E.Id AS EmployeeId,
    PEV.PairingEventTypeId,
    PEV.StartDate AS PEStartDate,
    FF.ArrivalActual AS FFArrival,
    TZ.ZuluOffset
FROM
   <my_table>
),
-- 在去重后的数据上计算LAG
PairingActivityInPairing AS (
SELECT
    *,
    LAG(FFArrival) OVER (PARTITION BY PairingId, PairingDutyId ORDER BY PairingActivityId) AS LagFFArrival
FROM DistinctData
ORDER BY
   PairingId, PairingDutyId, PairingActivityId
)
SELECT * FROM PairingActivityInPairing;

说明 #

1. 将DISTINCT分离到独立CTE中，确保先得到唯一行，再对这些行应用LAG函数，避免窗口函数在重复行上计算导致的异常结果。
2. 处理后每个分区内的第一行LagFFArrival为null，后续行正确取上一行的FFArrival，即使当前行FFArrival为null，也能获取到上一行的有效值，完全符合期望结果。

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