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?
-
3The 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 - sqlbotCommented Apr 17, 2013 at 5:57
-
Have a look at this workaround here and here– StuartLCCommented Jan 26, 2014 at 19:45
-
Actually, MySQL9 supports it. I have been using pandas instead which kind of sucks.– Andrew Scott EvansCommented Sep 18, 2017 at 18:56
-
1MySQL 8 supports window function. For reference: dev.mysql.com/doc/refman/8.0/en/window-functions.html– gvgvgvijayanCommented May 8, 2018 at 5:51
-
MySQL supports window functions since version 8.0. This link might help.– Hamid MohayejiCommented Nov 23, 2018 at 22:02
Add a comment
|
2 Answers
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
answered Apr 17, 2013 at 7:02
-
-
GROUP_CONCAT is not a window function, it is an ordered set function. Commented Sep 3, 2018 at 17:12
-
1
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.
