https://livesql.oracle.com/apex/livesql/file/tutorial_GNRYA4548AQNXC0S04DXVEV08.html
https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions#rank

drop table CARS purge;
create table CARS (
     id INTEGER GENERATED ALWAYS AS IDENTITY
    ,brand VARCHAR2(15) not null
    ,model VARCHAR2(10) not null
    ,year NUMBER(4) not null
    ,color VARCHAR2(10) not null
    ,category VARCHAR2(12) not null
    ,price NUMBER not null
    ,power NUMBER(4) not null
    ,fuel VARCHAR2(8) not null
)
;

Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Audi','A4','2001','gray','city','5400','150','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Audi','A6','2012','gray','limousine','12000','204','DIESEL');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('BMW','Serie 4','2020','white','sport','16000','240','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('BMW','X6','2018','blue','SUV','15000','280','DIESEL');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Volkswagen','Polo','2014','gray','city','4800','90','DIESEL');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Renault','Arkana','2023','green','SUV','35000','220','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Porche','Cayenne','2021','black','SUV','41000','280','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Tesla','Model 3','2023','black','city','30500','250','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Tesla','Model 3','2023','white','city','30500','250','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Tesla','Model 3','2022','black','city','24000','250','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Audi','A4','2022','red','city','26000','200','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Audi','Q5','2021','gray','SUV','38000','260','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('BMW','Serie 3','2022','white','city','46000','240','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('BMW','Serie 3','2023','white','city','44000','240','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('BMW','Serie 3','2021','white','city','42000','240','ELECTRIC');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Renault','Clio','2019','black','city','8900','110','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Renault','Clio','2020','black','city','9600','110','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Renault','Twingo','2019','red','city','7800','90','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Renault','Twingo','2022','green','city','9200','90','SP');
Insert into POC.CARS (BRAND,MODEL,YEAR,COLOR,CATEGORY,PRICE,POWER,FUEL) values ('Porche','911','2022','gray','sport','61000','310','SP');

commit;


-- display cars and total cars count
select 
   c.*
  ,count(*) over() as Total_count 
from 
  CARS c
;

-- display cars and the number of cars by brand
select 
   c.*
  ,count(*) over(partition by (brand)) as Brand_count 
from 
  CARS c
;


-- number of cars and sum of prices grouped by color
select color, count(*), sum(price)
from  CARS
group  by color;

-- integrating last group by query as analytic
-- adding that "inline" for each line
select 
   c.*
  ,count(*) over(partition by (color)) as count_by_color 
  ,sum(price) over(partition by (color)) as SUM_price_by_color
from 
  CARS c
;



-- average price by category
select CATEGORY, avg(price)
from  CARS
group  by CATEGORY;

-- for each car, the percentage of price/median price of it's category
select 
   c.*
  ,100*c.price/avg(c.price) over (partition by (category)) Price_by_avg_category_PERCENT
from 
  CARS c
;


select CATEGORY, average(price)
from  CARS
group  by CATEGORY;


-- order by in alalytic: runtime from FIRST key until CURRENT key
select b.*, 
       count(*) over (
         order by brick_id
       ) running_total, 
       sum ( weight ) over (
         order by brick_id
       ) running_weight
from   bricks b;


  BRICK_ID COLOUR     SHAPE          WEIGHT RUNNING_TOTAL RUNNING_WEIGHT
---------- ---------- ---------- ---------- ------------- --------------
         1 blue       cube                1             1              1
         2 blue       pyramid             2             2              3
         3 red        cube                1             3              4
         4 red        cube                2             4              6
         5 red        pyramid             3             5              9
         6 green      pyramid             1             6             10

6 rows selected.


select 
     c.*
    ,sum(c.price) over (order by c.id)
from 
    cars c;



        ID BRAND           MODEL            YEAR COLOR      CATEGORY          PRICE      POWER FUEL     SUM(C.PRICE)OVER(ORDERBYC.ID)
