SQL - 通过条件对行进行分组，直到发现异常

Question

我正在尝试根据某些条件将组列添加到数据集中。举个简单的例子:

╔════╦══════╗
║ ID ║ DATA ║
╠════╬══════╣
║  1 ║   12 ║
║  2 ║   20 ║
║  3 ║    3 ║
║  4 ║   55 ║
║  5 ║   11 ║
╚════╩══════╝

假设我们的标准是数据应大于 10。那么结果应类似于:

╔════╦══════╦═══════╗
║ ID ║ DATA ║ GROUP ║
╠════╬══════╬═══════╣
║  1 ║   12 ║     1 ║
║  2 ║   20 ║     1 ║
║  3 ║    3 ║     2 ║
║  4 ║   55 ║     3 ║
║  5 ║   11 ║     3 ║
╚════╩══════╩═══════╝

因此，在发生条件异常之前满足条件的所有行都成为组的一部分。组的编号不一定需要遵循这种模式，我只是觉得这是一个逻辑/简单的编号来解释我正在寻找的解决方案。

Answer 1

您可以通过查找 data <= 10 的每一行来计算组标识符。然后，组标识符就是给定行之前条件为 true 的行数。

select t.*,
       (select count(*)
        from t t2
        where t2.id <= t.id and
              t2.data <= 10
       ) as groupId
from t;

SQL Server 2012 具有累积和语法。该语句在该数据库中会更简单:

select t.*,
       sum(case when t2.data <= 10) over (order by id) as groupId
from t;

编辑:

上面没有考虑到小于10的值在它们自己的组中。上面的逻辑是他们开始一个新的组。

以下内容分配具有此约束的组 ID:

select t.*,
       ((select 2*count(*)
         from t t2
         where t2.id < t.id and
               t2.data <= 10
        ) + (case when t.id <= 10 then 1 else 0 end)
       ) as groupId
from t;