SQL 定位连续的值区间

SQL定位连续的值区间的攻略通常使用窗口函数和自连接完成，具体步骤如下：

1. 窗口函数生成行编号，按照要查询的列进行排序

在查询语句中使用ROW_NUMBER() OVER (ORDER BY column_name)来生成行编号，按照要查询的列进行排序，这个行编号将作为后面自连接表中的关联条件之一。

1. 将原表自连接

将原表自连接，连接条件为行编号相差一，同时还需要考虑其他列的条件限制，只有当后一行列值与前一行列值相差1时，才能判断这两行列值属于同一个连续区间。如下面的示例代码：

SELECT t1.*
FROM table_name t1
JOIN table_name t2
ON t1.row_number = t2.row_number + 1
AND t1.column_name = t2.column_name + 1

上述代码中，t1和t2是自连接得到的两个表，t1的行号为t2的行号+1，同时t1和t2的相邻行的列值相差1。

1. 使用GROUP BY进行结果合并

将自连接得到的结果按照起始值和结束值进行合并，得到连续区间的起始值和结束值。示例代码如下：

SELECT min(t1.column_name), max(t1.column_name)
FROM table_name t1
JOIN table_name t2
ON t1.row_number = t2.row_number + 1
AND t1.column_name = t2.column_name + 1
GROUP BY t1.column_name - t1.row_number

上述代码中，t1和t2是自连接得到的两个表，按照起始值和结束值进行分组，使用MIN和MAX函数计算出每个分组的起始值和结束值。

以下是两个实例：

1. 假设有一张名为user_activity的表，记录了用户每天的动态，有三个字段：id, user_id, date，其中user_id表示用户id，date表示日期，如下所示：

id | user_id | date
1    1        2022-01-01
2    1        2022-01-02
3    1        2022-01-03
4    1        2022-01-05
5    1        2022-01-07
6    2        2022-01-01
7    2        2022-01-03
8    2        2022-01-05

查询连续的日期区间，可以使用如下代码：

SELECT user_id, min(date), max(date)
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS row_number
  FROM user_activity
) t1
JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS row_number
  FROM user_activity
) t2
ON t1.user_id = t2.user_id
AND t1.row_number = t2.row_number + 1
AND DATE_ADD(t1.date, INTERVAL 1 DAY) = t2.date
GROUP BY user_id, t1.date - t1.row_number

上述代码中，将user_activity表自连接，连接条件为user_id相等、行号相差1且两行日期相差1天。按照user_id分组，t1.date - t1.row_number的计算结果不同的分组，代表不同的连续区间。

1. 假设有一张名为stock_price的表，记录了某只股票每天的收盘价，有两个字段：date, price，如下所示：

date         | price
'2022-01-01'   10
'2022-01-02'   11
'2022-01-03'   11
'2022-01-04'   10
'2022-01-05'   12
'2022-01-06'   13
'2022-01-07'   14

查询连续的收盘价区间，可以使用如下代码：

SELECT min(price), max(price)
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY date) AS row_number
  FROM stock_price
) t1
JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY date) AS row_number
  FROM stock_price
) t2
ON t1.row_number = t2.row_number + 1
AND t2.price = t1.price + 1
GROUP BY t1.price - t1.row_number

上述代码中，将stock_price表自连接，连接条件为行号相差1且两行price相差1。按照t1.price - t1.row_number的计算结果分组，代表不同的连续区间。