Home » SQL & PL/SQL » SQL & PL/SQL » Analytic function ranking (11.1.0.6.0)
Analytic function ranking [message #410427] Fri, 26 June 2009 22:13 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi,

I have a table,temp_rec,structure is provided below
CREATE TABLE TEMP_REC
(
  PARENTID  VARCHAR2(60 BYTE)                   NOT NULL,
  CHILDID   VARCHAR2(60 BYTE)                   NOT NULL,
  VAL       VARCHAR2(762 BYTE)                  NOT NULL
)


Some data into the table

Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '78056359', 'Z1');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '64850981', 'F8');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62103647', 'F8');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62102722', 'E3');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62102567', 'F8');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62102433', 'E3');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62102275', 'Z1');
Insert into TEMP_REC
   (PARENTID, CHILDID, VAL)
 Values
   ('1', '62102115', 'E3');


When I do a select on this table, I get the data like this
PARENTID	CHILDID	VAL

1	78056359	Z1
1	64850981	F8
1	62103647	F8
1	62102722	E3
1	62102567	F8
1	62102433	E3
1	62102275	Z1
1	62102115	E3


Now, I want to rank the result in such a way that Z1 gets 1st Rank, the two F8 gets second rank, E3 gets third rank, the next "F8" gets 4th rank and so on. [Note that it is based on the descending order of childid]. The result should be;

PARENTID	CHILDID	VAL  RANK

1	78056359	Z1    1
1	64850981	F8    2
1	62103647	F8    2
1	62102722	E3    3
1	62102567	F8    4
1	62102433	E3    5
1	62102275	Z1    6
1	62102115	E3    7



Can this be done with analytic functions? If so, can somebody post an example?

