18

I'm looking for analytical function like PARTITION BY in MySQL (see the docs for more info)

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.

Does it exist?

Andy K
  • 4,944
  • 10
  • 53
  • 82
ripper234
  • 222,824
  • 274
  • 634
  • 905
  • If this is like Windowing Functions, then no: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems – ripper234 Aug 04 '12 at 06:51
  • 2
    If you want windowing functions in an open source database you will need to use PostgreSQL. –  Aug 04 '12 at 08:13

4 Answers4

21

just wanted to tell you that you can use variables in MySQL to mimic analytic functions. SUM OVER, for example, could be done as follows:


SELECT amount, 
    @sum := @sum + amount as sum 
FROM tbl
JOIN (SELECT @sum := 0) s

If you want to PARTITION BY, it's possible but just a bit more complicated. Basically, you add another @variable to watch the account (or whatever you want to partition by), order by account (or your variable), and then reset the @sum when the account changes. As follows:


SELECT account, 
    amount, 
    (case when @account != account then @sum := amount else @sum := @sum + amount end) as sum,
    (case when @account != account then @account := account else @account end) as _
FROM (SELECT * FROM tbl ORDER BY account)
JOIN (SELECT @sum := 0) s
JOIN (SELECT @account := '') a

You'll note two major changes that had to be done to accomplish the partition effect:

  1. The main table (tbl) is encased in a sub select with an ORDER BY clause. This is necessary because when MySQL goes to do the @account variable testing the values need to already be ordered. If this didn't happen, you'd get incorrect sum values as well as account values.

  2. There is an 'extra' column aliased as _. You can ignore this column when using results, but the order of the @account check and change needs to be after the @sum check and change.

    Also with this, you could choose to reorder your columns if you didn't mind account being last. This is done by taking out the first account column since it duplicates with the last _ column and then obviously renamed the aliased _ to account.

Resources:

Community
  • 1
  • 1
Seaux
  • 3,459
  • 2
  • 28
  • 27
  • 4
    This should have been the accepted answer. I don't understand *why* it works, it's like magic lol. – Phrancis Sep 12 '14 at 15:50
17

No, it is one of the main lack of MySQL, compared to others DBMS like MSSQL, Oracle, PostgreSQL. I strongly doubt to see Window Functions in MySQL in any future, especially after Oracle acquisition of MySQL ...

Update 04/2018

MySQL 8.0 now supports window functions.

aleroot
  • 71,077
  • 30
  • 176
  • 213
  • 1
    MySQL also lacks recursive queries –  Aug 04 '12 at 08:45
  • Am I missing something or is this article describing analytic functions for mysql: http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/ ???? – Jens Schauder May 20 '15 at 05:46
  • 1
    Ah, now I understand, it is describing analytic functions and then describing how to achieve similar results without them in mysql ... – Jens Schauder May 20 '15 at 05:47
0

Although MySQL doesn't support analytic functions, MariaDB does. It's a drop-in replacement for MySQL and is created by the original developers of MySQL.

Kirby
  • 704
  • 4
  • 7
-5

There is a commercial product for SQL Server that provides in-database analytic functions and it can be connected to an oracle or MySQL database via 'linked servers'/odbc - here is an article describing it: http://westclintech.com/Blog/tabid/132/EntryId/88/Using-XLeratorDB-with-MySQL-and-other-RDBMS-s.aspx

This requires having a Windows o/s and SQL Server 2005 or later (which the Express version is free)

Joe Stampf
  • 1