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.