Oracle条件COUNT查询：按第二列±1范围分组统计第三列

我来帮你搞定这个Oracle的计数问题！先再明确下你的需求：我们要对第三列（假设叫col3）分组，在每个分组里，把第二列（col2）数值相差在±1范围内的记录归为一组，每组计1次；如果某条记录的col2和同组其他记录的差值超过±1，就单独计1次。最终你期望的总计数是5，完全没问题，用窗口函数就能实现。

解决思路 #

核心是用窗口函数标记连续的数值组：

• 先按col3分组、col2排序，用LAG()函数获取当前行的上一行col2值
• 判断当前行与上一行的col2差值是否超过1，超过则标记为新组的开始
• 累计新组标记，生成每个记录的分组编号，最后统计所有不同分组的数量

完整Oracle查询语句 #

假设你的表名为your_table，三列分别命名为col1、col2、col3（替换成你实际的表名和列名即可）：

WITH grouped_data AS (
    SELECT 
        col3,
        col2,
        -- 标记当前行是否为新组的起始行
        CASE 
            WHEN LAG(col2) OVER (PARTITION BY col3 ORDER BY col2) IS NULL THEN 1
            WHEN ABS(col2 - LAG(col2) OVER (PARTITION BY col3 ORDER BY col2)) > 1 THEN 1
            ELSE 0
        END AS is_new_group
    FROM your_table
),
group_numbers AS (
    SELECT 
        col3,
        -- 累计新组标记，生成唯一的分组ID
        SUM(is_new_group) OVER (PARTITION BY col3 ORDER BY col2) AS group_id
    FROM grouped_data
)
-- 统计所有独立分组的总数
SELECT COUNT(DISTINCT group_id) AS total_count
FROM group_numbers;

代码解释 #

1. grouped_data CTE：
   • 按col3分组、col2升序排序，确保我们按数值顺序处理每条记录
   • LAG(col2)获取同组前一行的col2值，判断当前行是否是新组：
     • 分组的第一行（LAG返回NULL）直接标记为新组
     • 若当前col2与前一行的差值绝对值>1，标记为新组
2. group_numbers CTE：
   • 对每个col3分组，累计is_new_group的值，这样连续符合条件的行会共享同一个group_id，新组的group_id会递增
3. 最终查询：
   • 统计所有不同group_id的数量，就是你要的总计数

验证示例数据 #

用你提供的测试数据验证：

• P021948的col2值为9441、9442、9446：
  • 9441是第一行，group_id=1；9442与9441差1，共享group_id=1；9446与9442差4>1，group_id=2 → 共2组
• P021950（9443）、P021951（9444）、P021952（9445）各为单独组 → 各1组
• 总计2+1+1+1=5，完全符合你的期望

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