# Работа с данными для прогнозов Исходные данные извлекаются из базы данных ВкусВилл (таблицы: Report.\[report\_zakaz\_tbl\], \[GeoReports\].\[Analytics\].\[EffectiveZonesOnlineServices\] и \[Geo\].\[geo\].\[tt\]), а также с внешних источников: API met.no и rp5.ru. Собранные данные сохраняются в следующих таблицах: - vv\_orders\_ts — временные ряды заказов; - test\_vv\_points — тестовые точки; - weather — данные о погоде; - weather\_stations — информация о метеостанциях; - calendar — календарные данные. Прогнозная модель CatBoost извлекает данные из указанных таблиц и выполняет их обработку. Результаты обработки, включая прогнозы модели CatBoost, сохраняются в таблице time\_series. На основе этих результатов производятся дальнейшие расчеты для аналитики. [![Описание прогноза (2).png](https://bookstack.ally.software/uploads/images/gallery/2025-09/scaled-1680-/opisanie-prognoza-2.png)](https://bookstack.ally.software/uploads/images/gallery/2025-09/opisanie-prognoza-2.png) --- ### Получение данных о заказах Для получения данных о заказах составляется отчет по заказам с фильтрацией по дате заказа и наличию даты поставки. Источником получения данных служит таблица Report.\[report\_zakaz\_tbl\] Запрос извлекает данные о заказах из таблицы отчетов за указанный период времени. Запрос фокусируется на заказах, где дата заказа находится в заданном диапазоне, и дата поставки не является NULL. Дополнительно вычисляется поле с датой поставки, увеличенной на 15 минут.

Текст запроса

Ниже представлен запрос для получения информации о заказах: ``` SELECT id_order as id, ShopNo as shopno, order_type, gettype, date_order, date_supply, date_supply_untill, date_posted, date_collect_start, date_collected, date_delivery_start, date_delivered, sum_paid, sum_paid_coupon, count_pos, latitude, longitude, client_type, order_weight, delivery_hottime, collecting_await_dur, collecting_dur, delivery_await_dur, delivery_dur, completed_agg, distance, DATEADD(Minute, 15, date_supply) as date_supply_vv FROM Report.[report_zakaz_tbl] rzt (NOLOCK) WHERE date_order BETWEEN '{{payload.from}}' AND '{{payload.to}}' AND date_supply IS NOT NULL ```

Поля запроса

Запрос возвращает 27 полей. Ниже приведена таблица с описаниями:

Поле SELECT	Исходное поле	Тип данных
id	id\_order	INT
shopno	ShopNo	INT
order\_type	order\_type	TinyInt
gettype	gettype	TinyInt
date\_order	date\_order	DateTime
date\_supply	date\_supply	DateTime
date\_supply\_untill	date\_supply\_untill	DateTime
date\_posted	date\_posted	DateTime
date\_collect\_start	date\_collect\_start	DateTime
date\_collected	date\_collected	DateTime
date\_delivery\_start	date\_delivery\_start	DateTime
date\_delivered	date\_delivered	DateTime
sum\_paid	sum\_paid	FLOAT
sum\_paid\_coupon	sum\_paid\_coupon	FLOAT
count\_pos	count\_pos	SmallInt
latitude	latitude	Decimal(19,16)
longitude	longitude	Decimal(19,16)
client\_type	client\_type	NVarChar(100) COLLATE Cyrillic\_General\_CI\_AS
order\_weight	order\_weight	Decimal(15,3)
delivery\_hottime	delivery\_hottime	DateTime
collecting\_await\_dur	collecting\_await\_dur	INT
collecting\_dur	collecting\_dur	INT
delivery\_await\_dur	delivery\_await\_dur	INT
delivery\_dur	delivery\_dur	INT
completed\_agg	completed\_agg	INT
distance	distance	Real
date\_supply\_vv	date\_supply	(вычисляемое)

Фильтры запроса

В запросе присутствуют 2 фильтра: - date\_order BETWEEN '{{payload.from}}' AND '{{payload.to}}' — Фильтр по дате заказа. Включает заказы, где дата заказа >= '{{payload.from}}' и <= '{{payload.to}}'. - date\_supply IS NOT NULL — Исключает заказы без указанной даты поставки.

