Как согласовать две таблицы в SQL, если количество строк совпадает

1. Проверка, которая проходит и ничего не доказывает

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

Почти все говорят о количестве строк, и вот оно.

source_rows | target_rows
         16 |          16

Шестнадцать против шестнадцати. В большинстве случаев миграции именно на этом проверка прекращается, заявка закрывается, а проблема всплывает через шесть недель в финансовом отчете.

Теперь еще один запрос к тем же двум таблицам.

source_revenue | target_revenue | difference
          9890 |           9490 |        400

Четыреста не хватает, у экземпляра с нужным количеством рядов. Строки и значения — это две разные вещи, и счетчик может ответить только на вопрос о строках.

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

2. Матч по ключу в обоих направлениях.

Для сравнения строк вам нужен ключ: столбец или набор столбцов, который идентифицирует один и тот же реальный объект в обеих таблицах. Тогда полное внешнее соединение по этому ключу, который сохраняет строки, которые не совпадают с любой стороны, и один взгляд на то, где находятся нули.

SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       CASE WHEN t.order_id IS NULL THEN 'source only'
            WHEN s.order_id IS NULL THEN 'target only' END AS where_it_is
FROM orders s
FULL OUTER JOIN orders_target t USING (order_id)
WHERE s.order_id IS NULL OR t.order_id IS NULL;

1008 | source only
1017 | target only

Приказ 1008 существует в исходнике, а не в копии: он утерян. Заказ 1017 существует в копии, а не в исходнике: он был придуман, что обычно означает, что загрузка выполнялась дважды или тестовая запись ускользнула.

Один проиграл, другой выиграл, так что счет стал равным. Это не совпадение, придуманное для этой страницы; это обычная форма неудачного повтора, и именно поэтому граф согласился.

Здесь всю работу выполняет полное внешнее соединение, и стоит пояснить, почему внутреннее соединение не работает: внутреннее соединение сохраняет только совпадающие строки, а это набор, который не содержит ни одной из ваших проблем. Если объединения еще не выполняются автоматически, руководство по объединениям покрывает, какие строки сохраняет каждый вид.

В некоторых двигателях отсутствует FULL OUTER JOIN. Портативная замена — это два антисоединения, по одному в каждом направлении, в сочетании с UNION ALL, с использованием NOT EXISTS скорее, чем NOT IN по причине в НУЛЕВОЕ руководство.

3. Сравните значения в совпадающих строках.

Строки, присутствующие с обеих сторон, все равно могут не совпадать, поэтому объедините их и сравните каждый столбец.

SELECT s.order_id, s.region, t.region, s.units, t.units
FROM orders s JOIN orders_target t USING (order_id)
WHERE s.region     IS DISTINCT FROM t.region
   OR s.units      IS DISTINCT FROM t.units
   OR s.unit_price IS DISTINCT FROM t.unit_price
   OR s.product    IS DISTINCT FROM t.product
   OR s.rep        IS DISTINCT FROM t.rep
   OR s.order_date IS DISTINCT FROM t.order_date;

1003 | East | east | 3 | 3
1010 | East | East | 5 | 4

Два ряда. Приказ 1003 имеет те же номера и другое написание региона. Приказ 1010 имеет пять единиц в исходнике и четыре в копии, куда и пошли недостающие 400: один стол в 220 стал четырьмя единицами вместо пяти, потеря 220, плюс потерянные 480 в приказе 1008 и изобретенные 300 в приказе 1017. Эту арифметику стоит произнести вслух, потому что способность точно объяснить число 400 — это разница между открытием и беспокойством: 480 снаружи, 300 внутри, 220 выбриты, что в сумме дает 400.

IS DISTINCT FROM это оператор, который следует использовать, а не <>, и разница имеет значение. Равнина <> сравнение с нулевым значением возвращает значение «неизвестно», а не «истина», поэтому значение, которое было заполнено в источнике, и значение, равное нулю в копии, не будет помечено. IS DISTINCT FROM рассматривает нулевой как значение, как и любое другое, и возвращает Верно для этого случая, а это именно то, что нужно для сверки.

4. Одно резюме, которое говорит все

Два запроса выше — это то, на что вы смотрите; это то, что вы сообщаете. Одна строка, четыре числа, и они должны сложиться.

matched | changed | source_only | target_only | total_keys
     13 |       2 |           1 |           1 |         17

Тринадцать рядов одинаковы с обеих сторон. Двое существуют на обоих и не согласны. Один только в источнике и один только в цели. Было задействовано семнадцать различных ключей, что больше, чем есть в каждой таблице, поскольку объединение двух таблиц из шестнадцати строк, отличающихся на одну строку в каждую сторону, равно семнадцати.

Проверьте это в обоих направлениях: 13 + 2 + 1 только источник равен 16, количество источников. 13 + 2 + 1 только цель равна 16, количество целей. Если эти два сложения не соответствуют двум подсчетам строк, ключ не уникален, и об этом рассказывается в десятом разделе.

Эта таблица является результатом. «С миграцией все в порядке» — это не утверждение, на основании которого можно действовать; «Тринадцать строк идентичны, две изменены, одна потеряна, одна добавлена, а чистая разница в доходах составляет 400» — это записка о передаче, и для ее выполнения требуется один запрос.

5. Столбец за столбцом

Как только вы узнаете, что две строки не совпадают, следующий полезный вопрос — в каких столбцах, потому что это указывает на причину, а не на симптом.

order_date | rep | region | product | units | unit_price
         0 |   0 |      1 |       0 |     1 |          0

