Does mysql have the equivalent of Oracle's "analytic functions"?

Question

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?

Answer 1

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:

• http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
• https://stackoverflow.com/a/2563940/263175