Oracle SQL查询：找出各Jobnumber重复时薪开始前的最高时薪年份

需求说明 #

需要编写Oracle SQL查询语句，从业务表my_GRID1_TEST中，为每个工号找出重复小时工资开始前的最高小时工资对应的年份。具体规则：每个工号在某一特定年份（各工号年份不同）之后，小时工资开始重复，需取该重复阶段之前的最高小时工资对应的记录。

表结构 #

CREATE TABLE "my_GRID1_TEST"
(    "YEAR" NUMBER(38,0),
"JOBCODE" NUMBER(38,0),
"HOURLY_RATE" NUMBER(38,15)
)

数据示例 #

注：示例中Jobnumber对应表结构中的JOBCODE字段

Year    Jobnumber  hourly_rate
2024    1126    35.582755
2023    1126    35.96506525
2022    1126    36.3511986025
2021    1126    36.74119328852501
2020    1126    37.13508792141026
1999    1126    44.59125280927884
1998    1126    44.94554923034844
1997    1126    45.30250287457605
1996    1126    45.662133671135376
1995    1126    45.662133671135376
1994    1126    45.662133671135376
2024    9196    33.05523
2023    9196    33.05523
2022    9196    33.412265
2021    9196    33.77287035
2005    9196    39.11636105858664
2004    9196    39.42959579152604
2003    9196    39.745179784962485
2002    9196    40.0631306583497
2001    9196    40.38346616328733
2000    9196    42.02154384293608
1999    9196    42.02154384293608
1998    9196    42.02154384293608
1997    9196    42.02154384293608
2024    6454    37.494306250000005
2023    6454    37.8957320125
1999    6454    46.95322894974278
1998    6454    48.07765385469215
1997    6454    48.07765385469215
1996    6454    48.07765385469215
1995    6454    48.07765385469215
1994    6454    48.07765385469215
1993    6454    48.07765385469215

预期结果集 #

SQL查询语句 #

WITH ranked_data AS (
    SELECT 
        YEAR,
        JOBCODE AS Jobnumber,
        HOURLY_RATE,
        -- 按工号分组，年份倒序排序
        ROW_NUMBER() OVER(PARTITION BY JOBCODE ORDER BY YEAR DESC) AS rn,
        -- 标记当前行与下一行的工资是否相同
        CASE WHEN LEAD(HOURLY_RATE) OVER(PARTITION BY JOBCODE ORDER BY YEAR DESC) = HOURLY_RATE 
             THEN 1 ELSE 0 END AS is_duplicate_next
    FROM my_GRID1_TEST
),
-- 找到每个工号首次出现重复的位置
first_duplicate AS (
    SELECT 
        Jobnumber,
        MIN(rn) AS first_dup_rn
    FROM ranked_data
    WHERE is_duplicate_next = 1
    GROUP BY Jobnumber
),
-- 筛选出重复开始前的所有记录，并取最高工资对应的年份
pre_duplicate_data AS (
    SELECT 
        rd.YEAR,
        rd.Jobnumber,
        rd.HOURLY_RATE,
        RANK() OVER(PARTITION BY rd.Jobnumber ORDER BY rd.HOURLY_RATE DESC, rd.YEAR DESC) AS rate_rank
    FROM ranked_data rd
    JOIN first_duplicate fd ON rd.Jobnumber = fd.Jobnumber
    WHERE rd.rn < fd.first_dup_rn
)
SELECT YEAR, Jobnumber, HOURLY_RATE
FROM pre_duplicate_data
WHERE rate_rank = 1;

语句解释 #

1. ranked_data CTE：按工号分组，年份倒序排列，同时标记当前行的下一行工资是否与当前行相同，用于识别重复工资的起始点。
2. first_duplicate CTE：找出每个工号中首次出现工资重复的位置（即重复阶段的起始行）。
3. pre_duplicate_data CTE：筛选出重复阶段开始前的所有记录，对这些记录按小时工资降序、年份降序排名，确保取到最高工资对应的最新年份。
4. 最终查询：取每个工号排名第一的记录，即为所需结果。

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