SQL Analytic Functions – I

--- SQL Analytic Functions
 REFERENCES:
 http://docs.oracle.com/database/121/SQLRF/functions004.htm#SQLRF06174
 http://orafaq.com/node/55

1.) Table with some data to test:

create table cmagno.tb_emp_salary
 (id_emp number,
 id_dept number,
 employee varchar2(30),
 salary number);

insert into cmagno.tb_emp_salary
 values
 (1,10,'EMP 1', 100);

insert into cmagno.tb_emp_salary
 values
 (2,10,'EMP 2', 150);

insert into cmagno.tb_emp_salary
 values
 (3,10,'EMP 3', 200);

insert into cmagno.tb_emp_salary
 values
 (4,10,'EMP 4', 250);

insert into cmagno.tb_emp_salary
 values
 (5,20,'EMP 5', 100);

insert into cmagno.tb_emp_salary
 values
 (6,20,'EMP 6', 150);

insert into cmagno.tb_emp_salary
 values
 (7,20,'EMP 7', 200);

insert into cmagno.tb_emp_salary
 values
 (8,20,'EMP 8', 250);

insert into cmagno.tb_emp_salary
 values
 (9,20,'EMP 9', 250);

SELECT * FROM CMAGNO.TB_EMP_SALARY;

ID_EMP ID_DEPT EMPLOYEE SALARY
 ------- -------------------- ------------- ----------
 1 10 EMP 1 100
 2 10 EMP 2 150
 3 10 EMP 3 200
 4 10 EMP 4 250
 5 20 EMP 5 100
 6 20 EMP 6 150
 7 20 EMP 7 200
 8 20 EMP 8 250
 9 20 EMP 9 250

-- So, let's play with some Analytic Functions

2.) Group By:

select count(*) Employees , sum(salary) Total_Salary, id_dept
 from cmagno.tb_emp_salary
 group by id_depth
 order by 3;

EMPLOYEES TOTAL_SALARY ID_DEPT
 --------- -------------------- --------------------
 4 700 10
 5 950 20

select count(*) Employees , sum(salary) Total_Salary, id_depth
 from cmagno.tb_emp_salary
 group by id_depth
 having count(*) > 4 -- Just dept with more than 4 employees
 order by 3;

EMPLOYEES TOTAL_SALARY ID_DEPT
 ----------- -------------------- --------------------
 5 950 20

3.) PARTITION BY
 -- Counting by partition
 select ID_EMP, ID_DEPT, count(*) over (partition by ID_DEPT) Total_per_Dept
 from cmagno.tb_emp_salary
 order by 1;

ID_EMP ID_DEPT TOTAL_PER_DEPT
 ------- ------------ --------------------
 1 10 4
 2 10 4
 3 10 4
 4 10 4
 5 20 5
 6 20 5
 7 20 5
 8 20 5
 9 20 5

-- Counting for all rows
 select ID_EMP, ID_DEPT, count(*) over () Total_per_Dept
 from cmagno.tb_emp_salary
 order by 1;

ID_EMP ID_DEPT TOTAL_PER_DEPT
 ------- ---------- ----------------
 1 10 9
 2 10 9
 3 10 9
 4 10 9
 5 20 9
 6 20 9
 7 20 9
 8 20 9
 9 20 9

-- Showing the SUm of the Salary for the DEPT
 select ID_EMP, ID_DEPT, sum(salary) over (partition by id_dept) SALARY_dept
 from cmagno.tb_emp_salary
 order by 1;

ID_EMP ID_DEPT SALARY_DEPT
 ------- --------- -------------
 1 10 700
 2 10 700
 3 10 700
 4 10 700
 5 20 950
 6 20 950
 7 20 950
 8 20 950
 9 20 950

4.) RANK

SELECT ID_EMP, ID_DEPT, SALARY,
 RANK() OVER (PARTITION BY ID_DEPT
 ORDER BY SALARY DESC NULLS LAST) RANK,
 DENSE_RANK() OVER (PARTITION BY
 ID_DEPT
 ORDER BY SALARY DESC NULLS LAST) DENSE_RANK
 FROM cmagno.tb_emp_salary
 ORDER BY 2, RANK;

ID_EMP ID_DEPT SALARY RANK DENSE_RANK
 ------- ----------- ----------- --------- -------------
 4 10 250 1 1
 3 10 200 2 2
 2 10 150 3 3
 1 10 100 4 4
 9 20 250 1 1
 8 20 250 1 1
 7 20 200 3 2
 6 20 150 4 3
 5 20 100 5 4

5.) LAG and LEAD

SELECT ID_DEPT, ID_EMP, SALARY,
 LEAD(SALARY, 1, 0) OVER (PARTITION BY ID_DEPT ORDER BY SALARY DESC NULLS LAST) NEXT_LOWER_SAL,
 LAG(SALARY, 1, 0) OVER (PARTITION BY ID_DEPT ORDER BY SALARY DESC NULLS LAST) PREV_HIGHER_SAL
 FROM CMAGNO.TB_EMP_SALARY
 ORDER BY ID_DEPT, SALARY DESC;

ID_DEPT ID_EMP SALARY NEXT_LOWER_SAL PREV_HIGHER_SAL
 -------- ---------- --------- ----------------- -----------------
 10 4 250 200 0
 10 3 200 150 250
 10 2 150 100 200
 10 1 100 0 150
 20 9 250 250 0
 20 8 250 200 250
 20 7 200 150 250
 20 6 150 100 200
 20 5 100 0 150

6.) STDDEV: Returns the sample standard deviation of an expression

SELECT employee, salary,
 STDDEV(salary) OVER (ORDER BY ID_EMP) "StdDev"
 FROM CMAGNO.TB_EMP_SALARY

EMPLOYEE SALARY StdDev
 ------------ ---------- ----------
 EMP 1 100 .000
 EMP 2 150 35.355
 EMP 3 200 50.000
 EMP 4 250 64.550
 EMP 5 100 65.192
 EMP 6 150 58.452
 EMP 7 200 55.635
 EMP 8 250 59.761
 EMP 9 250 61.237

7.) DENSE_RANK - Another

SELECT EMPLOYEE, ID_DEPT, SALARY,
 MIN(SALARY) KEEP (DENSE_RANK FIRST ORDER BY SALARY)
 OVER (PARTITION BY ID_DEPT) "Lower",
 MAX(SALARY) KEEP (DENSE_RANK LAST ORDER BY SALARY)
 OVER (PARTITION BY ID_DEPT) "Higher"
 FROM CMAGNO.TB_EMP_SALARY;

EMPLOYEE ID_DEPT SALARY Lower Higher
 ------------ ------------ ------------ ----------- ------------
 EMP 4 10 250 100 250
 EMP 3 10 200 100 250
 EMP 2 10 150 100 250
 EMP 1 10 100 100 250
 EMP 8 20 250 100 250
 EMP 7 20 200 100 250
 EMP 6 20 150 100 250
 EMP 9 20 250 100 250
 EMP 5 20 100 100 250
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s