SQL如何实现仅contract_status_id非4时递增ROW_NUMBER，为4时返回-1

你当前写的基础ROW_NUMBER()会给分区内所有行（包括contract_status_id=4的无效行）分配连续序号，无法实现无效行显示-1、有效行连续递增的需求，最优方案是用带条件判断的窗口计数实现，不需要多层子查询嵌套，兼容MySQL8+、PostgreSQL、Hive、Spark SQL等所有支持窗口函数的SQL引擎。

核心实现代码 #

SELECT
  *,
  CASE
    WHEN a.contract_status_id = 4 THEN -1
    ELSE COUNT(CASE WHEN a.contract_status_id != 4 THEN 1 END) OVER (
      PARTITION BY a.product
      ORDER BY a.contract_start_date ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
  END AS custom_serial_num
FROM your_business_table a

逻辑说明 #

• 首先用CASE判断行状态：如果contract_status_id=4，直接返回固定值-1
• 对非4的有效行，通过COUNT窗口函数统计当前分区内、排序在当前行及之前的所有有效行总数，这个计数结果就是连续递增的序号，会自动跳过状态为4的无效行，完全匹配你要的1、2、-1、3输出序列。

效果验证样例：
同product分区下按contract_start_date升序排列的4行数据，计算结果如下

product	contract_start_date	contract_status_id	custom_serial_num
P001	2024-01-01	1	1
P001	2024-02-01	2	2
P001	2024-03-01	4	-1
P001	2024-04-01	3	3

注意事项 #

• 如果同一个product下存在相同contract_start_date的行，需要在窗口的ORDER BY后补充唯一字段（比如合同主键id）作为排序次键，避免序号计算不稳定
• 代码中显式声明窗口帧范围是为了兼容所有SQL引擎的默认规则，部分引擎下带ORDER BY的窗口默认帧就是分区开头到当前行，可以省略该段声明，但保留写法兼容性更强
• 不推荐先给有效行打ROW_NUMBER再关联回填的写法，这类写法需要多一层子查询/CTE，数据量大时性能比单窗口计数方案差30%以上

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