I've spent the last couple of evenings trying to build a system for redistribution of votes in a ranked voting system. I've finally come up with the model for redistributing surpluses from a candidate whose number of votes exceeds the threshold.
I'm at the beginning of my learning curve and I've used Stack Overflow extensively during this process to gain knowledge about how to produce each and every query within this system.
Finally I've reached the result I want as can be seen below on the expense of a really confusing set of queries:
+-----------+-------+----------------+----------------------------+
| CANDIDATE | VOTES | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
+-----------+-------+----------------+----------------------------+
| 1 | 8 | -1 | 7 |
| 2 | 1 | 0.125 | 1.125 |
| 3 | 2 | 0.25 | 2.25 |
| 4 | 4 | 0.5 | 4.5 |
| 5 | 2 | 0.25 | 2.25 |
| 6 | 3 | 0 | 3 |
+-----------+-------+----------------+----------------------------+
As mentioned - these queries are horrible. Absolutely and utterly HORRIBLE. Now I need to understand how this could be simplified and at the same time as effective or even more effective than this mess of queries.
Could anyone explain how to optimize this in a way that I can learn more about writing good queries?
This is my (awful) query:
SELECT vote_candidate candidate, original_votes votes, surplus_redistribution redistribution, (original_votes + surplus_redistribution) votes_after_redistribution
FROM (
SELECT c.vote_candidate, (
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
) surplus_ratio, c.original_votes, LEAST(0,t.threshold - c.original_votes) surplus_redistribution
FROM (
SELECT o.vote_candidate, COUNT(*) original_votes
FROM vote_orders o
WHERE o.vote_order = 1
GROUP BY o.vote_candidate
) c
CROSS JOIN (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) AS threshold
FROM votes
) t
GROUP BY c.vote_candidate
UNION
SELECT vote_candidate, (
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
) surplus_ratio, COUNT(*) original_votes, (
ROUND((COUNT(*) * (
SELECT (
(MAX(votes_above_the_threshold) - (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)) / MAX(votes_above_the_threshold)
) ratio
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold = (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
)), 3)
) surplus_redistribution
FROM vote_orders
WHERE vote_order = 1
AND vote_candidate IN ((
SELECT vote_candidate
FROM vote_orders a
INNER JOIN (
SELECT vote_id, MIN(vote_order) AS min_vote_order
FROM vote_orders
WHERE vote_candidate NOT IN ((
SELECT vote_candidate
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
OR (
votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
AND votes_above_the_threshold = 0
)
OR (
votes_above_the_threshold = 0
)
)
) t
))
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 = (
SELECT vote_candidate
FROM (
SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
HAVING votes_above_the_threshold >= (
SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes
)
) t
WHERE votes_above_the_threshold >= (
SELECT MAX(votes_above_the_threshold)
FROM vote_orders
)
)
AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate
))
GROUP BY vote_candidate
ORDER BY vote_candidate ASC
) y
GROUP BY vote_candidate;
This is the schema:
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,
PRIMARY KEY vote_id(vote_id)
);
INSERT INTO votes
VALUES
(NULL, 1, 3, 2, 5, 4, 6),
(NULL, 1, 2, 4, 6, 3, 5),
(NULL, 5, 3, 2, 1, 4, 6),
(NULL, 6, 1, 5, 3, 4, 2),
(NULL, 2, 3, 5, 6, 1, 4),
(NULL, 4, 1, 6, 3, 2, 5),
(NULL, 3, 2, 6, 1, 5, 4),
(NULL, 4, 3, 1, 6, 2, 5),
(NULL, 1, 2, 4, 3, 6, 5),
(NULL, 1, 5, 3, 2, 4, 6),
(NULL, 4, 5, 6, 2, 3, 1),
(NULL, 1, 4, 2, 3, 5, 6),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 3, 6, 5, 1, 4, 2),
(NULL, 1, 2, 3, 4, 5, 6),
(NULL, 6, 5, 4, 3, 2, 1),
(NULL, 4, 3, 1, 5, 6, 2),
(NULL, 6, 3, 1, 2, 5, 4),
(NULL, 1, 4, 6, 3, 2, 5),
(NULL, 5, 3, 6, 4, 2, 1);
CREATE TABLE vote_orders
(
id INT NOT NULL AUTO_INCREMENT,
vote_id INT,
vote_order INT,
vote_candidate INT,
PRIMARY KEY id(id)
);
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;
The rules for redistributing works like this:
If a candidate exceeds the threshold in the first round, his/hers surplus is redistributed proportionally to the candidate ranked as number two on the winners ballots.
The ratio is calculated like this:
The surplus of the winner is divided by the total max votes rounded down to the nearest full integer. Then the redistributed value is added to the candidates.