Разработка шаблона для отчета, который принимает на вход несколько параметров и файл с разметкой и, используя данные о кассах и продажах в базе SQLite, формирует файл с отчетом.
1 Исходные данные
Исходные данные включают:
● Файл с разметкой двух брендов в формате csv с полями:
o brand
o product_name_hash (хэш от названия товара, по которому можно найти интересующие продажи в чеках)
● База данных SQLite (https://www.sqlite.org/) со следующими таблицами:
o kkt_activity – данные о первом и последнем чеке для всех касс
o kkt_categories – категории касс по виду деятельности
o kkt_info – основная информация о кассах, включая данные организации и торговой точки
o sales – продажи касс (чеки)
Примечание. Все названия полей в таблицах унифицированы. Таким образом, поля с одинаковым названием в разных таблицах ссылаются на одни и те же сущности, и по ним можно джойнить таблицы (например, kkt_number во всех таблицах указывает на конкретную уникальную кассу).
Ссылка на архив: test_base.zip
1.1 Таблица kkt_activity
Описание полей:
|
Поле |
Описание |
|
kkt_number |
Регистрационный номер кассы |
|
receipt_date_min |
Дата первого чека, полученного от кассы |
|
receipt_date_max |
Дата последнего чека, полученного от кассы |
1.2 Таблица kkt_categories
Описание полей:
|
Поле |
Описание |
|
kkt_number |
Регистрационный номер кассы |
|
category |
Категория кассы по виду деятельности |
|
date_from |
Дата начала действия записи (включается) |
|
date_till |
Дата окончания действия записи (не включается) |
|
version |
Версия классификации |
Примечания к таблице:
- Таблица содержит несколько версий классификации. Когда появляется новая версия классификатора торговых точек, то все кассы классифицируются за все время их работы и в таблице появляются новые записи для новой версии, при этом старые версии не удаляются и могут использоваться в старых отчетах.
- Классификация касс производится каждую неделю (так как касса могла начать торговать другими товарами). Таким образом, каждую неделю для каждой работающей кассы появляется новая запись в таблице для каждой версии классификатора.
- В таблице хранятся только записи для работающих касс, которые удалось классифицировать. Остальных касс в таблице нет.
1.3 Таблица kkt_info
Описание полей:
|
Поле |
Описание |
|
org_inn |
ИНН организации |
|
shop_id |
Идентификатор торговой точки (уникален в рамках конкретной организации) |
|
kkt_number |
Регистрационный номер кассы |
|
region |
Регион, в котором работает касса |
|
date_from |
Дата начала действия записи (включается) |
|
date_till |
Дата окончания действия записи (не включается) |
Примечания к таблице:
- Таблица содержит основную информацию о кассе за всю историю ее работы.
- Для каждой кассы в таблице может быть несколько записей, которые не пересекаются по периоду действия, обозначенному датами date_from и date_till.
- Данные таблицы являются выжимкой из более полной таблицы, поэтому в ней могут быть полностью идентичные записи с разными периодом действия. Это значит, что в исходной таблице изменились какие-то параметры кассы, которые в итоге не попали в выжимку для текущего задания. Это никак не влияет на принципы работы с таблицей.
1.4 Таблица sales
Описание полей:
|
Поле |
Описание |
|
org_inn |
ИНН организации |
|
kkt_number |
Регистрационный номер кассы |
|
receipt_date |
Дата продажи (чека) |
|
receipt_id |
Идентификатор продажи (чека) |
|
product_name_hash |
Хэш от названия товара |
|
total_sum |
Сумма продажи товара |
2 Требования к отчету
2.1 Входящие параметры
Должна быть возможность задать следующие параметры отчета:
- Путь к файлу product_names.csv.
- Период, задается двумя датами в формате yyyy-MM-dd (обе даты включаются в отчет):
- date_from
- date_to
- Фильтр kkt_category, принимающий на вход список категорий: может быть пустым или содержать любое кол-во категорий (например: FMCG, HoReCa).
- Признак необходимости группировки данных отчета по следующим полям:
- receipt_date
- region
- channel (данное поле вычисляется, описание ниже)
2.2 Содержание отчета
Отчет всегда формируется по продажам брендов из входящего файла product_names.csv в соответствии с разметкой. То есть для каждого бренда (brand) из файла нужно найти все продажи, имеющие соответствующие названия товаров (product_name_hash). Все прочие продажи в отчет попадать не должны.
2.2.1 Фильтры
У отчета должен быть только один опциональный фильтр kkt_category. Если он пустой, то параметр игнорируется. В противном случае при формировании отчета должны учитываться только продажи касс указанных категорий. В данном отчете используется всегда наиболее актуальная версия классификации (максимальная дата в поле version).
2.2.2 Разрезы
У отчета есть один обязательные разрез, который должен быть всегда – brand. Остальные разрезы опциональны и зависят от указанных признаков необходимости группировки во входящих параметрах отчета:
● receipt_date: если признак указан, то отчет должен быть дополнительно сгруппирован по полю receipt_date из таблицы sales
● region: если признак указан, то отчет должен быть дополнительно сгруппирован по полю region из таблицы kkt_info
● channel: если признак указан, то отчет должен быть дополнительно сгруппирован по полю channel, которое необходимо вычислить
Значение поля channel указывает на то, является ли организация торговой сетью, и вычисляется в соответствии со следующими правилами:
1. Значение вычисляется для организации целиком, то есть привязано к уникальному значению org_inn.
2. Значения зависят от количества активных торговых точек (shop_id из таблицы kkt_info в периоде, за который строится отчет (входящие параметры date_from и date_to).
3. Торговая точка считается активной, если есть хотя бы одна касса, у которой период активности (receipt_date_min и receipt_date_max из таблицы kkt_activity) пересекается с периодом отчета.
4. Возможные значения:
a. nonchain: у организации < 3 активных торговых точек в периоде, за который формируется отчет
b. chain: у организации >= 3 активных торговых точек в периоде, за который формируется отчет
2.2.3 Показатели
В рамках отчета рассчитываются два показателя:
- total_sum – сумма продаж бренда (по полю total_sum из таблицы sales)
- total_sum_pct – процент продаж бренда от продаж всех брендов (total_sum бренда, разделенный на сумму total_sum всех брендов в отчете, округленный до 2-х знаков после запятой)
2.2.4 Формат
Отчет должен формироваться в виде файла:
● Название: report
● Формат: csv
● Заголовки: названия полей
● Разделитель: запятая
2.3 Примеры отчета
Данные в примерах не являются реальными результатами, а приведены
2.3.1 Пример №1
Входящие параметры:
- Период:
- date_from: “2019-08-01”
- date_to: “2019-08-02”
- Фильтр kkt_category: не указан.
- Разрезы:
- receipt_date: false
- region: false
- channel: false
Результат (все числа взяты случайным образом для примера):
|
brand |
total_sum |
total_sum_pct |
|
parliament |
200 |
0.67 |
|
marlboro |
100 |
0.33 |
2.3.2 Пример №2
Входящие параметры:
- Период:
- date_from: “2019-08-01”
- date_to: “2019-08-02”
- Фильтр kkt_category: FMCG.
- Разрезы:
- receipt_date: true
- region: true
- channel: true
Результат (все числа взяты случайным образом для примера):
|
receipt_date |
region |
channel |
brand |
total_sum |
total_sum_pct |
|
2019-08-01 |
г. Москва |
chain |
parliament |
200 |
0.67 |
|
2019-08-01 |
г. Москва |
chain |
marlboro |
100 |
0.33 |
|
2019-08-02 |
г. Москва |
chain |
parliament |
150 |
0.75 |
|
2019-08-02 |
г. Москва |
chain |
marlboro |
50 |
0.25 |
|
2019-08-01 |
г. Москва |
nonchain |
parliament |
300 |
0.75 |
|
2019-08-01 |
г. Москва |
nonchain |
marlboro |
100 |
0.25 |
|
2019-08-02 |
г. Москва |
nonchain |
parliament |
100 |
0.50 |
|
2019-08-02 |
г. Москва |
nonchain |
marlboro |
100 |
0.50 |
|
2019-08-01 |
Санкт-Петербург |
chain |
parliament |
75 |
0.33 |
|
2019-08-01 |
Санкт-Петербург |
chain |
marlboro |
150 |
0.67 |
|
… |
… |
… |
… |
… |
… |
Примечание. Так как был указан фильтр, то в отчете отображаются только данные категории FMCG.