| Bug #28957 | Ordered Analytical Functions | ||
|---|---|---|---|
| Submitted: | 7 Jun 2007 19:30 | Modified: | 9 Jun 2007 1:44 |
| Reporter: | Jason Shuler | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 5.x | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | aggregate, analytic, OLAP, statistics | ||
[9 Jun 2007 1:44]
MySQL Verification Team
Thank you for the bug report feature request.
[15 May 2008 6:35]
Jay Mount
Hi, Is support for these window & "analytic" function from the SQL:2003 standard and db's like Oracle, SQL Server, DB2, etc... coming for MySQL. I saw this and one other feature request but no updates as to whether or not the work has been started or what stage it is in. Oracles functionality seems fairly solid including cumulative and rolling windows on rows or range (dates, numbers) with rank, lag, lead, first, last, etc... MySQL is an excellent db and the additional of these new SQL features would be a huge addition! Especially for data warehousing, reporting, analytical work, etc...
[9 Jul 2009 8:00]
Eli Revach
Any update on this ?
[6 Oct 2014 9:04]
Michał Staruch
Guys at MySQL, seriously, wake up - it's 2014 already, if you didn't notice. When do you plan to start supporting SQL:2003, in 2030?
[22 Oct 2015 19:36]
Daniël van Eeden
Related: Bug #35893 analytiacal function like RANK etc to be implemented
Description: Most of the big databases have recently added support for Ordered Analytical Functions - these are ANSI standard "modifiers" for the behavior of grouping functions that enable to you perform some complicated statistical calculations otherwise very difficult. Most of these extension apply to the standard aggregate functions, with the addition of the "OVER ()" statement. These allow you to partition records based on a field value, and then rank or analyse the records based only on the partition. Some of the immensely helpful features include: + Calculating a cumulative sum + Performing time-series analysis (the ability to subtract the value of the previous record from the current record) In particular, I have a table that contains cumulative time-based performance metrics, and I need to retreive the difference between rows. Right now, I will have to do this in arrays within the application. Example: Here is how I would do this using OAF: ~~~~ SELECT time_stamp, read_cnt, read_cnt - MAX(read_cnt) OVER (ORDER BY time_stamp DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS delta_read_cnt FROM perf_data ORDER BY time_stap DESC; ~~~~ The output would look like this: time read_cnt delta_read_cnt 00:00 100 <null> 00:05 123 23 00:10 214 91 00:16 220 6 ... This query could be extended to report the rate as well: (Note: this is Pseudo SQL - I am referencing a column alias rather than copying the entire definition for clarity) ~~~~ SELECT time_stamp, time_stamp - MAX(time_stamp) OVER (ORDER BY time_stamp DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS delta_time, read_cnt, read_cnt - MAX(read_cnt) OVER (ORDER BY time_stamp DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS delta_read_cnt, -- delta_read_cnt / delta_time AS read_rate -- FROM perf_data ORDER BY time_stap DESC; ~~~~ Notice there is no GROUP BY. This is not necessary, because all records are displayed. You may combine OAFs with standard aggregate functions as well. How to repeat: n/a Suggested fix: Implement Ordered Analytical Fuctions following ANSI SQL-2003 syntax.