WindowsFunction

WindowFunction

select * from emp; -- show the whole table.
begin;
create table emp(empid int primary key, name  text, department text, salary numeric);
insert into emp(empid, name, department, salary) values
(7,'Carol','Sales',4600.00),
(8,'sean','Sales',5600.00),
(1,'Cowen','Shipping',5400.00),
(2,'Tyler','Marketing',6300.00),
(5,'Charles','Sales',5400.00),
(3,'Dylan','Shipping',4800.00),
(4,'George','Marketing',7100.00),
(6,'Henry','Shipping',6600.00);
commit;



--show  name, department, salary, department average salary.
select name, department, salary, avg(salary) over w from emp
window w as (partition by department) order by department ;

-- show name, department, salary rank. rank is department wise. Rank is also order by salay desc.
select  name, department, salary, rank() over w
from emp
window w as (partition by department order by salary desc );

--show who have the top salary over each department.
with ss  as (select  name, department, salary, rank() over w
from emp
window w as (partition by department order by salary desc ))
select * from ss where rank = 1;

--show name, salary and all records aggregate sum of the all salary, and all records average salary.
select name, salary, sum(salary) over w, (avg(salary) over w) as average from emp window w as();

--show name, salary, the name's salary/overall salary--display in percentage wise.
select name, salary, (round(salary/sum(salary) over w *100,2))as pct from emp window  w as () order by pct;

--show department wise average salary and subtotal salary.
select name,department, avg(salary) over w, sum(salary) over w
from emp window w as (partition by department ) order by department;

--show the count of employee, total salary, average(salary) over all.  
select count(*) , sum(salary), avg(salary)  from emp;

-- Group By rollup, show department wise sum, avg and in the last row show overall sum and total.
select department, count(*), sum(salary), round(avg(salary),2)  as avg
from emp group by rollup (department) order by department

--Cumulative Total using order by. Show name, salary, cumulative salary based on salary order desc)
-- If use "range between unbounded preceding and current row" then two row have same value will
-- have the same cumuative sum total. When "Cowen" and "Charles" have the same salary, the cumulative sum is the same.
select name, salary, sum(salary)  over w
from emp window  w as (order by salary desc range between unbounded preceding and current row )

--use rows between unbounded preceding and current row.
select name, salary, sum(salary)  over w
from emp window  w as (order by salary desc rows between unbounded preceding and current row )

--show name, salary, average salary, everybody's salary diff with average salary.
select name, salary, avg(salary) over w ,  (salary-avg(salary) over w) as diff
from emp window w as () order by diff;

--Compare to next emp's salary.
select name, salary, (lead(salary,1) over w) as nextpersonSalary
from emp window  w as (order by salary desc)

-- compare to lowest paid employee global. the diff and the percent diff as pct.
select name, salary,last_value(salary) over w, (salary- (last_value(salary) over w)) as diff,
round( ((salary- (last_value(salary) over w))/emp.salary)*100,2) as pct
from emp window w as (order by salary desc rows between unbounded preceding and unbounded  following)

--rank and dense rank the salary.
select name, salary, rank() over w, dense_rank() over w from emp window w as (order by salary desc )

--compare department average, and different with department average.
select name,department, salary , avg(salary) over w, (salary- avg(salary) over w) as Department_Diff_AVG from emp window w as (partition by department )

--Within the same department, compare the salary. partitioned by department.
select name,department, salary, lead(salary,1) over w from emp window w as (partition by department order by salary desc );

--department rank and global rank.
select name, salary, department, (rank() over w) as gloabl_rank,
       rank() over (partition by department order by salary desc) as department_rank
from emp window  w as (order by salary desc) order by department;

All the Window function use case.

gistfile1.txt : The emp table content.