如何用SQL找出bar表beer列的最大相邻差值？

要解决这个问题，核心是要计算相邻分钟之间beer值的差值，然后找出最大的那个差值对应的分钟区间。你的初始SQL框架用了count(beer)，这其实不对——count是用来计数的，我们需要的是计算数值的差值，所以得用窗口函数或者自连接来实现。

下面分几种情况给出解决方案：

方案1：用窗口函数（推荐，支持SQL 2012+及现代数据库：MySQL8+、PostgreSQL、SQL Server等） #

这个方法用LAG()窗口函数来获取上一分钟的beer值，然后计算差值，再筛选出最大的那个：

WITH beer_diffs AS (
  SELECT
    minute,
    beer,
    -- 获取前一分钟的beer值和分钟数
    LAG(beer) OVER (ORDER BY minute) AS prev_beer,
    LAG(minute) OVER (ORDER BY minute) AS prev_minute,
    -- 计算绝对值差值（如果要同时考虑上升和下降的最大幅度）
    ABS(beer - LAG(beer) OVER (ORDER BY minute)) AS diff
  FROM bar
)
SELECT
  prev_minute AS 起始分钟,
  minute AS 结束分钟,
  diff AS 最大差值,
  prev_beer AS 起始值,
  beer AS 结束值
FROM beer_diffs
-- 筛选出等于最大差值的行，排除第一行（无前置数据）
WHERE diff = (SELECT MAX(diff) FROM beer_diffs)
AND prev_minute IS NOT NULL;

针对你的例子的结果： #

因为你的数据里最大差值是92-17=75，这个查询会返回：

方案2：只找最大下降差值（匹配你的人工观察） #

如果你只关心beer值下降的最大幅度（比如例子里从92跌到17），可以去掉绝对值，直接计算前值减当前值：

WITH beer_diffs AS (
  SELECT
    minute,
    beer,
    LAG(beer) OVER (ORDER BY minute) AS prev_beer,
    LAG(minute) OVER (ORDER BY minute) AS prev_minute,
    -- 只计算下降幅度（前值 - 当前值）
    prev_beer - beer AS drop_diff
  FROM bar
)
SELECT
  prev_minute AS 起始分钟,
  minute AS 结束分钟,
  drop_diff AS 最大下降差值,
  prev_beer AS 起始值,
  beer AS 结束值
FROM beer_diffs
WHERE drop_diff = (SELECT MAX(drop_diff) FROM beer_diffs)
AND prev_minute IS NOT NULL;

方案3：兼容老版本数据库（如MySQL 5.x，不支持窗口函数） #

如果你的数据库不支持窗口函数，可以用自连接的方式关联相邻分钟的行：

SELECT
  b1.minute AS 起始分钟,
  b2.minute AS 结束分钟,
  b1.beer - b2.beer AS 最大下降差值,
  b1.beer AS 起始值,
  b2.beer AS 结束值
FROM bar b1
JOIN bar b2 ON b2.minute = b1.minute + 1
-- 按下降幅度降序排列，取第一行就是最大的
ORDER BY (b1.beer - b2.beer) DESC
LIMIT 1;

这个方法通过b2.minute = b1.minute +1关联相邻的行，计算差值后排序取最大的那个。

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