sql server - Random Weighted Choice in T-SQL -

-

how randomly select table row in t-sql based on applied weight candidate rows?

for example, have set of rows in table weighted @ 50, 25, , 25 (which adds 100 not need to), , want select 1 of them randomly statistical outcome equivalent respective weight.

dane's answer includes self joins in way introduces square law. (n*n/2) rows after join there n rows in table.

what more ideal able parse table once.

declare @id int, @weight_sum int, @weight_point int declare @table table (id int, weight int)  insert @table(id, weight) values(1, 50) insert @table(id, weight) values(2, 25) insert @table(id, weight) values(3, 25)  select @weight_sum = sum(weight) @table  select @weight_point = floor(((@weight_sum - 1) * rand() + 1), 0)  select     @id = case when @weight_point < 0 @id else [table].id end,     @weight_point = @weight_point - [table].weight     @table [table] order     [table].weight desc

this go through table, setting @id each record's id value while @ same time decrementing @weight point. eventually, @weight_point go negative. means sum of preceding weights greater randomly chosen target value. record want, point onwards set @id (ignoring ids in table).

this runs through table once, have run through entire table if chosen value first record. because average position half way through table (and less if ordered ascending weight) writing loop possibly faster... (especially if weightings in common groups):

declare @id int, @weight_sum int, @weight_point int, @next_weight int, @row_count int declare @table table (id int, weight int)  insert @table(id, weight) values(1, 50) insert @table(id, weight) values(2, 25) insert @table(id, weight) values(3, 25)  select @weight_sum = sum(weight) @table  select @weight_point = round(((@weight_sum - 1) * rand() + 1), 0)  select @next_weight = max(weight) @table select @row_count   = count(*)    @table set @weight_point = @weight_point - (@next_weight * @row_count)  while (@weight_point > 0) begin     select @next_weight = max(weight) @table weight < @next_weight     select @row_count   = count(*)    @table weight = @next_weight     set @weight_point = @weight_point - (@next_weight * @row_count) end  -- # once @weight_point less 0, know randomly chosen record -- # in group of records [table].weight = @next_weight  select @row_count = floor(((@row_count - 1) * rand() + 1), 0)  select     @id = case when @row_count < 0 @id else [table].id end,     @row_count = @row_count - 1     @table [table]     [table].weight = @next_weight order     [table].weight desc

Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more