Scribd
Upload
23 views

Window Functions

Uploaded by

COORDINATION VENTES MCT
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views

Window Functions

Uploaded by

COORDINATION VENTES MCT
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

Window

Functions
crash course
Swipe 👉👉👉
Window functions in SQL are a powerful feature
that allows performing calculations across a set
of table rows related to the current row, but
without grouping the rows into a single output
row, as aggregation functions do. Instead, they
maintain row-wise results.

Key components of Window Functions:


1. OVER(): Specifies the window for the
function. It can include partitioning, ordering,
and frame clauses.
2. PARTITION BY: Divides the result set into
partitions, and the window function is applied
to each partition separately.
3. ORDER BY: Orders the rows within each
partition, defining the sequence for
calculations.
4. Window Frame: Optional specification to limit
the set of rows considered for each row’s
calculation within the window.
Types of Window Functions

Aggregate Window Functions: These are


aggregate functions that work over a window
of rows (e.g., SUM(), AVG(), MAX(), MIN(),
COUNT()).

Ranking Window Functions: Assign a rank or


a number to rows in a partition (e.g.,
ROW_NUMBER(), RANK(), DENSE_RANK(),
NTILE()).

Value Window Functions: Access data from


other rows in the window (e.g., LAG(), LEAD(),
FIRST_VALUE(), LAST_VALUE()).

Cumulative/Running Window Functions:


Perform cumulative calculations (e.g.,
CUME_DIST(), PERCENT_RANK()).
Examples of Different Window Functions

ROW_NUMBER(): Assigns a unique number


to each row starting from 1 for each
partition.

SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
row_num
FROM
employees;

Explanation: The ROW_NUMBER() function assigns a


unique rank starting from 1 to each employee within
their department, ordered by salary.
RANK(): Similar to ROW_NUMBER() but
gives the same rank to rows with the same
value.

SELECT
employee_id,
department_id,
salary,
RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
rank
FROM
employees;

Explanation: The RANK() function assigns the same


rank to employees with the same salary, skipping
ranks where applicable.
DENSE_RANK(): Similar to RANK(), but
does not skip ranks after ties.

SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
dense_rank
FROM
employees;

Explanation: The DENSE_RANK() function assigns


ranks to tied rows but does not leave gaps in the
rankings.
PERCENT_RANK(): Computes the relative
rank of a row within a partition.

SELECT
employee_id,
department_id,
salary,
PERCENT_RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
percent_rank
FROM
employees;

Explanation: The PERCENT_RANK() function assigns


a percentile rank to each employee based on their
salary within the department.
LAG(): Accesses the value of a column from
a previous row.

SELECT
employee_id,
department_id,
salary,
LAG(salary, 1) OVER(PARTITION BY
department_id ORDER BY salary) AS
previous_salary
FROM
employees;

Explanation: The LAG() function retrieves the


salary of the previous employee in the
department based on ascending salary order.
LEAD(): Accesses the value of a column
from a subsequent row.

SELECT
employee_id,
department_id,
salary,
LEAD(salary, 1) OVER(PARTITION BY
department_id ORDER BY salary) AS
next_salary
FROM
employees;

Explanation: The LEAD() function retrieves the


salary of the next employee in the department.
SAVE THIS POST
JOIN US ON TELEGRAM

We Post Free Data Science & Analytics material ,


Courses and Daily Job Notifications

JOIN USING LINK IN BIO

You might also like