如何按Time分组获取Direction最值人员的最小Distance？

原始数据表格 #

需求说明 #

每个Time分组下，同一Person的Direction值固定，Distance有多个记录。需要：

• 对每个Time，选出Direction最大的Person，并取其最小的Distance
• 同时选出Direction第二大的Person，并取其最小的Distance
• 最终每个Time仅返回一条记录，预期结果如下：

预期结果表格 #

解决方案 #

通过分组聚合+窗口函数排序的方式实现，具体步骤如下：

步骤1：提取每个人员在对应时间下的最小距离 #

先按week、Time、Person分组，计算每个人员在对应时间下的最小Distance，同时保留固定的Direction值：

WITH person_min_dist AS (
    SELECT 
        week,
        Time,
        Person,
        Direction,
        MIN(Distance) AS min_distance
    FROM your_table_name
    GROUP BY week, Time, Person, Direction
)

步骤2：对每个时间分组内的人员按方向排序 #

用ROW_NUMBER()窗口函数，在每个Time分组内按Direction降序排序，标记每个人员的排名：

, ranked_persons AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY week, Time ORDER BY Direction DESC) AS rn
    FROM person_min_dist
)

步骤3：合并排名第1和第2的记录 #

通过自连接，把同一Time下排名第1（方向最大）和第2（方向次大）的人员信息合并到一行：

SELECT 
    r1.week,
    r1.Time,
    r1.Person AS max_direction_person,
    r1.min_distance AS max_person_min_distance,
    r2.Person AS second_max_direction_person,
    r2.min_distance AS second_max_person_min_distance
FROM ranked_persons r1
LEFT JOIN ranked_persons r2 
    ON r1.week = r2.week 
    AND r1.Time = r2.Time 
    AND r1.rn = 1 
    AND r2.rn = 2
WHERE r1.rn = 1;

完整SQL代码 #

WITH person_min_dist AS (
    SELECT 
        week,
        Time,
        Person,
        Direction,
        MIN(Distance) AS min_distance
    FROM your_table_name
    GROUP BY week, Time, Person, Direction
),
ranked_persons AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY week, Time ORDER BY Direction DESC) AS rn
    FROM person_min_dist
)
SELECT 
    r1.week,
    r1.Time,
    r1.Person AS max_direction_person,
    r1.min_distance AS max_person_min_distance,
    r2.Person AS second_max_direction_person,
    r2.min_distance AS second_max_person_min_distance
FROM ranked_persons r1
LEFT JOIN ranked_persons r2 
    ON r1.week = r2.week 
    AND r1.Time = r2.Time 
    AND r1.rn = 1 
    AND r2.rn = 2
WHERE r1.rn = 1;

补充说明 #

• 如果存在多个人员Direction并列第1或第2的情况，ROW_NUMBER()会随机选取一个；若需保留所有并列结果，可改用RANK()或DENSE_RANK()函数，具体根据业务需求调整。
• 替换代码中的your_table_name为实际表名即可执行。

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