MySQL and window functions

Question

It seems that MySQL does not support window functions.
E.g. the simple: COUNT(*) OVER() AS cnt does not work.
What I am not sure is if this applies to commercial version as well (I assume the community version is limited subset).
If not, how does one work around this missing feature?

The community edition of MySQL Server is not a limited subset in any meaningful sense. The differences are in add-ons and plugins that do not affect core functionality. — Michael - sqlbot, Apr 17 '13 at 05:57
Actually, MySQL9 supports it. I have been using pandas instead which kind of sucks. — Andrew Scott Evans, Sep 18 '17 at 18:56
MySQL 8 supports window function. For reference: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html — gvgvgvijayan, May 08 '18 at 05:51
MySQL supports window functions since version 8.0. This link might help. — Hamid Mohayeji, Nov 23 '18 at 22:02

Shlomi Noach · Answer 1 · 2018-09-03T16:52:24.190

36

MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT().

There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, and linking to various solutions based on GROUP_CONCAT.

In particular, Selecting a specific non aggregated column data in GROUP BY and SQL: selecting top N records per group, another solution might be of interest to you and could give you a kick start.

Things you should note about GROUP_CONCAT():

Can use DISTINCT
Can use ORDER BY ... ASC/DESC
Can set SEPARATOR
As any aggregation function - it discards NULL values; plenty tricks on that.

(*) Support for Window Functions has been added in MySQL 8

edited Sep 03 '18 at 16:52

answered Apr 17 '13 at 07:02

Shlomi Noach

7,363
1
24
24

MySQL supports window functions as of Version 8 – Max Aug 23 '18 at 16:12
GROUP_CONCAT is not a window function, it is an ordered set function. – SQLRaptor Sep 03 '18 at 17:12
1

@SQLRaptor no one claimed it is. – ypercubeᵀᴹ Sep 03 '18 at 20:41

chx · Answer 2 · 2017-12-07T15:13:58.767

12

It really should be noted that MariaDB 10.2 (released in 2017 May) has window functions. That's certainly one avenue to pursue if you need MySQL and window functions.

edited Dec 07 '17 at 15:13

answered Dec 25 '16 at 17:37

chx

417
4
14

MySQL and window functions

2 Answers2

Linked