StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect N random records per group
text
Body
copied!<p>Hallo and good sunday to everybody. I need to select N random records from each group.</p> <p>Starting from the query of Quassnoi</p> <p><a href="http://explainextended.com/2009/03/01/selecting-random-rows/" rel="nofollow">http://explainextended.com/2009/03/01/selecting-random-rows/</a></p> <p>to select X random record I wrote this store procedure</p> <pre><code>delimiter // drop procedure if exists casualiPerGruppo // create procedure casualiPerGruppo(in tabella varchar(50),in campo varchar(50),in numPerGruppo int) comment 'Selezione di N record casuali per gruppo' begin declare elenco_campi varchar(255); declare valore int; declare finite int default 0; declare query1 varchar(250); declare query2 varchar(250); declare query3 varchar(250); declare query4 varchar(250); declare cur_gruppi cursor for select gruppo from tmp_view; declare continue handler for not found set finite = 1; drop table if exists tmp_casuali; set @query1 = concat('create temporary table tmp_casuali like ', tabella); prepare stmt from @query1; execute stmt; deallocate prepare stmt; set @query2 = concat('create or replace view tmp_view as select ',campo,' as gruppo from ',tabella,' group by ',campo); prepare stmt from @query2; execute stmt; deallocate prepare stmt; open cur_gruppi; mio_loop:loop fetch cur_gruppi into valore; if finite = 1 then leave mio_loop; end if; set @query3 = concat("select group_concat(column_name) into @elenco_campi from information_schema.columns where table_name = '",tabella,"' and table_schema = database()"); prepare stmt from @query3; execute stmt; deallocate prepare stmt; set @query4 = concat('insert into tmp_casuali select ', @elenco_campi,' from ( select @cnt := count(*) + 1, @lim :=', numPerGruppo, ' from ',tabella, ' where ',campo,' = ', valore, ' ) vars straight_join ( select r.*, @lim := @lim - 1 from ', tabella, ' r where (@cnt := @cnt - 1) and rand() < @lim / @cnt and ', campo, ' = ', valore , ') i'); prepare stmt from @query4; execute stmt; deallocate prepare stmt; end loop; close cur_gruppi; select * from tmp_casuali; end // delimiter ; </code></pre> <p>that I recall in this way to give you an idea:</p> <pre><code>create table prova ( id int not null auto_increment primary key, id_gruppo int, altro varchar(10) ) engine = myisam; insert into prova (id_gruppo,altro) values (1,'aaa'),(2,'bbb'),(3,'ccc'),(1,'ddd'),(1,'eee'),(2,'fff'), (2,'ggg'),(2,'hhh'),(3,'iii'),(3,'jjj'),(3,'kkk'),(1,'lll'),(4,'mmm'); call casualiPerGruppo('prova','id_gruppo',2); </code></pre> <p>My problem is that Quassnoi query, even though is very performant, it takes even 1 second on a large recorset. So if I apply it within my sp several times, the total time increases a lot.</p> <p>Can you suggest me a better way to solve my problem? Thanks in advance</p> <p>EDIT. </p> <pre><code>create table `prova` ( `id` int(11) not null auto_increment, `id_gruppo` int(11) default null, `prog` int(11) default null, primary key (`id`) ) engine=myisam charset=latin1; delimiter // drop procedure if exists inserisci // create procedure inserisci(in quanti int) begin declare i int default 0; while i < quanti do insert into prova (id_gruppo,prog) values ( (floor(1 + (rand() * 100))), (floor(1 + (rand() * 30))) ); set i = i + 1; end while; end // delimiter ; call inserisci(1000000); </code></pre> <p>@Clodoaldo: My stored procedure </p> <pre><code>call casualipergruppo('prova','id_gruppo',2); </code></pre> <p>gives me 200 records and takes about 23 seconds. Your stored procedure keeps on giving me Error Code : 1473 Too high level of nesting for select even though I increase varchar value to 20000. I don't know if there is any limit on unions involved in a query. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload