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: <PMDORAIS_at_gmail.com>
Date: 30 Oct 2006 12:50:55 -0800
Message-ID: <1162241455.047544.219720@k70g2000cwa.googlegroups.com>


Great!

You saved my life!!

Merci Michel!!

Pierre

Michel Cadot a écrit :

> <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:50:55 CST

Original text of this message

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