Join
Alex Compass
@alexbrin
SQL
January 13

Расхождения между таблицами

Поиск расхождений между таблицами

Способ 1

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

Есть 2 таблицы: band_extended и album. Эти 2таблицы содержат количество альбомов. И это количество альбомов должно совпадать в обеих таблицах.

SELECT COUNT(*) FROM album;
-- 121 918
SELECT SUM(n_albums) FROM band_extended;
-- 121 918

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

Как выглядят расхождения:

SELECT COUNT(*) FROM album WHERE band_id = -100;

Для группы с band_id (-100) в таблице album музыкальных альбомов 0.

SELECT n_albums FROM band_extended WHERE band_id = -100;

А в таблице band_extended количество альбомов 25.

Так и для группы с band_id 93:

SELECT * FROM album WHERE band_id = 93;
-- 11
SELECT n_albums FROM band_extended WHERE band_id = 93;
-- NULL

И третье расхождение:

SELECT * FROM album WHERE band_id IN (
SELECT band_id FROM band WHERE name = 'Queen');
-- 14
SELECT n_albums FROM band_extended 
WHERE band_id IN (
SELECT band_id FROM band WHERE name = 'Queen');
-- NO ROWS, 0

Ситуация легкая, потому что мы заранее знаем номера band_id, в которых идет расхождение.

Представим, что мы не знаем, где расхождение. И сейчас пошагово будем это выяснять.

ШАГ 1 - Сделать группировку строк, чтобы найти количество альбомов для каждой музыкальной группы. Это нужно для того, чтобы потом сравнивать 2 таблицы между собой в едином формате.

SELECT band_id, COUNT(*) as album_count FROM album GROUP BY 1;

Мы получили результат, где для каждой band_id видно количество альбомов.

ШАГ 2 - Выведем данные в таком же формате из таблицы band_extended. Но так как в таблице band_extended есть группы, у которых нет альбомов - их учитывать не нужно

SELECT band_id, n_albums as album_count 
FROM band_extended WHERE n_albums > 0;

ШАГ 3 - Сделаем сравнение двух таблиц с помощью EXCEPT (вычитание первого запроса из второго запроса)

SELECT band_id, COUNT(*) as album_count 
FROM album GROUP BY 1
EXCEPT
SELECT band_id, n_albums as album_count 
FROM band_extended WHERE n_albums > 0;

И вот мы получили те строки, в которых есть расхождения:

ШАГ 4 - Сделаем сравнение двух таблиц с помощью EXCEPT (вычитание первого запроса из второго запроса), но только сейчас первую таблицу вычитаем из второй

SELECT band_id, n_albums as album_count 
FROM band_extended WHERE n_albums > 0
EXCEPT
SELECT band_id, COUNT(*) as album_count 
FROM album GROUP BY 1;

Но это не очень удобный способ т.к. допустим, есть ситуация, когда band_id совпадает и есть в обеих таблицах, но количество альбомов разное. Удобнее, если разместить 2 значения albom_count рядом друг с другом.

Способ 2 (Более удобный, продолжение первого способа)

SELECT * 
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id;
и тд

Так как нам нужны только расхождения в таблице, дописываем условие WHERE:

WHERE table_1.album_count <> table_2.album_count

И запускаем весь запрос:

SELECT * 
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id
WHERE table_1.album_count <> table_2.album_count;

В итоге не получили ни одной строки. Проблема в значениях NULL.

Чтобы избежать проблем с опусканием строк со значениями NULL дописываем условие:

OR (table_1.album_count IS NULL AND table_2.album_count IS NOT NULL)
OR (table_1.album_count IS NOT NULL AND table_2.album_count IS NULL)

И запустим весь запрос:

SELECT * 
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id
WHERE table_1.album_count <> table_2.album_count
OR (table_1.album_count IS NULL AND table_2.album_count IS NOT NULL)
OR (table_1.album_count IS NOT NULL AND table_2.album_count IS NULL);

Все вроде здорово, мы получили все 3 расхождения.

Но для band_id = 93 видим, что якобы строки есть только в таблице album. Но это не так, хотя часть таблицы band_extended показывает, что значения NULL.

