•LAG(expr,n) 返回当前行的前 n 行的expr值

•LEAD(expr,n) 返回当前行的后 n 行的expr的值

可用于查询本月与上月数据比较的场景

WITH s AS (
SELECT '2025-01' AS billdate, 1000 AS money
UNION ALL
SELECT '2025-03' AS billdate, 1100 AS money
UNION ALL
SELECT '2025-05' AS billdate, 1200 AS money
UNION ALL
SELECT '2025-07' AS billdate, 1300 AS money
UNION ALL
SELECT '2025-09' AS billdate, 1400 AS money
UNION ALL
SELECT '2025-11' AS billdate, 1500 AS money
UNION ALL
SELECT '2025-02' AS billdate, 1600 AS money
UNION ALL
SELECT '2025-04' AS billdate, 1700 AS money
UNION ALL
SELECT '2025-06' AS billdate, 1800 AS money
UNION ALL
SELECT '2025-08' AS billdate, 1900 AS money
UNION ALL
SELECT '2025-10' AS billdate, 2000 AS money
UNION ALL
SELECT '2025-12' AS billdate, 2100 AS money
UNION ALL
SELECT '2026-01' AS billdate, 2200 AS money
)
SELECT *,LAG(money,1) OVER (ORDER BY billdate) AS pre_money,LEAD(money,1) OVER(ORDER BY billdate) AS aft_money FROM s

版权申明：内容来源网络，版权归原创者所有，如有侵权请联系删除

想了解更多干货，可通过下方扫码关注

可扫码添加上智启元官方客服微信👇