SQL如何标记数值趋势翻转触发行并在分区内置顶展示

通过CTE分层计算即可实现flip标记和指定排序规则，核心是先识别有效趋势的反转点，再按要求排序：

1. 第一层CTE完全保留你原有查询的计算逻辑，得到带单步趋势标签的基础结果
2. 第二层CTE计算每行之前最近的有效变动趋势（排除初始Base状态、数值无变化的No Change状态，避免平值段干扰趋势判断）
3. 最外层查询对比当前行趋势和上一个有效趋势，方向相反则标记flip=1，其余行标记0；排序时同id分区内优先展示flip=1的触发行，剩余行按age升序排列

完整实现代码如下：

WITH BaseData AS (
    SELECT 
        id, 
        (2022 - age) yearId, 
        age, 
        [value],
        CASE 
            WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY age) = 0 
                THEN 'Base' 
            WHEN [value] > LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY age) 
                THEN 'Increasing'
            WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY age) 
                THEN 'No Change'
            ELSE 'Decreasing'
        END AS [Order]
    FROM Test
),
TrendWithLast AS (
    SELECT *,
        -- 取当前行之前最近的非平、非初始的有效趋势
        LAST_VALUE(CASE WHEN [Order] IN ('Increasing', 'Decreasing') THEN [Order] END)
        IGNORE NULLS OVER (
            PARTITION BY id 
            ORDER BY age
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS last_trend
    FROM BaseData
)
SELECT
    id,
    yearId,
    age,
    [value],
    [Order],
    CASE
        WHEN [Order] IN ('Increasing', 'Decreasing')
            AND last_trend IS NOT NULL
            AND [Order] <> last_trend
        THEN 1
        ELSE 0
    END AS flip
FROM TrendWithLast
ORDER BY
    id,
    flip DESC,
    age ASC

• 该逻辑会自动适配中间存在数值不变（No Change）的场景，不会因为连续平值误判/漏判趋势翻转
• 按你给出的id=1分组示例，age=4、value=3的行只要满足前后趋势反向的规则，会自动标记flip=1，并排在该id分组的最顶部，其余行flip=0按age顺序排列
• 如果你使用的SQL环境不支持IGNORE NULLS语法，可以改用子查询关联的方式找上一个有效趋势，核心判断逻辑不变

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