SQL中如何根据列内特定值递增rank实现分组排名

你要实现的分组标记逻辑不需要使用Lag/Lead做相邻行差值计算，核心逻辑本质是按id升序排序时，截止当前行items字段值为'Product'的累计出现次数，直接用窗口累计求和即可实现，逻辑简单且兼容性强。

核心实现逻辑 #

• 按id字段升序划定计算窗口，范围覆盖从第一行到当前行
• 每行做条件判断：如果items值为'Product'则记1，否则记0
• 对上述判断值做累计求和，输出结果就是你需要的ranks字段

可直接运行的代码（兼容绝大多数支持窗口函数的SQL引擎） #

适用引擎包括MySQL 8.0+、PostgreSQL、Hive、Spark SQL、SQL Server、ClickHouse等：

SELECT
    id,
    items,
    SUM(CASE WHEN items = 'Product' THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ranks
FROM 你的表名;

针对你给出的样例数据，执行后返回结果完全匹配预期：

注意：如果你的业务场景中id不是严格递增的顺序字段，把ORDER BY id替换为实际的业务排序字段（比如数据生成时间、入库时间戳）即可。

为什么Lag/Lead实现容易出问题 #

如果硬要套用相邻行函数实现，需要先给Product行打分组标记，再做非空值向前填充，步骤繁琐且兼容性差：

1. 第一步先给所有items='Product'的行按顺序打上行号，非Product行标记为NULL
2. 第二步用窗口函数向前取最近的非NULL标记值作为ranks
   参考代码如下（部分老版本SQL引擎不支持IGNORE NULLS语法，会运行失败）：

WITH product_mark AS (
    SELECT
        id,
        items,
        CASE WHEN items = 'Product' THEN ROW_NUMBER() OVER (PARTITION BY items ORDER BY id) ELSE NULL END AS group_tag
    FROM 你的表名
)
SELECT
    id,
    items,
    LAST_VALUE(group_tag IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ranks
FROM product_mark;

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