mysql - 如何根据列值的差异在 SQL 中查找第一个有效行

Question

我试图找到一个可靠的查询，它返回可接受的插入范围的第一个实例。

研究:

• 下面的一些链接解决了类似的问题，但我无法找到任何适合我的链接。
• Find first available date, given a date range in SQL
• Find closest date in SQL Server
• MySQL difference between two rows of a SELECT Statement
• How to find a gap in range in SQL
• 还有更多...

目标查询函数:

• InsertRange(1) = (StartRange(i) - EndRange(i-1)) > NewValue

其中 InsertRange(1) 是查询应返回的值。换句话说，这将是满足上述条件的第一个实例。

表结构:

• 主键:StartRange
• 起始范围(i-1) <起始范围(i)
• 开始范围(i-1) + 结束范围(i-1) <开始范围(i)

示例数据集

下面是一个示例用户表(3 列)，具有一组范围分布。 StartRanges 始终严格按照升序排列，UserID 是任意字符串，只有 StartRange 和 EndRange 的顺序很重要:

StartRange  EndRange    UserID312         6896        user07134        16268       user116877       22451       user223137       25142       user325955       28272       user428313       35172       user535593       38007       user638319       38495       user738565       45200       user846136       48007       user9

My current Query

I am trying to use this query at the moment:

SELECT t2.StartRange, t2.EndRange 
FROM user AS t1, user AS t2 
WHERE (t1.StartRange - t2.StartRange+1) > NewValue 
ORDER BY t1.EndRange 
LIMIT 1

示例案例

给定表格，如果 NewValue = 800，则返回的答案应为 23137。这意味着，第一个可用插槽将在 user3 和 user4 之间(实际插槽大小 = 813):

InsertRange(1) = (StartRange(i) - EndRange(i-1)) > NewValue
InsertRange    = (StartRange(6) - EndRange(5))   > NewValue
   23137       =    25955       -     25142      >   800

更多评论

• 我上面的查询似乎适用于 StartRanges 紧密排列的特殊情况(即 StartRange(i) = StartRange(i-1) + EndRange(i-1) + 1)。这不再适用于一组不太紧凑的 StartRanges

Answer 1

请记住，SQL 表没有隐式行顺序。不过，按 StartRange 值对表进行排序似乎很公平。

我们可以通过编写一个查询来获取与它前面的行配对的每一行来解决这个问题。在 MySQL 中，很难做到漂亮，因为它缺少行编号功能。

这有效(http://sqlfiddle.com/#!9/4437c0/7/0)。它的性能可能很糟糕，因为它会生成 O(n^2) 中间行。 user0 没有行；它不能与任何前面的行配对，因为没有。

   select MAX(a.StartRange) SA, MAX(a.EndRange) EA, 
          b.StartRange SB, b.EndRange EB , b.UserID
     from user a
     join user b ON a.EndRange <= b.StartRange
    group by b.StartRange, b.EndRange, b.UserID

然后，您可以将其用作子查询，并应用您的条件，即

1. 差距 >= 800
2. 第一个匹配行(最低的 StartRange 值)ORDER BY SB
3. 只有一个LIMIT 1

这是查询 ( http://sqlfiddle.com/#!9/4437c0/11/0 )

SELECT SB-EA Gap, 
       EA+1 Beginning_of_gap, SB-1 Ending_of_gap, 
       UserId UserID_after_gap
  FROM (
       select MAX(a.StartRange) SA, MAX(a.EndRange) EA, 
              b.StartRange SB, b.EndRange EB , b.UserID
         from user a
         join user b ON a.EndRange <= b.StartRange
        group by b.StartRange, b.EndRange, b.UserID
        ) pairs
  WHERE SB-EA >= 800
  ORDER BY SB
  LIMIT 1

请注意，您实际上可能想要最小的匹配间隙而不是第一个匹配间隙。这称为最适合，而不是最适合。为此，您可以使用 ORDER BY SB-EA。

编辑:还有另一种使用 MySQL 连接相邻行的方法，它没有 O(n^2) 性能问题。它涉及使用用户变量来模拟 row_number() 函数。涉及的查询是一个毛球(这是一个技术术语)。在这个问题的答案的第三个备选方案中对此进行了描述。 How do I pair rows together in MYSQL?