Вычисляемые поля

В запросе есть вычисляемое поле: - DATEADD(Minute, 15, date\_supply) as date\_supply\_vv — Функция SQL Server для добавления 15 минут к полю date\_supply.

--- ### Получение данных о геозонах Для получения данных о геозонах составляется отчет по зонам с фильтрацией по магазинам. Источником получения данных служит таблицы \[GeoReports\].\[Analytics\].\[EffectiveZonesOnlineServices\] и \[Geo\].\[geo\].\[tt\] Запрос объединяет данные из двух таблиц для получения информации о зонах, услугах и временных интервалах.

Текст запроса

Ниже представлен запрос для получения информации о заказах: ``` SELECT tt.N AS id_tt, id_online_service, id_poly, date_add, geo, payway, time_start, time_end FROM [GeoReports].[Analytics].[EffectiveZonesOnlineServices] (NOLOCK) z JOIN [Geo].[geo].[tt] tt ON tt.id_TT = z.id_tt WHERE tt.name_TT LIKE '%ДС[_]% ```

Поля запроса

Запрос возвращает 8 полей. Ниже приведена таблица с описаниями:

Поле SELECT	Исходное поле	Тип данных
id\_tt	tt.N
id\_online\_service	id\_online\_service
id\_poly	id\_poly
date\_add	date\_add
geo	geo
payway	payway
time\_start	time\_start
time\_end	time\_end

Фильтры запроса

В запросе присутствуют 2 фильтра: - INNER JOIN на tt.id\_TT = z.id\_tt — Это объединяет записи только если есть совпадение по идентификатору точки (id\_tt). Если в z нет соответствующей записи в tt, она не попадет в результат. - WHERE tt.name\_TT like '%ДС\[\_\]%' — Фильтр по имени точки (магазина) в таблице tt.

--- ### Таблицы хранения полученных данных Исходные данные, используемые для построения прогноза, размещены в следующих таблицах: - vv\_orders\_ts — гипертаблица с информацией о заказах. Включает поля, заполняемые на основе исходной таблицы Report.\[report\_zakaz\_tbl\], а также ряд дополнительных полей. - test\_vv\_points — географические зоны, связанные с торговыми точками (ТТ). - weather — данные о погоде, загруженные из сервиса [https://api.met.no](https://api.met.no). - weather\_stations — погодные станции с сервиса rp5.ru. Применялись для загрузки исторических метеоданных. - calendar — табель-календарь, заполняемый посредством системы репликации. Кроме того, на базе этих таблиц создаются материализованные представления: - vv\_orders\_ts\_hash\_hourly — заказы в географической зоне за определенный час, исключая заказы с типом gettype = 6 (доставка через Яндекс). - vv\_lines\_ts\_hash\_hourly — количество собранных строк для географической зоны за конкретный час. Ниже приведено описание этих таблиц и представлений, в которым перечислены поля использующиеся для составления прогноза.

vv\_orders\_ts

Поле	Тип данных
id	int4
shopno	int4
order\_type	int4
gettype	int4
date\_order	timestamp
date\_supply	timestamp
date\_supply\_untill	timestamp
date\_posted	timestamp
date\_collect\_start	timestamp
date\_collected	timestamp
date\_delivery\_start	timestamp
date\_delivered	timestamp
sum\_paid	float8
sum\_paid\_coupon	float8
count\_pos	int2
latitude	numeric(19, 16)
longitude	numeric(19, 16)
client\_type	varchar(100)
order\_weight	numeric(15, 3)
delivery\_hottime	timestamp
collecting\_await\_dur	int4
collecting\_dur	int4
delivery\_await\_dur	int4
delivery\_dur	int4
completed\_agg	int4
distance	float4
date\_supply\_vv	timestamp
details	jsonb
tt\_id	int4
geohash	varchar(20)

test\_vv\_points

