Home » SQL & PL/SQL » SQL & PL/SQL » eliminate fat tails
eliminate fat tails [message #205384] Fri, 24 November 2006 07:22 Go to next message
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 #205388 is a reply to message #205384] Fri, 24 November 2006 07:41 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

http://www.orafaq.com/forum/t/59966/91729/
Re: eliminate fat tails [message #205393 is a reply to message #205384] Fri, 24 November 2006 08:24 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
sorry, but there is no search result for fat tail in the forum. Has anybody an clue how to solve the issue?
Re: eliminate fat tails [message #205395 is a reply to message #205393] Fri, 24 November 2006 08:37 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

I really don't get your question, that's why I posted the link.
But someone else might understand what you are after.

Cheers
Re: eliminate fat tails [message #205401 is a reply to message #205395] Fri, 24 November 2006 09:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
skooman
Messages: 912
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! Wink]

[Updated on: Fri, 24 November 2006 10:14]

Report message to a moderator

Previous Topic: Ref cursor
Next Topic: DBMS ERROR LOG giving error,plz help
Goto Forum:
  


Current Time: Sat Dec 10 18:37:10 CST 2016

Total time taken to generate the page: 0.10794 seconds