All Forums Database
TdMan 91 posts Joined 01/07
18 Sep 2007

Can anyone explain me the use of sampleid fn?

smilever 45 posts Joined 10/07
15 Oct 2007

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 clear1. SELECT city, state, SAMPLEID FROM stores SAMPLE WITH REPLACEMENTWHEN 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.

jcsheu 1 post Joined 12/09
27 Apr 2012

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?

ulrich 816 posts Joined 09/09
27 Apr 2012

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)
   select row_number() over(order by rid) mod 1000 as sid,
              day_of _calendar
      select random(1,1000000000) as rid, 
      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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.