如何在PostgreSQL中基于选手起止时间计算赛事平均耗时？

Got it, let's break down how to solve this problem. The key challenge here is pairing each runner's start time with their corresponding finish time—especially since a runner might participate multiple times (like R1 in your example with one start and two finishes). Here's a step-by-step solution:

Approach #

1. Isolate start and finish records: Split the data into two separate datasets: one for all start times, one for all finish times.
2. Pair corresponding events: For each runner, assign a sequential number to their start and finish times (ordered by timestamp). This ensures we match the earliest start to the earliest finish, the second start to the second finish, etc.
3. Calculate duration and average: Join the paired start/finish records, compute the duration for each valid race, then find the average of those durations.

SQL Query #

First, let's include your sample data in a CTE (you can replace this with your actual table name):

WITH race_data AS (
    SELECT 'R1' AS RunnerID, 'start' AS State, '2017-04-11 12:15:15.722415'::TIMESTAMP AS Timestamp_race UNION ALL
    SELECT 'R2' AS RunnerID, 'start' AS State, '2017-04-11 13:15:15.722415'::TIMESTAMP AS Timestamp_race UNION ALL
    SELECT 'R1' AS RunnerID, 'finish' AS State, '2017-04-11 15:15:15.722415'::TIMESTAMP AS Timestamp_race UNION ALL
    SELECT 'R1' AS RunnerID, 'finish' AS State, '2017-04-11 17:15:15.722415'::TIMESTAMP AS Timestamp_race
),
start_times AS (
    SELECT 
        RunnerID,
        Timestamp_race AS start_time,
        -- Assign a race number to each start for the runner
        ROW_NUMBER() OVER (PARTITION BY RunnerID ORDER BY Timestamp_race) AS race_num
    FROM race_data
    WHERE State = 'start'
),
finish_times AS (
    SELECT 
        RunnerID,
        Timestamp_race AS finish_time,
        -- Assign matching race number to each finish for the runner
        ROW_NUMBER() OVER (PARTITION BY RunnerID ORDER BY Timestamp_race) AS race_num
    FROM race_data
    WHERE State = 'finish'
)
SELECT 
    -- Calculate average duration (PostgreSQL handles interval averages natively)
    AVG(finish_time - start_time) AS average_race_duration
FROM start_times s
-- Only include races where both start and finish exist
JOIN finish_times f 
    ON s.RunnerID = f.RunnerID 
    AND s.race_num = f.race_num;

Explanation #

• CTEs: race_data mimics your sample data. In production, replace this with your actual table name (e.g., race_results).
• Window Functions: ROW_NUMBER() partitions the data by RunnerID and orders by Timestamp_race, so each runner's starts and finishes get numbered sequentially. This ensures we correctly pair each start with its corresponding finish.
• Join: The INNER JOIN ensures we only consider complete races (where a runner has both a start and finish time). If you want to include incomplete races (with NULL durations), use LEFT JOIN instead.
• Average Calculation: Subtracting two TIMESTAMP values in PostgreSQL returns an INTERVAL (e.g., 03:00:00 for 3 hours). The AVG() function works directly on intervals to give the average race duration.

Sample Result #

For your provided data, this query will return 03:00:00 as the average duration—since only R1's first race (start at 12:15, finish at 15:15) is a complete pair. R2 has no finish time, and R1's second finish has no matching start, so those are excluded.

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