sampleid

Database
Enthusiast

sampleid

Can anyone explain me the use of sampleid fn?
3 REPLIES
Enthusiast

Re: sampleid

Hi Sakthi,

Sampling is done based on a given condition and for each set of sample a unique sampleid is given.
I hope the following example wil make u clear

1. SELECT city, state, SAMPLEID FROM stores SAMPLE WITH REPLACEMENT
WHEN state = 'WI' THEN 4 ELSE 3 END ORDER BY 3.

This displays 7 rows.The first 4 rows have the sampleid 1 and the remaining 3 rows have the sampleid 2.For the first 4 rows the state is 'WI' and for the remaining 3 rows the state is other than 'WI'

Note:Here two distinct sampleid's (1,2)have been generated.

This is one of the examples.
If this helps you,I can give you different kind of examples.

N/A

Re: sampleid

I am doing some bootstrap simulation and was trying to , say, run 1,000 times with 200 samples with replacement.  The following query create 3 sample sets with 200 samples each

SELECT SAMPLEID,balance_amount

FROM Acct_table

SAMPLE WITH replacement 200,200,200

;

It is not practical to write "200" a thousand times.  Is there a way to code more effectively?

Senior Supporter

Re: sampleid

This might work for you

1. sample with replacement 200000 -> all in one

2. generate a random number for each row

3. calculate row_number based on random number sort

4. calculate row_number mod 1000 as sampleid

select sid,AVG(day_of _calendar)
from
(
select row_number() over(order by rid) mod 1000 as sid,
day_of _calendar
from
(
select random(1,1000000000) as rid,
c.day_of_calendar
from sys_calendar.calendar c
SAMPLE WITH replacement 200000
) as t
) as t2
group by 1
order by 1

The SQL is just calculating the AVG per sampleid to check that the result is resonable.

At least this can simply be changed to bigger sample sizes or more sample groups