Oracle SQL Analytic (Window) Functions: AVG, MAX, MIN, LAG, LEAD, ROW_NUMBER, RANK, DENSE_RANK

[admin]

In this youtube video,

I provide an in-depth explanation of Oracle SQL Analytic (Window) Functions, covering the following functions: AVG, MAX, MIN, LAG, LEAD, ROW_NUMBER, RANK, and DENSE_RANK. These functions are powerful tools for performing advanced queries and calculations across rows within a result set.

You'll learn:

How the AVG, MAX, and MIN functions calculate the average, maximum, and minimum values over a window of rows.
How LAG and LEAD can retrieve values from preceding or following rows within a window.
The differences between ROW_NUMBER, RANK, and DENSE_RANK for assigning row numbers and ranks to records.
Whether you're new to Oracle SQL or looking to enhance your query optimization skills, this tutorial will help you understand how to leverage these window functions effectively for more sophisticated data analysis.

Oracle Documentation
https://docs.oracle.com/en/database/ora ... tions.html

select * from emp

select deptno, sum(sal) from emp group by deptno

select deptno, EMPNO, ENAME, JOB, sal,
sum(SAL) over(partition by deptno order by sal) as dept_running_total from emp;

select deptno, EMPNO, ENAME, JOB, sal,
sum(SAL) over(order by sal) as running_total from emp;

select deptno, EMPNO, ENAME, JOB, sal,
round(avg(SAL) over(order by sal), 2) as running_avg from emp;

select deptno, EMPNO, ENAME, JOB, sal,
row_number() over(order by sal desc) as row_num,
rank() over(order by sal desc) as rnk,
dense_rank() over(order by sal desc) as dense_rnk
from emp;

select * from (
select deptno, EMPNO, ENAME, JOB, sal,
row_number() over(order by sal desc) as row_num,
rank() over(order by sal desc) as rnk,
dense_rank() over(order by sal desc) as dense_rnk
from emp e) rankings
where rankings.dense_rnk <=3

select empno, ENAME, sal, DEPTNO, sal,
row_number() over(partition by DEPTNO order by DEPTNO) as row_num,
lag(SAL,1,0) over (partition by DEPTNO order by DEPTNO) as dept_lag_salary,
lead(SAL,1,0) over (partition by DEPTNO order by DEPTNO) as dept_lead_salary
from emp;

lag_lead.jpg

Explained in the youtube video