Analytic Functions preserve the "PARTITION BY" clause (Part 4)

<< Part03     Part05>>

Now the question is: is it exists a way to not preserve the partitioning?

There are a lot of solutions to this question. In the next posts I'm going to show you the one I used.

First of all, I have to evaluate the previous row: if the location change, then there is a "point of change" as in the following picture

SELECT
    elaboration_day,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

 

 So, there are 2 different conditions to evaluate the "point of change":

1. WHEN next "location_type" is NULL (true only for the first row)
   
2. WHEN next "location_type" != actual "location_type"
   

 IF one of these 2 conditions are true THEN I use the elaboration_day, ELSE NULL

 SELECT
    elaboration_day,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

 

<< Part03     Part05 >>