Одно разногласие в регионе и одно в подразделениях, и больше нигде. Эта форма сообщает вам то, чего нет в списке плохих строк: это не разрывная загрузка, поскольку разрывная загрузка повреждает столбец во многих строках. Два разных столбца, каждый из которых ошибочен в одной строке, выглядят как два отдельных инцидента, вероятно, ручное редактирование и транскрипция.

Сравните это с формой, которую вы бы увидели, если бы, скажем, каждая дата была сдвинута на день: order_date будет читаться 16, а все остальное 0, и вы будете смотреть на часовые пояса, а не на строки. Тот же запрос, совершенно другой вывод, и их отличает количество столбцов, а не список строк.

Скажите вслух, что бы вы заподозрили, если бы unit_price вернулся как 14 из 16. При загрузке применялось округление или валютная разница, и вам приходилось читать код загрузки, а не данные.

6. Разницы, которую не увидит знак равенства

Перед пояснением: регион по порядку 1003 — «Восток» в одной таблице и «Восток» в другой. Скажите, замечает ли сравнение SQL.

'East' = 'east'                  false
LOWER('East') = LOWER('east')    true

В этом движке сравнение строк чувствительно к регистру, поэтому оно было уловлено. Не каждый механизм сортировки или сортировки столбцов ведет себя таким образом, и при сортировке без учета регистра эта строка будет указана как совпадающая. Какой общий урок: примирение столь же строго, как и его сравнение. Прежде чем доверять чистому результату, проверьте само сравнение на известной разнице.

При наивном сравнении обычно ускользают четыре вида различий. Случай, как указано выше. Завершающий пробел, где «Север» и «Север» отличаются для компьютера, а не для человека. Числовая точность: 10,00 и 10,004 отображаются как 10,00. И даты, несущие в себе время, где один и тот же день в разные часы сравнивается неодинаково.

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

region | source_rows | target_rows
East   |           4 |           3
North  |           5 |           5
South  |           4 |           4
West   |           3 |           3
east   |        NULL |           1

Появился пятый регион. Общие итоги по-прежнему равны шестнадцати, все отдельные строки присутствуют, и теперь в отчете есть категория, о которой никто никогда не слышал. Это тот же режим отказа, что и для любого почти повторяющегося значения, и процедура очистки для него находится в режиме найти персонажа, которого не видишь.

7. Отпечатки пальцев: по одному номеру на стол.

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

Более дешевый ход — это отпечаток пальца: хэшируйте каждую строку в число и складывайте числа. При сложении не учитывается порядок, поэтому две таблицы, содержащие одинаковые строки в разном порядке, дают одинаковую сумму.

SELECT SUM(HASH(order_id, order_date, rep, region, product, units, unit_price)) FROM orders;

source  128147106948374048609
target  136663960016580492445

Разные, поэтому таблицы различаются, и для каждой стороны потребовался один проход, при этом ничего не переносилось, кроме одного числа. Это ничего не говорит вам о том, где именно ведется торговля.

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

region | same
East   | false
North  | true
South  | true
West   | false
east   | false

Север и Юг доказуемо идентичны и не нуждаются в дополнительной проверке. Все по-другому на Востоке, Западе и в призрачном регионе. В большой таблице вы берете отпечатки пальцев по месяцам или по диапазону ключей, находите несколько сегментов, которые отличаются, и только затем выполняете сравнение на уровне строк. В этом вся стратегия: дешево сравнить, сузить, а потом присмотреться.

Одно предостережение. Хэш-функции различаются в зависимости от движка, поэтому отпечаток пальца, вычисленный в Постгрес, не будет совпадать с отпечатком пальца, вычисленным в SQL Server, а для сравнения между системами требуется хэш, который могут вычислить оба, обычно MD5 объединенной, последовательно отформатированной строки. Большая часть этой работы — добиться идентичного форматирования с обеих сторон.

8. Что делать с найденным

Примирение, которое заканчивается списком разногласий, — это полдела. У каждой категории свой следующий ход.

И записывайте то, что вы нашли, даже если оно небольшое, потому что для человека, получающего систему, важно число, а не количество строк. «Четыреста из 9890, что составляет 4,0%, в две строки, оба объяснены» — это предложение, которое кто-то может подписать. Пробный прогон миграции существует для того, чтобы выработать именно это предложение, прежде чем кто-либо станет зависеть от новой системы, и руководство по пробному запуску вот где это вписывается в более широкий процесс.

Крайние случаи, которые нарушают примирение

Шесть, которые стоит знать.

Ключ не уникален. Если у какой-либо стороны есть ключ дважды, соединение умножается, и каждый последующий подсчет будет неправильным. Сначала проверьте обе стороны: COUNT(*) против COUNT(DISTINCT key), на каждом столе, прежде чем присоединиться к чему-либо.

Ключ не тот ключ. Перенумерация систем. Если цель назначила свои собственные идентификаторы, вы сопоставляете естественный ключ, а если такового нет, вы перешли в Разрешение объекта.

Законные различия. Цель может намеренно исключать отмененные записи или хранить другой формат даты. Прежде чем проводить сравнение, согласуйте ожидаемые различия в письменном виде, иначе вы сообщите о них как об ошибках.

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

Плавающая точка. Две системы могут вычислить одинаковую сумму и не согласиться в пятнадцатом десятичном знаке. Прежде чем сравнивать деньги, округлите их до точности, которая важна для бизнеса.

Нулевые значения по сравнению с неправильным оператором. <> молча пропускает изменение значения на нулевое. Использовать IS DISTINCT FROM, а если в вашем движке его нет, COALESCE обе стороны к контрольному значению, которое не может встречаться в данных.