Бөлісу құралы:

Руководство по Python. Создание функций данных с помощью T-SQL

  • Мақала

Область применения: SQL Server 2017 (14.x) и более поздних версий Управляемый экземпляр SQL Azure

Из третьей части этой серии из пяти учебников вы узнаете, как создавать характеристики на основе необработанных данных с помощью функции Transact-SQL. Затем вы вызовите эту функцию из хранимой процедуры SQL, чтобы создать таблицу, содержащую значения характеристик.

Процесс конструирования признаков (создание признаков на основе необработанных данных) — важнейший этап расширенного аналитического моделирования.

Работая с этой статьей, вы узнаете о следующем.

  • Изменение пользовательской функции для вычисления расстояния поездки
  • Сохранение признаков с помощью другой пользовательской функции

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

Во второй части вы изучили образец данных и создали несколько графиков.

В четвертой части вы научитесь загружать модули и вызывать необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server.

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

Определение функции

Значения расстояний, которые содержатся в исходных данных, основаны на показаниях счетчиков и необязательно отражают расстояние по карте или фактическое расстояние поездки. Поэтому необходимо вычислить прямое расстояние между местами посадки и высадки с помощью координат, доступных в исходном наборе данных по работе такси в Нью-Йорке. Это можно сделать с помощью формулы гаверсинуса в пользовательской функции Transact-SQL.

Вы используете пользовательскую функцию T-SQL fnCalculateDistanceдля вычисления расстояния по формуле гаверсинуса, а затем другую пользовательскую функцию T-SQL fnEngineerFeaturesдля создания таблицы, содержащей все характеристики.

Вычисление расстояния поездки с помощью функции fnCalculateDistance

Функция fnCalculateDistance включена в образец базы данных. Вкратце изучите код.

  1. В Среде Management Studio разверните узел Programmability, разверните функции и скалярные функции.

  2. Щелкните правой кнопкой мыши функцию fnCalculateDistance, а затем выберите команду Изменить, чтобы открыть скрипт Transact-SQL в новом окне запроса.

    Должно отобразиться примерно следующее:

    CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
-- User-defined function that calculates the direct distance between two geographical coordinates
RETURNS float
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
  END
  RETURN @distance
END
GO

Примечания:

  • Функция является скалярной и возвращает одно значение предопределенного типа.
  • Эта функция принимает в качестве входных значений широты и долготы мест посадки и высадки. Формула гаверсинуса преобразует координаты в радианы и использует полученные значения для вычисления прямого расстояния в километрах между этими двумя местами.

Сохранение характеристик с помощью функции fnEngineerFeatures

Для добавления вычисленного значения в таблицу, которую можно использовать для обучения модели, применяется пользовательская функция T-SQL fnEngineerFeatures. Это табличная функция, которая принимает несколько столбцов в качестве входных значений и выводит таблицу с несколькими столбцами характеристик. Назначение этой функции — создать набор характеристик, который будет использоваться при построении модели. Функция fnEngineerFeatures вызывает ранее созданную функцию T-SQL fnCalculateDistanceдля получения прямого расстояния между местами посадки и высадки.

Вкратце изучите код.

CREATE FUNCTION [dbo].[fnEngineerFeatures] (
@passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0)
RETURNS TABLE
AS
  RETURN
  (
  -- Add the SELECT statement with parameter references here
  SELECT
    @passenger_count AS passenger_count,
    @trip_distance AS trip_distance,
    @trip_time_in_secs AS trip_time_in_secs,
    [dbo].[fnCalculateDistance](@pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude) AS direct_distance
  )
GO

Чтобы убедиться в том, что эта функция работает, можно с ее помощью вычислить географическое расстояние поездок, для которых расстояние по счетчику было равно 0, но места посадки и высадки были разными.

    SELECT tipped, fare_amount, passenger_count,(trip_time_in_secs/60) as TripMinutes,
    trip_distance, pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) AS direct_distance
    FROM nyctaxi_sample
    WHERE pickup_longitude != dropoff_longitude and pickup_latitude != dropoff_latitude and trip_distance = 0
    ORDER BY trip_time_in_secs DESC

Как видите, расстояние по счетчику не всегда соответствует географическому расстоянию. Вот почему формирование характеристик имеет такое большое значение.

В следующей части вы научитесь использовать характеристики данных для создания и обучения модели машинного обучения с помощью языка Python.

Следующие шаги

Работая с этой статьей, вы выполните следующие задачи:

  • Изменение пользовательской функции для вычисления расстояния поездки
  • Сохранение признаков с помощью другой пользовательской функции

Руководство по Python. Обучение и сохранение модели Python с помощью T-SQL