#mysql
#mysql
Вопрос:
Я просмотрел StackOverflow и Интернет для ответа на следующий вопрос, но ни один из ответов, которые я нашел, не работает для меня. Это моя проблема.
У меня есть два запроса, которые я хочу объединить с помощью UNION . Мне удалось объединить их, как ожидалось, но я не могу заставить их группироваться так, как я хочу. Я хочу, чтобы дублированные строки с перераспределенным значением 0 были скрыты путем упорядочивания вложенных запросов таким образом, чтобы они были приоритетными при группировании… (плохое объяснение, которое я знаю — я надеюсь, что приведенное ниже графическое представление объясняет это лучше. Строки, которые я хочу удалить, отмечены маленькой стрелкой на правом поле).
Как, черт возьми, я мог бы это сделать?
----------- ------- --------------- ---------------- ----------------------------
| CANDIDATE | VOTES | RANKED_CHOICE | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
----------- ------- --------------- ---------------- ----------------------------
| 1 | 8 | 0 | 0 | 8 |
| 2 | 1 | 6 | -1 | 0 |
| 2 | 1 | 0 | 0 | 1 | >
| 3 | 2 | 0 | 0 | 2 |
| 4 | 4 | 0 | 0 | 4 |
| 5 | 2 | 0 | 0 | 2 |
| 6 | 3 | 0 | 0 | 3 | >
| 6 | 3 | 0 | 1 | 4 |
----------- ------- --------------- ---------------- ----------------------------
-- The resulting table that's shown on the screen
SELECT vote_candidate candidate, original_votes votes, ranked_choice, redistribution, (original_votes redistribution) votes_after_redistribution
FROM (
-- Create the first table with original information
SELECT c.vote_candidate, c.original_votes, '0' ranked_choice, '0' redistribution
FROM (
SELECT o.vote_candidate, COUNT(*) original_votes
FROM vote_orders o
WHERE o.vote_order = 1
GROUP BY o.vote_candidate
) c
GROUP BY c.vote_candidate
-- Union a second table containing the second ranked choice of an eliminated candidate and the redistribution.
-- This is done in two steps. In the first step we find out the ranking. In the second step we union the ranked
-- candidate and its' redistribution with each other
UNION
SELECT vote_candidate, original_votes, ranked_choice, redistribution
FROM ((
SELECT vote_candidate, IFNULL(d.original_votes, 0) original_votes, IFNULL(COUNT(*), 0) ranked_choice, (0 - IFNULL(d.original_votes, 0)) redistribution
FROM vote_orders a
-- Get the second favored vote from each eliminated candidates ballots
INNER JOIN (
SELECT vote_id, c, MIN(minimum_vote)
FROM (
SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
GROUP BY c
) b
ON a.vote_id = b.vote_id
-- Get the eliminated candidates votes at the beginning of this round
LEFT OUTER JOIN
(
SELECT vote_candidate o, COUNT(*) 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 redistribution DESC
LIMIT 1
-- Union the candidates redistribution
UNION
(
SELECT vote_candidate, d.original_votes, '0' ranked_choice, (CASE
WHEN IFNULL(d.original_votes, 0) = 0
THEN (0 - IFNULL(d.original_votes, 0))
ELSE (
SELECT MIN(minimum_vote)
FROM (
SELECT vote_candidate c, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
GROUP BY c
)
END) redistribution
FROM vote_orders a
INNER JOIN (
SELECT vote_id, MIN(minimum_vote)
FROM (
SELECT vote_id, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
) b
ON a.vote_id = b.vote_id
LEFT OUTER JOIN
(
SELECT vote_candidate o, COUNT(*) original_votes
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) d
ON a.vote_candidate = d.o
-- Determine which candidate to add the redistribution to
WHERE vote_candidate = (
SELECT IFNULL(COUNT(*), 0) ranked_choice
FROM vote_orders a
INNER JOIN (
SELECT vote_id, c, MIN(minimum_vote)
FROM (
SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
GROUP BY c
) b
ON a.vote_id = b.vote_id
)
GROUP BY vote_candidate
ORDER BY redistribution DESC
LIMIT 1
)
)) y
) z
-- This is where the grouping fails on me
-- GROUP BY vote_candidate
ORDER BY vote_candidate ASC;
Это схема:
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;
Комментарии:
1. Этот запрос довольно запутанный. Существует чрезмерное количество подвыборов, и, честно говоря, действительно сложно составить представление о том, что вы пытаетесь сделать. Я уверен, что есть лучший способ написать этот запрос, я просто не могу понять, что вы хотите от данных. Просто посмотрев на это несколько минут, я вижу несколько мест, где вы можете устранить лишний / избыточный код. Пока вы не очистите этот запрос, будет чрезвычайно трудно получить то, что вы ищете.
2. Ну, мне нужно добавить, что я новичок в MySQL и только начинаю учиться. Это более или менее метод проб и ошибок. Я ценю вашу честность, и со временем мне удастся упростить ее, но пока это то, с чем мне приходится работать. 🙂
3. Я могу это абсолютно понять. Дело в том, что sql заключается в том, что вам, по сути, нужно приучить себя подходить к проблеме иначе, чем в объектно-ориентированных языках программирования. В этом случае ваш запрос выполняется не слишком медленно, потому что vote_orders — это такая маленькая таблица, но обычно дополнительные выборки значительно замедляют извлечение данных. Вместо того чтобы думать рекурсивно, попробуйте подумать о том, какие значения вы хотите получить для каждого столбца одной точки данных, и попытайтесь получить информацию о столбце одновременно. Затем разверните.
Ответ №1:
На самом деле, вы уже знаете, что такое vote_candidate и количество голосов сразу. Это простой запрос,
Select vote_candidate, count(*)
From vote_orders
Where vote_order = 1
Group by vote_candidate
Это основа всего запроса, поскольку в выборке отсутствуют только ranked_choice , redistribution и votes_after_redistribution (хотя это довольно незначительно, поскольку это вычисление из двух других столбцов). Итак, я бы рекомендовал использовать всю проделанную вами работу для поиска двух недостающих столбцов. По сути, вы должны изменить свой запрос, чтобы он был вложенным выбором, чтобы найти только недостающие столбцы.
Я изменил ваш подвыбор, чтобы находить значения ranked_choice и redistribution только в том случае, если они не равны 0. Затем я собираю эти значения (используя левое внешнее соединение). Если в подвыборке ничего нет, то по умолчанию мы будем принимать значение 0.
Ваш предыдущий запрос начался с присвоения всем значениям по умолчанию значения 0, а затем возврата информации для строк, которые не равны 0. Давайте пропустим всю дополнительную работу. Обратите внимание, я по-прежнему рекомендую очистить вложенный выбор, тем более, что я удалил количество исходных голосов. Некоторые объединения могут быть дополнительными, поскольку вам больше не нужно искать эту информацию. скрипка: http://sqlfiddle.com /#!2/1b0cb/51
-- The resulting table that's shown on the screen
SELECT v.vote_candidate candidate,
count(*) votes,
IfNull(z.ranked_choice, 0) ranked_choice,
IfNull(z.redistribution, 0) redistribution,
(count(*) IfNull(z.redistribution, 0)) votes_after_redistribution
FROM vote_orders v left outer join
(
-- Union a second table containing the second ranked choice of an eliminated candidate and the redistribution.
-- This is done in two steps. In the first step we find out the ranking. In the second step we union the ranked
-- candidate and its' redistribution with each other
SELECT vote_candidate, ranked_choice, redistribution
FROM ((
SELECT vote_candidate,
IFNULL(COUNT(*), 0) ranked_choice,
(0 - IFNULL(d.original_votes, 0)) redistribution
FROM vote_orders a
-- Get the second favored vote from each eliminated candidates ballots
INNER JOIN (
SELECT vote_id, vote_candidate c, MIN(minimum_vote)
FROM (
SELECT vote_id, vote_candidate, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
) b
ON a.vote_id = b.vote_id
-- Get the eliminated candidates votes at the beginning of this round
LEFT OUTER JOIN
(
SELECT vote_candidate o, COUNT(*) 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 redistribution DESC
LIMIT 1
-- Union the candidates redistribution
UNION
(
SELECT vote_candidate,
0 ranked_choice,
(CASE
WHEN IFNULL(d.original_votes, 0) = 0
THEN (0 - IFNULL(d.original_votes, 0))
ELSE (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
END) redistribution
FROM vote_orders a
INNER JOIN (
SELECT vote_id, MIN(minimum_vote)
FROM (
SELECT vote_id, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
) b
ON a.vote_id = b.vote_id
LEFT OUTER JOIN
(
SELECT vote_candidate o, COUNT(*) original_votes
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) d
ON a.vote_candidate = d.o
-- Determine which candidate to add the redistribution to
WHERE vote_candidate = (
SELECT IFNULL(COUNT(*), 0) ranked_choice
FROM vote_orders a
INNER JOIN (
SELECT vote_id, c, MIN(minimum_vote)
FROM (
SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t1
WHERE minimum_vote = (
SELECT MIN(minimum_vote)
FROM (
SELECT COUNT(*) minimum_vote
FROM vote_orders
WHERE vote_order = 1
GROUP BY vote_candidate
) t2
)
GROUP BY c
) b
ON a.vote_id = b.vote_id
)
GROUP BY vote_candidate
ORDER BY redistribution DESC
LIMIT 1
)
)) y
) z
on v.vote_candidate = z.vote_candidate
Where v.vote_order = 1
GROUP BY v.vote_candidate
ORDER BY v.vote_candidate ASC;
Комментарии:
1. Этот ответ стоит гораздо больше, чем просто точка. Это не только решило мою проблему — вы также дали мне несколько бесценных советов о том, как мыслить в этом процессе!