Does mysql have the equivalent of Oracle’s “analytic functions”?

by

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:

More Related Contents:

  1. How to select the nth row in a SQL database table?
  2. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  3. Which DB technology is sued to solve the following search issue? [closed]
  4. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  5. 1052: Column ‘id’ in field list is ambiguous
  6. How to do a batch insert in MySQL
  7. Count the occurrences of DISTINCT values
  8. difference between primary key and unique key
  9. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  10. SQL: Repeat a result row multiple times, and number the rows
  11. How do I add a custom CHECK constraint on a MySQL table?
  12. Cast int to varchar
  13. MySQL equivalent of DECODE function in Oracle
  14. Import SQL file into mysql
  15. How to change the default port of mysql from 3306 to 3360
  16. How to skip certain database tables with mysqldump?
  17. “where 1=1” statement [duplicate]
  18. How to set a MySQL row to READ-ONLY?
  19. MySQL: LAST_INSERT_ID() returns 0
  20. Using reserved words in column names
  21. Calculating a Moving Average MySQL?
  22. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  23. How to insert a file in MySQL database?
  24. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  25. What does SQL Select symbol || mean?
  26. Strings as Primary Keys in MYSQL Database [closed]
  27. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  28. How to speed up sql queries ? Indexes?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment