如何用SQL实现固定基准行逐行计算差值而非相邻行差值？

你之前写的lag(avg_rev,1) over (order by product)本质是算相邻上一行和当前行的差值，基准值是“排在当前行前面的那一行”，会跟着排序位置乱跑，根本没法固定锚定product='A'那行的500营收值当计算基准，和需求不匹配。
直接用下面两种写法就能出正确结果：

• 简单易读版（小表用着最顺手）
  直接把A产品的营收值单独查出来当固定常量，逐行减当前行营收就行：

  SELECT
    product,
    avg_rev,
    (SELECT avg_rev FROM product WHERE product = 'A') - avg_rev AS diff_a
  FROM product
  

• 高效窗口版（大表跑着更快）
  用全表范围的窗口，把A产品的营收值提取出来当成每一行都能拿到的公共基准，不用额外多扫一遍表，性能更好：

  SELECT
    product,
    avg_rev,
    MAX(CASE WHEN product = 'A' THEN avg_rev END) OVER () - avg_rev AS diff_a
  FROM product
  

  这里的OVER ()是不带任何排序、分区的全表窗口，CASE判断只会在product是A的那行拿出对应的avg_rev值，其他行返回null，外层套MAX就能把这个值提出来，让全表每一行都能拿到固定的500当基准值做计算。

跑出来的结果完全和要求的目标表一致：A行差值是500-500=0，B行500-400=100，C行500-250=250，D行500-100=400。就算后续表内的行顺序调整、新增其他产品数据，这个提取出来的基准值也始终是A产品的平均营收，不会像lag函数那样跟着行序偏移变来变去。

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