如何嵌套两个MySQL查询，将子查询结果代入LAG函数偏移量参数

核心原因 #

MySQL 中窗口函数LAG()的第二个偏移量参数仅支持传入固定常量，不支持动态传入列值（也就是你关联得到的B.N），所以你之前的写法无法生效。

解决思路 #

我们可以通过给每个机器的时间序列数据编号，再匹配对应偏移位置的数值，替代动态LAG的效果：

1. 先给每个machine_id下符合时间、组件过滤条件的记录，按时间升序编排行号
2. 关联统计每个machine_id总记录数N的子查询
3. 用自关联匹配到每个行对应偏移N-1位置的utilisation值，做差值计算

可用SQL示例 #

WITH hardware_ranked AS (
    -- 给每个机器的时序数据编行号
    SELECT
        time,
        machine_id,
        utilisation,
        ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY time) AS rn
    FROM hardware
    WHERE
        $__unixEpochFilter(time)
        AND component = 'network'
),
machine_count AS (
    -- 统计每个机器的总记录数N
    SELECT
        machine_id,
        COUNT(utilisation) AS N
    FROM hardware
    WHERE
        $__unixEpochFilter(time)
        AND component = 'network'
    GROUP BY machine_id
)
SELECT
    hr.time AS "time",
    hr.machine_id,
    hr.utilisation - hr_prev.utilisation AS DataUsed
FROM hardware_ranked hr
JOIN machine_count mc ON hr.machine_id = mc.machine_id
-- 关联偏移N-1位置的记录
LEFT JOIN hardware_ranked hr_prev 
    ON hr.machine_id = hr_prev.machine_id 
    AND hr_prev.rn = hr.rn - (mc.N - 1)
ORDER BY hr.time

如果你使用的MySQL版本低于8.0不支持CTE语法，把上述CTE改写为嵌套子查询即可，逻辑完全一致。

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