SQL множественный подсчет данных из нескольких таблиц с объединением всех

Мой существующий SQL выглядит так, как показано ниже, он будет генерировать только счетчик total_pending_req.

SELECT count(table1.employee_code) as total_requests, table1.employee_code as emp_code
FROM table1
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table2.employee_code) as total_requests, table2.employee_code as emp_code
FROM table2
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table3.employee_code) as total_requests, table3.employee_code as emp_code
FROM table3
WHERE employee_status = 'PENDING'
GROUP BY emp_code

Это вернет результат ниже,

Я хочу получить количество запросов как total_pending_req, total_rejected_req и total_completed_req с учетом 3 разных таблиц. Все таблицы имеют одинаковые коды состояния PENDING, COMPLETED и REJECTED. Окончательный результат должен быть таким,

Я хотел бы иметь представление о том, как более эффективно извлекать данные, так как я должен использовать UNION ALL. Могу ли я узнать, есть ли лучший подход для более эффективного извлечения данных? Я был бы признателен за вашу помощь в этом.

какую СУБД вы используете? MySQL, SQL Server, PostgreSQL,...... отметьте это правильно

— 09.01.2023 06:22

@ RF1991 Извините, я использую Postgresql. Я обновил теги.

— 09.01.2023 06:24

sql database postgresql

09.01.2023 06:17

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Тот факт, что ваши строки появляются в несколько отношений table{1,2,3} просто раздражающее отвлечение. Давайте уже сделаем это одним отношением. Мы могли бы создать таблицу или представление.

CREATE VIEW combined AS
(SELECT * FROM table1
 UNION ALL
 SELECT * FROM table2
 UNION ALL 
 SELECT * FROM table3
 UNION ALL
)

Хороший!

После этого он становится тривиальным GROUP BY.

SELECT    employee_code, employee_status, COUNT(*)
FROM      combined
GROUP BY  employee_code, employee_status

Вы можете сформулировать это как КТР если вы против DDL. Или создайте ВИД, который делает СОЮЗ ВСЕХ тяжёлых работ. Или МАТЕРИАЛИЗОВАННОЕ ВИДЕНИЕ, что угодно.

Выходной формат: три emps × три состояния, или девять рядов.

Если вам действительно нужны три строки, не стесняйтесь SELECT из этого отношения переформатировать его.

Это выглядит великолепно, но проблема в том, что таким образом будет создана еще одна таблица. В настоящее время все три таблицы содержат данные, которые часто обновляются. Я просто создаю процесс, используя приведенный выше SQL-запрос, чтобы создать API для получения результата, как указано выше. Могу ли я узнать, есть ли альтернативный способ решить эту проблему?

— 09.01.2023 06:38

Ваше замечание описывает отвращение к DDL, поскольку DROP/INSERT, безусловно, может занимать много времени, и это понятное беспокойство. Я призываю вас обратить пристальное внимание на предложенные мной варианты CTE/VIEW/MATERIALIZED VIEW. Распределение строк по разным таблицам отвлекает вас от сосредоточения внимания на унифицированном отношении, содержащем все данные, необходимые для эффективного выполнения GROUP BY.

— 09.01.2023 07:12

Я пытался объяснить это старшему поколению. Но они ожидают только запроса. Кроме того, я исследовал разрыв в производительности между хранимым процессом и представлением. Как я вижу, между ними нет большого разрыва. Видите ли вы какие-либо другие недостатки в том, чтобы не использовать хранимую процедуру?

— 09.01.2023 10:47

@RYJ Он не создает другую таблицу, просто просматривает описание. На самом деле вы можете сделать это и внутри одиночного представления (внутри процедуры) - select what_you_want from (select ... union alll ... etcetc) без создания промежуточного представления.

— 09.01.2023 10:51

Где «хранимая процедура» вообще стала иметь отношение к этому вопросу? Я не вижу, чтобы это упоминалось явно или подразумевалось. Время, затраченное на выполнение запроса, казалось важным, поэтому я упомянул, что MATERIALIZED VIEW может быть привлекательным, если нет индексов для поддержки производительных запросов VIEW. Обратите внимание, что предложение CTE по сути идентично VIEW — оно создает отношение в удобном формате, из которого мы можем извлечь соответствующие строки.

— 09.01.2023 10:54

@Arvo Плохо, я думал, что это таблица, а не представление, поэтому я запутался. Просто понял суть.

— 09.01.2023 10:57

@J_H На самом деле, я собираюсь создать хранимую процедуру, используя этот запрос. Затем внутреннее приложение (Spring Boot) вызовет этот процесс для получения данных. Плохо, я запутался, я думал, что это таблица, а не представление, запрос, который вы упомянули выше. Я считаю, что мне нужно больше рассказать о MATERIALIZED VIEW, как вы упомянули, это было бы точным решением для этого. Я ценю ваши комментарии помощи, чтобы понять основы.

— 09.01.2023 11:05

09.01.2023 06:27

Ответ принят как подходящий

вы можете использовать CASE и Union All следующим образом

select 
table1.employee_code as emp_code,
case employee_status = 'PENDING' then count(table1.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table1.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table1.employee_code)  else 0 end as REJECTED   
from FROM table1
GROUP BY emp_code
UNION ALL
select 
table2.employee_code as emp_code,
case employee_status = 'PENDING' then count(table2.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table2.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table2.employee_code)  else 0 end as REJECTED   
from FROM table2
GROUP BY emp_code
UNION ALL
select 
table3.employee_code as emp_code,
case employee_status = 'PENDING' then count(table3.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table3.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table3.employee_code)  else 0 end as REJECTED   
from FROM table3
GROUP BY emp_code

Или используя SUM и Case

SELECT 
table1.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table1
GROUP BY emp_code
UNION ALL
SELECT 
table2.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table2
GROUP BY emp_code
UNION ALL
SELECT 
table3.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table3
GROUP BY emp_code

Основную часть стоимости реализации проекта составляют затраты на техническое обслуживание. Найдите правильную абстракцию. Не. Повторить. Сам. СУХОЙ .

— 09.01.2023 07:52

ценю ваше уведомление @J_H. но желаемый результат доступен только с запросом

— 09.01.2023 07:57

Несмотря на то, что есть проблема с производительностью и обслуживанием, я ожидал именно этого запроса. Я также согласен с @J_H, но с текущей структурой таблицы мой руководитель ожидает только запроса.

— 09.01.2023 10:39

09.01.2023 06:39