Segregate results based on order in Oracle SQL-CodePudding

I'm trying to get the job number from a list of employees. If they have not moved departments over the years, then that should be considered one job. Once they move to a new department, that should be considered a new job. The issue I am facing is if they move back to a department, with my requirements that should also be considered a new job. So a flow would go like:

Sales -> Job 1
IT -> Job 2
Sales -> Job 3
Marketting -> Job 4.

I have made a super simplified query below to demonstrate what I am trying to achieve:

SELECT RANK() OVER(ORDER BY last_year_in_role) JOB_NO, name, Role
FROM
    (
    SELECT MAX(years_at_company) last_year_in_role, Name, Role
    FROM (
        SELECT 'Bob' name, 1 years_at_company, 'Sales' role FROM DUAL
        UNION
        SELECT 'Bob', 2, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 3, 'Sales' FROM DUAL
        UNION 
        SELECT 'Bob', 4, 'IT' FROM DUAL
        UNION
        SELECT 'Bob', 5, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 6, 'Marketing' FROM DUAL
        )
    GROUP BY Name, Role
    )
;

This produces the following results:

This is wrong as it groups all the time at SALES together, even though there was a break in the data. I feel this issue might be able to be solved with a WINDOW function somehow partitioning on ROLE, but I have had no success.

CodePudding user response：

Since this is a Gaps & Island problem where "beaches" are defined as department changes. You can detect them using LAG() analytical function. For example, you can do:

with data as (
        SELECT 'Bob' name, 1 years_at_company, 'Sales' role FROM DUAL
        UNION
        SELECT 'Bob', 2, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 3, 'Sales' FROM DUAL
        UNION 
        SELECT 'Bob', 4, 'IT' FROM DUAL
        UNION
        SELECT 'Bob', 5, 'Sales' FROM DUAL
        UNION
        SELECT 'Bob', 6, 'Marketing' FROM DUAL
)
select *
from (
  select d.*,
    case when role = lag(role) over(partition by name order by years_at_company)
         then 0 else 1 end as beach
  from data d
) x
order by name, years_at_company

Result:

 NAME  YEARS_AT_COMPANY  ROLE       BEACH 
 ----- ----------------- ---------- ----- 
 Bob   1                 Sales      1     
 Bob   2                 Sales      0     
 Bob   3                 Sales      0     
 Bob   4                 IT         1     
 Bob   5                 Sales      1     
 Bob   6                 Marketing  1

See running example at db<>fiddle.

The query above preprocessed the data to produce the beach column. With it you can clearly distinguish which rows actually represent new jobs and which ones don't.

You can continue processing the data to aggregate it to filtering according to your needs, but this should give you all the info you need.