Вопрос:

Как объединить результаты нескольких таблиц на основе одного столбца (через UNION ALL)

sql teradata union-all

55 просмотра

7 ответа

18 Репутация автора

Хорошо, оператор SQL, который я написал, прекрасно работает, но я хотел бы сделать его менее громоздким и использовать только один ACCEPTANCE_DATE, поскольку он одинаков во всех таблицах.

Я пытаюсь объединить результаты нескольких таблиц с помощью оператора UNION ALL. Приведенный ниже пример работает отлично.

SEL COUNT(*)FROM
MY_DATABASE.HUMAN_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.FINANCIAL_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.INFRASTRUCTURE_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09';

Все таблицы имеют столбцы одинакового типа, например, каждая таблица имеет столбец с именем «ACCEPTANCE_DATE». Результат, который я получаю, правильный. Тем не менее, я объединяю множество таблиц в одном запросе (используя UNION ALL) и мне интересно, есть ли способ преобразовать этот запрос, чтобы мне не приходилось обновлять ACCEPTANCE_DATE = '2015-08-09' в каждом операторе select , В идеале я хотел бы определить это только один раз, особенно когда я использую более 30 предложений UNION ALL, например

SEL * FROM
    (SEL COUNT(*)FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09'; 

Приведенный ниже оператор извлекает данные.

SEL COUNT(*)FROM
MY_DATABASE.HUMAN_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.FINANCIAL_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.INFRASTRUCTURE_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09';

Как описано выше, я хотел бы, чтобы это было что-то вроде ниже:

SEL * FROM
    (SEL COUNT(*)FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09';
Автор: Andy Источник Размещён: 09.08.2019 07:02

Ответы (7)


2 плюса

56 Репутация автора

Может быть, это то, что вы ищете:

SEL COUNT(*) FROM
    (SEL ACCEPTANCE_DATE, 1 AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 2 AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 3 AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ORIGIN;

Вы можете даже дать ORIGIN несколько более значимых имен и показать их потом:

SEL ORIGIN, COUNT(*) FROM
    (SEL ACCEPTANCE_DATE, 'HUMAN' AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'FINANCIAL' AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'INFRASTRUCTURE' AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ORIGIN;

Что приводит к двум столбцам. Теперь, это все еще не решает проблему с не отображаемым третьим значением, но таким образом вы знаете, какие значения отсутствуют, и можете легко отличить, какие должны быть нулями. Если этого недостаточно для вас, тогда код становится противнее. Я мог бы придумать решение позже.


Чтобы решить проблему отсутствия записи для пустых таблиц, я подумал о двух возможных решениях. Выбор зависит от того, будет ли операция выполнена один раз или это повторяющееся действие или только одно временное событие. Если вы планируете делать это несколько раз, было бы неплохо создать в базе данных таблицу со всеми именами исходных таблиц (или с некоторыми ярлыками). Для этого минимального примера давайте рассмотрим такую ​​вещь под именем SOURCE_TABLES:

SELECT RESOURCE FROM SOURCE_TABLES
/*
    RESOURCE:
    HUMAN
    FINANCIAL
    INFRASTRUCTURE
*/

В этом случае ранее предоставленный скрипт нуждается в небольшой модификации:

SEL ST.RESOURCE, COUNT(T1.ACCEPTANCE_DATE) FROM SOURCE_TABLES ST
    LEFT JOIN (SEL ACCEPTANCE_DATE, 'HUMAN' AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'FINANCIAL' AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'INFRASTRUCTURE' AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
    ON ST.RESOURCE = T1.ORIGIN
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ST.RESOURCE;

Здесь, используя LEFT JOIN, вы гарантируете, что каждая запись из таблицы присутствует в выходных данных, даже если в T1 нет строк с указанным источником. COUNT (T1.ACCEPTANCE_DATE) использует тот факт, что значения NULL не суммируются со счетчиком.

Теперь, если по какой-либо причине вам не нравится идея создания таблицы (вы не можете создать объект в базе данных или это слишком хлопотно для одного действия), вы можете придерживаться идеи чисел, которые проще генерируется на лету. Приведенное ниже решение использует ту же идею, что и выше, но является более гибким с точки зрения количества таблиц, из которых оно считывает, и, очевидно, не требует создания дополнительной таблицы. Учитывая, что вы упомянули 30 таблиц, это может быть лучшим вариантом. Можно утверждать, что это менее читабельно, хотя:

WITH numbers AS (
SEL 1 AS number
UNION ALL
SEL number + 1 FROM numbers WHERE number + 1 <= 3 -- Change 3 to the number of sourcing tables
), input_merged AS ( -- if we already use the WITH clause we can do so for merging input. It's more readable
SEL ACCEPTANCE_DATE, 1 AS ORIGIN FROM MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
SEL ACCEPTANCE_DATE, 2 AS ORIGIN FROM MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
SEL ACCEPTANCE_DATE, 3 AS ORIGIN FROM MY_DATABASE.INFRASTRUCTURE_RESOURCES
-- add further sources accordingly...
)
SEL COUNT(ACCEPTANCE_DATE) FROM numbers n
    LEFT JOIN input_merged im ON n.number = im.ORIGIN
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY n.number;

Это должно дать первый запрашиваемый и желаемый результат.

Что касается числовой части в выражении WITH, вы можете обратиться к этому , обратите внимание, что в этом решении я использовал WITH, чтобы также объединить ввод, как это сделал Кристоф. Если вы используете базу данных ORACLE, лучше использовать CONNECT BY LEVEL для создания последовательности чисел.

Надеюсь, теперь вы можете достичь того, что вы хотели!

Автор: TheDecks Размещён: 09.08.2019 07:15

1 плюс

2033 Репутация автора

Не уверен, что это стандартный SQL или просто синтаксис Microsoft, но в Microsoft SQL Server вы можете сделать это следующим образом:

WITH PreSelect AS (
  SELECT ACCEPTANCE_DATE FROM HUMAN_RESOURCES
  UNION ALL 
  SELECT ACCEPTANCE_DATE FROM FINANCIAL_RESOURCES
  UNION ALL 
  SELECT ACCEPTANCE_DATE FROM INFRASTRUCTURE_RESOURCES
)
SELECT COUNT(*) FROM PreSelect WHERE ACCEPTANCE_DATE = '2015-08-09';

Или с источником, как предлагает TheDecks, если вам нужно каждое значение отдельно.

Автор: Christoph Размещён: 09.08.2019 07:22

1 плюс

2033 Репутация автора

2-я попытка:

WITH PreSelect AS (
  SELECT 'Infrastructure Resources' AS Origin, ACCEPTANCE_DATE FROM INFRASTRUCTURE_RESOURCES
  UNION ALL 
  SELECT 'Human Resources' AS Origin, ACCEPTANCE_DATE FROM HUMAN_RESOURCES
  UNION ALL 
  SELECT 'Financial Resources' AS Origin, ACCEPTANCE_DATE FROM FINANCIAL_RESOURCES
)
SELECT Origin, COUNT(*) FROM PreSelect 
WHERE ACCEPTANCE_DATE = '2015-08-09' 
GROUP BY Origin
ORDER BY 2 DESC;

Эта версия не суммирует ее и предоставляет говорящие метки и упорядочивает их по наибольшему убыванию.

Автор: Christoph Размещён: 09.08.2019 08:24

0 плюса

152 Репутация автора

Да, cte - лучший вариант для этого ... В CTE он фильтрует данные, используя предложение where из результата (объединение всех)

Автор: vijay sahu Размещён: 10.08.2019 02:57

0 плюса

2033 Репутация автора

3-я попытка:

WITH PreSelect AS (
  SELECT 'Infrastructure Resources' AS Origin, ACCEPTANCE_DATE FROM INFRASTRUCTURE_RESOURCES
  UNION ALL 
  SELECT 'Human Resources' AS Origin, ACCEPTANCE_DATE FROM HUMAN_RESOURCES
  UNION ALL 
  SELECT 'Financial Resources' AS Origin, ACCEPTANCE_DATE FROM FINANCIAL_RESOURCES
), 
Categories AS (
  SELECT DISTINCT Origin FROM PreSelect
),
ReferenceDate AS (
    SELECT Origin, COUNT(*) RecordCount FROM PreSelect 
    WHERE ACCEPTANCE_DATE = '2015-08-09' 
    GROUP BY Origin
)
SELECT c.Origin, ISNULL(rd.RecordCount, 0) AS RecordCount FROM Categories c
LEFT OUTER JOIN ReferenceDate rd ON  c.Origin = rd.Origin 
ORDER BY 2 DESC;

Таким же образом появляются строки с 0 записями ...

Автор: Christoph Размещён: 10.08.2019 06:56

0 плюса

1086 Репутация автора

Если ваша проблема заключается в том, что вы хотите определить только ACCEPTANCE_DATEодин раз, то вы можете сохранить свой исходный SQL и использовать макрос или SP и параметризовать ACCEPTANCE_DATEв качестве входного значения.

Если вы хотите переписать SQL, попробуйте что-то вроде этого:

SELECT MyCount FROM (
  SELECT ACCEPTANCE_DATE, MyCount
  FROM (
    SELECT ACCEPTANCE_DATE, COUNT(*) AS MyCount
    FROM MY_DATABASE.HUMAN_RESOURCES
    GROUP BY ACCEPTANCE_DATE
  )

  UNION ALL

  SELECT ACCEPTANCE_DATE, MyCount
  FROM (
    SELECT ACCEPTANCE_DATE, COUNT(*) AS MyCount
    FROM MY_DATABASE.FINANCIAL_RESOURCES
    GROUP BY ACCEPTANCE_DATE
  )

  UNION ALL

  SELECT ACCEPTANCE_DATE, MyCount
  FROM (
    SELECT ACCEPTANCE_DATE, COUNT(*) AS MyCount
    FROM MY_DATABASE.INFRASTRUCTURE_RESOURCES
    GROUP BY ACCEPTANCE_DATE
  )
) src
WHERE ACCEPTANCE_DATE = '2015-08-09';

Скорее всего, это будет не очень хорошо, если в этих таблицах будет много строк, если только у вас не определена оптимизация, например, PPI для ACCEPTANCE_DATEполей.

Я не проверял это, поэтому у вас могут быть некоторые синтаксические ошибки, но они должны получить то, что вы хотите.

Автор: ravioli Размещён: 11.08.2019 08:31

0 плюса

47034 Репутация автора

Вы можете использовать Макрос так, как предложил @ravioli:

REPLACE MACRO my_counts(inDate DATE) AS
 (
   SELECT 'HUMAN_RESOURCES' AS tab, Count(*)
   FROM MY_DATABASE.HUMAN_RESOURCES
   WHERE ACCEPTANCE_DATE=:inDate
   UNION ALL
   SELECT 'FINANCIAL_RESOURCES', Count(*)
   FROM MY_DATABASE.FINANCIAL_RESOURCES
   WHERE ACCEPTANCE_DATE=:inDate
   UNION ALL
   SELECT 'INFRASTRUCTURE_RESOURCES', Count(*)
   FROM MY_DATABASE.INFRASTRUCTURE_RESOURCES
   WHERE ACCEPTANCE_DATE=:inDate;
 );

EXEC my_counts(DATE '2015-08-09');

Создайте этот макрос либо в базе данных, где у вас есть права на создание макроса, либо в вашем собственном пользователе (но никто другой, кроме вас, не сможет его использовать).

Или вы используете общее табличное выражение для определения даты:

WITH cte AS 
 (
   SELECT DATE '2015-08-09' AS ACCEPTANCE_DATE
 )
SELECT 'HUMAN_RESOURCES' AS tab, Count(*)
FROM MY_DATABASE.HUMAN_RESOURCES
WHERE ACCEPTANCE_DATE=(SELECT ACCEPTANCE_DATE FROM cte)
UNION ALL
SELECT 'FINANCIAL_RESOURCES', Count(*)
FROM MY_DATABASE.FINANCIAL_RESOURCES
WHERE ACCEPTANCE_DATE=(SELECT ACCEPTANCE_DATE FROM cte)
UNION ALL
SELECT 'INFRASTRUCTURE_RESOURCES', Count(*)
FROM MY_DATABASE.INFRASTRUCTURE_RESOURCES
WHERE ACCEPTANCE_DATE=(SELECT ACCEPTANCE_DATE FROM cte);

(SELECT ACCEPTANCE_DATE FROM cte)Будет выполняться один раз , а затем передается в качестве параметра для каждого Select.

Автор: dnoeth Размещён: 11.08.2019 11:40
Вопросы из категории :
32x32