---------- --------------- ---------- ---------- ---------- ------------ ---------- ---------- -------- -----------------------------
         1 Audi            A4               2001 gray       city               5400        150 SP                                5400
         2 Audi            A6               2012 gray       limousine         12000        204 DIESEL                           17400
         3 BMW             Serie 4          2020 white      sport             16000        240 SP                               33400
         4 BMW             X6               2018 blue       SUV               15000        280 DIESEL                           48400
         5 Volkswagen      Polo             2014 gray       city               4800         90 DIESEL                           53200
         6 Renault         Arkana           2023 green      SUV               35000        220 ELECTRIC                         88200
         7 Porche          Cayenne          2021 black      SUV               41000        280 SP                              129200
         8 Tesla           Model 3          2023 black      city              30500        250 ELECTRIC                        159700
         9 Tesla           Model 3          2023 white      city              30500        250 ELECTRIC                        190200
        10 Tesla           Model 3          2022 black      city              24000        250 ELECTRIC                        214200
        11 Audi            A4               2022 red        city              26000        200 SP                              240200
        12 Audi            Q5               2021 gray       SUV               38000        260 SP                              278200
        13 BMW             Serie 3          2022 white      city              46000        240 ELECTRIC                        324200
        14 BMW             Serie 3          2023 white      city              44000        240 ELECTRIC                        368200
        15 BMW             Serie 3          2021 white      city              42000        240 ELECTRIC                        410200
        16 Renault         Clio             2019 black      city               8900        110 SP                              419100
        17 Renault         Clio             2020 black      city               9600        110 SP                              428700
        18 Renault         Twingo           2019 red        city               7800         90 SP                              436500
        19 Renault         Twingo           2022 green      city               9200         90 SP                              445700
        20 Porche          911              2022 gray       sport             61000        310 SP                              506700

20 rows selected.


-- adding PARTITION by EXPR will "group by EXPR" and reset FIRST key for each group
select 
     c.*
    ,sum(c.price) over (partition by brand order by c.id)
from 
    cars c;


        ID BRAND           MODEL            YEAR COLOR      CATEGORY          PRICE      POWER FUEL     SUM(C.PRICE)OVER(PARTITIONBYBRANDORDERBYC.ID)
---------- --------------- ---------- ---------- ---------- ------------ ---------- ---------- -------- ---------------------------------------------
         1 Audi            A4               2001 gray       city               5400        150 SP                                                5400
         2 Audi            A6               2012 gray       limousine         12000        204 DIESEL                                           17400
        11 Audi            A4               2022 red        city              26000        200 SP                                               43400
        12 Audi            Q5               2021 gray       SUV               38000        260 SP                                               81400
         3 BMW             Serie 4          2020 white      sport             16000        240 SP                                               16000
         4 BMW             X6               2018 blue       SUV               15000        280 DIESEL                                           31000
        13 BMW             Serie 3          2022 white      city              46000        240 ELECTRIC                                         77000
        14 BMW             Serie 3          2023 white      city              44000        240 ELECTRIC                                        121000
        15 BMW             Serie 3          2021 white      city              42000        240 ELECTRIC                                        163000
         7 Porche          Cayenne          2021 black      SUV               41000        280 SP                                               41000
        20 Porche          911              2022 gray       sport             61000        310 SP                                              102000
         6 Renault         Arkana           2023 green      SUV               35000        220 ELECTRIC                                         35000
        16 Renault         Clio             2019 black      city               8900        110 SP                                               43900
        17 Renault         Clio             2020 black      city               9600        110 SP                                               53500
        18 Renault         Twingo           2019 red        city               7800         90 SP                                               61300
        19 Renault         Twingo           2022 green      city               9200         90 SP                                               70500
         8 Tesla           Model 3          2023 black      city              30500        250 ELECTRIC                                         30500
         9 Tesla           Model 3          2023 white      city              30500        250 ELECTRIC                                         61000
        10 Tesla           Model 3          2022 black      city              24000        250 ELECTRIC                                         85000
         5 Volkswagen      Polo             2014 gray       city               4800         90 DIESEL                                            4800

20 rows selected.



-- when the keys of ORDER BY are not distinct, over (order by KEY) the analytic function will not change for lignes having the same KEY value
-- to force the compute from previous line to current add : rows between unbounded preceding and current row



select b.*, 
       count(*) over (
         order by weight
       ) running_total, 
       sum ( weight ) over (
         order by weight
       ) running_weight
from   bricks b
order  by weight;


  BRICK_ID COLOUR     SHAPE          WEIGHT RUNNING_TOTAL RUNNING_WEIGHT
---------- ---------- ---------- ---------- ------------- --------------
         1 blue       cube                1             3              3
         3 red        cube                1             3              3
         6 green      pyramid             1             3              3
         4 red        cube                2             5              7
         2 blue       pyramid             2             5              7
         5 red        pyramid             3             6             10


select b.*, 
       count(*) over (
         order by weight
         rows between unbounded preceding and current row
       ) running_total, 
       sum ( weight ) over (
         order by weight
         rows between unbounded preceding and current row
       ) running_weight
from   bricks b
order  by weight;



  BRICK_ID COLOUR     SHAPE          WEIGHT RUNNING_TOTAL RUNNING_WEIGHT
---------- ---------- ---------- ---------- ------------- --------------
         1 blue       cube                1             1              1
         3 red        cube                1             2              2
         6 green      pyramid             1             3              3
         4 red        cube                2             4              5
         2 blue       pyramid             2             5              7
         5 red        pyramid             3             6             10

6 rows selected.
