Last active
December 13, 2021 12:10
-
-
Save jianhe-fun/ad7a974c45f9cd3487fb8ebd645ab0ea to your computer and use it in GitHub Desktop.
WindowsFunction
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
gistfile1.txt : The emp table content.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
All the Window function use case.