SQL Server 2012：查询Miles值持续上升（允许≤5%回落）的ID

要解决这个问题，我们需要识别出那些Miles序列整体呈上升趋势，且即使出现回落，幅度也不超过前一个Miles值5%的ID。以下是具体的SQL实现和逻辑说明：

分析需求 #

根据你的示例：

• ABC的Miles序列是21→24→25：全程上升，符合要求
• DEF的Miles序列是18→18→20：中间持平，最终上升，符合要求
• PQR的Miles序列是19→17：回落幅度约10.5%（超过5%），不符合
• XYZ的Miles序列是13→11：回落幅度约15.3%（超过5%），不符合

核心规则：

1. 相邻Miles值的回落幅度不能超过前一个值的5%（即当前Miles ≥ 前一个Miles × 0.95）
2. 序列的最终Miles值必须大于初始值，确保整体呈上升趋势

SQL 查询代码 #

假设你的表名为your_table，执行以下查询：

WITH ranked_data AS (
    -- 为每个ID的记录分配序号，同时获取前一行的Miles值
    SELECT 
        ID,
        Name,
        Miles,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn,
        LAG(Miles) OVER (PARTITION BY ID ORDER BY rn) AS prev_miles
    FROM your_table
),
trend_check AS (
    -- 计算回落比例，并获取每个ID的首尾Miles值
    SELECT 
        ID,
        Name,
        CASE 
            WHEN prev_miles IS NOT NULL AND Miles < prev_miles 
            THEN CAST(Miles AS FLOAT) / prev_miles 
            ELSE 1.0 
        END AS drop_ratio,
        FIRST_VALUE(Miles) OVER (PARTITION BY ID ORDER BY rn) AS first_miles,
        LAST_VALUE(Miles) OVER (
            PARTITION BY ID 
            ORDER BY rn 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_miles
    FROM ranked_data
)
-- 筛选符合条件的ID和Name
SELECT DISTINCT ID, Name
FROM trend_check
WHERE 
    drop_ratio >= 0.95
    AND last_miles > first_miles;

代码说明 #

1. ranked_data CTE：

   • 使用ROW_NUMBER()为每个ID下的记录按顺序编号（如果你的表有时间/顺序字段，建议替换(SELECT NULL)为该字段，比如ORDER BY record_date，确保排序逻辑准确）
   • 使用LAG()函数获取当前行的前一行Miles值，用于后续计算回落幅度

2. trend_check CTE：

   • drop_ratio：计算当前Miles相对前一行的比例，如果当前值更小，则计算回落比例；否则默认设为1.0（上升或持平）
   • first_miles和last_miles：分别获取每个ID的第一个和最后一个Miles值，用于判断整体趋势是否上升

3. 最终筛选：

   • 确保所有回落比例都不低于0.95（即回落幅度≤5%）
   • 确保最终Miles值大于初始值，保证整体呈上升趋势

注意事项 #

• 如果你的表中有明确的时间/顺序字段（比如记录生成时间），一定要用该字段替换ORDER BY (SELECT NULL)，否则排序可能不稳定，导致结果错误
• 如果需要允许持平（比如DEF的情况），当前逻辑已经支持，因为持平的drop_ratio为1.0，满足≥0.95的条件

内容的提问来源于stack exchange，提问作者Touhid K.