Learning of SQL Day 31

 

Day 31: Window Functions

Introduction:

• Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single result for a set of rows, window functions return a value for each row while preserving the original row structure.

Key Concepts:

• Window Function: A function that performs a calculation across a set of rows related to the current row.

• OVER() Clause: Defines the window (set of rows) over which the window function operates.

• PARTITION BY: Divides the result set into partitions to which the window function is applied.

• ORDER BY: Orders the rows within each partition.

SQL Commands and Examples:

1. Basic Window Function:

   • Calculate the running total of a column using the SUM() function.

sql

-- Calculate the running total of credits for each student
SELECT StudentID, CourseID, Credits,
       SUM(Credits) OVER (ORDER BY StudentID) AS RunningTotal
FROM StudentCourses;

2. Using PARTITION BY:

   • Use the PARTITION BY clause to calculate the function within each partition.

sql

-- Calculate the running total of credits for each student within each course
SELECT StudentID, CourseID, Credits,
       SUM(Credits) OVER (PARTITION BY CourseID ORDER BY StudentID) AS RunningTotal
FROM StudentCourses;

3. ROW_NUMBER() Function:

   • Assign a unique sequential integer to rows within a partition.

sql

-- Assign a unique row number to each student within each course
SELECT StudentID, CourseID, Credits,
       ROW_NUMBER() OVER (PARTITION BY CourseID ORDER BY StudentID) AS RowNumber
FROM StudentCourses;

4. RANK() Function:

   • Assign a rank to each row within a partition, with gaps in the ranking.

sql

-- Rank students by credits within each course
SELECT StudentID, CourseID, Credits,
       RANK() OVER (PARTITION BY CourseID ORDER BY Credits DESC) AS Rank
FROM StudentCourses;

Practice Exercise:

1. Use the StudentCourses table with columns StudentID, CourseID, Credits.

2. Write a query to calculate the running total of credits for each student.

3. Write a query to calculate the running total of credits for each student within each course.

4. Write a query to assign a unique row number to each student within each course.

5. Write a query to rank students by credits within each course.

sql

-- Calculate the running total of credits for each student
SELECT StudentID, CourseID, Credits,
       SUM(Credits) OVER (ORDER BY StudentID) AS RunningTotal
FROM StudentCourses;

-- Calculate the running total of credits for each student within each course
SELECT StudentID, CourseID, Credits,
       SUM(Credits) OVER (PARTITION BY CourseID ORDER BY StudentID) AS RunningTotal
FROM StudentCourses;

-- Assign a unique row number to each student within each course
SELECT StudentID, CourseID, Credits,
       ROW_NUMBER() OVER (PARTITION BY CourseID ORDER BY StudentID) AS RowNumber
FROM StudentCourses;

-- Rank students by credits within each course
SELECT StudentID, CourseID, Credits,
       RANK() OVER (PARTITION BY CourseID ORDER BY Credits DESC) AS Rank
FROM StudentCourses;

Important Tips:

• Window functions provide powerful analytical capabilities, allowing you to perform calculations across rows while maintaining the original row structure.

• Use the PARTITION BY and ORDER BY clauses to define the window and order within which the function operates.

Understanding window functions allows you to perform complex and flexible data analyses.