eliminate fat tails [message #205384] |
Fri, 24 November 2006 07:22  |
steffeli
Messages: 112 Registered: July 2006
|
Senior Member |
|
|
Hi
How can I set values of a parameter as null if the values are in the lowest or highest 1% of all values?
Thanks
Stefan
|
|
|
|
|
|
Re: eliminate fat tails [message #205401 is a reply to message #205395] |
Fri, 24 November 2006 09:04   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you want to get the top 1% of values from a list, then Mr Analytic Function is your friend.
select num
from (select num
,percent_rank() over (order by num ) pct_rank
from (select level num from dual connect by level <= 1000))
where pct_rank >= 0.99
|
|
|
Re: eliminate fat tails [message #205402 is a reply to message #205384] |
Fri, 24 November 2006 09:14   |
steffeli
Messages: 112 Registered: July 2006
|
Senior Member |
|
|
ok, I try again:
I have a table with several thousand records and a parameter p1 with numbers. Now, I want to set p1 = null, if the value is very high or low. Meaning: if I rank p1, I want to set null all values which are in the highest 1% of all values or in the lowest 1% of all values.
Thanks for your help
Stefan
|
|
|
Re: eliminate fat tails [message #205408 is a reply to message #205402] |
Fri, 24 November 2006 10:05   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, to get the top or bottom 1%, then just modify the query like this:select num
from (select num
,percent_rank() over (order by num ) pct_rank
from (select level num from dual connect by level <= 1000))
where (pct_rank >= 0.99 or pct_Rank <=0.01)
All you need to do from there is include this select as part of an update.
UPDATE table
SET column = null
WHERE column in (<query posted above>)
Had you provided any Create Table scripts or sample data, I could have constructed you the finished product, but as it is, this is whay you get.
|
|
|
Re: eliminate fat tails [message #205409 is a reply to message #205402] |
Fri, 24 November 2006 10:12  |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
Then you could do something like:
SELECT num
FROM (SELECT num
,percent_rank() over(ORDER BY num) pct_rank
FROM your_table)
WHERE num =
(CASE WHEN pct_rank >= 0.99 OR pct_rank <= 0.01 THEN NULL ELSE '&p' END)
PS What is the functional requirement that leads to this question?? Because it comes down to: if you want to see something that's pretty rare, you don't get to see anything... Or did I misunderstand your question?
[Edit: JRowbottom interpreted the question quite differently, focused on the set=null, where I focused on the parameter. Interesting what the requirement actually is! ]
[Updated on: Fri, 24 November 2006 10:14] Report message to a moderator
|
|
|