Python 자습서: T-SQL을 사용하여 데이터 기능 만들기

아티클
07/12/2024

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL Managed Instance

이 5부 자습서 시리즈의 3부에서는 Transact-SQL 함수를 사용하여 원시 데이터에서 기능을 만드는 방법을 알아봅니다. 그런 다음 SQL 저장 프로시저에서 해당 함수를 호출하여 기능 값이 포함된 테이블을 만듭니다.

원시 데이터에서 기능을 만드는 이 기능 엔지니어링 프로세스는 고급 분석 모델링에서 중요한 단계가 될 수 있습니다.

이 문서에서는 다음을 수행합니다.

이동 거리를 계산하는 사용자 지정 함수 수정
다른 사용자 지정 함수를 사용하여 기능 저장

1부에서는 필수 구성 요소를 설치하고 샘플 데이터베이스를 복원했습니다.

2부에서는 샘플 데이터를 탐색하고 몇 가지 플롯을 생성했습니다.

4부에서는 SQL Server 저장 프로시저를 통해 모듈을 로드하고 필요한 함수를 호출하여 모델을 만들고 학습시킵니다.

5부에서는 4부에서 학습시키고 저장한 모델을 운영하는 방법을 알아봅니다.

함수 정의

원본 데이터에 보고된 거리 값은 보고된 미터 거리를 기반으로 하며 반드시 지리적 거리나 이동 거리를 나타내는 것은 아닙니다. 따라서 원본 NYC Taxi 데이터 세트에서 사용할 수 있는 좌표를 사용하여 픽업 지점과 하차 지점 사이의 직접 거리를 계산해야 합니다. 이렇게 하려면 사용자 지정 Transact-SQL 함수에 Haversine 수식을 사용합니다.

하나의 사용자 지정 T-SQL 함수인 fnCalculateDistance를 사용하여 Haversine 수식을 사용하여 거리를 계산하고 두 번째 사용자 지정 T-SQL 함수인 fnEngineerFeatures를 사용하여 모든 기능을 포함하는 테이블을 만듭니다.

fnCalculateDistance를 사용하여 트립 거리 계산

fnCalculateDistance 함수는 샘플 데이터베이스에 포함됩니다. 잠깐 코드를 검토합니다.

Management Studio에서 프로그래밍 기능을 확장하고 Functions를 확장한 다음 스칼라 반환 함수를 확장합니다.

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

참고:

이 함수는 미리 정의된 형식의 단일 데이터 값을 반환하는 스칼라 반환 함수입니다.
함수는 이동 시 승하차 위치에서 얻은 위도 및 경도 값을 입력으로 사용합니다. Haversine 수식은 위치를 라디안으로 변환하고 해당 값을 사용하여 두 위치 사이의 직접 거리(마일)를 컴퓨팅합니다.

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을 통해 Machine Learning 모델을 만들고 학습하는 방법을 알아봅니다.

다음 단계

이 문서에서는 다음 작업을 수행합니다.

이동 거리를 계산하는 사용자 지정 함수 수정
다른 사용자 지정 함수를 사용하여 기능 저장

Python 자습서: T-SQL을 사용하여 Python 모델 학습 및 저장

다음을 통해 공유

Python 자습서: T-SQL을 사용하여 데이터 기능 만들기

함수 정의

fnCalculateDistance를 사용하여 트립 거리 계산

fnEngineerFeatures를 사용하여 기능 저장

다음 단계

피드백

추가 리소스