Статьи

Как использовать SQL PIVOT для сравнения двух таблиц в вашей базе данных

Февраль 6, 2019

Это может случиться очень легко. Вы адаптируете таблицу, добавляя новый столбец:

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 .
84
Share: