Руководство по R. Создание функций данных

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

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

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

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

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

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

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

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

Сведения о формировании признаков

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

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

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

В общем виде процесс выглядит так:

• создание функции T-SQL, выполняющей вычисления;

• вызов функции для создания данных признаков;

• сохранение данных признаков в таблице.

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

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

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

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

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

   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
   

   • Эта функция с табличным значением принимает несколько столбцов в качестве входных данных и выводит таблицу с несколькими столбцами признаков.

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

2. Чтобы убедиться в том, что эта функция работает, вычислите с ее помощью географическое расстояние поездок, для которых расстояние по счетчику было равно 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
   

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

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

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