Статьи

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

Апрель 25, 2018

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

ALTER TABLE payments ADD code NUMBER(3);

Вы продолжаете реализовывать свою бизнес-логику — абсолютно никаких проблем. Но затем, позже (возможно, в производстве), некоторые пакетные задания не выполняются, потому что они делают серьезные предположения о типах данных. А именно, предполагается, что две таблицы paymentsи payments_archiveимеют одинаковый тип строки:

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
  );

Имея один и тот же тип строки, вы можете просто переместить строку из одной таблицы в другую, например, используя такой запрос:

INSERT INTO payments_archive
SELECT * FROM payments
WHERE value_date < SYSDATE - 30;

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

То, что вы получаете сейчас, это:

ORA-00913: too many values

Исправление очевидно, но, вероятно, бедная душа, которая должна это исправить, — это не вы, а кто-то еще, кто должен выяснить среди, возможно, сотен столбцов, которые не совпадают. Вот как (в Oracle):

Используйте PIVOT для сравнения двух таблиц!

Конечно, вы можете не использовать PIVOTи просто выбрать все столбцы из любой таблицы из представлений словаря:

SELECT
  table_name,
  column_name
FROM all_tab_cols
WHERE table_name LIKE 'PAYMENTS%'

Это даст следующий результат:

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

Не очень читабельно. Конечно, вы можете использовать операции set и apply INTERSECTи MINUS( EXCEPT), чтобы отфильтровать совпадающие значения. Но намного лучше

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;

И вышесказанное теперь производит:

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?

Это просто. Комментарии встроены:

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;

Вот и все. Не так сложно, правда?

Хорошая вещь в этом синтаксисе состоит в том, что мы можем сгенерировать столько дополнительных столбцов, сколько захотим, очень легко:

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)…

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. Следующее утверждение эквивалентно предыдущему:

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может быть сокращено еще больше, используя стандартное FILTERпредложение SQL , о котором мы недавно писали в блоге .

Итак, в PostgreSQL вы можете написать следующий запрос:

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. Читайте о:

35
Share: