SQL Overview: Transforming Data with SQL Window Functions

Photo by bady abbas on Unsplash

Window functions provide a way to perform calculations across a set of table rows related to the current row. These functions can be a powerful tool for data analysis and reporting. I’ll cover the basic syntax, common use cases, and examples using the SQL standard.

1. Basic Syntax:

The basic syntax of a window function in MySQL is similar to the SQL standard:

SELECT
  column1,
  column2,
  window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING) AS result_column
FROM
  your_table;

2. Common Window Functions:

2.1. Aggregation Functions:

• These functions perform calculations over a window of rows and return a single value.
• AVG():

-- Calculate the average salary for each department
SELECT
  department,
  AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM
  employees;

• SUM():

SELECT SUM(column) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING) AS sum_column;

• MIN():

SELECT MIN(column) OVER (PARTITION BY…