Home » SQL & PL/SQL » SQL & PL/SQL » Random Sample
icon8.gif  Random Sample [message #231870] Wed, 18 April 2007 12:29 Go to next message
pmood@charter.net
Messages: 3
Registered: April 2007
Location: Missouri, USA
Junior Member
I need help with selecting a % of records from a file. I have a file by processor and instead of selecting 10% sample of records from the entire file, I need to select 10% of the records entered for each processor within the file. I need to display at least one record per processor (round up if % less than one)

Example
Processor Total Entries Random Sample (10%)
A 62 6 records randomly listed
B 29 2 records randomly listed
C 2 1 record randomly listed

I appreciate any suggestion you may have. Thanks
Re: Random Sample [message #231871 is a reply to message #231870] Wed, 18 April 2007 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT .... SAMPLE ....
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2105143
Re: Random Sample [message #231885 is a reply to message #231870] Wed, 18 April 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with
  data as (
    select tab.*,
           row_number() over (partition by processor order by dbms_random.value) rn,
           count(*) over (partition by processor) cnt
    from tab
  )
select ...
from data
where rn <= ceil(10/100 * cnt)
/

Regards
Michel

Re: Random Sample [message #231887 is a reply to message #231885] Wed, 18 April 2007 13:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sample is way more efficient
Re: Random Sample [message #231890 is a reply to message #231887] Wed, 18 April 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but, afaik, "sample" works at the physical level (blocks or rows) and not at the data level.
OP wants 10% for each processor (with a minimum of 1 row per processor) and not 10% of the whole table which can lead to different percentages per processor and even no row for some of them.

Regards
Michel
icon14.gif  Re: Random Sample [message #231897 is a reply to message #231890] Wed, 18 April 2007 15:02 Go to previous message
pmood@charter.net
Messages: 3
Registered: April 2007
Location: Missouri, USA
Junior Member
Thanks so much!!! This worked like a charm!!! Cool
Previous Topic: Strange output - Please Advise
Next Topic: File count UTL_FILE
Goto Forum:
  


Current Time: Sun Dec 11 04:38:07 CST 2016

Total time taken to generate the page: 0.14133 seconds