Произошло это потому что в первом подзапросе WHERE n_albums > 0. И это условие как раз отбросило строку band_id = 93 для таблицы band_extended. Потому что для этой строки в колонке n_albums значение NULL. И когда мы пишем n_albums > 0, мы отбрасываем это значение.

Поэтому нужно добавить еще одно условие в условие WHERE n_albums > 0, а именно:

OR n_albums IS NULL
SELECT * 
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0 OR n_albums IS NULL
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id
WHERE table_1.album_count <> table_2.album_count
OR (table_1.album_count IS NULL AND table_2.album_count IS NOT NULL)
OR (table_1.album_count IS NOT NULL AND table_2.album_count IS NULL);

Устранение расхождений между таблицами

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

Первое, что мы сделаем - это создадим таблицу на основе запроса, который мы делали последним в предыдущем уроке

CREATE TABLE table_comperison AS
SELECT table_1.band_id as band_id_band_extended,
       table_1.album_count as album_count_band_extended,
	   table_2.band_id as band_id_album,
       table_2.album_count as album_count_album
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0 OR n_albums IS NULL
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id
WHERE table_1.album_count <> table_2.album_count
OR (table_1.album_count IS NULL AND table_2.album_count IS NOT NULL)
OR (table_1.album_count IS NOT NULL AND table_2.album_count IS NULL);

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

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

В данной ситуация вся задача распадается на 3 отдельные подзадачи:

  1. band_id = 93 - в таблице band_extended есть нужная строка строка со значением 93, но в этой строке неправильное значение в колонке n_albums. Тут будет использоваться команда UPDATE т.к. строка уже существует в таблице band_extended
  2. band_id = -100. Строка есть в band_extended, но для нее нет ни одной строки в таблице album. Так как мы приняли, что таблица album верная, то в этой ситуации будет использовать DELETE т.к. эту запись нужно просто удалить.
  3. band_id = 192. Тут нужна команда INSERT т.к. нужно добавить строки в таблицу band_extended. 
-- 1. band_id = 93 - UPDATE
UPDATE band_extended as band_alias
   SET n_albums = table_1.album_count
  FROM (
        SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	  GROUP BY 1
       ) as table_1
 WHERE band_alias.band_id = table_1.band_id
   AND band_alias.band_id IN (
                              SELECT band_id_band_extended
	                            FROM table_comperison
                               WHERE band_id_band_extended IS NOT NULL
                                 AND band_id_album IS NOT NULL
                             );

Этот запрос SQL выполняет обновление столбца "n_albums" в таблице "band_extended" на основе подсчета количества альбомов для каждой группы.

  1. Сначала создается временная таблица table_1 с помощью подзапроса, который выбирает band_id из таблицы album и подсчитывает количество альбомов для каждой группы с помощью COUNT(*) в столбце album_count.
  2. Затем осуществляется обновление столбца "n_albums" в таблице "band_extended" через операцию SET, присваивая ему значение album_count из временной таблицы table_1.
  3. Далее используется фраза FROM, чтобы указать, что данные для обновления берутся из временной таблицы table_1.
  4. Условие WHERE связывает таблицы band_extended и table_1 по band_id, чтобы обновление происходило только для соответствующих записей.
  5. Также есть условие AND для фильтрации только тех записей, где band_id в таблице band_extended присутствует в подзапросе SELECT в скобках, который выбирает band_id_band_extended из таблицы table_comperison, удовлетворяющие определенным условиям: band_id_band_extended не равен NULL и band_id_album не равен NULL.
--2. band_id = -100 - DELETE
DELETE 
  FROM band_extended
 WHERE band_id IN (
                   SELECT band_id_band_extended 
	                 FROM table_comperison
                    WHERE band_id_band_extended IS NOT NULL
                      AND band_id_album IS NULL);

Этот запрос SQL удаляет записи из таблицы "band_extended" на основе определенных условий, указанных в подзапросе.

  1. В подзапросе SELECT в скобках выбираются band_id_band_extended из таблицы table_comperison, удовлетворяющие определенным условиям: band_id_band_extended не равен NULL и band_id_album равен NULL.
  2. Затем оператор DELETE указывает таблицу "band_extended" как целевую для удаления.
  3. Условие WHERE связывает записи в таблице "band_extended" с результатом подзапроса, устанавливая критерии для удаления. Только те записи, где band_id соответствует band_id_band_extended из подзапроса, будут удалены из таблицы "band_extended".
