重新分配选票，同时淘汰候选人 [英] Redistribute votes while eliminating candidates

问题描述

考虑这个想法.我有一个排名选择投票系统，其中不符合条件的候选人将在不同回合中被淘汰.在此示例中，我们有6位候选人在初选中竞选两个席位.至此，我们进入了第四轮(没有获胜者)，其中候选人3和5已被淘汰.在这一轮中，候选人4被淘汰，他/她的选票将重新分配给在每个选票上标记为第二或第三选择的候选人，其中候选人4被标记为第一选择.

Consider this thought. I have a ranking choice voting system, where candidates that doesn't meet the threshold are eliminated during different rounds. In this example we have 6 candidates running for two seats in a primary election. At this point we have reached round 4 (with no winner) where candidates 3 and 5 have already been eliminated. In this round candidate 4 is being eliminated and his/hers votes are about to be redistributed to those candidates marked as second or third choice on each ballot where candidate 4 was marked as first choice.

我们在所有候选人都是第4个候选人的情况下进行了所有选票(碰巧是4个选票).他们看起来像这样:

We grab all ballots where candidate 4 is the first choice (which happens to be 4 ballots). They look like this:

候选人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

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

重新分配的选票(以粗体显示):
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 are (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

现在解决我的问题:我需要编写一个MySQL查询，以获取这些值并计算通过此重新分配获得的每个给定候选人的票数. IE. MySQL查询的结果应如下所示:

Now for my problem: I need to write a MySQL query that grabs these values and counts the number of votes each given candidate recieves through this redistribution. I.e. the result with the MySQL query should look like this:

候选人1:3票
候选人6:1票

Candidate 1: 3 votes
Candidate 6: 1 vote

此查询需要知道候选3和5已被消除，因此前往下一列以查找任何其他候选.

This query need to know that candidates 3 and 5 are already eliminated thus heading for the next column to find any other candidate.

我如何编写MySQL查询以获取此结果?

How would I write a MySQL query to get this result?

推荐答案

喜欢玩.

我认为，如果可以按照以下方式使用您当前的数据布局:-

I think using your current layout of data if could be 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)) AS vote_candidate,
                COUNT(*)
FROM votes
WHERE vote_candidate_a = 4
GROUP BY vote_candidate;

这几乎与幻想派回答的一样.我并不热衷于此，因为它似乎不容易阅读，并且(如果数据量很大)可能效率很低.

This is pretty much as fancypants has answered. I am not keen on this as it doesn't seem easy to read and (if the amount of data is large) is likely to be inefficient.

我将更倾向于将数据拆分为不同的表结构，每组投票多行，每位候选人一个.如果系统处于开发的早期阶段(即，您只是生成新表)，则这样做会容易得多.

I would be more inclined to split the data off into a different table structure, with multiple rows per set of votes, one per candidate. This would be far easier to do if the system is in the early stages of development (ie, you just generate the new tables).

假设您现有的数据设置如下:-

Assuming your existing data was set up as 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 INTO 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 could be generated as follows:-

CREATE TABLE vote_orders
(
    id INT NOT NULL AUTO_INCREMENT,
    vote_id INT,
    vote_order INT,
    vote_candidate INT
);

INSERT INTO vote_orders (id, vote_id, vote_order, vote_candidate)
SELECT NULL, vote_id, 1, vote_candidate_a FROM votes
UNION
SELECT NULL, vote_id, 2, vote_candidate_b FROM votes
UNION
SELECT NULL, vote_id, 3, vote_candidate_c FROM votes
UNION
SELECT NULL, vote_id, 4, vote_candidate_d FROM votes
UNION
SELECT NULL, vote_id, 5, vote_candidate_e FROM votes
UNION
SELECT NULL, vote_id, 6, vote_candidate_f FROM votes;

然后您可以简单地使用以下方法获得投票.这将使用子查询来获取尚未使用的最高投票，然后将其与数据结合起来.

You could then simply use the following to get the votes. This uses a sub query to get the highest vote that hasn't already been used and then joins that back against the data.

SELECT vote_candidate, COUNT(*)
FROM vote_orders a
INNER JOIN
(
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM vote_orders
    WHERE vote_candidate NOT IN (4,3,5)
    GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
    SELECT vote_id
    FROM vote_orders
    WHERE vote_candidate  = 4
    AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate

SQL在这里摆弄:-

SQL fiddle here:-

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

混合解决方案(两全其美！):-

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

SELECT vote_candidate, COUNT(*)
FROM 
(
    SELECT vote_id, 1 AS vote_order, vote_candidate_a AS vote_candidate FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 2, vote_candidate_b FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 3, vote_candidate_c FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 4, vote_candidate_d FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 5, vote_candidate_e FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 6, vote_candidate_f FROM votes WHERE vote_candidate_a = 4
) a
INNER JOIN
(
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM 
    (
        SELECT vote_id, 2 AS vote_order, vote_candidate_b AS vote_candidate FROM votes
        UNION
        SELECT vote_id, 3, vote_candidate_c FROM votes
        UNION
        SELECT vote_id, 4, vote_candidate_d FROM votes
        UNION
        SELECT vote_id, 5, vote_candidate_e FROM votes
        UNION
        SELECT vote_id, 6, vote_candidate_f FROM votes
    ) a
    WHERE vote_candidate NOT IN (4,3,5)
    GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
GROUP BY vote_candidate;

这篇关于重新分配选票，同时淘汰候选人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！