SQL, Автоматизация, Анализ данных, Лайфхаки в аудите

ГАВЕРСИНУС.2

Время прочтения: 4 мин.

О том, как использовать формулу гаверсинусов для проверки факта грузоперевозки, а также определения времени простоя автотранспорта в точке доставки груза.

Фактическое время простоя автотранспорта полезно знать, когда сверх лимитированный простой необходимо оплачивать дополнительно.

В качестве исходных данных располагаем таблицей с информацией о выполненных заявках на грузоперевозки:

CREATE TABLE .[AutoRoute](
	[ID заявки] [nvarchar](255) NULL,
	[Номер автомобиля] [float] NULL,
	[Дата подачи ТС] [datetime] NULL,
	[тип точки маршрута] [float] NULL,
	[Адреса маршрута] [nvarchar](255) NULL,
	[кол-во минут ожидания] [float] NULL,
	[Ожидание (простой под погрузкой/ разгрузкой), мин] [time](0) NULL,
	[Протяженность маршрута, км] [float] NULL,
	[Километраж] [float] NULL
) ON [PRIMARY]

Таблица с данными GPS/Глонасс передатчиков с автомобилей выполняющих заявки на перевозки.

CREATE TABLE .[AutoTrack](
	[Старт дата время] [datetime] NULL,
	[Старт положение] [nvarchar](255) NULL,
	[Финиш дата время] [datetime] NULL,
	[Финиш положение] [nvarchar](255) NULL,
	[Длительность] [time](0) NULL,
	[Номер автомобиля] [float] NULL,
	[Долгота старта] [float] NULL,
	[Широта старта] [float] NULL,
	[Долгота финиша] [float] NULL,
	[Широта финиша] [float] NULL
) ON [PRIMARY]

Таблица содержит информацию о движении автомобиля, т.е. откуда выехал и куда приехал.

Мы не ставим перед собой задачу проверить весь маршрут передвижения автомобиля, т.к. по факту он движется не в соответствии с заявкой. Наша задача проверить был ли автомобиль в указанной в заявке точке, и сколько времени он там провел. Для адресов выполненных заявок определяем координаты – широту и долготу. Как это сделать я писала в статье «Гаверсинус».   В таблице треков эта информация уже имеется. Создадим таблицу для координат и заполним ее данными.

CREATE TABLE .[AutoRouteCoord](
	[Адрес] [nvarchar](255) NULL,
	[Широта] [float] NULL,
	[Долгота] [float] NULL
) ON [PRIMARY]

Как определить был ли автомобиль в интересующей нас точке? При помощи функции, реализующей формулу гаверсинусов, определим абсолютное расстояние между точками из выполненных заявок и финишными точками из треков. Финишная точка – точка куда автомобиль прибыл. Если это расстояние будет менее определенной величины (у нас 500 метров), то считаем, что автомобиль посетил эту точку. Теперь осталось найти время, когда автомобиль покинул эту точку и посчитать сколько прошло минут.

(select top 1  st.[Старт дата время]
		 from .[AutoTrack] st
		 where st.[Старт положение]=tr.[Финиш положение]
			and tr.[Финиш дата время]<st.[Старт дата время]
			and st.[Номер автомобиля]=tr.[Номер автомобиля]
	     order by st.[Старт дата время]
	  ) [дата и время выезда из точки]

Воспользуемся подзапросом – выбираем из таблицы треков все записи, у которых стартовое положение совпадает с финишным положением основного запроса, номера машин совпадают, дата и время старта более даты и времени финиша основного запроса, сортируем данные в порядке возрастания даты старта, нас интересует первая запись. Для подсчета количества минут используем функцию DATEDIFF().

SELECT z.[ID заявки]
,z.[Номер автомобиля]
,z.[Дата подачи ТС]
,z.[Адреса маршрута]
	,tr.[Финиш положение] [Финиш: положение]
,tr.[Финиш дата время] [дата и время приезда в точку]
	,(select top 1  st.[Старт дата время]
		 from .[AutoTrack] st
		 where st.[Старт положение]=tr.[Финиш положение]
			and tr.[Финиш дата время]<st.[Старт дата время]
			and st.[Номер автомобиля]=tr.[Номер автомобиля]
	     order by st.[Старт дата время]
	  ) [дата и время выезда из точки]
	  ,datediff(mi
			,tr.[Финиш дата время]
			,(select top 1  st.[Старт дата время]
			  from .[AutoTrack] st
			  where st.[Старт положение]=tr.[Финиш положение]
			  and tr.[Финиш дата время]<st.[Старт дата время]
			  and st.[Номер автомобиля]=tr.[Номер автомобиля]
			  order by st.[Старт дата время])
			) [количество минут нахождения в точке]
	  ,round(.[formula_gaversinusov] (c.[Долгота], c.[Широта],tr.[Долгота финиша] ,tr.[Широта финиша]),2)   [расстояние]
  FROM .[AutoRoute] z 
	inner join .[AutoRouteCoord] c 
		on z.[Адреса маршрута]=c.Адрес
	left join .[AutoTrack] tr 
		on z.[Номер автомобиля]=tr.[Номер автомобиля]
		and .[formula_gaversinusov] (c.[Долгота], c.[Широта],tr.[Долгота финиша] 
			,tr.[Широта финиша] )<500
		and cast(tr.[Финиш дата время] as date)=cast(z.[Дата подачи ТС] as date)

Таким образом, мы написали запрос, позволяющий:

  • определить находился ли указанный автомобиль на расстоянии менее 500 метров от адресов, указанных в выполненной заявки
  • сколько времени провел автомобиль в этой точке.

Результат работы запроса на примере одного автомобиля.

Если для адреса маршрута имеются столбцы, содержащие значение «NULL», это значит, что согласно трекам, автомобиль не приближался к данной точке в этот день.

Далее необходимо сравнить время простоя, указанное в заявках и фактическое, определенное нами при помощи формулы гаверсинусов.

Так можно выявить недостоверные данные о простоях в точках погрузки/разгрузки, предъявленные к оплате грузоперевозчиками.

Советуем почитать