Базы данных
Работа с несколькими таблицами: фильтрация, связь и подсчёт
В задаче № 3 даны две-три связанные таблицы, например «Товары» и «Операции». Вопрос обычно требует отобрать строки по условию, объединить информацию из разных таблиц и сосчитать итог.
Как устроена связь
Таблицы связаны ключом: значение, которое есть в обеих таблицах. В таблице «Товары» у каждого товара свой артикул, а в таблице «Операции» артикул указывает, о каком товаре идёт речь. Сам артикул уникален только в справочной таблице; в таблице операций он может повторяться.
Пример данных (сокращённо):
| Артикул | Отдел | Наименование |
|---|---|---|
| A1 | Молоко | Молоко 1л |
| A2 | Молоко | Кефир 1л |
| B1 | Хлеб | Батон |
| Магазин | Артикул | Тип операции | Количество |
|---|---|---|---|
| S1 | A1 | Поступление | 100 |
| S1 | A1 | Продажа | 30 |
| S2 | A2 | Поступление | 50 |
| S2 | A2 | Продажа | 20 |
| S1 | B1 | Поступление | 40 |
| S1 | A1 | Продажа | 10 |
Порядок решения
- Определите, какая информация лежит в какой таблице. В нашем примере отдел есть только в «Товарах», а тип операции и количество — только в «Операциях».
- Отберите нужные артикулы по условию из первой таблицы (отдел «Молоко» даёт
A1иA2). - Отберите строки второй таблицы с этими артикулами и нужным типом операции.
- Сосчитайте итог: сумму количества или число строк.
Вопрос: на сколько увеличилось количество товаров отдела «Молоко»? Поступления: 100 + 50 = 150. Продажи: 30 + 20 + 10 = 60. Разница: 150 − 60 = 90. Если спросить про продажи только магазина S1, получим 30 + 10 = 40.
Решение на Python
Для файлов с большим числом строк надёжнее написать программу. Данные читают из файлов в формате CSV или из электронной таблицы:
goods = [('A1', 'Молоко'), ('A2', 'Молоко'), ('B1', 'Хлеб')]
ops = [
('S1', 'A1', 'Поступление', 100), ('S1', 'A1', 'Продажа', 30),
('S2', 'A2', 'Поступление', 50), ('S2', 'A2', 'Продажа', 20),
('S1', 'B1', 'Поступление', 40), ('S1', 'A1', 'Продажа', 10),
]
dept = dict(goods) # артикул -> отдел
income = sum(q for s, a, t, q in ops if dept[a] == 'Молоко' and t == 'Поступление')
sold = sum(q for s, a, t, q in ops if dept[a] == 'Молоко' and t == 'Продажа')
print(income - sold) # 90Словарь dept играет роль связи между таблицами: по артикулу из операции мгновенно находим отдел.
В электронной таблице
Тот же результат даёт столбец-«помощник» с отделом, подтянутым из справочной таблицы, и функции СУММЕСЛИМН для суммы по нескольким условиям. Подробнее — в статье «Электронные таблицы: формулы и рабочие шаблоны».
Типичные ошибки
- Считают по таблице операций, не отобрав артикулы нужного отдела.
- Смешивают поступления и продажи: тип операции нужно учитывать отдельно.
- Забывают про дату: если вопрос про период, условие по дате обязательно.
- Объединяют таблицы по неверному полю (по названию вместо артикула): названия могут повторяться.