mardi 29 décembre 2015

select random rows based on elements of a column with sql in sqlite

I have a customer table with 2 columns, customerID and customerType, in which there are totally 1000 records. there are five values for customerType: 'A', 'B', 'C', 'D', 'E'. Each record has a customerType among these.

My question is how to randomly select 100 records, in which each customerType is evenly distributed,i.e. 20 rows for 'A', 20 rows for 'B'...

My current method is

select customerID from customer where customerType = 'A' order by random() limit 20 union
select customerID from customer where customerType = 'B' order by random() limit 20 union  
select customerID from customer where customerType = 'C' order by random() limit 20 union
select customerID from customer where customerType = 'D' order by random() limit 20 union
select customerID from customer where customerType = 'E' order by random() limit 20 union

union works,but is very verbose.Could you please show me if there is a more simple solution?

Besides, I also tried nested sql,

select customerID from customer  order by random() limit 20 where customerType in 
(select customerType from customer group by customerType)

but an syntax error occurs as where should not be after order by

I really need help from stackoverflow community. Could anyone do me a favor?

Aucun commentaire:

Enregistrer un commentaire