Получить количество таблиц PER каждого типа электронной почты
В продолжение моего предыдущего вопроса: Получите подсчет каждого типа электронной почты из нескольких таблиц
Мне нужно было получить разъяснения...
Поэтому мне действительно нужно иметь несколько таблиц.
Затем мне нужно получить количество всех «gmails», «msfts», «others» и «vmgs» из каждой таблицы.
Все столы имеют одинаковый дизайн. Таблица 1 может выглядеть так:
| EMAIL | MY_ISP | STATUS |
-------------------------
| email1| gmail | active |
-------------------------
| email2| msft | unsub |
-------------------------
| email3| vmg | active |
-------------------------
| email4| gmail | active |
-------------------------
Таблица2 может выглядеть так:
| EMAIL | MY_ISP | STATUS |
-------------------------
| email1| gmail | unsub |
-------------------------
| email2| msft | active |
-------------------------
| email3| vmg | active |
-------------------------
| email5| gmail | unsub |
-------------------------
Электронное письмо может существовать в нескольких таблицах, но не будет указано в одной и той же таблице дважды. Один и тот же адрес электронной почты может быть АКТИВНЫМ в одной или нескольких таблицах и отсутствовать в другой.
Мне нужно получить текущий подсчет всех АКТИВНЫХ электронных писем в каждой таблице.
И мне нужно отобразить конечный продукт следующим образом:
TABLE | GMAIL | MSFT | OTHER | VMG
-----------------------------------
T1 | 5000 | 7000 | 4500 | 475
-----------------------------------
T2 | 4520 | 6789 | 4450 | 425
-----------------------------------
T3 | 4400 | 6500 | 4123 | 410
-----------------------------------
Что-то в этом роде. Вплоть до Т15.
По мере того, как я продолжаю добавлять новые таблицы, общее количество должно постепенно уменьшаться, пока не станет практически равным 0.
Надеюсь, это имеет смысл.
Мне трудно осознать эту логику.
Согласно моему предыдущему вопросу, я могу получить общую сумму MY_ISP, используя этот запрос:
SELECT COUNT(`my_isp`) AS 'COUNT', `my_isp` FROM `table1` GROUP BY `my_isp`
И выплюнет что-то вроде этого:
COUNT | my_isp
---------------
4000 | Gmail
---------------
2000 | MSFT
---------------
10000 | other
---------------
15000 | VMG
---------------
И я могу получить итоговые данные для каждого MY_ISP, используя что-то вроде этого:
select my_isp ,sum(cnt) AS total
from (select my_isp,count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
UNION ALL
select my_isp,count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
) subquery_name
group by my_isp
Вышеописанное на самом деле работает великолепно. Но это не то, что просили.
Повторюсь: мне нужно подсчитать количество GMAILS, MSFT, VMG и OTHER для каждой таблицы.
Помогите пожалуйста разобраться, как должен выглядеть этот запрос, потому что я заблудился.
Я продолжу проводить тесты и обновлять вопросы, если добьюсь некоторого прогресса.
** РЕДАКТИРОВАТЬ **
Я думаю, может быть, мне нужно создать VIEW, который будет отображать разные MY_ISP в качестве заголовков столбцов и каждую TABLE в качестве строк.
@JNeville Итак, вы предлагаете извлечь данные обратно в Excel и выполнить сводную таблицу?
Я переместил свой комментарий в ответ. Когда мы переводим значения строк в столбцы, это называется PIVOT, на самом деле во многих СУБД есть ключевое слово PIVOT для обработки именно этого сценария, Mysql не входит в их число (если только это не новая добавленная функция, которую я пропустил). Это очень похоже на сводную таблицу в Excel, но реализовано с помощью кода для набора результатов в СУБД. В своем ответе я рассказал, как это сделать, используя выражения регистра в MySQL.
Ответы 2
Добавьте имя таблицы в качестве другого столбца в каждый запрос в UNION.
select table_name, my_isp ,sum(cnt) AS total
from (
select 't1' AS table_name, my_isp, count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
UNION ALL
select 't2' AS table_name, my_isp, count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
) subquery_name
group by table_name, my_isp
Чтобы поместить каждого поставщика в столбец, вы можете свести полученные данные. См. Как я могу вернуть вывод сводной таблицы в MySQL?
Чтобы решить эту проблему, нужно либо сначала ОБЪЕДИНИТЬ все таблицы вместе, а затем СВЯЗАТЬ результаты, чтобы поместить интернет-провайдера в столбец. ИЛИ, альтернативно, СВЕДИТЕ каждую таблицу, чтобы поместить ISP в столбец, а затем ОБЪЕДИНИТЕ их вместе. Я бы выбрал первый вариант, поскольку PIVOT требует больших вычислительных затрат, поэтому лучше всего выполнить этот шаг один раз.
Это будет выглядеть примерно так:
SELECT tablename,
COUNT(CASE WHEN ISP = 'gmail' THEN email END) as gmail,
COUNT(CASE WHEN ISP = 'msft' THEN email END) as msft,
COUNT(...
COUNT(...
FROM
(
SELECT 't1' as tablename, my_isp, email FROm table1
UNION ALL
SELECT 't2', my_isp, email FROM table2
UNION ALL
SELECT 't3', my_isp, email FROM table3
UNION ALL
SELECT....
) sub
GROUP BY tablename
ORDER BY tablename
Я не смотрел ваш связанный вопрос, но я уверен, что это обсуждалось подробно, и я чувствую, что его также необходимо добавить сюда, поскольку это решение UNION является пластырем для решения реальной проблемы: а именно, все этих данных уже должно быть в одной таблице. Правильная схема по существу будет выглядеть как результат подзапроса в этом ответе. Это может оказаться невозможным, если вы не отвечаете за схему или сбор данных, но это дает пищу для размышлений. Изменение схемы уменьшит сложность этого запроса, поскольку подзапрос станет ненужным, и уменьшит вычислительные затраты на его выполнение, поскольку данные уже будут правильно объединены.
Что представляет собой «имя таблицы»? Я начинаю SELECT с одной из своих таблиц?
tablename — это просто то, что мы называем новым столбцом, который мы генерируем. Мы устанавливаем значение, хранящееся в этом столбце, в строковый литерал t1. Желаемый результат в вашем вопросе имеет столбец с названием TABLE, и это то, что вам придется установить вручную в своем SQL, и мы делаем это с помощью этого 't1' as tablename кода.
Я объединил все данные в одну таблицу. Когда я попытался выполнить запрос, все зависло.
Я добавил dbfiddle к ответу, чтобы лучше визуализировать результат, используя образец/входные данные, изложенные в вашем вопросе. Я думаю, это может помочь.
Ты гений, мой друг. Вы помогли мне решить мою проблему. Ваша рабочий пример очень помогла. Если бы я мог дать вам больше одного голоса, я бы это сделал. Спасибо.
Рад, что dbfiddle помог соединить точки!
Скорее всего, все это следует объединить в одну таблицу. Возможно, задайте вопрос о том, как их объединить.