Tutorial Python: Membuat Fitur Data menggunakan T-SQL

Artikel
05/30/2023

Berlaku untuk: SQL Server 2017 (14.x) dan Azure SQL Managed Instance yang lebih baru

Di bagian tiga dari seri tutorial lima bagian ini, Anda akan mempelajari cara membuat fitur dari data mentah dengan menggunakan fungsi Transact-SQL. Anda kemudian akan memanggil fungsi tersebut dari prosedur tersimpan SQL untuk membuat tabel yang berisi nilai fitur.

Proses rekayasa fitur, membuat fitur dari data mentah, dapat menjadi langkah penting dalam pemodelan analitik tingkat lanjut.

Dalam artikel ini, Anda akan:

Mengubah fungsi kustom untuk menghitung jarak perjalanan
Menyimpan fitur menggunakan fungsi kustom lain

Di bagian satu, Anda menginstal prasyarat dan memulihkan database sampel.

Di bagian dua, Anda menjelajahi data sampel dan menghasilkan beberapa plot.

Di bagian empat, Anda akan memuat modul dan memanggil fungsi yang diperlukan untuk membuat dan melatih model menggunakan prosedur tersimpan SQL Server.

Di bagian lima, Anda akan mempelajari cara mengoperalisasi model yang Anda latih dan simpan di bagian empat.

Tentukan Fungsi

Nilai jarak yang dilaporkan dalam data asli didasarkan pada jarak meter yang dilaporkan, dan tidak selalu mewakili jarak geografis atau jarak yang dilalui. Oleh karena itu, Anda harus menghitung jarak langsung antara titik penjemputan dan pengantaran, dengan menggunakan koordinat yang tersedia di himpunan data NYC Taxi sumber. Anda dapat melakukan ini dengan menggunakan rumus Haversine dalam fungsi Transact-SQL kustom.

Anda akan menggunakan satu fungsi T-SQL kustom, fnCalculateDistance, untuk menghitung jarak menggunakan rumus Haversine, dan menggunakan fungsi T-SQL kustom kedua, fnEngineerFeatures, untuk membuat tabel yang berisi semua fitur.

Menghitung jarak perjalanan menggunakan fnCalculateDistance

Fungsi fnCalculateDistance disertakan dalam database sampel. Luangkan waktu satu menit untuk meninjau kode:

Di Management Studio, perluas Programmability, perluas Functions , lalu fungsi bernilai skalar.

Klik kanan fnCalculateDistance, dan pilih Ubah untuk membuka skrip Transact-SQL di jendela kueri baru.

Hasilnya akan terlihat seperti ini:

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

Catatan:

Fungsi ini adalah fungsi bernilai skalar, mengembalikan satu nilai data dari jenis yang telah ditentukan sebelumnya.
Fungsi ini mengambil nilai garis lintang dan bujur sebagai input, yang diperoleh dari lokasi penjemputan dan pengantaran perjalanan. Rumus Haversine mengonversi lokasi menjadi radian dan menggunakan nilai tersebut untuk menghitung jarak langsung dalam mil di antara kedua lokasi tersebut.

Menyimpan fitur menggunakan fnEngineerFeatures

Untuk menambahkan nilai komputasi ke tabel yang dapat digunakan untuk melatih model, Anda akan menggunakan fungsi T-SQL kustom, fnEngineerFeatures. Fungsi ini adalah fungsi bernilai tabel yang mengambil beberapa kolom sebagai input, dan menghasilkan tabel dengan beberapa kolom fitur. Tujuan dari fungsi ini adalah untuk membuat set fitur untuk digunakan dalam membangun model. Fungsi fnEngineerFeatures memanggil fungsi T-SQL yang dibuat sebelumnya, fnCalculateDistance, untuk mendapatkan jarak langsung antara lokasi pickup dan dropoff.

Luangkan waktu satu menit untuk meninjau kode:

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

Untuk memverifikasi bahwa fungsi ini berfungsi, Anda dapat menggunakannya untuk menghitung jarak geografis untuk perjalanan tersebut di mana jarak terukur adalah 0 tetapi lokasi penjemputan dan pengantaran berbeda.

    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

Seperti yang Anda lihat, jarak yang dilaporkan oleh meteran tidak selalu sesuai dengan jarak geografis. Inilah sebabnya mengapa rekayasa fitur penting.

Di bagian berikutnya, Anda akan mempelajari cara menggunakan fitur data ini untuk membuat dan melatih model pembelajaran mesin menggunakan Python.

Langkah berikutnya

Dalam artikel ini, Anda:

Memodifikasi fungsi kustom untuk menghitung jarak perjalanan
Menyimpan fitur menggunakan fungsi kustom lain

Tutorial Python: Melatih dan menyimpan model Python menggunakan T-SQL

Bagikan melalui