SQL 定位连续值区间的开始值和结束值

定位连续值区间的开始值和结束值可以使用SQL窗口函数中的LEAD和LAG函数，它们可以分别返回当前行之后和之前的行，通过与当前行进行比较来确定区间的开始和结束。以下是详细步骤的攻略和两条实例：

步骤

1.输入LEAD或LAG函数，然后在括号中输入要比较的字段和偏移量，偏移量表示要比较的行是当前行之前还是之后的第几行。

2.使用OVER关键字定义分组，以确保针对每个分组的行应用函数。

3.使用CASE语句检查比较字段的差异，以确定区间的开始和结束位置。

4.将结果分组并按照需要进行排序。

示例1

假设我们有以下sales表：

id	date	revenue
1	2021-01-01	100
2	2021-01-02	200
3	2021-01-03	300
4	2021-01-06	400
5	2021-01-07	500
6	2021-01-08	600

我们想要定位销售额连续上升的区间。

可以使用以下代码：

SELECT 
  MIN(date) AS start_date, MAX(date) AS end_date, MIN(id) AS start_id, MAX(id) AS end_id, SUM(revenue) AS total_revenue
FROM (
  SELECT 
    *, 
    CASE 
      WHEN revenue <= LAG(revenue) OVER (ORDER BY date) THEN 1 
      ELSE 0 
    END AS new_group
  FROM sales
) AS t
WHERE new_group = 1
GROUP BY new_group
ORDER BY start_date;

在这个例子中，我们首先使用LEAD函数在每个日期行之后查找销售额，并使用LAG函数在每个日期行之前查找销售额。然后，我们使用CASE语句检查比较结果，并将其分为新的分组。最后，我们按照新的分组分组并按照销售日期排序。

输出将显示销售额连续上升的区间的开始和结束日期，以及总销售额和开始和结束行的ID：

start_date	end_date	start_id	end_id	total_revenue
2021-01-01	2021-01-03	1	3	600
2021-01-06	2021-01-08	4	6	1500

示例2

假设我们有以下students表：

id	name	grade
1	Alice	90
2	Bob	60
3	Carol	70
4	David	80
5	Ellen	90
6	Frank	70

我们想要找到成绩连续下降的区间。

可以使用以下代码：

SELECT 
  MIN(id) AS start_id, MAX(id) AS end_id, MIN(grade) AS end_grade, MAX(grade) AS start_grade
FROM (
  SELECT 
    *, 
    CASE 
      WHEN grade >= LEAD(grade) OVER (ORDER BY id) THEN 1 
      ELSE 0 
    END AS new_group
  FROM students
) AS t
WHERE new_group = 1
GROUP BY new_group
ORDER BY start_id;

在这个例子中，我们首先使用LEAD函数在每个id行之后查找成绩，并使用GRADE函数在每个id行之前查找成绩。然后，我们使用CASE语句检查比较结果，并将其分为新的分组。最后，我们按照新的分组分组并按照分数开始ID排序。

输出将显示成绩连续下降的区间的开始和结束行的ID以及开始和结束分数：

start_id	end_id	end_grade	start_grade
2	3	70	60
6	6	70	70

在这个例子中，我们只有两个区间，因为有两个连续的学生成绩下降了。