SQL如何检测列值变化并统计员工首份与第二份工作的岗位类别变更次数

首先你原来的data_apply2里的窗口函数排序逻辑存在错误：row_number() over(partition by worker_id order by worker_id) as worker_num 中同一个员工的worker_id是固定值，排序规则无效，无法正确区分首份、第二份工作的先后顺序，需要改成按申请ID升序排序才能匹配工作申请的时间顺序：

row_number() over(partition by worker_id order by id_application asc) as worker_num

接下来可以用LEAD窗口函数直接提取同个员工下一份工作的岗位类别，再筛选统计即可，完整可运行的SQL如下：

with data_apply2 as(
    with data_apply as(
        with all_apply as(
            with job_id as(
                select job_category,
                row_number() over(order by job_category) as job_id
                from job_post
                group by job_category
            )
            select jp.*, job_id.job_id from job_post jp
            join job_id
            on job_id.job_category=jp.job_category
        )
        select ja.worker_id, wk.name, ja.id as id_application, aa.job_category, aa.job_id
        from job_post_application ja
        join all_apply aa
        on aa.id=ja.job_post_id
        join workers wk
        on wk.id = ja.worker_id
    )
    select *, 
    -- 修正排序规则，按申请ID升序区分工作先后顺序
    row_number() over(partition by worker_id order by id_application asc) as worker_num 
    from data_apply
),
-- 新增CTE提取每个员工下一份工作的岗位类别
worker_job_change as (
    select 
        worker_id,
        name,
        job_category as first_job_category,
        LEAD(job_category,1) over(partition by worker_id order by worker_num asc) as second_job_category,
        worker_num
    from data_apply2
    where worker_num <=2 -- 仅保留前两份工作的数据减少计算量
)
-- 统计首份和第二份岗位类别不同的员工总数
select count(distinct worker_id) as change_worker_count
from worker_job_change
where worker_num =1 -- 仅取第一行数据对比，避免重复统计
and first_job_category <> second_job_category
and second_job_category is not null -- 排除只有一份工作的员工

逻辑说明 #

• 修正工作序号的排序逻辑后，保证序号1对应员工首份申请的工作，序号2对应第二份申请的工作
• 用LEAD窗口函数直接提取同个员工下一行的岗位类别作为第二份工作的类别，不需要额外关联表
• 最后筛选首份、第二份岗位类别不同，且确实有第二份工作的员工，去重计数即可得到符合要求的统计结果

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