Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Random sample by subgroup

Re: Random sample by subgroup

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 30 Oct 2006 21:09:51 +0100
Message-ID: <45465c10$0$2835$426a74cc@news.free.fr>

<PMDORAIS_at_gmail.com> a écrit dans le message de news: 1162234976.272547.259500_at_h48g2000cwc.googlegroups.com...
| Hi,
|
| First, i'm a newbie with sql and i would like to do the following:
|
| I want to extract a random sample for each value of a certain field in
| a table;
|
|
| For example
|
|
| table
|
| clientid name group
| 1 xxx a
| 2 yyy b
| 3 ccc c
|
| etc...
|
| and i would like a sample of 20% of the records for each group (a, b,
| c).... how can i do that...??
|
| thanks!
|

Something like (GROUP and NAME are reserved words don't use them):

with
  data as (
    select clientid, nom, groupe,

           row_number () over (partition by groupe order by dbms_random.value) rn,
           count(*) over (partition by groupe) cnt
    from mytable
  )
select clientid, nom, groupe
from data
where rn <= 0.2 * cnt
/

Regards
Michel Cadot Received on Mon Oct 30 2006 - 14:09:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US