SQL按id分组统计polarity变化间隔行数的实现方法

需求说明 #

按id分组，统计相邻两次polarity变化之间的行数，源表包含id、polarity、date三个字段：

样例数据 #

• id=1：
  • 12/1 polarity=0，12/2 polarity=1，12/3-12/4 polarity=0，12/5 polarity=1
• id=2：
  • 12/1-12/3 polarity=0，12/4 polarity=1，12/5-12/7 polarity=0，12/8 polarity=1

期望输出 #

返回id、n两个字段，n为间隔行数：

• id=1 对应n值：1、2
• id=2 对应n值：3、3

实现逻辑 #

这是典型的SQL连续值分组（岛屿问题），通过窗口函数分三步实现：

1. 按id分区、date升序排序，用LAG()函数获取上一行的polarity值，标记当前行与上一行值是否不同，生成变化标识
2. 对变化标识做累加求和，为每一段连续相同polarity的行生成唯一的分组编号
3. 按id+分组编号聚合计算每组行数，筛选出polarity=0的连续段（匹配样例输出规则），排除每个id下最后一个无后续变化的分段，得到最终结果

可运行代码 #

支持MySQL 8.0+、PostgreSQL、Hive、SparkSQL等所有兼容标准窗口函数的SQL引擎：

WITH compare_prev AS (
    SELECT
        id,
        polarity,
        `date`,
        -- 当前行与上一行polarity不同则标记为1，相同为0
        CASE
            WHEN LAG(polarity, 1) OVER (PARTITION BY id ORDER BY `date`) = polarity THEN 0
            ELSE 1
        END AS change_flag
    FROM your_source_table -- 替换为实际表名
),
group_continue AS (
    SELECT
        id,
        polarity,
        -- 累加变化标识，连续相同polarity的行将获得相同grp编号
        SUM(change_flag) OVER (PARTITION BY id ORDER BY `date`) AS grp
    FROM compare_prev
),
agg_group AS (
    SELECT
        id,
        grp,
        polarity,
        COUNT(*) AS n,
        -- 标记每个id下的最后一个分组
        MAX(grp) OVER (PARTITION BY id) AS last_grp
    FROM group_continue
    GROUP BY id, grp, polarity
)
SELECT id, n
FROM agg_group
WHERE
    polarity = 0 -- 按样例规则取polarity=0的连续段，若业务需要统计其他值可修改此处
    AND grp < last_grp -- 排除最后一个无后续变化的分段
ORDER BY id, grp;

结果校验 #

代入样例数据运行：

1. id=1的连续段分组结果：
   • grp=1，polarity=0，行数1
   • grp=2，polarity=1，行数1
   • grp=3，polarity=0，行数2
   • grp=4，polarity=1，行数1（最后一个分组，排除）
     筛选后得到n值1、2，符合预期
2. id=2的连续段分组结果：
   • grp=1，polarity=0，行数3
   • grp=2，polarity=1，行数1
   • grp=3，polarity=0，行数3
   • grp=4，polarity=1，行数1（最后一个分组，排除）
     筛选后得到n值3、3，符合预期

注意：如果使用PostgreSQL等引擎，字段名date需要用双引号转义，将代码中的反引号替换为双引号即可。

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