--3. band_id = 192 - INSERT
    INSERT INTO band_extended (band_id, name, year, comment, n_albums, n_songs)
    SELECT b.band_id, b.name, b.year, b.comment,
           COALESCE(albums.album_count, 0) as n_albums,
	       COALESCE(songs.songs_count, 0) as n_songs
      FROM band as b 
LEFT OUTER JOIN (SELECT band_id, COUNT(*) as album_count FROM album GROUP BY 1) as albums
        ON albums.band_id = b.band_id
LEFT OUTER JOIN (SELECT band_id, COUNT(*) as song_count FROM song GROUP BY 1) as songs
        ON songs.band_id = b.band_id
     WHERE b.band_id IN (
					SELECT band_id_album 
					  FROM table_comperison
					 WHERE band_id_band_extended IS NULL
					   AND band_id_album IS NOT NULL);
INSERT INTO band_extended (band_id, name, year, comment, n_albums, n_songs)

Эта часть запроса указывает, что данные будут вставлены в таблицу band_extended и перечисляет столбцы, в которые будет производиться вставка.

SELECT b.band_id, b.name, b.year, b.comment, 
COALESCE(albums.album_count, 0) as n_albums, 
COALESCE(songs.songs_count, 0) as n_songs

Здесь используется оператор SELECT для выбора данных из таблицы band (алиас как b) и результатов агрегированных функций COUNT(*) для таблиц album и song. Функция COALESCE используется для замены NULL значений на 0.

FROM band as b

Указывает, что выборка данных будет производиться из таблицы band под псевдонимом b.

LEFT OUTER JOIN (
         SELECT band_id, COUNT(*) as album_count 
           FROM album GROUP BY 1) as albums 
ON albums.band_id = b.band_id

В этой строке используется оператор LEFT OUTER JOIN для объединения таблицы band с результатами агрегированной функции COUNT(*) для таблицы album. Новая таблица, обозначаемая как albums, содержит количество альбомов для каждого band_id из таблицы band.

LEFT OUTER JOIN (
       SELECT band_id, COUNT(*) as song_count 
         FROM song GROUP BY 1) as songs 
ON songs.band_id = b.band_id

Аналогично, здесь используется оператор LEFT OUTER JOIN для объединения таблицы band с результатами агрегированной функции COUNT(*) для таблицы song. Новая таблица, обозначаемая как songs, содержит количество песен для каждого band_id из таблицы band.

WHERE b.band_id IN (
               SELECT band_id_album 
                 FROM table_comperison 
                WHERE band_id_band_extended IS NULL 
                AND band_id_album IS NOT NULL)

Условие WHERE фильтрует записи из таблицы band, чтобы вставка происходила только для тех записей, у которых band_id соответствует определенным условиям из подзапроса.

Таким образом, этот запрос вставляет данные в таблицу band_extended на основе данных из таблицы band и результатов агрегированных функций COUNT(*) для таблиц album и song с применением определенных условий фильтрации.

Теперь запустим проверочный запрос из прошлого урока и проверим:

SELECT table_1.band_id as band_id_band_extended,
       table_1.album_count as album_count_band_extended,
	   table_2.band_id as band_id_album,
       table_2.album_count as album_count_album
  FROM (
        SELECT band_id, n_albums as album_count 
	      FROM band_extended 
	     WHERE n_albums > 0 OR n_albums IS NULL
       ) as table_1
FULL JOIN
        (
		SELECT band_id, COUNT(*) as album_count 
		  FROM album 
	    GROUP BY 1
		) as table_2
ON table_1.band_id = table_2.band_id
WHERE table_1.album_count <> table_2.album_count
OR (table_1.album_count IS NULL AND table_2.album_count IS NOT NULL)
OR (table_1.album_count IS NOT NULL AND table_2.album_count IS NULL);

То есть таблица пуста и, значит, все сделано правильно!

Alex Compass
January 13, 10:46
0 views