如何将使用LAG窗口函数的SQL语句改写为采用EXISTS的查询形式

改写说明 #

你原SQL的逻辑是筛选各品类当年利润较上一年下滑的记录，返回对应品类及利润差额。改用EXISTS实现的写法如下，执行结果和原SQL完全一致：

WITH Sales AS (
    SELECT 'Office Supplies' Category , 2014 Year,22593.42 Profit UNION ALL
    SELECT 'Technology', 2014, 21492.83 UNION ALL
    SELECT 'Furniture', 2014,   5457.73 UNION ALL
    SELECT 'Office Supplies',   2015,   25099.53  UNION ALL
    SELECT 'Technology',    2015,   33503.87  UNION ALL
    SELECT 'Furniture', 2015,   50000.00  UNION ALL
    SELECT 'Office Supplies',   2016,   35061.23  UNION ALL
    SELECT 'Technology',    2016,   39773.99  UNION ALL
    SELECT 'Furniture', 2016,   6959.95
)
SELECT 
    s1.Category,
    s1.Profit - s2.Profit AS Diff
FROM Sales s1
WHERE EXISTS (
    SELECT 1 FROM Sales s2
    WHERE s2.Category = s1.Category
      AND s2.Year = s1.Year - 1
      AND s2.Profit > s1.Profit
)

逻辑解释 #

对于Sales表的每一条记录s1，EXISTS子查询会校验是否存在满足以下所有条件的记录s2：

• 和s1属于同一品类
• 年份比s1早一年
• 利润高于s1
  满足条件的s1就是利润同比下滑的记录，计算得到的Diff和原窗口函数LAG计算的结果完全相同。

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