mysql - Redistribute votes while eliminating candidates -


consider thought. have ranking choice voting system, candidates doesn't meet threshold eliminated during different rounds. in example have 6 candidates running 2 seats in primary election. @ point have reached round 4 (with no winner) candidates 3 , 5 have been eliminated. in round candidate 4 being eliminated , his/hers votes redistributed candidates marked second or third choice on each ballot candidate 4 marked first choice.

we grab ballots candidate 4 first choice (which happens 4 ballots). this:

candidate 4:
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

the redistributed votes (in bold):
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

now problem: need write mysql query grabs these values , counts number of votes each given candidate recieves through redistribution. i.e. result mysql query should this:

candidate 1: 3 votes
candidate 6: 1 vote

edit: query need know candidates 3 , 5 eliminated heading next column find other candidate.

how write mysql query result?

been having play.

i think using current layout of data if done as:-

select coalesce(if(vote_candidate_b in (4,3,5), null, vote_candidate_b),                 if(vote_candidate_c in (4,3,5), null, vote_candidate_c),                 if(vote_candidate_d in (4,3,5), null, vote_candidate_d),                 if(vote_candidate_e in (4,3,5), null, vote_candidate_e),                 if(vote_candidate_f in (4,3,5), null, vote_candidate_f)) vote_candidate,                 count(*) votes vote_candidate_a = 4 group vote_candidate;

this pretty fancypants has answered. not keen on doesn't seem easy read , (if amount of data large) inefficient.

i more inclined split data off different table structure, multiple rows per set of votes, 1 per candidate. far easier if system in stages of development (ie, generate new tables).

assuming existing data set follows:-

create table votes (     vote_id int not null auto_increment,     vote_candidate_a int,     vote_candidate_b int,     vote_candidate_c int,     vote_candidate_d int,     vote_candidate_e int,     vote_candidate_f int );  insert votes values (null, 4, 1, 6, 3, 2, 5), (null, 4, 3, 1, 6, 2, 5), (null, 4, 5, 6, 2, 3, 1), (null, 4, 3, 1, 5, 6, 2);

my format generated follows:-

create table vote_orders (     id int not null auto_increment,     vote_id int,     vote_order int,     vote_candidate int );  insert vote_orders (id, vote_id, vote_order, vote_candidate) select null, vote_id, 1, vote_candidate_a votes union select null, vote_id, 2, vote_candidate_b votes union select null, vote_id, 3, vote_candidate_c votes union select null, vote_id, 4, vote_candidate_d votes union select null, vote_id, 5, vote_candidate_e votes union select null, vote_id, 6, vote_candidate_f votes;

you use following votes. uses sub query highest vote hasn't been used , joins against data.

select vote_candidate, count(*) vote_orders inner join (     select vote_id, min(vote_order) min_vote_order     vote_orders     vote_candidate not in (4,3,5)     group vote_id ) b on a.vote_id = b.vote_id , a.vote_order = b.min_vote_order inner join (     select vote_id     vote_orders     vote_candidate  = 4     , vote_order = 1 ) c on a.vote_id = c.vote_id group vote_candidate

sql fiddle here:-

http://www.sqlfiddle.com/#!2/7d48c/10

a hybrid solution (worst of both worlds!):-

select vote_candidate, count(*)  (     select vote_id, 1 vote_order, vote_candidate_a vote_candidate votes vote_candidate_a = 4     union     select vote_id, 2, vote_candidate_b votes vote_candidate_a = 4     union     select vote_id, 3, vote_candidate_c votes vote_candidate_a = 4     union     select vote_id, 4, vote_candidate_d votes vote_candidate_a = 4     union     select vote_id, 5, vote_candidate_e votes vote_candidate_a = 4     union     select vote_id, 6, vote_candidate_f votes vote_candidate_a = 4 ) inner join (     select vote_id, min(vote_order) min_vote_order          (         select vote_id, 2 vote_order, vote_candidate_b vote_candidate votes         union         select vote_id, 3, vote_candidate_c votes         union         select vote_id, 4, vote_candidate_d votes         union         select vote_id, 5, vote_candidate_e votes         union         select vote_id, 6, vote_candidate_f votes     )     vote_candidate not in (4,3,5)     group vote_id ) b on a.vote_id = b.vote_id , a.vote_order = b.min_vote_order group vote_candidate;

Comments

Post a Comment

Popular posts from this blog

commonjs - How to write a typescript definition file for a node module that exports a function? -

consider, toml node module can use: // toml.d.ts declare module toml { export function parse(value:string):any; } declare module "toml" { export = toml; } then: /// <reference path="../../../../../defs/toml/toml.d.ts"/> import toml = require('toml'); toml.parse(...); however, node module export single function, such 'glob' ( https://github.com/isaacs/node-glob ). the node usage of module be: var glob = require("glob") glob("*.js", {}, callback(err, files) { ... }); you'd naively expect this: // glob.d.ts declare function globs(paths:string, options:any, callback:{(err:any, files:string[]):void; ...but because typescripts 'import' semantics bit strange, seems can use 'import .. = require()' statement alias modules . trying call: /// <reference path="../../../../../defs/glob/glob.d.ts"/> import blog = require('glob'); results in: error ts2072: mod...
Read more

openid - Okta: Failed to get authorization code through API call -

i'm integrating okta own idp server using okta's api. i'm implementing authorization code flow following steps below: in own server, use /api/v1/authn endpoint sessiontoken. use sessiontoken obtain authorization calling endpoint: /oauth2/v1/authorize?client_id=" + clientid + "&sessiontoken=" + sessiontoken + "&response_type=code&response_mode=query&scope=openid&redirect_uri=" + redirecturl + "&state=evanyang&nonce=" it's supposed return response status code 302 , location header containing redirect url code value. however, keep getting response status code 200 , without location header, html body saying "you using unsupported browser." , "javascript disabled on browser." according api documentation: http://developer.okta.com/docs/api/resources/oidc.html#authentication-request , sessiontoken parameter sufficient this: an okta one-time sessiontoken. allows api-based ...
Read more

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more