Учебник по Python. Создание характеристик данных с помощью T-SQLPython tutorial: Create Data Features using T-SQL

Применимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) и более поздние версии ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) and later ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

Из третьей части этой серии из пяти учебников вы узнаете, как создавать характеристики на основе необработанных данных с помощью функции Transact-SQLTransact-SQL.In part three of this five-part tutorial series, you'll learn how to create features from raw data by using a Transact-SQLTransact-SQL function. Затем вы вызовите эту функцию из хранимой процедуры SQL, чтобы создать таблицу, содержащую значения характеристик.You'll then call that function from a SQL stored procedure to create a table that contains the feature values.

Процесс конструирования признаков (создание признаков на основе необработанных данных) — важнейший этап расширенного аналитического моделирования.The process of feature engineering, creating features from the raw data, can be a critical step in advanced analytics modeling.

Работая с этой статьей, вы узнаете о следующем.In this article, you'll:

  • Изменение пользовательской функции для вычисления расстояния поездкиModify a custom function to calculate trip distance
  • Сохранение признаков с помощью другой пользовательской функцииSave the features using another custom function

В первой части были установлены необходимые компоненты и восстановлена демонстрационная база данных.In part one, you installed the prerequisites and restored the sample database.

Во второй части вы изучили образец данных и создали несколько графиков.In part two, you explored the sample data and generated some plots.

В четвертой части вы научитесь загружать модули и вызывать необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server.In part four, you'll load the modules and call the necessary functions to create and train the model using a SQL Server stored procedure.

Из пятой части вы узнаете, как ввести в эксплуатацию модели, которые были обучены и сохранены в соответствии с инструкциями в четвертой части.In part five, you'll learn how to operationalize the models that you trained and saved in part four.

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

Значения расстояний, которые содержатся в исходных данных, основаны на показаниях счетчиков и необязательно отражают расстояние по карте или фактическое расстояние поездки.The distance values reported in the original data are based on the reported meter distance, and don't necessarily represent geographical distance or distance traveled. Поэтому необходимо вычислить прямое расстояние между местами посадки и высадки с помощью координат, доступных в исходном наборе данных по работе такси в Нью-Йорке.Therefore, you'll need to calculate the direct distance between the pick-up and drop-off points, by using the coordinates available in the source NYC Taxi dataset. Это можно сделать с помощью формулы гаверсинуса в пользовательской функции Transact-SQLTransact-SQL .You can do this by using the Haversine formula in a custom Transact-SQLTransact-SQL function.

Вы используете пользовательскую функцию T-SQL _fnCalculateDistance_для вычисления расстояния по формуле гаверсинуса, а затем другую пользовательскую функцию T-SQL _fnEngineerFeatures_для создания таблицы, содержащей все характеристики.You'll use one custom T-SQL function, fnCalculateDistance, to compute the distance using the Haversine formula, and use a second custom T-SQL function, fnEngineerFeatures, to create a table containing all the features.

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

  1. Функция fnCalculateDistance включена в образец базы данных.The function fnCalculateDistance is included in the sample database. Вкратце изучите код.Take a minute to review the code.

  2. В среде Среда Management StudioManagement Studioпоследовательно разверните узлы Программируемость, Функции и Скалярные функции.In Среда Management StudioManagement Studio, expand Programmability, expand Functions and then Scalar-valued functions. Щелкните правой кнопкой мыши функцию fnCalculateDistance, а затем выберите команду Изменить , чтобы открыть скрипт Transact-SQLTransact-SQL в новом окне запроса.Right-click fnCalculateDistance, and select Modify to open the Transact-SQLTransact-SQL script in a new query window.

    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
    

Примечания.Notes:

  • Функция является скалярной и возвращает одно значение предопределенного типа.The function is a scalar-valued function, returning a single data value of a predefined type.
  • Она принимает в качестве входных значений широту и долготу мест посадки и высадки.It takes latitude and longitude values as inputs, obtained from trip pick-up and drop-off locations. Формула гаверсинуса преобразует координаты в радианы и использует полученные значения для вычисления прямого расстояния в километрах между этими двумя местами.The Haversine formula converts locations to radians and uses those values to compute the direct distance in miles between those two locations.

Для добавления вычисленного значения в таблицу, которую можно использовать для обучения модели, применяется другая функция: fnEngineerFeatures.To add the computed value to a table that can be used for training the model, you'll use another function, fnEngineerFeatures.

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

  1. Вкратце ознакомьтесь с кодом настраиваемой функции T-SQL, fnEngineerFeatures, которая включена в состав образца базы данных.Take a minute to review the code for the custom T-SQL function, fnEngineerFeatures, which is included in the sample database.

    Это табличная функция, которая принимает несколько столбцов в качестве входных значений и выводит таблицу с несколькими столбцами характеристик.This function is a table-valued function that takes multiple columns as inputs, and outputs a table with multiple feature columns. Назначение этой функции — создать набор характеристик, который будет использоваться при построении модели.The purpose of this function is to create a feature set for use in building a model. Функция fnEngineerFeatures вызывает ранее созданную функцию T-SQL _fnCalculateDistance_для получения прямого расстояния между местами посадки и высадки.The function fnEngineerFeatures calls the previously created T-SQL function, fnCalculateDistance, to get the direct distance between pickup and dropoff locations.

    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, но места посадки и высадки были разными.To verify that this function works, you can use it to calculate the geographical distance for those trips where the metered distance was 0 but the pick-up and drop-off locations were different.

        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
    

    Как видите, расстояние по счетчику не всегда соответствует географическому расстоянию.As you can see, the distance reported by the meter doesn't always correspond to geographical distance. Вот почему формирование характеристик имеет такое большое значение.This is why feature engineering is important.

В следующей части вы научитесь использовать характеристики данных для создания и обучения модели машинного обучения с помощью языка Python.In the next part, you'll learn how to use these data features to create and train a machine learning model using Python.

Дальнейшие шагиNext steps

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

  • Изменение пользовательской функции для вычисления расстояния поездкиModified a custom function to calculate trip distance
  • Сохранение признаков с помощью другой пользовательской функцииSaved the features using another custom function