Re: Random sample by subgroup

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