SQL PostgreSQL Добряки

Объединение множеств или как работать со статистическими свойствами через SQL запрос.

В данной статье разберем работу с таблицами, которые носят название “Closure” (таблицы соответствий), и рассмотрим, как не запутаться в объединении таких таблиц. Постараемся всеми возможными способами построить правильный запрос

Разберем в примере:

Технику объединения таблиц [inner, left, rigth] join .. on
Особенность работы оператора WHERE
Спасательный круг в виде оператора Count(), который подсчитывает количество строк.

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

Схема свойств

В таблице sc546 находятся вид свойства (например, бренд товара), а сами свойства задаются через таблицу соответствий SC562 (closure), а в sc556 - название бренда “Samsung”.

Итак, наша цель вывести полный справочник номенклатуры с названием брендов (если бренда нет, то оставить пустым поле). Работа с объединением множеств в чистом виде.

Поехали!

Для начала нужны данные. Возьмем данные, которые необходимы для демонстрации (к примеру, поля SC84.SP97 - Мин. Остаток, SC84.sp2417 - Вид Номенклатуры и т.д. не представляют для нас ценности, так как они уточняют свойства товара). Зададим необходимые таблицы в том виде, который требуется для примера, без дополнительной информации (оставим только значимые и поля для связи таблиц):

