mysql - SQL to get even distribution from n groups - fetch random items -

-

i have following tables:

table product id int(11) title varchar(400)  table tag id int(11) text varchar(100)  table product_tag_map product_id int(11) tag_id int(11)

product_tag_map maps tags product. distribution of tags in system isn't normal, i.e., tags have more products others.

i'm trying write sql fetch 25 random products: 5 products per tag, 5 tags (so that's 5x5 = 25).

found answer here: how can distribution using id in(1,2,3,4)

but doesn't yield random products - fetches same products per tag.

here sql have:

set @last_tag = 0; set @count_tag = 0;  select distinct id ( select   product.*,   @count_tag := if(@last_tag = product_tag_map.tag_id, @count_tag, 0) + 1 tag_row_number,   @last_tag := product_tag_map.tag_id product   left join product_tag_map on (product_tag_map.product_id=product.id)   product_tag_map.tag_id in (245,255,259,281,296) ) subquery tag_row_number <= 5;

how make return random products per tag?

any appreciated! thanks.

there lot of tricks in query :

  1. add level of nesting use limit in subquery : mysql subquery limit
  2. add row_number functionality mysql : how select first/least/max row per group in sql

the final result lot of subquery:

select tag.name, t0.id mapid (     select *           , @num := if(@type = tag_id, @num + 1, 1) row_number          , @type := tag_id dummy     (         select *         map m         tag_id in         (             select *                         (                 select id                 tag                 order rand() limit 5             ) t         )      order tag_id, rand()     ) maintable   , (select @num:=0) foo   , (select @type:=0) foo2 ) t0      inner join tag         on t0.tag_id = tag.id row_number <= 5

sql fiddle

the idea select first 5 random tags. not difficult, simple order rand() limit 5.

then tricky part simulate row_number() over(partition tag_id, rand()), because ranking each item randomly, partition tag need. declare variable , query show.

finally, filter row_number, , have 25 random items!


Popular posts from this blog

Php - Delimiter must not be alphanumeric or backslash -

-
this question has answer here: delimiter must not alphanumeric or backslash , preg_match 5 answers this question has been asked don't know how fix problem in situation. getting following php errror: php message: php warning: preg_match(): delimiter must not alphanumeric or backslash in... on line 227 line 227 following: if (preg_match($match, $_server['http_user_agent'])) and function: $oslist = array ( 'windows 3.11' => 'win16', 'windows 95' => '(windows 95)|(win95)|(windows_95)', 'windows 98' => '(windows 98)|(win98)', 'windows 2000' => '(windows nt 5.0)|(windows 2000)', 'windows xp' => '(windows nt 5.1)|(windows xp)', 'windows server 2003' => '(windows nt 5.2)', 'windows vista' => '(windows nt 6.0)', 'w...
Read more

Delphi interface implements -

-
i expect reference counting should work on outer aggregating object in interface implementation. if can refer example: clarity in classes implementing multiple interfaces (alternative delegation): here minimal reproduction of behaviour: program so16210993; {$apptype console} type ifoo = interface procedure foo; end; tfooimpl = class(tinterfacedobject, ifoo) procedure foo; end; tcontainer = class(tinterfacedobject, ifoo) private ffoo: ifoo; public constructor create; destructor destroy; override; property foo: ifoo read ffoo implements ifoo; end; procedure tfooimpl.foo; begin writeln('tfooimpl.foo called'); end; constructor tcontainer.create; begin inherited; ffoo := tfooimpl.create; end; destructor tcontainer.destroy; begin writeln('tcontainer.destroy called');//this line never runs inherited; end; procedure main; var foo : ifoo; begin foo := tcontainer.create; foo.foo; end; begin main; readln...
Read more

css - invalid property value for padding -

-
i getting invalid property value padding , don't know why. code written in ruby btw. when check browser has right value pixels, crossed out w line , isn't reading attribute. in advance. <% mywaldo = mapping::mywaldos.sample %> <!doctype html> <html> <head> <title>sinatra single-serve</title> <link href="css/bootstrap.css" rel="stylesheet" type="text/css"/> <style> div { width: 1024px; height: 768px; background-image: url("img/waldos/<%= mywaldo[0]%>") ; } #waldo { position:absolute; top: <%= mywaldo[2]%>; left: <%= mywaldo[1]%>; padding: <%= mywaldo[3]%>, <%= mywaldo[4]%>; } </style> </head> <body> <div> <a id="waldo" href="">aa</a> </div> <%= yield %> </body> ...
Read more