如何使用DENSE_RANK根据Date和Value识别值变化对应的连续周期

你遇到的是典型的连续序列中相同值分组（岛屿问题），直接用DENSE_RANK()无法实现的原因是它是对全局的Value/Date做排序分组，不会识别「连续相同」的区间属性。

你之前的两种写法错误原因：

1. 按Date, Value排序：Date是唯一递增的，每一行的排序键都不同，DENSE_RANK()自然每行返回不同值
2. 仅按Value排序：全局相同的Value会被分到同一组，不会区分前后不连续的相同值区间

标准SQL实现方案（支持窗口函数的数据库通用，含MySQL8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等） #

通过「变化标记累加」的逻辑实现，步骤如下：

1. 用LAG()窗口函数取同ID下按日期排序的上一行Value，和当前行Value对比，值发生变化时标记为1，否则为0
2. 对上述标记做累计求和，每次遇到1时累计值+1，正好对应每一次值变化后周期号+1的需求

完整代码示例 #

SELECT 
    ID,
    Date,
    Value,
    SUM(change_flag) OVER (PARTITION BY ID ORDER BY Date) AS Period
FROM (
    SELECT 
        ID,
        Date,
        Value,
        -- 首行/值和上一行不同时标记为1，否则为0
        CASE WHEN LAG(Value) OVER (PARTITION BY ID ORDER BY Date) = Value THEN 0 ELSE 1 END AS change_flag
    FROM 你的表名
) t

结果验证 #

对应你给出的ID=1的测试数据，计算过程和预期完全匹配：

老版本MySQL（5.x不支持窗口函数）兼容方案 #

可以用用户变量的方式实现：

SELECT 
    ID,
    Date,
    Value,
    Period
FROM (
    SELECT 
        t.*,
        @period := IF(@pre_id = ID AND @pre_val = Value, @period, @period + 1) AS Period,
        @pre_id := ID,
        @pre_val := Value
    FROM 你的表名 t,
         (SELECT @pre_id := NULL, @pre_val := NULL, @period := 0) init
    ORDER BY ID, Date
) res

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