#sql #group-by #sum
Вопрос:
Я работаю с двумя различными наборами данных covid, которые включают в себя отчетные данные из всех стран. Проблема, с которой я сталкиваюсь, связана с функциями sum и group by , где SQL суммирует все сгруппированные строки, что излишне увеличивает вывод. В приведенном ниже примере общее число смертей должно составить 4849 при общем количестве случаев 17 441.
*Примечание: Наборы данных включали город/провинцию для каждой страны, и ниже уже сгруппированы по странам
CREATE TABLE covid.deaths( country varchar(255), deaths int ); CREATE TABLE covid.confirmed_cases( country varchar(255), cases int ); *country* *deaths* *cases* China 0 747 China 0 519 China 0 1500 China 0 264 China 1 159 China 3 1992 China 2 1008 China 4 1632 China 7 1650 China 6 190 China 213 1260 China 8 1197 China 58 3260 China 13 362 China 22 703 China 4512 998 Однако, когда я выполняю запрос ниже, я получаю 279 056 общих случаев и 77 584 смертельных случая. Пытаясь самостоятельно решить эту проблему, я удалил одну из sum функций (но сохранил дату) и обнаружил, что при выводе SQL заполняет общую сумму по столбцу для всех строк, а затем суммирует эти столбцы, когда присутствуют обе sum функции (по сути, уравнение, которое использует SQL, является total deaths or cases x number of rows ).
SELECT COALESCE(d.country_region, "Unknown") AS country, SUM(d._11_16_21) as deaths, SUM(c._11_16_21) as cases FROM `covid.deaths` as d JOIN `covid.confirmed_cases` as c ON d.country_region = c.country_region WHERE d.country_region = "China" GROUP BY d.country_region Вывод с sum удаленной функцией cases
SELECT COALESCE(d.country_region, "Unknown") AS country, SUM(d._11_16_21) as deaths, c._11_16_21 as cases FROM `covid.deaths` as d JOIN `covid.confirmed_cases` as c ON d.country_region = c.country_region WHERE d.country_region = "China" GROUP BY d.country_region, c._11_16_21 *country* *deaths* *cases* China 4849 747 China 4849 519 China 4849 1500 China 4849 264 China 4849 159 China 4849 1992 China 4849 1008 China 4849 1632 China 4849 1650 China 4849 190 China 4849 1260 China 4849 1197 China 4849 3260 China 4849 362 China 4849 703 China 4849 998 Есть ли способ, чтобы SQL отображал в качестве выходных данных только уникальную сумму?
*Примечание: Используется объединение, так как есть некоторые названия стран, которые null
Комментарии:
1. Пожалуйста, обновите свой вопрос, чтобы показать инструкции create для таблиц
2. Ваш запрос ссылается на столбцы country_region, country и _11_16_21, которых нет в ваших образцах данных. Пожалуйста, уточните свой вопрос, предоставив фактические исходные данные. Исходя из результатов, похоже, что ваши исходные таблицы содержат более одной строки на регион страны. Эту проблему можно решить, либо используя подзапросы для предварительной агрегации каждой таблицы отдельно, либо добавив дополнительные поля в критерии объединения, чтобы каждая строка в таблице confirmed_cases совпадала только с одной строкой в таблице смертей.
Ответ №1:
Суммируйте их отдельно, прежде чем присоединять случаи covid к смертям
SELECT COALESCE(d.country_region, c.country_region, 'Unknown') AS country, d.deaths, c.cases FROM ( SELECT country_region , SUM(`_11_16_21`) as deaths FROM `covid.deaths` WHERE country_region = 'China' GROUP BY country_region ) as d JOIN ( SELECT country_region , SUM(`_11_16_21`) as cases FROM `covid.confirmed_cases` WHERE country_region = 'China' GROUP BY country_region ) as c ON c.country_region = d.country_region Комментарии:
1. Обратите внимание, что я изменил ПОЛНОЕ СОЕДИНЕНИЕ на обычное ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Потому что я предполагаю, что это для MySQL или MariaDB. Таким образом, есть большая вероятность, что ПОЛНОЕ ПРИСОЕДИНЕНИЕ не поддерживается в вашей базе данных.
2. Внутреннее объединение работает до тех пор, пока все регионы страны сообщают о случаях заболевания и смерти. Если строки отсутствуют в любом наборе, результаты будут отображаться в разделе «Отчет».
3. Хорошо, но просто имейте в виду, что нечто подобное
FULL OUTER JOINсуществует в большинстве СУБД. Но альтернатива просто уродлива (объединение запроса на соединение слева и справа).