如何在SQL中获取各ID首尾日期Result并判断变化？是否可建索引？

一、获取每个ID的最早/最晚日期对应的Result #

方法1：子查询关联（兼容基础SQL语法） #

先通过子查询算出每个ID的最小和最大日期，再关联原表筛选对应记录：

WITH id_date_boundaries AS (
    SELECT 
        ID,
        MIN(Date) AS min_date,
        MAX(Date) AS max_date
    FROM your_table
    GROUP BY ID
)
SELECT 
    t.ID,
    t.Date,
    t.Result
FROM your_table t
JOIN id_date_boundaries b 
    ON t.ID = b.ID 
    AND t.Date IN (b.min_date, b.max_date)
ORDER BY t.ID, t.Date;

方法2：窗口函数（更简洁高效） #

用ROW_NUMBER()标记每个ID的第一条（最早）和最后一条（最晚）记录，直接筛选：

SELECT ID, Date, Result
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS rn_desc
    FROM your_table
) t
WHERE rn_asc = 1 OR rn_desc = 1
ORDER BY ID, Date;

二、判断Result的增减趋势 #

在上述基础上，聚合每个ID的初始和最终Result，通过CASE语句判断趋势：

WITH id_trend_data AS (
    SELECT 
        ID,
        FIRST_VALUE(Result) OVER (PARTITION BY ID ORDER BY Date) AS initial_result,
        LAST_VALUE(Result) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_result
    FROM your_table
)
SELECT DISTINCT
    ID,
    initial_result,
    final_result,
    CASE 
        WHEN final_result > initial_result THEN '上升'
        WHEN final_result < initial_result THEN '下降'
        ELSE '持平'
    END AS change_trend
FROM id_trend_data;

如果需要把趋势和预期结果表结合，可使用：

WITH id_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS rn_desc,
        FIRST_VALUE(Result) OVER (PARTITION BY ID ORDER BY Date) AS initial_result,
        LAST_VALUE(Result) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_result
    FROM your_table
),
trend_info AS (
    SELECT DISTINCT
        ID,
        CASE 
            WHEN final_result > initial_result THEN '上升'
            WHEN final_result < initial_result THEN '下降'
            ELSE '持平'
        END AS change_trend
    FROM id_records
)
SELECT 
    ir.ID,
    ir.Date,
    ir.Result,
    ti.change_trend
FROM id_records ir
JOIN trend_info ti ON ir.ID = ti.ID
WHERE ir.rn_asc = 1 OR ir.rn_desc = 1
ORDER BY ir.ID, ir.Date;

三、索引优化建议 #

针对该查询，建议创建复合覆盖索引：

CREATE INDEX idx_id_date_result ON your_table (ID, Date, Result);

该索引包含了查询中所有用到的字段：按ID分组、按Date排序、直接获取Result，可以完全覆盖查询逻辑，避免回表查询原数据，大幅提升查询效率，尤其是数据量较大时效果明显。

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