MySQL计算不同stepId间平均时间差（保留同stepId最新数据）

Hey there! Let's work through this problem step by step to get the result you need in MySQL.

First, we need two key things: keep only the latest record for each (session, stepId) pair, then calculate the average time difference between consecutive steps. Here's a complete, commented SQL solution:

-- First, rank records to find the latest entry per (session, stepId)
WITH latest_steps AS (
    SELECT 
        session, 
        stepId, 
        starttime,
        -- Assign a rank where 1 = latest starttime in each (session, stepId) group
        ROW_NUMBER() OVER (PARTITION BY session, stepId ORDER BY starttime DESC) AS rn
    FROM your_table_name  -- Replace with your actual table name
),
-- Filter to keep only the latest records
filtered_steps AS (
    SELECT session, stepId, starttime
    FROM latest_steps
    WHERE rn = 1
),
-- Calculate time differences between consecutive steps
step_time_diffs AS (
    SELECT 
        session,
        -- Get the starttime of the previous step in the same session
        TIMESTAMPDIFF(MINUTE, LAG(starttime) OVER (PARTITION BY session ORDER BY stepId), starttime) AS time_diff_minutes
    FROM filtered_steps
)
-- Compute the average time difference per session
SELECT 
    session,
    AVG(time_diff_minutes) AS average_step_time_diff_minutes
FROM step_time_diffs
WHERE time_diff_minutes IS NOT NULL  -- Exclude the first step (no prior step to compare)
GROUP BY session;

A quick breakdown of how this works: #

1. latest_steps CTE: Uses ROW_NUMBER() to rank each record within its session and stepId group, sorted by starttime descending. The highest-ranked row (rn=1) is the most recent entry we want to keep.
2. filtered_steps CTE: Filters out all non-latest records, leaving us with a clean dataset where each (session, stepId) pair appears exactly once.
3. step_time_diffs CTE: Uses LAG() to pull the starttime of the previous step in the same session (ordered by stepId). TIMESTAMPDIFF() calculates the difference in minutes—you can swap MINUTE with SECOND or HOUR if you need a different unit.
4. Final SELECT: Computes the average of the time differences, ignoring the first step (since it has no prior step to compare, resulting in a NULL value).

Important note if your starttime is stored as a string: #

If your starttime column is a VARCHAR (like the example's 10:00 format), you'll need to convert it to a TIME type first for accurate calculations. Adjust the latest_steps CTE like this:

WITH latest_steps AS (
    SELECT 
        session, 
        stepId, 
        STR_TO_DATE(starttime, '%H:%i') AS starttime,  -- Convert string to TIME
        ROW_NUMBER() OVER (PARTITION BY session, stepId ORDER BY STR_TO_DATE(starttime, '%H:%i') DESC) AS rn
    FROM your_table_name
)

This will ensure the time difference calculations work correctly.

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