按条件统计行并分组剩余数据：标记行组是否满足行数阈值

现有数据表结构及数据如下：

| RowID|  Date_id   | Diff
| -----+------------+-----
|  78  | 2010-07-17 | 1
|  80  | 2010-07-18 | 1
|  81  | 2010-07-19 | 1
|  82  | 2010-07-20 | 1
|  83  | 2012-05-22 | 670
|  84  | 2012-05-23 | 1
|  85  | 2012-05-24 | 1
--
| 300  | 2012-12-24 | 1
| 301  | 2012-12-25 | 1
| 302  | 2012-12-26 | 1

需求：

• 以Diff > 183的行为分割点，将数据划分为多个分组（无分割点则整表为一个分组）
• 统计每个分组的行数，若行数大于100，组内所有行标记为Y，否则标记为N
• 期望结果如下：

| RowID|  Date_id   | Diff | 100
| -----+------------+------+-----
|  78  | 2010-07-17 | 1    | N
|  80  | 2010-07-18 | 1    | N
|  81  | 2010-07-19 | 1    | N
|  82  | 2010-07-20 | 1    | N
|  83  | 2012-05-22 | 670  | Y
|  84  | 2012-05-23 | 1    | Y
|  85  | 2012-05-24 | 1    | Y
--
| 300  | 2012-12-24 | 1    | Y
| 301  | 2012-12-25 | 1    | Y
| 302  | 2012-12-26 | 1    | Y

可以通过窗口函数+分组统计实现，以下是基于标准SQL的代码（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库）：

WITH grouped_data AS (
    -- 生成每个行的分组ID，以Diff>183的行为分割点
    SELECT 
        RowID,
        Date_id,
        Diff,
        -- 累积统计当前行及之前的分割点数量，作为组ID
        SUM(CASE WHEN Diff > 183 THEN 1 ELSE 0 END) OVER (ORDER BY RowID) AS group_id
    FROM your_table
),
group_counts AS (
    -- 统计每个分组的总行数
    SELECT 
        group_id,
        COUNT(*) AS row_count
    FROM grouped_data
    GROUP BY group_id
)
-- 根据分组行数标记Y/N
SELECT 
    gd.RowID,
    gd.Date_id,
    gd.Diff,
    CASE WHEN gc.row_count > 100 THEN 'Y' ELSE 'N' END AS `100`
FROM grouped_data gd
JOIN group_counts gc ON gd.group_id = gc.group_id
ORDER BY gd.RowID;

逻辑说明 #

1. 分组ID生成：通过SUM() OVER (ORDER BY RowID)累积计算分割点（Diff>183）的数量，每遇到一个分割点，后续行的组ID自动+1，实现数据分组。
2. 分组行数统计：对每个组ID统计总行数。
3. 标记结果：根据分组行数是否大于100，给每行标记Y或N。

如果数据库不支持CTE（如MySQL 5.x），可以用子查询改写：

SELECT 
    gd.RowID,
    gd.Date_id,
    gd.Diff,
    CASE WHEN gc.row_count > 100 THEN 'Y' ELSE 'N' END AS `100`
FROM (
    SELECT 
        RowID,
        Date_id,
        Diff,
        SUM(CASE WHEN Diff > 183 THEN 1 ELSE 0 END) OVER (ORDER BY RowID) AS group_id
    FROM your_table
) gd
JOIN (
    SELECT 
        group_id,
        COUNT(*) AS row_count
    FROM (
        SELECT 
            SUM(CASE WHEN Diff > 183 THEN 1 ELSE 0 END) OVER (ORDER BY RowID) AS group_id
        FROM your_table
    ) t
    GROUP BY group_id
) gc ON gd.group_id = gc.group_id
ORDER BY gd.RowID;

内容的提问来源于stack exchange，提问作者Ticho