Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 using an sql query? ps. I use mysql.

In addition I find this ... select ... from ... where ... ORDER BY rand() LIMIT 150 but this gives me only the first dataset

Thanks in advance

share|improve this question

2 Answers 2

Easiest solution would be to store the datasets into tables. After you use your query to get the first dataset:

 INSERT INTO dataset1 SELECT * FROM foo ORDER BY rand() LIMIT 125

then do:

 INSERT INTO dataset2 SELECT foo.* FROM foo 
   LEFT JOIN dataset1 d1 ON foo.key=d1.key WHERE d1.key IS NULL
share|improve this answer
    
Thank you very much. Can I also do this, but instead of creating new tables, to store the results of each dataset to a variable? (It is because I will do this at many different datasets, and I prefer not to create 2 tables for each of these datasets). Thanks a lot for your time, and info. –  Andreas May 1 '13 at 18:16
    
@Andreas, you can use tables and store only the IDs, not all the data. –  ypercube May 18 '13 at 18:40

Your queries are going somewhere. Therefore you can get all the data in one go and tag them differently, e.g. (SQL Fiddle)

select if(rownum>=@rn*0.75,1,2) part, q3.*
from (
  select @rn:=@rn+1 rownum, q2.*
  from (select @rn:=0) q1,
       (select * from random500 order by rand()) q2
) q3
order by part, something;

If the data never leaves the server, you can create a temporary table with the additional part column, e.g.

create temporary table tmp as
select if(rownum>=@rn*0.75,1,2) part, q3.*
from (
  select @rn:=@rn+1 rownum, q2.*
  from (select @rn:=0) q1,
       (select * from random500 order by rand()) q2
) q3
order by part, something;

And use it within the same session, e.g. select ... from tmp where part = 1

Otherwise, you can create it as a normal table and dispose of it later.

share|improve this answer
    
Thank you very much for your help :) –  Andreas May 7 '13 at 14:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.