Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-window-functions/ below:

MySQL Window Functions - GeeksforGeeks

MySQL Window Functions are advanced SQL capabilities that enable expensive calculations across sets of rows related to the current row. Aggregate functions collapse the result set. These functions, in general, permit ranking, running totals, moving averages, and access to data from other rows within the same result set. Window functions are particularly helpful in analytical queries and reporting.

In this article, We will learn about MySQL Window Functions with the help of examples and so on.

• Window functions in SQL perform calculations across a set of table rows related to the current row.
  
• Unlike aggregate functions which return a single value for a group of rows, window functions return a result for each row in the result set.
  
• These functions are highly useful for tasks such as ranking, calculating running totals, and finding moving averages while still retaining individual row details.

Syntax:

The basic syntax for a window function in MySQL is as follows:

window_function_name([expression]) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [ROWS or RANGE frame_clause]
)

where,

• window_function_name: which is nothing but the name of your window function, check this example which can be ROW_NUMBER,.
• PARTITION BY: Result set is divided into partitions, and then the window function is applied.
• ORDER BY: This specifies the order of the rows within each partition.
• ROWS or RANGE — Defines the window of rows that should be considered for the calculation.

Now we will be learning different Windows Functions in MySQL:

This function is used to assigns a unique sequential integer to rows within a partition

Example:

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

Output:

employee_id

department_id

salary

row_num

101

1

90000

1

102

1

85000

2

103

2

95000

1

104

2

70000

2

The use of this function id to leave gaps in the ranking when they are ties and also assigns a ranking within a partition.

Example:

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

Output:

employee_id

department_id

salary

rank

dense_rank

101

1

90000

1

1

102

1

85000

2

2

103

1

85000

2

2

104

1

75000

4

3

The use of this function is to calculate the sum of the columns with in a window.

Example:

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees;

Output:

employee_id

salary

cumulative_salary

101

50000

50000

102

60000

110000

103

70000

180000

104

80000

260000

This function is responsible for the moving average of the across the set of rows.

Example:

SELECT
    employee_id,
    salary,
    AVG(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

Output:

employee_id

salary

moving_avg

101

50000

50000.0

102

60000

55000.0

103

70000

60000.0

104

80000

70000.0

LEAD() and LAG() functions allow you to access subsequent or previous rows' data without the need for self-joins.

Example:

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

Output:

employee_id

salary

next_salary

previous_salary

101

50000

60000

NULL

102

60000

70000

50000

103

70000

80000

60000

104

80000

NULL

70000

Window Functions use the OVER() clause, which can be further customized using ORDER BY and PARTITION BY clauses. Below are the different ways to use MySQL window functions with these clauses, along with practical examples.

The ORDER BY clause within the OVER() function is essential to determine the order in which the rows are processed. This ordering influences how window functions, such as ROW_NUMBER(), RANK(), and cumulative calculations, are applied.

Example: The following query demonstrates how to calculate the cumulative salary for employees, ordered by their employee_id.

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees;

Output:

employee_id

salary

cumulative_salary

101

50000

50000

102

60000

110000

103

70000

180000

104

80000

260000

Explanation:

• SUM(salary) calculates the cumulative sum of salaries.
• OVER (ORDER BY employee_id) ensures that the rows are ordered by employee_id before applying the window function.
• The result will show the cumulative_salary, which is the running total of salaries.

The PARTITION BY clause is used to divide the result set into partitions. The window function is then applied independently to each partition. This is useful when you want to perform calculations within groups, such as department-wise cumulative salaries.

Example: In this example, we calculate the dept_cumulative_salary for each department, ordered by the salary in descending order.

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

Output:

employee_id

department_id

salary

dept_cumulative_salary

105

1

90000

90000

104

1

85000

175000

103

1

75000

250000

108

2

95000

95000

107

2

70000

165000

106

2

60000

225000

The window frame also defines which set of rows is included when calling a window function.

ROWS: This defines the number of rows in the frame.

RANGE: Indicates the range of values to be included.

Example of ROWS

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_salary
FROM employees;

Output:

employee_id

salary

sum_salary

101

50000

50000

102

60000

110000

103

70000

180000

104

80000

210000

Example of RANGE

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW) AS sum_salary
FROM employees;

Output:

employee_id

salary

sum_salary

101

50000

50000

102

60000

110000

103

70000

180000

104

80000

260000

If we talk about the frames clauses then it indicates that which subset of rows the windows function applies the calculation. Let's see an example of it.

Example:

SELECT
employee_id,
salary,
SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_salary
FROM employees;

Output:

employee_id

salary

sum_salary

101

50000

50000

102

60000

110000

103

70000

180000

104

80000

210000

• Efficiency: Accomplish complex calculations that otherwise may involve multiple joins or subqueries in an single query.
• Flexibility: Apply functions across partitions of data. Very useful in reporting, analytics.
• Clarity: Simplify SQL queries by staying away from convoluted logic and nested subqueries.

• Performance: In particular, window functions may be very dear in computation, especially over large input datasets.
• One of the limitation is that it can take some time to become fluent with the syntax and behavior

Windows Function very powerful feature in MySQL. It extends SQL's capabilities to run more complex and efficient queries. Window functions realize complicated calculations and analyses directly in your SQL queries, reducing the need for additional application logics or processing. Although in some cases views can add complexity and sometimes raise performance considerations, the flexibility and clarity they bring to the table make them an important tool in any SQL developer's arsenal.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4