Page 1 of 2
Get DENSE RANK in a grouped data
Posted: Thu Mar 08, 2007 12:13 pm
by Dsnew
I need to get the rank (basically dense rank) of the SALES_NUMBER based on the CAR_BRAND in a particular group based on STATE, CITY, ZIP
My source and target tables are oracle. can anybody point me in the right direction.
Source
--------
Code: Select all
STATE CITY ZIP CAR_BRAND SALES_NUMBER
---------- ---------- ---------- ---------- ------------
CA SACRAMENTO 95834 NISSAN 500
NY NY 10038 LEXUS 100
NY NY 10038 BMW 200
NY NY 10038 VOLVO 250
PA MILLMONT 17845 BMW 50
PA MILLMONT 17845 VOLVO 50
PA MILLMONT 17845 HONDA 150
TX SAN MARCOS 78666 VOLVO 50
TX SAN MARCOS 78666 BMW 100
Output required
------------------
Code: Select all
STATE CITY ZIP CAR_BRAND SALES_NUMBER RANK
---------- ---------- ---------- ---------- ------------ ------
CA SACRAMENTO 95834 NISSAN 500 1
NY NY 10038 LEXUS 100 1
NY NY 10038 BMW 200 2
NY NY 10038 VOLVO 250 3
PA MILLMONT 17845 BMW 50 1
PA MILLMONT 17845 VOLVO 50 1
PA MILLMONT 17845 HONDA 150 2
TX SAN MARCOS 78666 VOLVO 50 1
TX SAN MARCOS 78666 BMW 100 2
Appreciate your response
Thanks
Clara
Posted: Thu Mar 08, 2007 12:18 pm
by ganesh123
You need to give some more information about how you give RANKS ? Like based on what conditions ? The output you shown is confusing. We cannot guess the requirement.....
Re: Get DENSE RANK in a grouped data
Posted: Thu Mar 08, 2007 12:28 pm
by gateleys
1. Sort your input based on STATE, CITY, ZIP.
2. Use a stage variable to remember the concatenated string State:City:Zip of the previous row and compare it with the current row's concatenated value. You will use the RowProcCompareWithPreviousValue function available in the SDK to achieve this.
3. Use a derivation such that if current string is new, then RANK = 1, else RANK = RANK + 1.
gateleys
Posted: Thu Mar 08, 2007 12:57 pm
by DSguru2B
It looks like a goup change detection and giving a running number. Its been covered a lot. Search the forum for various ways. One has been described by gateleys.
Posted: Thu Mar 08, 2007 1:00 pm
by Dsnew
There is a little more to it -
If you check the 5th and 6th row, as the SALES_NUMBERS are same they should get the same rank "1" and the successive rank after the 2 consecutive rank 1's should get 2 not 3.
(Rank Vs Dense Rank)
Posted: Thu Mar 08, 2007 1:00 pm
by ray.wurlod
Does "dense rank" imply two levels of aggregation?
Posted: Thu Mar 08, 2007 1:05 pm
by ganesh123
Use a stage variable to remember the concatenated string State:City:Zip of the previous row and compare it with the current row's concatenated value. You will use the RowProcCompareWithPreviousValue function available in the SDK to achieve this.
In that case also concate SALES_NUMBER and compare with the previous row.
Posted: Thu Mar 08, 2007 1:19 pm
by Dsnew
That might not work in this case, ganesh123
Ray, Dense Rank(Oracle) would mean getting the 1,1,2 as opposed to Rank 1,1,3.
Did I get your question right?
Posted: Thu Mar 08, 2007 1:36 pm
by ray.wurlod
No, but my assumption was off the mark. In your case "dense rank" appears to mean rank without gaps left by ties.
Posted: Thu Mar 08, 2007 2:20 pm
by DSguru2B
Dsnew wrote:That might not work in this case, ganesh123
Well, if you add the fourth column in the grouping, I dont see why It wont work. You will get 1,1,2.
Posted: Thu Mar 08, 2007 2:27 pm
by gateleys
Dsnew wrote:That might not work in this case, ganesh123
Resource-consuming............yeah, it may be.
But, not work ..................... did you try it? And if yes, can you spit the results out?
gateleys
Posted: Thu Mar 08, 2007 2:31 pm
by Dsnew
DSguru2B wrote:Dsnew wrote:That might not work in this case, ganesh123
Well, if you add the fourth column in the grouping, I dont see why It wont work. You will get 1,1,2.
You will get 1,1,2 for rows 5, 6, 7 but all the others will get a rank 1
Posted: Thu Mar 08, 2007 2:42 pm
by gateleys
OK, what if you change the step 3 logic to -
3. Use a derivation such that if (current string is new and PreviousSalesNum = SalesNum), then RANK = 1, else if RANK = RANK + 1.
NOTE: You will need another stage variable to store previuos SalesNum
gateleys
Posted: Thu Mar 08, 2007 2:44 pm
by DSguru2B
Well you will have to do a column by column check then. Compare column 1, if they are same, check scond column, if same check third, if same, check the 5th column.
Now if the 5th column is same, refresh the rank to 1, if its different, increment the rank.
If any of the first three columns are different, start from 1 again.
Makes sense ???
Posted: Thu Mar 08, 2007 2:49 pm
by Dsnew
After sorting the rank should look like below.
I had not sorted in the desc order in my previous post. Sorry about that.
Code: Select all
STATE CITY ZIP CAR_BRAND SALES_NUMBER RANK
---------- ---------- ---------- ---------- ------------ -------
CA SACRAMENTO 95834 NISSAN 500 1
NY NY 10038 VOLVO 250 1
NY NY 10038 BMW 200 2
NY NY 10038 LEXUS 100 3
PA MILLMONT 17845 HONDA 150 1
PA MILLMONT 17845 BMW 50 2
PA MILLMONT 17845 VOLVO 50 2
TX SAN MARCOS 78666 BMW 100 1
TX SAN MARCOS 78666 VOLVO 50 2