SQL查询需求：计算重复出现的event_type对应最近与次近时间的value差值

Hey there, let's solve this SQL problem together. The goal is to find event_type entries that have more than one record, then calculate the difference between the value of the most recent entry and the second most recent one.

Step 1: Recap the Data Structure & Sample Data #

First, let's confirm the table setup and sample records you provided:

CREATE TABLE events (
 event_type int,
 value int,
 time1 datetime
);

INSERT INTO events (event_type, value, time1) VALUES 
(2, 5, '2015-05-09 12:42:00'),
(4, -42, '2015-05-09 13:19:57'),
(2, 2, '2015-05-09 14:48:30'),
(2,7, '2015-05-09 12:54:39'),
(3,16, '2015-05-09 13:19:57'),
(3,20, '2015-05-09 15:01:09');

Step 2: Efficient Solution Using Window Functions #

Here's a clean, readable way to get your desired result with window functions:

WITH ranked_events AS (
    SELECT
        event_type,
        value,
        -- Assign rank to each record, latest first per event_type
        ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY time1 DESC) AS record_rank,
        -- Pull the value of the second most recent record
        LAG(value) OVER (PARTITION BY event_type ORDER BY time1 DESC) AS second_latest_value
    FROM events
)
SELECT
    event_type,
    value - second_latest_value AS value
FROM ranked_events
-- Keep only the latest record, and ensure there's a second record to compare
WHERE record_rank = 1 AND second_latest_value IS NOT NULL;

Breakdown of How This Works #

• CTE ranked_events: We use two window functions here:
  • ROW_NUMBER() sorts records in each event_type group by time1 (newest first) and assigns a rank. The most recent entry gets record_rank = 1.
  • LAG(value) grabs the value from the immediately preceding record in the sorted group—this is exactly the second most recent value for that event_type.
• Main Query: We filter to only keep the latest record (record_rank = 1) and exclude any event_type with only one entry (using second_latest_value IS NOT NULL). Then we compute the difference between the latest and second latest values.

Expected Output #

Running this query will return exactly the result you need:

event_type | value
------------+-----------
2 | -5
3 | 4

Alternative Approach: Self-Join #

If you prefer a self-join instead of window functions, here's another valid solution:

WITH ranked_events AS (
    SELECT
        event_type,
        value,
        ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY time1 DESC) AS rn
    FROM events
)
SELECT
    re_latest.event_type,
    re_latest.value - re_second.value AS value
FROM ranked_events re_latest
JOIN ranked_events re_second
    ON re_latest.event_type = re_second.event_type
    AND re_latest.rn = 1
    AND re_second.rn = 2;

This works by joining the CTE to itself, matching the latest record (rn=1) with the second latest (rn=2) for each event_type.

内容的提问来源于stack exchange，提问作者Szymon Fraś