Want to show your appreciation? Please to my charity.

Sunday, April 24, 2016

Analytical Function in MySQL - ROW_NUMBER, RANK, DENSE_RANK

It was unfortunate that MySQL doesn't support analytical function. The problem got my attention when we were considering MySQL as staging and/or operational database for a Data Warehouse solution. A bit research reveals that it was indeed not very difficult to emulate some of them. Here is the workaround to get the the result of ROW_NUMBER, RANK and DENSE_RANK in MySQL.

Let's take an example to emulate the standard SQL99 analytical query below:

  ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS row_number,
  RANK() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS dense_rank,
FROM table1 t

The MySQL workaround can be written with a mix of variables and sub queries:

  @row_num:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2, @row_num+1, 1) AS row_number,
  @dense:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2, IF(@prev_col3=col3, @dense, @dense+1), 1) AS dense_rank,
  @rank:=IF(@prev_col1=t.col1 AND @prev_col2=t.col2 AND @prev_col3=col3, @rank, @row_num) AS rank,
FROM (SELECT * FROM table1 ORDER BY col1, col2, col3 DESC) t,
     (SELECT @row_num:=1, @dense:=1, @rank:=1, @prev_col1:=NULL, @prev_col2:=NULL, @prev_col3:=NULL) var

This solution requires no self join, no temp table, still single pass, and generic enough to adapt it to any use case. But I admit that it is quite convoluted. Very sad that MySQL doesn't include such basic functions that are well achievable.


Mahendra V.M said...


Rick James said...

MariaDB just announced "Window Functions" in MariaDB 10.2 -- https://mariadb.com/kb/en/mariadb/window-functions/ . These may eventually obviate your nice, concise, SQL.

-- Rick James

Dan said...

Thank you very much! The ranking solutions I found on Stackoverflow didnt quite work the way I wanted to, but your workaround was the trick. Thanks for sharing with the community!

ToolmakerSteve said...

@prev_col1, 2, 3 are never set to a new value, so all row_nums are "1". After "t.*", need ", @prev_col1:=t.col1, @prev_col2:=t.col2, @prev_col3:=t.col3".

Unknown said...
This comment has been removed by the author.
jberryman said...

I think this is relying on undefined behavior, e.g. this variation gives me incorrect results (row_number is not ordered with respect to the `id` column):

SELECT @row_num:=@row_num+1 AS row_number, t.id FROM (SELECT * FROM table1 WHERE col = 264 ORDER BY id) t, (SELECT @row_num:=0) var;

Anonymous said...

@jberryman re "(SELECT * FROM table1 WHERE col = 264 ORDER BY id) t, (SELECT @row_num:=0) var;"

Your Order BY is in the wrong place. It should be after the implicit join:
(SELECT * FROM table1 WHERE col = 264) t, (SELECT @row_num:=0) var

and you might find the column order matters (remember you're operating in a grey area of MySQL functionality) so it's good not to stray to far from the original example
"SELECT t.id, @row_num:=@row_num+1 AS row_number"

Unknown said...

please give some example table

Anonymous said...

It is indeed a solution. It doesnot work when ranking is in the span of an interval.

My data is
user id timestamp.

if a user comes 10 times to my website i will have user id and 10 timestamps in 10 rows.
I need to group all the user events in one label where the time of events was within an hour period..
the query in mssql is

Could we have a corresponding query in mysql.

Please help i am stuck for hours here.

Anonymous said...

Nice blog..! I really loved reading through this article. Thanks for sharing such
a amazing post with us and keep blogging...Well written article Thank You for Sharing with Us pmp training in velachery | pmp training class in chennai | pmp training fee | project management training certification | project management training in chennai

Sathya said...

Nice post. It is really interesting. Thanks for sharing the post!
Fridge Online Shopping | Refrigerator Online Shopping
Refrigerator Price Online | Online Fridge Price
Washing Machine Sale | Washing Machine Online Offers
Mobile Phone Offers Online | Buy Mobile Online
Sathya Online Shopping

beobeoyeu068@gmail.com said...

Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng sau:

Lều xông hơi tại nhà

Lều xông hơi hồng ngoại

Cảm ơn các bạn!

beobeoyeu068@gmail.com said...

Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng sau:

Lợi ích khi ngâm chân với tinh dầu oải hương

Chậu ngâm chân giá rẻ

Những nguyên tắc khi dùng bồn mát xa chân

Cảm ơn các bạn!

phannhathoang144@gmail.com said...

Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng

Tác dụng thần kỳ của giảo cổ lam 7 lá

Giảo cổ lam giá rẻ tại Hà Nội

Bao nhiêu tiền 1 kg giảo cổ lam

phannhathoang144@gmail.com said...

Bài viết rất hay: Chúng tôi chuyên cung cấp các sản phẩm chất lượng

Lều xông hơi giá rẻ tại hà nội

Những ai có thể dùng lều xông hơi tại nhà

Tại sao nên xông hơi với tinh dầu sả

Avijit said...

I regularly wouldn't be so connected by any articles relating to this subject, however yours caught my eye. It resembled an extraordinary sweet shouting out to me to eat it. This is acceptable substance.

SEO services in kolkata
Best SEO services in kolkata
SEO company in kolkata
Best SEO company in kolkata
Top SEO company in kolkata
Top SEO services in kolkata
SEO services in India
SEO copmany in India

Avijit said...

I have actually never perused such overwhelmingly great substance like this. I concur with your focuses and your thoughts. This information is extremely incredible. Much obliged.

Denial management software
Denials management software
Hospital denial management software
Self Pay Medicaid Insurance Discovery
Uninsured Medicaid Insurance Discovery
Medical billing Denial Management Software
Self Pay to Medicaid
Charity Care Software
Patient Payment Estimator
Underpayment Analyzer
Claim Status

Pankaj Singh said...

Thanks for this amazing blog, visit Ogen Infosystem for creative web design and development services at an affordable price.
Best Website Designing Company in Delhi

Sunder said...

Great Article & Thanks for sharing with oflox, Digital Marketing Company In Dehradun

Unknown said...

Water bodies are the main source of transportation for international freight forwarding. Due to this, sea freight company in Delhi,
Freight Forwarder in Vietnam
Shipping Company In India

Unknown said...

visit here
iso certification in delhi
iso certification in noida
iso certification in gurgaon
iso certification in faridabad

Post a Comment