如何使用SQL计算行之间的差异？

Question

我有一个任务，需要创建一个表格，并在其中添加一个新列，显示当前行和下一行年龄的差值。年龄应按降序排列。这是否可以使用SQL完成? 我不确定应该使用哪个SQL语句，因为任务中没有具体说明。

我理解应该在括号内填写什么内容。

SELECT name, age, (...) AS difference
FROM Animals
ORDER BY age DESC;

我有一个名为“动物”的表

id  |  name  | age
 1  | 莫莉  |  4
 2  | 杰克逊|  8 
 3  | 温卡  |  38
 4  | 波利  |  7

结果表应该是这样的:

  名字  | 年龄 | 差异
 温卡  | 38  |  30
 杰克逊|  8  |  1
 波利  |  7  |  3
 莫莉  |  4  |

- lisa

---

ANSI/ISO标准SQL, 该网站中的sql标签包含LAG()和LEAD()，可读取先前和下一个记录。但是因为您提到了括号，我假设您正在使用SQL Server（MSSQL）作为关系数据库管理系统。 - Raymond Nijland

可能是SQL行之间的差异的重复问题。 - Graiton

当年龄出现"并列"的情况时，应该怎么处理？ - Raymond Nijland

Answer 1

您需要使用lead()函数：

SELECT 
  name, 
  age, 
  age - lead(age) over (order by age desc, name) AS difference
FROM Animals
ORDER BY age DESC

查看演示.
结果:

> name    | age | difference
> :------ | --: | ---------:
> Wonka   |  38 |         30
> Jackson |   8 |          1
> Polly   |   7 |          3
> Molly   |   4 |       

- forpas

---

@Lisa age - lead(age) over (order by age desc, name <ASC|DESC>) AS difference 可能甚至是“必需的”，以更好地处理“平局”。因为当年龄相同时，lead(age) over (order by age desc) 将随机返回。但不确定这对主题发起者有多重要。 - Raymond Nijland

Answer 2

您需要使用lag()/lead()和order by：最初的回答。

select a.*,
       (a - lag(age) over (order by age)) as diff
from animals a
order by age desc;

lag()中的order by不需要与外部查询中的order by匹配。第一个定义了“上一行”，而第二个则用于数据的呈现。"原始答案"

- Gordon Linoff

---

@Lisa age - lead(age) over (order by age desc, name <ASC|DESC>) AS difference 可能甚至是“必需的”，以更好地处理“平局”。因为当年龄相同时，lead(age) over (order by age desc) 将随机返回。但不确定这对主题发起者有多重要。 - Raymond Nijland

Answer 3

您可以使用 MAX 函数来实现所需的结果，该函数作用于相应的行窗口。

with Animals as (
  select 1 as id, 'Molly' as name, 4 as age union all
  select 2, 'Jackson', 8 union all
  select 3, 'Wonka', 38 union all
  select 4, 'Polly', 7
)
select
  name, age,
  age - max(age) over(
          order by age
          rows between unbounded preceding
          and 1 preceding
        )
from Animals
order by age desc;

输出：

+---------+-----+------------+
|  name   | age | difference |
+---------+-----+------------+
| Wonka   |  38 | 30         |
| Jackson |   8 | 1          |
| Polly   |   7 | 3          |
| Molly   |   4 | NULL       |
+---------+-----+------------+

- Zanjato