//Srini
Re: Analytic function ranking [message #410435 is a reply to message #410427] Sat, 27 June 2009 00:35 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Why 2 F8 should get second rank when child id is different for them?

You should read about dense_rank or rank function in oracle documentation to achieve this but just note that 2 f8 will not get same rank since the child id on which you intend to rank them is different

[Updated on: Sat, 27 June 2009 00:36]

Report message to a moderator

Re: Analytic function ranking [message #410436 is a reply to message #410427] Sat, 27 June 2009 00:45 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi

That is my requirement.

I've read about rank and dense rank and posted this as I felt it would not be possible.
Re: Analytic function ranking [message #410440 is a reply to message #410436] Sat, 27 June 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why DENSE_RANK does not fit your requirement?

Regards
Michel

[Updated on: Sat, 27 June 2009 01:08]

Report message to a moderator

Re: Analytic function ranking [message #410444 is a reply to message #410440] Sat, 27 June 2009 01:25 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi

If you look at the expected result, can you advise how this is possible with dense rank or rank function?

I've provided all the test data
Re: Analytic function ranking [message #410448 is a reply to message #410444] Sat, 27 June 2009 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post what you tried with dense_rank and explain us why it does not fit the requirement: "[rank] is based on the descending order of childid" maybe we can see what does not work.

Regards
Michel
Re: Analytic function ranking [message #410450 is a reply to message #410448] Sat, 27 June 2009 02:07 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Yes, this is what I tried with dense rank.

select parentid,childid,val,dense_rank()over (partition by val order by childid desc)
from temp_rec
order by childid desc


the result is

1	78056359	Z1	1
1	64850981	F8	1
1	62103647	F8	2
1	62102722	E3	1
1	62102567	F8	3
1	62102433	E3	2
1	62102275	Z1	2
1	62102115	E3	3



My expected result is what that was posted earlier. Also, note that, for the row "F8" I need rank as "2" and for the second occurrence of F8, [child id 62102567] the rank should be "4". This is because, a value of E3 [child id 62102722] comes between two sets of "F8".

Precisely, sort all the childs in descending order and apply the rank. Any distinct value of VAL encountered while reading the rows should result in the rank value being incremented by 1.

[Updated on: Sat, 27 June 2009 02:11]

Report message to a moderator

Re: Analytic function ranking [message #410453 is a reply to message #410450] Sat, 27 June 2009 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you partition by val?

Regards
Michel
Re: Analytic function ranking [message #410454 is a reply to message #410453] Sat, 27 June 2009 02:46 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
That is how I approached the problem. May be, if you can guide me in the correct direction, it would be much appreciated.
Re: Analytic function ranking [message #410456 is a reply to message #410454] Sat, 27 June 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already gave you a clue: you only have one requirement (at least only one you mentioned): "[rank] is based on the descending order of childid".

Regards
Michel
Re: Analytic function ranking [message #410458 is a reply to message #410456] Sat, 27 June 2009 02:51 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Smile

But, the catch is in my expected output.I'm not a power analytic user.

May be, if you can post the approach it will also benefit somebody reading this thread.
Re: Analytic function ranking [message #410460 is a reply to message #410458] Sat, 27 June 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can gave you mor than what I said but the query itself.
You just have a small change to make in your query. Just carefully read our last posts.

Regards
Michel
Re: Analytic function ranking [message #410462 is a reply to message #410460] Sat, 27 June 2009 03:04 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Did you see the expected output that I posted initially?
Re: Analytic function ranking [message #410480 is a reply to message #410462] Sat, 27 June 2009 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and it does not fit the requirement you also posted.

Regards
Michel

[Updated on: Sat, 27 June 2009 05:07]

Report message to a moderator

Re: Analytic function ranking [message #410481 is a reply to message #410480] Sat, 27 June 2009 05:09 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Thanks Michel.

I tried one more variation

select parentid,childid,val,rank() over (order by childid desc)
from temp_rec
order by childid desc


wasn't close enough still Sad
Re: Analytic function ranking [message #410482 is a reply to message #410481] Sat, 27 June 2009 05:10 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Smile

then, I'm skeptic on your clues.

The requirement is to get the desired output posted earlier with the result sorted based on the descending order of the child id.
Re: Analytic function ranking [message #410483 is a reply to message #410482] Sat, 27 June 2009 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The requirement is to get the desired output posted earlier with the result sorted based on the descending order of the child id.

Wrong! A requirement is not based on an example hut on a sentence. An example can clarify it but it is not the requirement.
If you can't explain with words the requirements you can't write a SQL to do it.
The only answer to your requirement as you express it is:
select '
1	78056359	Z1    1
1	64850981	F8    2
1	62103647	F8    2
1	62102722	E3    3
1	62102567	F8    4
1	62102433	E3    5
1	62102275	Z1    6
1	62102115	E3    7
' from dual;

There it displays the output you want.

Regards
Michel

[Updated on: Sat, 27 June 2009 05:15]

Report message to a moderator

Re: Analytic function ranking [message #410484 is a reply to message #410483] Sat, 27 June 2009 05:17 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Your answer is not smart!

Is there a possibility to do it with analytic functions? that is the question in the first place.
Re: Analytic function ranking [message #410485 is a reply to message #410484] Sat, 27 June 2009 05:20 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
And take some time to read it carefully here..

Quote:

Now, I want to rank the result in such a way that Z1 gets 1st Rank, the two F8 gets second rank, E3 gets third rank, the next "F8" gets 4th rank and so on. [Note that it is based on the descending order of childid]. The result should be;



especially the line

"the next "F8" gets 4th rank"
Re: Analytic function ranking [message #410488 is a reply to message #410485] Sat, 27 June 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a requirement.
Requirements/specifications do not require an example. Requiremennts are rules that must be applied to any data, any example.
Now someone can give a query for THIS example and you will say but if I add this row the result should be XXX and your query does not work, someone else will give another query that fit the 2 examples and you will say but if I change this row to that the result shoould YYY and your query does not work and then...
Explain in details the rules, then we can convert them to SQL.

My answer EXACTLY fit your requirement for your example, so it is smart accordingly to the question. In what my answer is not valid?

Regards
Michel
Re: Analytic function ranking [message #410490 is a reply to message #410488] Sat, 27 June 2009 06:53 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Requirement is well defined

1.sort all child ids under a parent in descending order
2.apply the ranking in such a way on the sorted output so that identical and consequitive values (as read in val column) get same rank (under same parent)

Your answer is not smart.it is specific to the data and is not generic.


Re: Analytic function ranking [message #410493 is a reply to message #410490] Sat, 27 June 2009 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is specific to the data and is not generic.

As well as your first requirements.

Now the requirement is well defined.

Regards
Michel
Re: Analytic function ranking [message #410494 is a reply to message #410493] Sat, 27 June 2009 07:37 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Not really.

It was clear and is made more clearer now. It all depends on the interpretation capabilities. Somebody gets it with statements, some with examples & some with both.
Re: Analytic function ranking [message #410495 is a reply to message #410494] Sat, 27 June 2009 08:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I tried to read all the posts, but I couldn't.
I don't care why you need it.
There might be a better solution. I got this in my mind.
But this may help you.
SELECT parentid, 
       childid, 
       val, 
       Sum(Decode(x,val,0, 
                    1)) 
         OVER(PARTITION BY parentid ORDER BY childid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) abcd 
FROM   (SELECT parentid, 
               childid, 
               val, 
               Lag(val) 
                 OVER(PARTITION BY parentid ORDER BY childid DESC) x 
        FROM   temp_rec);
By
Vamsi

[Updated on: Sat, 27 June 2009 08:19]

Report message to a moderator

Re: Analytic function ranking [message #410496 is a reply to message #410494] Sat, 27 June 2009 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It was clear and is made more clearer now. It all depends on the interpretation capabilities

Once again it's wrong. Requirements are not a matter of interpretation, it is the opposite, it must be so clear and precise that it avoids any interpretation.

Now if you read your requirements you do not want to rank rows but number (or rank) group of rows and these groups are defined by your requirements:
A group is defined by any consecutive rows with the same parentid, when ordering them by childid, that have the same val.
The rank if the group is then defined by the rank of the min/max/avg/... childid of the group in descending order.

Regards
Michel

[Updated on: Sat, 27 June 2009 09:41]

Report message to a moderator

Re: Analytic function ranking [message #410516 is a reply to message #410496] Sat, 27 June 2009 17:55 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
The interpretation mentioned earlier is the "clear requirement" understanding potentials.
Re: Analytic function ranking [message #410517 is a reply to message #410516] Sat, 27 June 2009 18:34 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Vamsi,

Great piece of SQL..

Thanks a lot.
Re: Analytic function ranking [message #410518 is a reply to message #410517] Sat, 27 June 2009 19:06 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Vamsi,

A question.

What is the significance of "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" in your SQL?

I get the same output even when it is removed.
Re: Analytic function ranking [message #410522 is a reply to message #410518] Sun, 28 June 2009 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Analytic Functions
You get the same because it is the default clause.

Three other ways to get it:
SQL> with
  2    flagged as (
  3      select parentid, childid, val,
  4             case 
  5               when nvl(lag(val) over (partition by parentid order by childid desc),'#') != val
  6                 then row_number() over (partition by parentid order by childid desc)
  7             end flag
  8      from temp_rec
  9    ),
 10    grouped as (
 11      select parentid, childid, val, 
 12             max(flag) over (partition by parentid order by childid desc) grp,
 13             nvl(max(flag) over (partition by parentid order by childid desc
 14               rows between unbounded preceding and 1 preceding),0) prev_grp
 15      from flagged
 16    )
 17  select parentid, childid, val, 
 18         grp 
 19         - nvl(sum(decode(prev_grp,grp,1,0)) 
 20                 over (partition by parentid order by grp
 21                   range between unbounded preceding and 1 preceding)
 22              ,0) rk
 23  from grouped
 24  /
PARENTID CHILDID  VAL         RK
-------- -------- --- ----------
1        78056359 Z1           1
1        64850981 F8           2
1        62103647 F8           2
1        62102722 E3           3
1        62102567 F8           4
1        62102433 E3           5
1        62102275 Z1           6
1        62102115 E3           7

8 rows selected.

SQL> with
  2    flagged as (
  3      select parentid, childid, val,
  4             case 
  5               when nvl(lag(val) over (partition by parentid order by childid desc),'#') != val
  6                 then row_number() over (partition by parentid order by childid desc)
  7             end flag
  8      from temp_rec
  9    ),
 10    grouped as (
 11      select parentid, childid, val, 
 12             max(flag) over (partition by parentid order by childid desc) grp,
 13             decode(max(flag) over (partition by parentid order by childid desc
 14                       rows between unbounded preceding and 1 preceding),
 15                    max(flag) over (partition by parentid order by childid desc),
 16                    1, 0) flag2
 17      from flagged
 18    )
 19  select parentid, childid, val, 
 20         grp 
 21         - nvl(sum(flag2) 
 22                 over (partition by parentid order by grp
 23                   range between unbounded preceding and 1 preceding) 
 24              ,0) rk
 25  from grouped
 26  /
PARENTID CHILDID  VAL         RK
-------- -------- --- ----------
1        78056359 Z1           1
1        64850981 F8           2
1        62103647 F8           2
1        62102722 E3           3
1        62102567 F8           4
1        62102433 E3           5
1        62102275 Z1           6
1        62102115 E3           7

8 rows selected.

SQL> with
  2    flagged as (
  3      select parentid, childid, val,
  4             case 
  5               when nvl(lag(val) over (partition by parentid order by childid desc),'#') != val
  6                 then 1
  7             end flag
  8      from temp_rec
  9    )
 10  select parentid, childid, val, 
 11         count(flag) over (partition by parentid order by childid desc) rk
 12  from flagged
 13  /
PARENTID CHILDID  VAL         RK
-------- -------- --- ----------
1        78056359 Z1           1
1        64850981 F8           2
1        62103647 F8           2
1        62102722 E3           3
1        62102567 F8           4
1        62102433 E3           5
1        62102275 Z1           6
1        62102115 E3           7

8 rows selected.

The last one is my preferred one: it flags each start of group (as the other ones) then count the number of flags.

Regards
Michel

[Updated on: Sun, 28 June 2009 00:26]

Report message to a moderator

Re: Analytic function ranking [message #410528 is a reply to message #410427] Sun, 28 June 2009 01:53 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Michel,

Yes, the third one is equally good. Thanks for the inputs.

Re: Analytic function ranking [message #410529 is a reply to message #410522] Sun, 28 June 2009 01:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I tried similar to the third one.
I used decode and got error. I couldn't realize that case can be used. Sad

By
Vamsi
Re: Analytic function ranking [message #410530 is a reply to message #410529] Sun, 28 June 2009 02:12 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
This is a decode version of Michel's solution.

with
    flagged as (
        select parentid, childid, val,
               Decode(nvl(lag(val) over (partition by parentid order by childid desc),'#'),val,NULL,1)
                    as flag
        from temp_rec
      )
   select parentid, childid, val, 
          count(flag) over (partition by parentid order by childid desc) rk
   from flagged


Re: Analytic function ranking [message #410531 is a reply to message #410530] Sun, 28 June 2009 02:24 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Good!
I didn't remember what I've written then. May be I was wrong in some syntax.

By
Vamsi
Previous Topic: ORA-12546 : TNS:permission denied (merged 2)
Next Topic: value expand to extra 2 digit (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 08:30:51 CST 2016

Total time taken to generate the page: 0.20850 seconds