SQL技术求助：获取加油交易当前与上一笔信息并计算指标

直接上可运行的SQL代码，适配你的表结构和需求：

SELECT
    File AS 当前来源信息,
    FleetNo_PT AS 资产编号,
    Date_Refuel AS 当前加油日期,
    Quantity AS 当前加油量,
    Reading AS 当前里程表读数,
    -- 提取上一笔交易的核心信息
    LAG(Date_Refuel) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel) AS 上一笔加油日期,
    LAG(Reading) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel) AS 上一笔里程表读数,
    LAG(Quantity) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel) AS 上一笔加油量,
    -- 计算需求字段
    DATEDIFF(DAY, LAG(Date_Refuel) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel), Date_Refuel) AS 加油间隔天数,
    Reading - LAG(Reading) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel) AS KM_Driven_行驶里程,
    CASE 
        WHEN Quantity > 0 AND LAG(Reading) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel) IS NOT NULL
        THEN (Reading - LAG(Reading) OVER (PARTITION BY FleetNo_PT ORDER BY Date_Refuel)) / Quantity
        ELSE NULL
    END AS 油耗率_每升行驶公里数
FROM
    你的加油交易表名 -- 替换为实际表名
ORDER BY
    FleetNo_PT, Date_Refuel;

核心逻辑说明： #

• PARTITION BY FleetNo_PT：严格按同一资产分组，避免跨资产取错上一笔数据
• ORDER BY Date_Refuel：按加油日期排序，确保取到的是时间顺序上的前一笔交易
• LAG()函数：SQL里专门用于获取分组排序后上一行数据的窗口函数，和你在DAX里用的前值逻辑本质一致，但效率更高
• 油耗率的CASE判断：防止出现除以0或无前置里程的错误计算值

额外提醒： #

1. 如果同一资产同一天有多笔交易，建议在ORDER BY里补充交易时间/记录ID等字段，确保排序精准
2. 每个资产的第一笔交易，所有关联上一笔的字段会显示NULL，衍生计算值也会是NULL，属于正常情况（无前置数据可关联）
3. 记得把代码里的你的加油交易表名替换成实际的表名称

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