Это может случиться очень легко. Вы адаптируете таблицу, добавляя новый столбец:
1
|
ALTER TABLE payments ADD code NUMBER(3); |
Вы продолжаете, реализуя свою бизнес-логику — абсолютно никаких проблем. Но затем, позже (возможно, в производстве), некоторые пакетные задания не выполняются, потому что они делают серьезные предположения о типах данных. А именно, предполагается, что две таблицы payments
и payments_archive
имеют одинаковый тип строки:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
CREATE TABLE payments ( id NUMBER(18) NOT NULL , account_id NUMBER(18) NOT NULL , value_date DATE , amount NUMBER(25, 2) NOT NULL ); CREATE TABLE payments_archive ( id NUMBER(18) NOT NULL , account_id NUMBER(18) NOT NULL , value_date DATE , amount NUMBER(25, 2) NOT NULL ); |
Имея один и тот же тип строки, вы можете просто переместить строку из одной таблицы в другую, например, используя такой запрос:
1
2
3
|
INSERT INTO payments_archive SELECT * FROM payments WHERE value_date < SYSDATE - 30; |
(не то, чтобы использование приведенного выше синтаксиса было хорошей идеей в целом, на самом деле это плохая идея. но вы поняли)
То, что вы получаете сейчас, это:
1
|
ORA-00913: too many values |
Исправление очевидно, но, вероятно, бедная душа, которая должна это исправить, — это не вы, а кто-то еще, кто должен выяснить среди, возможно, сотен столбцов, которые не совпадают. Вот как (в Oracle):
Используйте PIVOT для сравнения двух таблиц!
Конечно, вы можете не использовать PIVOT
и просто выбрать все столбцы из любой таблицы из представлений словаря:
1
2
3
4
5
|
SELECT table_name, column_name FROM all_tab_cols WHERE table_name LIKE 'PAYMENTS%' |
Это даст следующий результат:
01
02
03
04
05
06
07
08
09
10
11
|
TABLE_NAME COLUMN_NAME ------------------ --------------- PAYMENTS ID PAYMENTS ACCOUNT_ID PAYMENTS VALUE_DATE PAYMENTS AMOUNT PAYMENTS CODE PAYMENTS_ARCHIVE ID PAYMENTS_ARCHIVE ACCOUNT_ID PAYMENTS_ARCHIVE VALUE_DATE PAYMENTS_ARCHIVE AMOUNT |
Не очень читабельно. Конечно, вы можете использовать операции над множествами и применять INTERSECT
и MINUS
( EXCEPT
) для фильтрации соответствующих значений. Но намного лучше
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
SELECT * FROM ( SELECT table_name, column_name FROM all_tab_cols WHERE table_name LIKE 'PAYMENTS%' ) PIVOT ( COUNT (*) AS cnt FOR (table_name) IN ( 'PAYMENTS' AS payments, 'PAYMENTS_ARCHIVE' AS payments_archive ) ) t; |
И вышесказанное теперь производит:
1
2
3
4
5
6
7
|
COLUMN_NAME PAYMENTS_CNT PAYMENTS_ARCHIVE_CNT ------------ ------------ -------------------- CODE 1 0 ACCOUNT_ID 1 1 ID 1 1 VALUE_DATE 1 1 AMOUNT 1 1 |
Теперь очень легко определить столбец, который отсутствует в таблице PAYMENTS_ARCHIVE
. Как видите, результат исходного запроса дает одну строку на столбец AND на таблицу. Мы взяли этот результат и повернули его «FOR» для имени таблицы, так что теперь мы получим только одну строку на столбец
Как читать PIVOT?
Это просто. Комментарии встроены:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SELECT * -- This is the table that we're pivoting. Note that -- we select only the minimum to prevent side-effects FROM ( SELECT table_name, column_name FROM all_tab_cols WHERE table_name LIKE 'PAYMENTS% ' ) -- PIVOT is a keyword that is applied to the above -- table. It generates a new table, similar to JOIN PIVOT ( -- This is the aggregated value that we want to -- produce for each pivoted value COUNT(*) AS available -- This is the source of the values that we want to -- pivot FOR (table_name) -- These are the values that we accept as pivot -- columns. The columns names are produced from -- these values concatenated with the corresponding -- aggregate function name IN ( ' PAYMENTS ' AS payments, ' PAYMENTS_ARCHIVE' AS payments_archive ) ) t; |
Вот и все. Не так сложно, правда?
Хорошая вещь в этом синтаксисе состоит в том, что мы можем сгенерировать столько дополнительных столбцов, сколько захотим, очень легко:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
SELECT * FROM ( SELECT table_name, column_name, cast (data_type as varchar (6)) data_type FROM all_tab_cols WHERE table_name LIKE 'PAYMENTS%' ) PIVOT ( COUNT (*) AS cnt, MAX (data_type) AS type -- new function here FOR (table_name) IN ( 'PAYMENTS' AS p, 'PAYMENTS_ARCHIVE' AS a ) ) t; |
… Производящий (после дополнительного ошибочного DDL)…
1
2
3
4
5
6
7
|
COLUMN_NAME P_CNT P_TYPE A_CNT A_TYPE ----------- ---------- ------ ---------- ------ CODE 1 NUMBER 0 ACCOUNT_ID 1 NUMBER 1 NUMBER ID 1 NUMBER 1 NUMBER VALUE_DATE 1 DATE 1 TIMESTAMP AMOUNT 1 NUMBER 1 NUMBER |
Таким образом, мы можем обнаружить еще больше недостатков между различными типами строк таблиц. В приведенном выше примере мы использовали MAX()
, потому что мы должны предоставить функцию агрегирования, даже если каждый поворотный столбец соответствует ровно одной строке в нашем примере — но это не обязательно.
Что если я не использую Oracle?
SQL Server также поддерживает PIVOT, но другие базы данных не поддерживают. Вы всегда можете эмулировать PIVOT
используя GROUP BY
и CASE
. Следующее утверждение эквивалентно предыдущему:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
|
SELECT t.column_name, count ( CASE table_name WHEN 'PAYMENTS' THEN 1 END ) p_cnt, max ( CASE table_name WHEN 'PAYMENTS' THEN data_type END ) p_type, count ( CASE table_name WHEN 'PAYMENTS_ARCHIVE' THEN 1 END ) a_cnt, max ( CASE table_name WHEN 'PAYMENTS_ARCHIVE' THEN data_type END ) a_type FROM ( SELECT table_name, column_name, data_type FROM all_tab_cols WHERE table_name LIKE 'PAYMENTS%' ) t GROUP BY t.column_name; |
Этот запрос теперь будет давать тот же результат для всех других баз данных.
Разве это не …?
Да, это! Вышеупомянутое использование агрегатных функций в сочетании с CASE
может быть сокращено еще больше, используя стандартное предложение SQL FILTER
о котором мы недавно писали в блоге .
Итак, в PostgreSQL вы можете написать следующий запрос:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
|
SELECT t.column_name, count (table_name) FILTER ( WHERE table_name = 'payments' ) p_cnt, max (data_type) FILTER ( WHERE table_name = 'payments' ) p_type, count (table_name) FILTER ( WHERE table_name = 'payments_archive' ) a_cnt, max (data_type) FILTER ( WHERE table_name = 'payments_archive' ) a_type FROM ( SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name LIKE 'payments%' ) t GROUP BY t.column_name; |
Ссылка: | Как использовать SQL PIVOT для сравнения двух таблиц в вашей базе данных от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ . |