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

Part02 >>

The Analytic Functions are very powerful and I use them every day. What I learned is that some behaviors could seems strange or bugs, but it's the normal way how to the Analytic Function works.

This is the scenario: a slot-machine has a counter that identify how much money is inserted until now. For example if on 03/Jan at 23:59:59, the counter is 10, then 10€ are inserted into machine until 03/Jan.

On  04/Jan at 23:59:59, the counter is 11. This means that 11€ are inserted into machine until 04/Jan.

On 04/Jan the machine gained 1€ (11 - 10).

In order to know how much money a slot machine has gained in one day I need the counters of the machine, day by day.

A slot machine can be in a Bar or in Warehouse if it needs repair. If it is in the Bar then people can play, otherwise, if the machine is in Warehouse, it isn't used and gain 0 (zero). Anyway, because the machine is "ACTIVE" (potentially it could gain money), by default we assign 560€ as "FLAT" value (it is a convention used by the govern).

So, the CNTTOTIN is the counter IN and represent the money the people insert into machine. At the same way, there is the CNTTOTOT and represent the money the people win (coin OUT from the machine).

When FLAT is used, it means that the machine is not read, then the previous CNTTOTIN is used as counter. In this way, the TAXABLE report the FLAT value, meanwhile the WINNINGS is set to 0 (zero).

 

 

COLUMN NAME	MEANING
ELABORATION_DAY	Day of elaboration
MACHINE_ID	Machine identifier
CNTTOTIN	IN counter
CNTTOTOT	OUT counter
COMMERCIAL_ID	Location identifier
WAREHOUSE_ID	Warehouse identifier
WINNINGS	How much money the people wins in a day (2)
TAXABLE	How much money the machine gained in a day (flat value if "active" and in warehouse) (1)(2)
LOCATION_TYPE	CME for COMMERCIAL LOCATION; WRH for WAREHOUSE
CONTRACT_BASE_ID	Contract identifier if the machine is in WRH, NULL if in CME
CONTRACT_COMMERCIAL_ID	Contract identifier if the machine is in CME, NULL if in WRH
OPERATION_TYPE	"Read" if the cpunter is detected, "Flat" otherwise

(1) The column name reflect the fact that from the value, we don't have yet substracted taxe

(2) The values are in €cent

 

Here, how to create the table and popolate it.

 

CREATE TABLE AWP_MACHINE (

    ELABORATION_DAY          DATE, 

    MACHINE_ID               NUMBER(15),

    CNTTOTOT                 NUMBER(15),

    CNTTOTIN                 NUMBER(15),

    COMMERCIAL_ID            NUMBER(15),

    WAREHOUSE_ID             NUMBER(15),

    WINNINGS                 NUMBER(15),

    TAXABLE                  NUMBER(15),

    LOCATION_TYPE            VARCHAR2(3 BYTE),

    CONTRACT_BASE_ID         NUMBER(15),

    CONTRACT_COMMERCIAL_ID   NUMBER(15),

    OPERATION_TYPE           VARCHAR2(8 BYTE) );

Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('01/01/2020', 'DD/MM/YYYY'), 1205770, 73535500, 55272700, 542703, NULL,
    200, 1000, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('02/01/2020', 'DD/MM/YYYY'), 1205770, 73537900, 55274700, 542703, NULL,
    2000, 2400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('03/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, 542703, NULL,
    10300, 9100, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('04/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('05/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('06/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('07/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('08/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('09/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('10/01/2020', 'DD/MM/YYYY'), 1205770, 73556400, 55288700, 542703, NULL,
    3700, 9400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('11/01/2020', 'DD/MM/YYYY'), 1205770, 73558800, 55289100, 542703, NULL,
    400, 2400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('12/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, 542703, NULL,
    1000, 1400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('13/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('14/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('15/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
COMMIT;

In order to calculate TAXABLE and WINNINGS for the first day, you need an extra insert

Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('31/12/2019', 'DD/MM/YYYY'), 1205770, 73534500, 55272500, 542703, NULL,
    NULL, NULL, 'CME', 2656069, 'Read');

Part02 >>