• Data stored in
tables
MySQL • Rows hold on
individual records
WINDOW FUNCTION
window function is used to perform calculations • Columns hold
across a set of rows related to the current row. It different types of
allows you to define a window or a subset of rows.
data
• Can create
relationship
Window Functions: Functions that operate on a group of
rows (a "window") rather than individual rows.
OVER Clause: This clause specifies the window over
which the function is applied, and it's where you define
WINDOW the frame.
FUNCTIONS: PARTITION BY: Divides the result set into
groups, applying the window function independently
within each group.
ORDER BY: Specifies the order of rows within the
window,
Ranking functions: Assign ranks to rows based on
specified criteria (e.g., RANK, DENSE_RANK,
ROW_NUMBER, ).
Aggregate functions as window functions: Calculate
TYPES OF aggregates within a window (e.g., SUM(), AVG(),
COUNT(), MIN(), MAX()).
FUNCTIONS: Offset functions: Access values from other rows within
the window (e.g., LEAD(), LAG(), FIRST_VALUE(),
LAST_VALUE()).
RANK:
Skips numbers for tied values: If multiple rows have
the same value according to the ordering criteria,
they are assigned the same rank, and subsequent
ranks skip numbers to account for the ties.
This creates gaps in the ranking sequence.
RANKING DENSE_RANK:
FUNCTIONS: Assigns consecutive ranks even for tied values: Each
tied value receives the same rank, and the next rank
immediately follows without skipping numbers.
This creates a continuous ranking sequence without
gaps.
SUM(): Calculates the total sum of values in a column.
AVG(): Calculates the average value in a column.
COUNT(): Counts the number of non-null values in a
AGGREGATE column.
FUNCTIONS MIN(): Returns the minimum value in a column.
MAX(): Returns the maximum value in a column.
LEAD(value_expression, offset): Accesses a value
from a subsequent row within the window frame.
LAG(value_expression, offset): Accesses a value from
a previous row within the window frame.
OFFSET FIRST_VALUE(value_expression): Returns the first
FUNCTIONS: value in the window frame.
LAST_VALUE(value_expression): Returns the last value
in the window frame.
BETWEEN <n> PRECEDING AND <n>
FOLLOWING: Includes a specified number of rows
before and after the current row.
BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING: Includes all rows in the
partition.
FRAMES BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW: Includes rows from the start of the partition up
to the current row.
BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING: Includes rows from the current row to
the end of the partition.
1. Rank students by mark within their respective
departments, allowing identification of top
performers in each group.
2. Calculate a moving average of student marks. (use
1 preceding and 1 following or any frames)
ASSIGNMEN 3. Retrieves values from previous and subsequent
rows. (use lead and lag)
T 4. Identifies the first and last order dates for each
customer. (use first value and last value)
5. Assigns a quartile number (1-4) to each student
based on their rank. (use NTILE)
1. Compare LAG and LEAD functions, providing
examples of their usage.
2. Explain what window functions are and how they
differ from aggregate functions.
3. Describe the syntax and components of the OVER
clause.
ASSIGNMEN 4. Evaluate the window framing options (ROWS,
T RANGE) and their impact on results.
5. Discuss performance considerations when using
window functions, especially with large datasets.