CREATE TABLE test.sc84
(
  id TEXT
, descr TEXT
);
INSERT INTO test.sc84(id, descr) VALUES ('   QXC000','PHILIPS HP4881 (фен)');
INSERT INTO test.sc84(id, descr) VALUES ('   QXD000','PHILIPS HP4891 (фен)');
INSERT INTO test.sc84(id, descr) VALUES ('   O8A000','PHILIPS HD4628* (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('   O8B000','PHILIPS HD4665 (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('   O8C000','PHILIPS HD4680 (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('   O89000','BOSCH TWK 7007 (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('   O88000','BOSCH TWK 7003 (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('  11KO000','LG GR-P247JYLW');
INSERT INTO test.sc84(id, descr) VALUES ('   NCU000','LG DKS-6100BQ (DVD-плеер)');
INSERT INTO test.sc84(id, descr) VALUES ('   NCV000','LG DKS-6100Q (DVD-плеер)');
INSERT INTO test.sc84(id, descr) VALUES ('   NQY000','LG XH-TK550Q');
INSERT INTO test.sc84(id, descr) VALUES ('   R6N000','POLARIS PMP 1004C (маникюрный набор)');
INSERT INTO test.sc84(id, descr) VALUES ('   TW4000','POLARIS PIR 2024 (утюг)');
INSERT INTO test.sc84(id, descr) VALUES ('   TW5000','POLARIS PIR 2025AD (утюг)');
INSERT INTO test.sc84(id, descr) VALUES ('   O8F000','POLARIS PWK 2013C (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('   O8G000','POLARIS PWK 2032C (чайник)');
INSERT INTO test.sc84(id, descr) VALUES ('  160G000','SAMSUNG GT-C3510TV Chic Whit');
INSERT INTO test.sc84(id, descr) VALUES ('   KG3000','SONY MemoryStickDUO PRO 2GB (память)');
INSERT INTO test.sc84(id, descr) VALUES ('   KG4000','SONY MemoryStickPRO MSX 2GB (память)');
INSERT INTO test.sc84(id, descr) VALUES ('   KG5000','SONY MemoryStickDUO PRO 512MB (память)');
INSERT INTO test.sc84(id, descr) VALUES ('  14S5000','HP DeskJet 1000 (принтер)');
CREATE TABLE test.sc546
(
  id TEXT
, descr TEXT
);
INSERT INTO test.sc546(id, descr) VALUES (' 	8   ','BRAND');
INSERT INTO test.sc546(id, descr) VALUES ('	QD000','Высота для встраивания');
CREATE TABLE test.sc556
(
  id TEXT
, descr TEXT
);
INSERT INTO test.sc556(id, descr) VALUES ('	1E   ','BOSCH');
INSERT INTO test.sc556(id, descr) VALUES ('	5N   ','HP');
INSERT INTO test.sc556(id, descr) VALUES (' 	Z   ','LG');
INSERT INTO test.sc556(id, descr) VALUES ('	19   ','PHILIPS');
INSERT INTO test.sc556(id, descr) VALUES ('	18   ','POLARIS');
INSERT INTO test.sc556(id, descr) VALUES (' 	L   ','SAMSUNG');
INSERT INTO test.sc556(id, descr) VALUES (' 	K   ','Sony');
CREATE TABLE test.sc562
(
  id TEXT
, sp563 TEXT
, sp564 TEXT
, parentext TEXT
);
INSERT INTO test.sc562(id, sp563,  parentext) VALUES ('  1CUL000',' 	8   ','   KG3000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1CUP000','	QD000',' 	K   ','   KG4000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1CUP000',' 	8   ',' 	K   ','   KG4000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1CUS000',' 	8   ',' 	K   ','   KG5000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1LNO000',' 	8   ',' 	Z   ','   NCU000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1LNS000',' 	8   ',' 	Z   ','   NCV000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1N16000',' 	8   ',' 	Z   ','   NQY000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1OI1000',' 	8   ','	1E   ','   O88000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1OI5000',' 	8   ','	1E   ','   O89000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1OI9000',' 	8   ','	19   ','   O8A000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1OIT000',' 	8   ','	18   ','   O8F000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1OIX000',' 	8   ','	18   ','   O8G000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1XNZ000',' 	8   ','	19   ','   QXC000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1XO3000',' 	8   ','	19   ','   QXD000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1YOD000',' 	8   ','	18   ','   R6N000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  256D000',' 	8   ','	18   ','   TW4000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  256H000',' 	8   ','	18   ','   TW5000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  2SRV000',' 	8   ',' 	Z   ','  11KO000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  33OP000',' 	8   ','	5N   ','  14S5000');
INSERT INTO test.sc562(id, sp563, sp564, parentext) VALUES ('  1CUP000','	QD000',' 	K   ','   KG4000');

Итого в таблице sc84 - 21 строка.

Технология работы:

Составление кандидата в запросы.
Тестирование запроса по count() (используем визуальный контроль).
Выявление исключений.
Переход к пункту 1.

Кандидат №1.

Попробуем решить задачу в лоб и набьем нужные шишки через where:

/*кандидат 1*/
SELECT
  sc84.descr as product_name,
  sc546.descr as stat_svo,
  sc556.descr as brand_name
FROM
  test.sc546,
  test.sc556,
  test.sc562,
  test.sc84
WHERE
  sc562.parentext = sc84.id
  AND sc562.sp563 = sc546.id
  AND sc562.sp564 = sc556.id
  AND sc546.descr = 'BRAND' ;

Получаем 17 строк, что не совсем правильно.

Кандидат 1 чере where

Вся заминка в следующих позициях:

Проблемные значения

Попробуем через inner.

/*кандидат 1 через inner*/
SELECT
  sc84.descr as product_name,
  sc546.descr as stat_svo,
  sc556.descr as brand_name
FROM
test.sc84
inner join test.sc562 on sc562.parentext = sc84.id
inner join test.sc546 on sc562.sp563 = sc546.id
inner join test.sc556 on sc562.sp564 = sc556.id
where
sc546.descr = 'BRAND' ;

Результат тот же. 17 строк. Объединение через where работает так же как и inner join. В этом смысле запросы эквивалентные.

Кандидат 1 через inner join

Кандидат №2.

Готовим второго кандидата. По идее test.sc84 больше, чем остальные. Поэтому для нее нужно использовать Left. По остальным предположим пойдет и inner join:

/*кандидат 2*/
SELECT
  sc84.descr as product_name,
  sc546.descr as stat_svo,
  sc556.descr as brand_name
FROM
test.sc84
left join test.sc562 on sc562.parentext = sc84.id
inner join test.sc546 on sc562.sp563 = sc546.id
inner join test.sc556 on sc562.sp564 = sc556.id
where
sc546.descr = 'BRAND' ;

ничего не изменилось. Все те же 17. Пока это объяснять не буду. Объясню в конце.

Кандидат 2

Кандидат №3.

Один из выводов в том, что объединение inner не годиться. Попробуем все left:

/*кандидат 3*/
SELECT
  sc84.descr as product_name,
  sc546.descr as stat_svo,
  sc556.descr as brand_name
FROM
test.sc84
left join test.sc562 on sc562.parentext = sc84.id
left join test.sc546 on sc562.sp563 = sc546.id
left join test.sc556 on sc562.sp564 = sc556.id
where sc546.descr = 'BRAND' ;

В итоге получаем уже 18. Но не то, что мы просили.

Кандидат 3 3

Кандидат №4.

Придумаем следующий запрос. У товара может быть свойство или не быть его. Причина первая - возможно, нет такого свойства в природе и вторая причина - нет ключа. Вроде как должно быть full. Пробуем.

/*кандидат 4*/
SELECT
  sc84.descr as product_name,
  sc546.descr as stat_svo,
  sc556.descr as brand_name
FROM
test.sc84
left join test.sc562 on sc562.parentext = sc84.id
full join test.sc546 on sc562.sp563 = sc546.id
full join test.sc556 on sc562.sp564 = sc556.id
where sc546.descr = 'BRAND' ;

Не прошло. Опять 18 строк.

Кандидат 4

Кандидат №5 финальный.

Итак, все решения “в лоб” не показали результата. Поехали разбираться.
Все дело, конечно, в операторе where, который из таблицы может выбрать только те кортежи, которые удовлетворяют условию. Если у нас в связующей таблице (SC562) нет такого id, то объединение будет неправильным, так как where sc546.descr = 'BRAND' в данном случае действует на 4 таблиц!!! Вот именно с этим и надо бороться, ограничивая действия where на нужные таблицы. Выход может быть следующим: произвести объединение сначала таблиц sc562, sc546, sc556 и потом соединить с таблицей sc84, где sc84 больше.

/*кандидат 5*/
SELECT
  sc84.descr as product_name,
  t1.stat_svo,
  t1.brand_name
FROM
test.sc84
left join (
            select sc546.descr as stat_svo, sc556.descr as brand_name, sc562.parentext
            from
            test.sc562
            inner join test.sc546 on sc562.sp563 = sc546.id
            inner join test.sc556 on sc562.sp564 = sc556.id
            where sc546.descr = 'BRAND'  
          )  as t1  on t1.parentext = sc84.id ;

Итог 21 строка, как и ожидалось.
Можно еще написать правильные запросы, но это уже самостоятельно, и без подзапросов. Для меня важно показать технику объединения множеств.

Кандидат 5 финальный

Вывод

При большом количестве объединений не всегда тривиально строятся запросы, которые правильно работают. В этом примере используется схема, которая очень проста для понимания и данных не очень много. Всегда можно визуально все проверить. Здесь пришлось контролировать количество элементов множества. Берем на вооружение функцию count(), которая позволяет контролировать мощность множества. Count() - это как контрольная сумма, без которой мы не сможем двигаться дальше к верным данным. Иногда для проверки на дублирование данных советую применять Count(distinct ) совместно с count().

На практике можно использовать без страха только объединение inner join, которое представляет пересечение множеств, в остальных случаях требуется очень четко контролировать множества.

PS. Иногда у аналитиков действует негласное правило: “делай все последовательно, не пихай в запрос все - иначе получишь совсем не то, что спрашивал”.

Подробности: Опубликовано: 27 Май 2014; Просмотров: 4031

Main Menu

Материалы

Друзья проекта

Теги

Пример объединения множеств на SQL