如何使用SQL查询每个ID对应年份的最小差值？

Alright, let's tackle this problem where you need to calculate the smallest difference between any two years for each ID in your dataset. First, let's restate the problem with your sample data for clarity:

Sample Input:

ID	year
1	2001
1	2001
2	2003
2	2004
2	2010
3	2000

Desired Output:

ID	min_year_diff
1	0
2	1
3	0

Solution 1: Using Window Functions (Modern Databases) #

Most modern SQL databases (like PostgreSQL, MySQL 8+, SQL Server, etc.) support window functions, which make this task straightforward. Here's the query:

WITH ranked_years AS (
    SELECT 
        ID,
        year,
        -- Get the previous year in the sorted list for the same ID
        LAG(year) OVER (PARTITION BY ID ORDER BY year) AS prev_year
    FROM your_table_name
    WHERE year IS NOT NULL -- Optional: filter out null years if needed
)
SELECT 
    ID,
    -- If there's only one year (prev_year is null), return 0; else get the smallest difference
    COALESCE(MIN(ABS(year - prev_year)), 0) AS min_year_diff
FROM ranked_years
GROUP BY ID
ORDER BY ID;

How this works: #

• LAG(year) OVER (PARTITION BY ID ORDER BY year): Groups rows by ID, sorts them by year, and pulls the year value from the immediately preceding row.
• ABS(year - prev_year): Calculates the absolute difference between the current year and the previous one (avoids negative values).
• COALESCE(MIN(...), 0): Handles cases where an ID has only one year (since prev_year will be null, the MIN result is null—we replace this with 0). For IDs with duplicate years, the difference will be 0, which becomes the minimum automatically.

Solution 2: Self-Join (Legacy Databases) #

If you're working with an older database that doesn't support window functions, a self-join is a reliable alternative:

SELECT 
    t1.ID,
    COALESCE(MIN(ABS(t1.year - t2.year)), 0) AS min_year_diff
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.ID = t2.ID 
    AND t1.ctid <> t2.ctid -- Use a unique row identifier to avoid joining a row to itself (adjust based on your DB: e.g., `ROWID` for Oracle, primary key column if available)
WHERE t1.year IS NOT NULL
GROUP BY t1.ID
ORDER BY t1.ID;

Notes for this approach: #

• The join condition t1.ctid <> t2.ctid ensures we don't compare a row to itself (which would give a difference of 0 even if all years are unique). Adjust this to your database's unique row identifier if needed.
• If your table has duplicate years for an ID, the self-join will still capture the 0 difference, which becomes the minimum.

Just remember to replace your_table_name with the actual name of your table in both queries!

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