Поле	Тип данных	Описание
geohash	varchar(20)	Геохэш области
tt\_id	numeric	ИД иорговой точки
coeff	int4	Вероятность того, что заказ в данной области попадет в данную ТТ. Определяется на основе статистики заказов за предыдущие 3 дня

weather

Поле	Тип данных	Описание
weather\_station\_id	int4	ИД погодной станции
date	timestamp	Время
temperature	numeric(3, 1)	Температура
humidity	int4	Влажность
wind\_speed	int4	Скорость ветра

weather\_stations

Поле	Тип данных	Описание
id	serial4	ИД погодной станции
active	bool	Признак активности

calendar

Поле	Тип данных	Описание
id	serial4	ИД
date	date	Дата
type	int4	Тип (2 - суббота, 3 - воскресенье, 4 - предпраздничный день, 5 - праздник)

vv\_orders\_ts\_hash\_hourly

Для составления представления формируется запрос который формирует представление для хранения агрегированных данных о количестве заказов, сгруппированных по геохешу и часовым интервалам. Исключаются заказы с типом получения gettype = 6. Представление обновляется автоматически. Источником получения данных служит таблица vv\_orders\_ts. Текст запроса: ``` CREATE MATERIALIZED VIEW vv_orders_ts_hash_hourly WITH (timescaledb.continuous) AS SELECT geohash, time_bucket('1 hour', ts.date_supply_vv) AS bucket, COUNT(*) AS cnt FROM vv_orders_ts ts WHERE ts.gettype != 6 GROUP BY geohash, bucket; ``` Поля запроса:

Поле	Тип данных	Описание
geohash	varchar(20)
bucket	timestamp
cnt	int8

vv\_lines\_ts\_hash\_hourly

Для составления представления формируется запрос который формирует представление для хранения агрегированных данных о суммарном количестве позиций (строк) в заказах, сгруппированных по геохешу и часовым интервалам. Источником получения данных служит таблица vv\_orders\_ts. Текст запроса: ``` CREATE MATERIALIZED VIEW vv_lines_ts_hash_hourly WITH (timescaledb.continuous) AS SELECT geohash, time_bucket('1 hour', ts.date_supply_vv) AS bucket, SUM(ts.count_pos) AS cnt FROM vv_orders_ts ts GROUP BY geohash, bucket; ``` Поля запроса:

Поле	Тип данных	Описание
geohash	varchar(20)
bucket	timestamp
cnt	int8

--- ### Хранение результатов обработки исходных данных Данные для прогнозов, сами прогнозы и результаты их анализа хранятся во временных рядах в таблице time\_series:

Поле	Тип данных	Описание
id	bigserial
tstamp	timestamptz
type	int4	Описывает тип данных
restaurant\_id	int4
user\_id	int4
value	float8	Значение прогноза
details	jsonb

Тип прогноза, для которого сформирован результат обработки, определяется значением поля type. Возможные числовые значения поля и их интерпретация приведены ниже. Сформированный прогноз по часам помещается в таблицу под типами 4-7

type	Описание	Подразделение	Тип	Источник
1	Данные по доставке	Последняя миля	Исходные данные	Репликатор
2	Данные по сборке	Последняя миля	Исходные данные	Репликатор
4	Прогноз доставка	Последняя миля	Прогноз	Python
5	Прогноз сборка	Последняя миля	Прогноз	Python
6	Прогноз по геозонам доставка	Последняя миля	Прогноз	Python
7	Прогноз по геозонам сборка	Последняя миля	Прогноз	Python
51	Оценка трудоемкости розница	Розница	Исходные данные	Node-RED
52	Прогноз суммарной трудоемкости Розница	Розница	Прогноз	Python
53	Прогноз трудоемкости кассиров Розница	Розница	Прогноз	Python
55	Факт суммарной выработки Розница	Розница	Аналитика	pgAgent
56	Факт выработки кассиров Розница	Розница	Аналитика	pgAgent
154	Курьеры факт	Последняя миля	Аналитика	Node-RED
155	Курьеры план	Последняя миля	Аналитика	Node-RED
156	Курьеры прогноз	Последняя миля	Аналитика	Node-RED
157	Заказы план	Последняя миля	Аналитика	Node-RED