如何获得任意连续范围内的最大值

Question

考虑无符号整数索引列中的以下值:

1
2
3
3
2
4
6
8
9

对于所提供的任何数字，我想获得其连续范围内的最大值(在存在下一个连续数字时继续向前迈进)。

例如，给定输入2;3和4的连续值在列表中存在(无论其顺序如何)，但5不存在;因此我们的连续范围将是2,3,4;因此期望值为4，即该连续范围内的最大值;提供了1、3或4中的任何一个，也应该得到4。因此:

input           expected output
-------------------------------
1,2,3,4                       4  -- any value of the input yields 4
5                             5  -- the input value doesn't even exist in the list
6                             6  -- it's the only value
7                             7  -- the input value doesn't even exist in the list
8,9                           9

那么，如何使用MySQL在任何连续范围内获得最大的值?

Answer 1

使用递归CTE:

WITH RECURSIVE cte AS (
SELECT x FROM tablename WHERE x = ?
UNION 
SELECT t.x
FROM tablename t INNER JOIN cte c
ON t.x = c.x + 1
)
SELECT COALESCE(MAX(x), ?) x FROM cte;

或，与DENSE_RANK()窗口函数:

SELECT COALESCE(MAX(CASE WHEN x = rn THEN x END), ?) x
FROM (
SELECT x, DENSE_RANK() OVER (ORDER BY x) + ? rn
FROM tablename
WHERE x > ? AND EXISTS (SELECT * FROM tablename WHERE x = ?)
) t

将?替换为所需的input值。

Answer 2

这可能是一个间隙隔离问题，可能需要使用窗口函数来解决，并使用CTE递归来解决。

首先，我们可以尝试使用CTE递归生成器调用startnum范围从最小的数字到最大的数据(从我们的样本是1,9)，因为它们之间有一些缺失的数字。

下一步我们可能会计算一个grp，它是gap-and-island问题的特征逻辑。

逻辑可能如下所示。

连续(重叠)数据是一个集合(continuous range of sequence) - (values ​​based on a certain order of conditions sequence)产生相同的分组。

所以我们可以用

• 序列连续范围:startNum
• 按一定顺序的值:dense_rank窗口函数。

使用这个逻辑，我们可能会得到一个grp列作为sqlfiddle

查询# 1

WITH RECURSIVE cte AS(
SELECT MIN(val) startNum, MAX(val) endNum
FROM T
UNION ALL
SELECT startNum + 1,endNum
FROM cte
WHERE startNum + 1 <= endNum
)
SELECT GROUP_CONCAT(DISTINCT startNum) input,
MAX(startNum) 'expected output'
FROM (
SELECT val,
startNum ,
startNum - cast(dense_rank() OVER(ORDER BY val)as signed) grp
FROM cte t1
LEFT JOIN T t2
ON t1.startNum = t2.val
) t1
GROUP BY grp
ORDER BY 2;

tbody> <<tr>567

输入	预期输出
1, 2, 3, 4	4
	5
	6
	7
8、9	9