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:

SELECT 
  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,
  t.* 
FROM table1 t

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

SELECT
  @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,
  t.*
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.

27 comments:

Mahendra V.M said...

Super

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
ORDER BY id;

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
SELECT *,DENSE_RANK() OVER(PARTITION BY user1 ORDER BY DATEPART(HH,timestamp)) FROM #temp.

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,
visit
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

Gaurav Sharma said...

Good blog informatve for readers such a nice content keep posting thanks for sharing
Best Hotel in Mussoorie
business news in hindi
CHOPTA – “Mini Switzerland of Uttrakhand"
TOP 20 HILL STATIONS OF INDIA

CrownQQ Official said...

CrownQQ Agen DominoQQ BandarQ dan Domino99 Online Terbesar

Yuk Buruan ikutan bermain di website CrownQQ
Sekarang CROWNQQ Memiliki Game terbaru Dan Ternama loh...

10 permainan :
=> Poker
=> Bandar Poker
=> Domino99
=> BandarQ
=> AduQ
=> Sakong
=> Capsa Susun
=> Bandar 66
=> Perang Baccarat
=> Perang Dadu

Keunggulan bermain di CrownQQ :
=> Bonus Refferal 20%
=> Bonus Turn Over 0,5%
=> Minimal Depo 20.000
=> Minimal WD 20.000
=> 100% Member Asli
=> Pelayanan DP & WD 24 jam
=> Livechat Kami 24 Jam Online
=> Bisa Dimainkan Di Hp Android
=> Di Layani Dengan 5 Bank Terbaik
=> 1 User ID 10 Permainan Menarik
=> Menyediakan deposit Via Pulsa

Link Resmi CrownQQ:
- maincrownqq.com
- maincrownqq.net
- maincrownqq.org

Info Lebih lanjut Kunjungi :
Website : DominoQQ Online
Daftar CrownQQ : Poker Online
Info CrownQQ : Kontakk
Linktree : Agen Poker Online

WHATSAPP : +6287771354805
Line : CS_CROWNQQ
Facebook : CrownQQ Official
Kemenangan CrownQQ : Agen BandarQ

Rajabandarq said...

Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq

Rajabandarq said...

Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq
Agen bandarq

Kristen Ashley said...

Kristen Ashleey is an Author, blogger, and one of the exceptional writers specialized in health and medical sector. She is one of the founders of Online Health Mantra – the first ever healthcare platform ensuring health services reaching at every individual level.

Packers and Movers said...

Packers and Movers in Haridwar

Packers and Movers in Rishikesh

Packers and Movers in Roorkee

Packers and Movers in Rudraprayag

Packers and Movers in Pauri

Packers and Movers in Almora

Packers and Movers in Kashipur

Post a Comment