使用重新分配系统计算选票的复杂查询-后续行动

Question

我以前发过一个关于简化查询以使用分配系统计算选票的问题。其中一位贡献者@200_success指出，它计算盈余再分配的依据是第二位候选人的原始选票，而不是他们在获胜者选票上的第二位。

我如何以最好的方式简化它？这是其查询的结果表：

 +-----------+-------+---------------+----------------+----------------------------+
 | CANDIDATE | VOTES | RANKED_CHOICE | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
 +-----------+-------+---------------+----------------+----------------------------+
 |         1 |     8 |             0 |             -1 |                          7 |
 |         2 |     1 |             4 |            0.5 |                        1.5 |
 |         3 |     2 |             1 |          0.125 |                      2.125 |
 |         4 |     4 |             2 |           0.25 |                       4.25 |
 |         5 |     2 |             1 |          0.125 |                      2.125 |
 |         6 |     3 |             0 |              0 |                          3 |
 +-----------+-------+---------------+----------------+----------------------------+

SELECT vote_candidate candidate, original_votes votes, ranked_choice, 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, '0' ranked_choice, 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 ALL
  SELECT vote_candidate, (
    SELECT (
      (
        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
        )
      )
    ) ratio
  ) surplus_ratio, d.original_votes, COUNT(*) ranked_choice, (
    ROUND((COUNT(*) * (
      SELECT (
        (
          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
          )
        )
      ) ratio
      FROM (
        SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        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
      ) t
      WHERE votes_above_the_threshold >= (
        SELECT MAX(votes_above_the_threshold)
        FROM vote_orders
      )
      LIMIT 1
    )), 3)
  ) surplus_redistribution 
  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 = 1
    AND vote_order = 1
  ) c
  ON a.vote_id = c.vote_id
  LEFT OUTER JOIN
  (
    SELECT vote_candidate o, COUNT(*) AS original_votes
    FROM vote_orders 
    WHERE vote_order = 1
    GROUP BY vote_candidate
  ) d
  ON a.vote_candidate = d.o
  GROUP BY vote_candidate
  ORDER BY surplus_redistribution DESC
) y
GROUP BY vote_candidate;

表模式：

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;

Answer 1

它看起来更好，但我仍然建议使用变量来替换这个片段。

SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
FROM votes

您也可以编写一个包含逻辑的存储过程。虽然它也可能是作为视图存储在架构中，但是使用一个带一个字段的视图返回一个记录似乎有点傻。

此值还应该是返回单个值的存储proedure。

 SELECT (
      (MAX(votes_above_the_threshold) - (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )) / MAX(votes_above_the_threshold)
    ) ratio

执行一次存储过程，并将结果存储在变量中。尽量减少筑巢的层次。当您到达cntl+c时，停止并考虑如何重用这段特定的代码。

Answer 2

我在DDL中看到的一个大问题是，您的candidate是votes中的多列，而不是candidates表的外键。这本身将使您的结构更好，更容易使用。

但是房间里的大象是显而易见的:小象！他们太多了！

另一件事是，请使用-- Comments或/* Comments */使您的代码更容易为下一个要检查它的人理解。就像现在一样，它看起来像一堵没有押韵或理由的SQL代码墙。

听着，我很想回顾一下这段代码，我试过，花了几个小时在一个word编辑器中突出显示不同的部分，但它是如此令人难以置信的复杂。我的建议是从一开始就开始，但这一次要牢记您的预期结果，从那里开始工作，并在它们出现时修复语法和其他错误。尽可能使用变量进行重复操作。祝你好运。