如何使用match_recognize实现SQL员工人数阈值分组需求

完全可以用MATCH_RECOGNIZE完成这个需求，下面结合示例场景给出具体实现方案：

假设输入表结构 #

假设你的输入表名为employee_counts，包含以下字段和数据：

实现SQL #

WITH base_data AS (
    SELECT 
        region,
        role,
        emp_count,
        -- 给USA地区打标识，方便后续筛选或保留
        CASE WHEN region = 'USA' THEN 'Y' ELSE 'N' END AS is_usa
    FROM employee_counts
)
SELECT 
    region,
    final_role,
    total_emp,
    is_usa
FROM base_data
MATCH_RECOGNIZE(
    PARTITION BY region, is_usa
    ORDER BY emp_count ASC
    MEASURES
        CASE 
            WHEN classifier() = 'SMALL_GROUP' THEN 'Other Roles'
            ELSE role
        END AS final_role,
        SUM(emp_count) OVER (PARTITION BY region, match_number) AS total_emp
    ONE ROW PER MATCH
    PATTERN (SMALL_GROUP+ | LARGE_ROLE)
    DEFINE
        SMALL_GROUP AS SUM(emp_count) OVER (PARTITION BY region ORDER BY emp_count ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) < 6,
        LARGE_ROLE AS emp_count >= 6
)
-- 按需选择：过滤USA地区，或移除WHERE保留带标识的所有记录
WHERE is_usa = 'N';

逻辑说明 #

1. base_data CTE：先给USA地区添加标识字段is_usa，既支持后续过滤，也可以选择保留该地区并通过标识区分。
2. MATCH_RECOGNIZE核心逻辑：
   • PARTITION BY region, is_usa：按地区和USA标识分组，确保每个地区独立处理。
   • ORDER BY emp_count ASC：按员工数从小到大排序，优先合并人数最少的角色。
   • PATTERN规则：匹配两种场景——连续的小角色组合（SMALL_GROUP+），或单个满足阈值的大角色（LARGE_ROLE）。
   • DEFINE定义：
     • SMALL_GROUP：累计员工数小于6的角色会被归为同一组，直到累计总数≥6。
     • LARGE_ROLE：员工数本身≥6的角色直接保留原名称。
   • MEASURES输出：将小角色组统一命名为Other Roles，计算每组的总员工数；大角色保留原名称和人数。
3. 筛选控制：通过最后一行的WHERE子句可以直接过滤USA地区，若需要保留该地区只需移除这个条件，通过is_usa字段区分即可。

示例输出结果 #

执行上述SQL后，会得到如下结果（已过滤USA）：

如果需要调整合并逻辑（比如从大到小合并、或合并到刚好≥6即停止），只需修改ORDER BY的排序方向，或调整DEFINE中的累计条件即可。

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