Oracle中查询当年、上年销售额及二者差值的SQL实现

针对需求，直接使用Oracle内置的LAG窗口函数即可实现，无需自连接表，写法简洁执行效率高，对应SQL语句如下：

SELECT
    year,
    total_sale AS current_total_sale,
    LAG(total_sale, 1) OVER (ORDER BY year) AS previous_total_sale,
    total_sale - LAG(total_sale, 1) OVER (ORDER BY year) AS difference
FROM Sales
ORDER BY year;

语句说明 #

• LAG()是Oracle的窗口函数，语法为LAG(取值字段, 偏移行数) OVER(排序规则)，这里按年份升序排序后，取当前行往前偏移1行的销售额，也就是上一年的总销售额。
• 排序后第一行（即2015年的数据）往前没有可取值的行，会自动返回NULL，和预期输出的第一行结果完全匹配。
• 销售额差值直接用当年销售额减上年销售额计算即可，上年值为NULL时，差值计算结果也会自动返回NULL，不需要额外写判空逻辑。

如果表中存在年份断层（例如缺失2017年数据），需要严格匹配年份差为1的上年数据，而非排序后的上一行，可以使用左连接写法：

SELECT
    a.year,
    a.total_sale AS current_total_sale,
    b.total_sale AS previous_total_sale,
    a.total_sale - b.total_sale AS difference
FROM Sales a
LEFT JOIN Sales b ON a.year = b.year + 1
ORDER BY a.year;

上述两种写法在年份连续的场景下，返回结果和你给出的预期格式完全一致。

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