Базы данных: ключи, связи и отбор записей

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

Таблица, запись и поле

База данных — организованный набор сведений об объектах и событиях. В реляционной базе они представлены таблицами. Строка таблицы называется записью, столбец — полем. Значение в ячейке относится к определённому полю одной записи.

Например, запись в таблице «Товар» описывает один товар. Поля «Наименование» и «Количество в упаковке» содержат его название и размер упаковки. А запись в «Движении товаров» описывает одну операцию: поступление или продажу. Это разные объекты учёта.

Ключ: как найти нужную запись

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

Название не всегда подходит на роль ключа. Например, один и тот же продукт может продаваться в упаковках разного объёма. Каждой такой позиции назначается свой артикул, даже если названия совпадают.

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

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

Связь «один ко многим»

Одному товару соответствует много операций, но каждая операция в этой базе относится к одному товару. Такая связь называется «один ко многим». Аналогичная связь действует между магазином и его операциями.

Ниже — сокращённые данные EGE-03-0001 ↗. Из справочника взят один товар, из журнала — две операции с ним. Остальные строки и поля здесь не показаны.

Обе операции ссылаются на артикул 42. Для каждой из них масса упаковки равна 0,5 кг. Одинаковые артикулы и количества не делают строки лишними: ID операций и магазины различаются. Удаление одной строки потеряет реальную поставку.

Соединение таблиц

Соединить таблицы — значит подобрать к записи связанные с ней сведения. Для операции 889 артикул 42 даёт название «Крахмал картофельный» и массу 0,5 кг. ID магазина M1 даёт район «Октябрьский». Получается одна строка с данными из трёх таблиц:

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

Перенос значения с помощью ВПР

В электронной таблице связанные сведения можно добавить в новые столбцы функцией ВПР (VLOOKUP). Она ищет код в первом столбце указанного диапазона и возвращает значение из другого столбца той же строки.

В файле основного примера артикул операции находится в D2, а справочник товаров занимает A2:F65 листа «Товар». Количество в упаковке — пятый столбец этого справочника. Формула для свободной ячейки H2 на листе операций:

Excel с русскими именами функций:

=ВПР(D2;Товар!$A$2:$F$65;5;0)

LibreOffice Calc с русскими именами функций:

=ВПР(D2;Товар.$A$2:$F$65;5;0)

Различается разделитель между именем листа и адресом диапазона: ! в Excel, . в стандартной записи Calc. Если включены английские имена функций, используется VLOOKUP. В примерах аргументы разделены точкой с запятой.

Знаки $ фиксируют справочник. Формула копируется вниз до последней операции. Так каждая строка получает размер именно своей упаковки. Для артикула 42 результат равен 0,5. В другом файле границы диапазона, столбец с артикулом и номер возвращаемого столбца определяются заново по заголовкам.

Последний аргумент 0 существенен: коды должны совпасть точно. При пропущенном аргументе ВПР может выполнить приблизительный поиск. Если точный поиск выдаёт ошибку, нужно проверить наличие кода и формат значений: число 42 и текст «42» могут восприниматься по-разному. Подмена ошибки нулём способна незаметно занизить итог.

Отбор: какие записи участвуют

Условие отбора для каждой записи даёт один из двух результатов: подходит или не подходит. Несколько обязательных требований соединяются союзом «и». Альтернативные допустимые значения — союзом «или».

Например, отбор товара 42 в магазинах M1 или M5 означает:

Артикул = 42 и (магазин = M1 или магазин = M5).

Скобки объединяют альтернативы по одному признаку. Условие «артикул = 42 или магазин = M1» значительно шире: в него попадут и другие товары магазина M1, и товар 42 из других магазинов.

Период «с 1 по 8 июня включительно» содержит обе даты. Сортировка по дате только меняет порядок строк; она сама по себе не отбирает период. При фильтрации неподходящие строки скрываются, но остаются в файле.

Итог: что именно складывается

После отбора выполняется агрегирование. Число записей и сумма количеств отвечают на разные вопросы. Две поставки по 180 упаковок — это две записи, но 360 упаковок.

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

Например, если в столбце F записано количество упаковок, а H содержит массу одной упаковки в килограммах, формула =F2*H2 в I2 вычисляет массу операции. После копирования вниз сумма по отфильтрованным строкам определяется так:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;I2:I2273)

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

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

Применение в заданиях

Как по связанным таблицам вычислить массу товара, поступившего в магазины нужного района? Что меняется в расчёте, если требуется найти выручку или изменение остатка?

Эти вопросы разобраны на примерах в статье «Задание №3. Поиск и расчёты в связанных таблицах».