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

<< Part01     Part03 >>

Question. I want to know how many continues days the slot-machine was in specific location

Start using minimum columns:

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

 If I count the days by LOCATION_TYPE:

SELECT
    elaboration_day,
    count(location_type) over (partition by machine_id, location_type) nrd,
    location_type,
    comme+rcial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

I don't have 3 + 3 + 6 + 3 days. Instead I have 6 + 9 days as shown in the following picture

This is because the COUNT analytic function, count the days where the machine was during the interval I use (15 days in this case: 01/Jan - 15/Jan)

 

 

<< Part01     Part03 >>