PostgreSQL缺失数据场景下LAG函数计算薪资变动的实现方案

Got it, let's tackle this problem step by step. It sounds like your issue with the LAG function probably comes down to not partitioning correctly by each player—since we need to track salary changes per player first before aggregating stats across all players for each year. Here's a solid approach to get the results you want:

Step 1: Calculate Individual Player Salary Changes #

First, we'll use the LAG() window function to pull each player's previous year's salary, but crucially, we need to partition the data by player_id so each player's salary history is tracked independently. We'll compute the difference between the current year's salary and the prior year's to get the change.

Step 2: Aggregate Stats by Year #

Once we have all valid player salary changes (excluding the first year each player appears, since there's no prior data to compare), we can group by year to calculate the minimum, maximum, and average changes across all players that year.

Here's the full SQL query using a CTE (Common Table Expression) to break this down clearly:

WITH player_salary_changes AS (
    SELECT
        year,
        salary - LAG(salary) OVER (PARTITION BY player_id ORDER BY year) AS salary_change
    FROM
        salaries
)
SELECT
    year,
    MIN(salary_change) AS min_salary_change,
    MAX(salary_change) AS max_salary_change,
    ROUND(AVG(salary_change), 2) AS avg_salary_change
FROM
    player_salary_changes
WHERE
    salary_change IS NOT NULL  -- Exclude first year for each player
GROUP BY
    year
ORDER BY
    year;

Handling Gaps in Player Salary Data #

If you have players who skip years (e.g., a player has salary data for 2020 and 2022 but not 2021), the above query will still calculate a change between 2020 and 2022. If you only want to include consecutive year changes (ignoring gaps), you can add a check to ensure the prior year is exactly one year before the current year:

WITH player_salary_changes AS (
    SELECT
        year,
        player_id,
        salary,
        LAG(salary) OVER (PARTITION BY player_id ORDER BY year) AS prev_salary,
        LAG(year) OVER (PARTITION BY player_id ORDER BY year) AS prev_year
    FROM
        salaries
),
valid_consecutive_changes AS (
    SELECT
        year,
        salary - prev_salary AS salary_change
    FROM
        player_salary_changes
    WHERE
        prev_year = year - 1  -- Only include consecutive years
)
SELECT
    year,
    MIN(salary_change) AS min_salary_change,
    MAX(salary_change) AS max_salary_change,
    ROUND(AVG(salary_change), 2) AS avg_salary_change
FROM
    valid_consecutive_changes
GROUP BY
    year
ORDER BY
    year;

This should give you exactly the annual stats you're looking for: minimum, maximum, and average salary changes across all players compared to the previous year.

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