ORACLE Analytic Functions for Data Warehousing 1/ Arabic 

ORACLE Analytic Functions for Data Warehousing 1/ Arabic
--analytic functions allow developers to perform tasks in SQL that were previously confined to procedural languages.
-- let's start by aggregate functions ( sum, avg, count,...)
select * from EMPLOYEES;
select avg(salary)
from employees;
select DEPARTMENT_ID, avg(salary)
from employees
group by DEPARTMENT_ID
order by 1
/* Analytic functions also operate on subsets of rows,
similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query
*/
select avg(salary) from employees;
select employee_id, first_name,salary,(select avg(salary) from employees) avg_sal
from
EMPLOYEES;
--analytic_function([ arguments ]) OVER (analytic_clause)
--The analytic_clause breaks down into the following optional elements.
--[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
select employee_id, first_name,salary, avg(salary) over() avg_sal
from
EMPLOYEES;
select employee_id, first_name,salary, avg(salary) over( order by EMPLOYEE_ID )
from
EMPLOYEES;
select DEPARTMENT_ID, avg(salary)
from employees
group by DEPARTMENT_ID
order by 1
select employee_id, first_name,salary,DEPARTMENT_ID, avg(salary) over(PARTITION BY DEPARTMENT_ID) avg_sal_dept
from
EMPLOYEES;
select employee_id, first_name,salary,DEPARTMENT_ID, avg(salary) over(PARTITION BY DEPARTMENT_ID order by employee_id )
from
EMPLOYEES
drop table concan_card;
create table concan_card
( rec_serial number primary key,
game_name varchar2(100),
game_round number,
player_name varchar2(100),
game_round_score number
);
insert into concan_card values(1,'GAME1',1,'ahmed', -60);
insert into concan_card values(2,'GAME1',1,'khaled', 200);
insert into concan_card values(3,'GAME1',1,'ali', 30);
insert into concan_card values(4,'GAME1',2,'ahmed', 10);
insert into concan_card values(5,'GAME1',2,'khaled', -30);
insert into concan_card values(6,'GAME1',2,'ali', 100);
insert into concan_card values(7,'GAME1',3,'ahmed', -30);
insert into concan_card values(8,'GAME1',3,'khaled', 100);
insert into concan_card values(9,'GAME1',3,'ali', 6);
select * from concan_card
select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY player_name order by player_name,game_round )
from
concan_card

select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY player_name order by rec_serial )
from
concan_card
select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY game_name,player_name order by rec_serial )
from
concan_card
insert into concan_card values(10,'GAME2',1,'ahmed', -60);
insert into concan_card values(11,'GAME2',1,'khaled', 200);
insert into concan_card values(12,'GAME2',1,'ali', 30);
insert into concan_card values(13,'GAME2',2,'ahmed', 10);
insert into concan_card values(14,'GAME2',2,'khaled', -30);
insert into concan_card values(15,'GAME2',2,'ali', 100);
insert into concan_card values(16,'GAME2',3,'ahmed', -30);
insert into concan_card values(17,'GAME2',3,'khaled', 100);
insert into concan_card values(18,'GAME2',3,'ali', 6);

select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY player_name order by player_name,game_round )
from
concan_card

select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY player_name order by rec_serial )
from
concan_card
select rec_serial,game_name ,game_round,player_name, game_round_score,
sum(game_round_score) over ( PARTITION BY game_name,player_name order by rec_serial )
from
concan_card