Oracle中按车辆分组时如何基于最大最小日期获取对应起止点值

需求说明 #

按车辆分组统计每辆车的全程起点、终点及最晚记录时间：

• 总起点：对应车辆最早一条记录的Start字段值
• 总终点：对应车辆最晚一条记录的End字段值
• 结果日期：对应车辆最晚一条记录的Date字段值

实现代码 #

通用SQL写法（兼容绝大多数支持窗口函数的数据库） #

用ROW_NUMBER标记分组内最早、最晚的记录，再关联取值：

WITH ranked_trips AS (
    SELECT 
        Vehicle,
        Start,
        End,
        Date,
        ROW_NUMBER() OVER (PARTITION BY Vehicle ORDER BY Date ASC) AS rn_early,
        ROW_NUMBER() OVER (PARTITION BY Vehicle ORDER BY Date DESC) AS rn_late
    FROM 你的表名
)
SELECT 
    t1.Vehicle,
    t1.Start AS 总起点,
    t2.End AS 总终点,
    t2.Date AS 结果日期
FROM ranked_trips t1
INNER JOIN ranked_trips t2 ON t1.Vehicle = t2.Vehicle
WHERE t1.rn_early = 1 AND t2.rn_late = 1
ORDER BY t1.Vehicle;

简化写法（支持FIRST_VALUE函数的数据库适用） #

SELECT DISTINCT
    Vehicle,
    FIRST_VALUE(Start) OVER (PARTITION BY Vehicle ORDER BY Date ASC) AS 总起点,
    LAST_VALUE(End) OVER (PARTITION BY Vehicle ORDER BY Date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 总终点,
    MAX(Date) OVER (PARTITION BY Vehicle) AS 结果日期
FROM 你的表名
ORDER BY Vehicle;

样例运行结果 #

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