SQL查询求助：计算区域年度差值并筛选负差值最多的TOP3区域

修复LAG()跨区域取数的问题 #

你当前的LAG()函数没按区域做分区，导致不同区域的年度数据被混在一起计算，所以只有结果第一行是NULL，其他区域的首年差值错误取到了别的区域的最后一年数据。

解决方法很直接：在LAG()的OVER子句里加上PARTITION BY 区域字段，再按年度排序，这样就只会在同一个区域内取上一年的数据，每个区域的首个年度差值自然会返回NULL。

举个实际代码例子（假设表名为area_stats，区域字段是area，年度字段是stat_year，要计算差值的指标字段是metric）：

SELECT
  area,
  stat_year,
  metric,
  metric - LAG(metric) OVER (PARTITION BY area ORDER BY stat_year) AS year_diff
FROM area_stats;

筛选负差值出现次数最多的前3个区域 #

可以基于上面的差值计算结果，通过统计分组的方式实现需求，用CTE或者子查询都能完成：

用CTE的写法（主流SQL方言均支持） #

WITH area_diff_stats AS (
  SELECT
    area,
    metric - LAG(metric) OVER (PARTITION BY area ORDER BY stat_year) AS year_diff
  FROM area_stats
)
SELECT
  area,
  COUNT(*) AS negative_diff_times
FROM area_diff_stats
WHERE year_diff < 0
GROUP BY area
ORDER BY negative_diff_times DESC
LIMIT 3;

子查询写法（兼容老版本SQL） #

SELECT
  area,
  COUNT(*) AS negative_diff_times
FROM (
  SELECT
    area,
    metric - LAG(metric) OVER (PARTITION BY area ORDER BY stat_year) AS year_diff
  FROM area_stats
) AS temp
WHERE year_diff < 0
GROUP BY area
ORDER BY negative_diff_times DESC
LIMIT 3;

核心逻辑是：先算出每个区域的年度连续差值，过滤出差值为负的记录，按区域分组统计负差值的次数，最后按次数从多到少排序，取前3个区域。

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