Showing posts with label random sampling in database. Show all posts
Showing posts with label random sampling in database. Show all posts

Wednesday, August 29, 2012

Random Sampling using SQL

The following SQL scripts create a view by taking 30% random samples from a table.

Step 1 . Create random numbers within range of 0 and 99 (100 bins).
create table tbl_app_id_w_rnd as select app_id, 
mod(abs(dbms_random.random()),100) rnd from CELL_PHONE_SERVICE_APPS;

Step 2. Create a view that contains 30% of the records.
create view v_data_testing
as select a.* from CELL_PHONE_SERVICE_APPS a,
tbl_app_id_w_rnd b where a.app_id=b.app_id and  rnd < 30;

Please see a newer post. dbms_random.value is a better function than dbms_random.random.