如何为生产数据表添加增量分类Run列区分产品生产批次？

我有一张生产数据表，其中包含产品类型列。同一产品可能会分多批次生产（比如示例中prod-X生产了两次，中间切换过prod-Y），需要新增一个Run列，生成run-1、run-2、run-3这类递增的批次标识，用来区分同一产品的不同生产批次。

数据示例 #

解决方案 #

核心逻辑是通过窗口函数判断产品是否切换，对切换点累计计数后拼接成目标格式。以下是主流数据库的实现代码：

MySQL 8.0+/MariaDB 10.2+ #

-- 先新增Run列
ALTER TABLE production_data ADD COLUMN Run VARCHAR(20);

-- 更新Run列的值
UPDATE production_data pd
JOIN (
    SELECT 
        Datetime,
        CONCAT('run-', SUM(CASE WHEN prev_type != type OR prev_type IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY Datetime)) AS run_id
    FROM (
        SELECT 
            Datetime,
            type,
            LAG(type) OVER (ORDER BY Datetime) AS prev_type
        FROM production_data
    ) AS sub
) AS run_data ON pd.Datetime = run_data.Datetime
SET pd.Run = run_data.run_id;

PostgreSQL #

-- 新增Run列
ALTER TABLE production_data ADD COLUMN Run VARCHAR(20);

-- 更新数据
UPDATE production_data pd
SET Run = run_data.run_id
FROM (
    SELECT 
        Datetime,
        CONCAT('run-', SUM(CASE WHEN prev_type != type OR prev_type IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY Datetime)) AS run_id
    FROM (
        SELECT 
            Datetime,
            type,
            LAG(type) OVER (ORDER BY Datetime) AS prev_type
        FROM production_data
    ) AS sub
) AS run_data
WHERE pd.Datetime = run_data.Datetime;

SQL Server #

-- 新增Run列
ALTER TABLE production_data ADD Run VARCHAR(20);

-- 更新数据
WITH run_cte AS (
    SELECT 
        Datetime,
        type,
        LAG(type) OVER (ORDER BY Datetime) AS prev_type
    FROM production_data
),
run_id_cte AS (
    SELECT 
        Datetime,
        CONCAT('run-', SUM(CASE WHEN prev_type != type OR prev_type IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY Datetime)) AS run_id
    FROM run_cte
)
UPDATE pd
SET pd.Run = ric.run_id
FROM production_data pd
JOIN run_id_cte ric ON pd.Datetime = ric.Datetime;

注意事项 #

• 假设表名为production_data，且Datetime列是唯一且能按生产顺序排序的字段；如果没有唯一时间，可替换为自增ID或其他有序字段排序。
• LAG(type)函数用来获取上一行的产品类型，以此判断是否发生产品切换。
• 通过累计求和得到批次序号，最终拼接成run-N的格式。

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