MySQL查询问题：按产品重置行号并实现递推值计算

问题核心 #

你的代码存在两个关键问题：

1. 全局变量@row_num未在切换产品时重置为1，导致不同产品的行号连续递增；
2. 递推变量@running未按产品重置，且首个日期的计算逻辑不符合需求（你写的是market_value_1/100，但需求是market_value_1/100 -1）。

修正后的MySQL 5.x版本代码 #

通过对比当前行与上一行的产品来重置变量，实现按产品独立计算：

SELECT 
    product,
    ipdate,
    market_value_1 AS mv1,
    row_num,
    ROUND(running_value, 8) AS running_value
FROM (
    SELECT 
        product,
        ipdate,
        market_value_1,
        -- 按产品重置行号
        CASE 
            WHEN @prev_product = product THEN @row_num := @row_num + 1
            ELSE @row_num := 1
        END AS row_num,
        -- 按产品执行递推计算
        CASE 
            WHEN @prev_product = product THEN 
                @running := market_value_1 / @running
            ELSE 
                @running := (market_value_1 / 100) - 1
        END AS running_value,
        -- 更新上一个产品标记
        @prev_product := product
    FROM report_combined,
         (SELECT @row_num := 0, @running := 0, @prev_product := '') AS init_vars
    ORDER BY product, ipdate
) AS sub_query;

代码说明 #

• 变量初始化：子查询init_vars初始化三个变量，分别用于行号计数、存储递推值、记录上一行产品名称；
• 行号重置：通过CASE判断当前产品是否与上一行一致，不一致则将行号重置为1；
• 递推逻辑：产品首行按(market_value_1/100)-1计算，后续行用当日market_value_1除以前一行的递推结果；
• 状态更新：最后更新@prev_product为当前产品，确保下一行的判断准确。

MySQL 8.0+版本优化方案 #

使用窗口函数替代全局变量，代码更简洁易维护：

WITH product_rows AS (
    SELECT 
        product,
        ipdate,
        market_value_1,
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY ipdate) AS row_num
    FROM report_combined
)
SELECT 
    product,
    ipdate,
    market_value_1 AS mv1,
    row_num,
    ROUND(
        CASE 
            WHEN row_num = 1 THEN (market_value_1 / 100) - 1
            ELSE market_value_1 / LAG(running_value) OVER (PARTITION BY product ORDER BY ipdate)
        END,
        8
    ) AS running_value
FROM product_rows
ORDER BY product, ipdate;

这里用PARTITION BY product自动按产品分组重置行号，LAG()函数直接获取上一行的计算值，避免了全局变量的潜在问题。

内容的提问来源于stack exchange，提问作者Paul van Zyl