MySQL Window Functions or Analytic Functions


MySQL 8.0.2 introduces SQL window functions, or analytic functions as they are also sometimes called. They join CTEs (available since 8.0.1) as two of our most requested features, and are long awaited and powerful features. This is the first of a series of posts describing the details. Let’s get started!

 

Introduction

Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g. COUNT or SUM. But where a grouped aggregate collapses this set of rows into a single row, a window function will perform the aggregation for each row in the result set, letting each row retain its identity:

Given this simple table, notice the difference between the two SELECTs:

mysql> CREATE TABLE t(i INT);
mysql> INSERT INTO t VALUES (1),(2),(3),(4);

mysql> SELECT SUM(i) AS sum FROM t;
+——+
| sum |
+——+
| 10 |
+——+

mysql> SELECT i, SUM(i) OVER () AS sum FROM t;
+——+——+
| i | sum |
+——+——+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
+——+——+

 

Read More

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s