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 ║
╚════╩══════╩═══════╝

因此，所有符合条件的行直到出现条件异常的地方都成为了一个组的一部分。组的编号不一定需要遵循这种模式，我只是觉得这是一个逻辑/简单的编号方式，以便解释我正在寻找的解决方案。

- user2755428

---

你能解释一下如何根据“数据大于10”的条件获取这些组吗？我会得到 “1，2，3，3，4” 作为组ID。 - Gordon Linoff

第一行符合条件，第二行也符合条件，但第三行不符合条件... 因此，符合条件的每一行都会被分组，直到出现异常。如果原帖不清楚，很抱歉。 - user2755428

为什么值为3的行被单独分成一组？ - Gordon Linoff

@GordonLinoff 在进行分组后，我希望能够统计每个组中的项目数量。如果说第二组中有3个项目，那是不正确的，因为实际上只有2个项目符合条件。 - user2755428

Answer 1

你可以通过查找每一行中数据<=10来计算群组标识符。然后，群组标识符就是给定行之前满足该条件的行数。

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;

- Gordon Linoff

---

2count() 是必要的吗？ - user2755428

Answer 2

这可以很容易地通过递归查询实现：

;WITH CTE 
     AS (SELECT *, 
                1 AS [GROUP] 
         FROM   TABLEB 
         WHERE  ID = 1 
         UNION ALL 
         SELECT T1.ID, 
                T1.DATA, 
                CASE 
                  WHEN T1.DATA < 10 THEN T2.[GROUP] + 1 
                  ELSE T2.[GROUP] 
                END [GROUP] 
         FROM   TABLEB T1 
                INNER JOIN CTE T2 
                        ON T1.ID = T2.ID + 1) 
SELECT * 
FROM   CTE 

可以在SQL Fiddle找到一个工作示例。

祝好运！

- Gidil