2
$\begingroup$

I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone knows how to do that? For example using an sql query

$\endgroup$
3
$\begingroup$

You did not provide SQL product so I will give one example. You need a random function from your DBMS. For example oracle provides dbms_random.value, SQL server NEWID or RAND(). MySql provides RAND().

Assume that you have a table named DATA which holds your 500 records.

WITH TEMP
(
  SELECT  ROW_NUMBER() AS ROW_ID , NEWID() as RANDOM_VALUE,D.*
        FROM   DATA D
       ORDER BY RANDOM_VALUE
)

SELECT 'Training',T.* FROM TEMP T
WHERE ROW_ID =< 375
UNION
SELECT 'Test',T.* FROM TEMP T
WHERE ROW_ID > 375

Trick is to Order by Random Function. 375 = 500 * 75/100. that is %75 of data is training and %25 is testing.

This example for SQL Server but it will work with Oracle and Other vendors if you change random function.

$\endgroup$
  • $\begingroup$ Thank you very much. You helped me very much! Voting as best answer $\endgroup$ – user21849 May 7 '13 at 14:07
1
$\begingroup$

A sequential algorithm using conditional probabilities can also be used. To demonstrate a simple case, suppose you want to sample 1 item out of 4. Generate a uniform[0,1] random number $U_1$. If $U_1 < {1 \over 4}$, select the first element. If not, generate $U_2$. If $U_2 < {1 \over 3}$, select the second element. If not, generate $U_3$. If $U_3 < {1 \over 2}$, select the third element. If not, select the last element. The probability of selecting the first element is $1 \over 4$. The probability of selecting the 2nd element is $({3 \over 4})({1 \over 3}) = {1 \over 4}$. And so on for the 3rd and 4th elements. Google "Faster Methods for Random Sampling" to find a paper by JS Vitter that presents several sequential methods.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy