Disclaimer: SQL позволяет собирать данные множеством способов, если считаете что приведенные в статье запросы можно улучшить, оставьте соответсвующий комментарий :)
P.S. в статье запросы написаны на postgresql
Итак, для начала ознакомимся с предметной областью
У нас есть маленький магазичник, но довольно проходной, потому что торгует всем, начиная от ПИВА и заканчивая ВОДКОЙ…
Владельцу понадобилось узнать, сколько людей заходит в магазин и в какое время, чтобы скоординировать смены кассиров и поставки. Во время максимальной посещаемости нужен выход дополнительного кассира, во время минимальной посещаемости можно раскладывать поступивший товар.
Для учета количества посетителей на магнитные рамки у входа было установлено 2 лазерных датчика.
Данные датчики при каждом срабатывании пишут соотвествующие данные в таблицу sensor_triggers, где sensor_id — идентификтор датчика, а created_at — время срабатывания.
У датчика #1 — sensor_id = 1, а у датчика #2 — sensor_id = 2
По очередности срабатывания датчиков можно сделать вывод, вошел человек в магазин или вышел.
Данные в таблице выглядят так:
Записи #3, #4, #5 — Лжесрабатывания, потому что нарушена очередность срабатывания датчиков, и на основании этих данных нельзя сделать вывод, вошел человек или вышел.
Возможные причины лжесрабатываний
Итак, с предметной областью мы закончили, теперь переходим к отчету.
Для начала, уточняем у заказчика что он хочет видеть, по возможности просим предоставить пример. Нам повезло, владелец показал, что он хочет видеть.
“Отчет по проходимости магазина с почасовой группировкой”
Выводы из примера:
1) В отчете должны отображаться промежутки с нулевой посещаемостью;
2) В отчете нужен итог за день;
3) Возможна погрешность в данных на вход и выход.
Получается, что нам необходимо:
- Из-за того, что в отчете необходимо показывать промежутки времени без посетителей надо сгенерировать возможные промежутки времени, чтобы к ним джойнить данные по входу и выходу;
- Обработать данные из таблицы sensor_triggers, чтобы в них прослеживалось количество входящих и выходящих людей, а также надо отбросить лжесрабатывания;
- Добавить строку с итогами.
Примерная структура псевдозапроса
Полезно в виде черновика или наброска продумать структуру будующего запроса, тогда можно будет определиться в количестве
hours — временные интервалы
incomes — количество вошедших людей
outcomes — количество вышедших людей
rows — данные сгрупированные по часам
final_rows — агригированные rows
Генерируем промежутки времени
Не будем особо заморачиваться с автоматической генерацией строк, просто перечислим возможные варианты. Для генерации можно использовать рекурсивный WITH (WITH RECURSIVE).
hour_literal соответсвует первому столбцу отчета, а hour_id необходим для соединения с данными о входе и выходе посетителей.
Обрабатываем данные таблицы sensor_triggers
- Данные необходимо сгруппировать по часам;
- В данном случае проще не отбрасывать лжесрабатывания, а определить набор правил, когда засчитывать срабатывания как вход или выход. Будем считать, что человек с минимально возможной скоростью передвижения задействует два датчика за 5 секунд, тогда нам остается выбрать из таблицы последовательно задействованные датчики с максимальным периодом между срабатываниями = 5с. Тогда нам достаточно найти запись одного датчика, и второго с указанной разницей во времени.
Создаем incomes и outcomes
outcomes выглядит абсолютно так же, только меняется порядок датчиков.
Собираем отчет из всех подготовленных данных
Код финального запроса
В общем-то на этом и все, в конце используется UNION ALL чтобы добавить строку в конец выборки, т.к. UNION ALL просто соединяет две выборки в указанном порядке, в отличии от UNION, который объединяет выборки и удаляет дубликаты (для поиска дубликатов выборка сортируется).
Дальше владелец магазина пообщался с эффективным менеджером и решил считать KPI продаж, основанный на проходимости магазина и количестве проданного товара, но это уже другая история.
Спасибо, что прочитали до конца! Надеюсь, что статья научит чему-нибудь новому :)