Random sample by subgroup

#1

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!

#2

<PMDORAIS (AT) gmail (DOT) com> a écrit dans le message de news: 1162234976.272547.259500 (AT) h48g20...oglegroups.com...

Quote:

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

#3

Great!

You saved my life!!

Merci Michel!!

Pierre

Michel Cadot a écrit :

Quote:

PMDORAIS (AT) gmail (DOT) com> a écrit dans le message de news: 1162234976.272547..259500 (AT) h48g2000cwc (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Random sample by subgroup

comp.databases.oracle.misc comp.databases.oracle.misc

Random sample by subgroup - 10-30-2006 , 01:02 PM

Re: Random sample by subgroup - 10-30-2006 , 02:09 PM

Re: Random sample by subgroup - 10-30-2006 , 02:50 PM