Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2007
11:30 AM

09-18-2007
11:30 AM

Can anyone explain me the use of sampleid fn?

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-15-2007
02:43 AM

10-15-2007
02:43 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-27-2012
11:02 AM

04-27-2012
11:02 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-27-2012
11:58 AM

04-27-2012
11:58 AM

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