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

<< Part04     Part06 >>

Now, using the LAST_VALUE analytic function, I can "fill the blank"

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    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'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    machine_id,
    LAST_VALUE (fix) IGNORE NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    fix_1_)
select * from fix_2_
    order by elaboration_day;

 

 

I'll explain why the LAST_VALUE "fill the blank" in a different post.

At this point I can separate the 4 interval. To show this I use again COUNT and ROW_NUMBER analytic function

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    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'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    machine_id,
    LAST_VALUE (fix) IGNORE NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    fix_1_)
----------------------
select
    elaboration_day,
    machine_id,
    count(location_type) over (partition by machine_id, location_type, fix) nrd,
    row_number() over (partition by machine_id, location_type, fix order by elaboration_day) rn,
    location_type,
    commercial_id,
    warehouse_id
from fix_2_
    order by elaboration_day;

 

How you can see, now COUNT function (NRD column) count only rows in it a local range and not over all. The same is true for ROW_NUMBER function (NR column) that restart each time LOCATION_TYPE change.

<< Part04     Part06 >>