Tutorial Python: Menyiapkan data untuk mengategorikan pelanggan dengan pembelajaran mesin SQL

Artikel
07/04/2024

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

Di bagian dua dari seri tutorial empat bagian ini, Anda akan memulihkan dan menyiapkan data dari database menggunakan Python. Nantinya dalam seri ini, Anda akan menggunakan data ini untuk melatih dan menyebarkan model pengklusteran di Python dengan SQL Server Pembelajaran Mesin Services atau di Kluster Big Data.

Di bagian dua dari seri tutorial empat bagian ini, Anda akan memulihkan dan menyiapkan data dari database menggunakan Python. Nantinya dalam seri ini, Anda akan menggunakan data ini untuk melatih dan menyebarkan model pengklusteran di Python dengan Azure SQL Managed Instance Pembelajaran Mesin Services.

Dalam artikel ini, Anda akan mempelajari cara:

Memisahkan pelanggan di sepanjang dimensi yang berbeda menggunakan Python
Memuat data dari database ke dalam bingkai data Python

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

Di bagian ketiga, Anda akan mempelajari cara membuat dan melatih model pengklusteran K-Means di Python.

Di bagian empat, Anda akan mempelajari cara membuat prosedur tersimpan dalam database yang dapat melakukan pengklusteran di Python berdasarkan data baru.

Prasyarat

Bagian dua dari tutorial ini mengasumsikan Anda telah memenuhi prasyarat bagian satu.

Memisahkan pelanggan

Untuk mempersiapkan pengklusteran pelanggan, Anda akan terlebih dahulu memisahkan pelanggan di sepanjang dimensi berikut:

orderRatio = mengembalikan rasio pesanan (jumlah total pesanan sebagian atau dikembalikan sepenuhnya versus jumlah total pesanan)
itemsRatio = mengembalikan rasio item (jumlah total item yang dikembalikan versus jumlah item yang dibeli)
moneterRatio = rasio jumlah pengembalian (jumlah total item moneter yang dikembalikan versus jumlah yang dibeli)
frekuensi = frekuensi pengembalian

Buka buku catatan baru di Azure Data Studio dan masukkan skrip berikut.

Di string koneksi, ganti detail koneksi sesuai kebutuhan.

# Load packages.
import pyodbc
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from scipy.spatial import distance as sci_distance
from sklearn import cluster as sk_cluster

################################################################################################

## Connect to DB and select data

################################################################################################

# Connection string to connect to SQL Server named instance.
conn_str = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=<server>; DATABASE=tpcxbb_1gb; UID=<username>; PWD=<password>')

input_query = '''SELECT
ss_customer_sk AS customer,
ROUND(COALESCE(returns_count / NULLIF(1.0*orders_count, 0), 0), 7) AS orderRatio,
ROUND(COALESCE(returns_items / NULLIF(1.0*orders_items, 0), 0), 7) AS itemsRatio,
ROUND(COALESCE(returns_money / NULLIF(1.0*orders_money, 0), 0), 7) AS monetaryRatio,
COALESCE(returns_count, 0) AS frequency
FROM
(
  SELECT
    ss_customer_sk,
    -- return order ratio
    COUNT(distinct(ss_ticket_number)) AS orders_count,
    -- return ss_item_sk ratio
    COUNT(ss_item_sk) AS orders_items,
    -- return monetary amount ratio
    SUM( ss_net_paid ) AS orders_money
  FROM store_sales s
  GROUP BY ss_customer_sk
) orders
LEFT OUTER JOIN
(
  SELECT
    sr_customer_sk,
    -- return order ratio
    count(distinct(sr_ticket_number)) as returns_count,
    -- return ss_item_sk ratio
    COUNT(sr_item_sk) as returns_items,
    -- return monetary amount ratio
    SUM( sr_return_amt ) AS returns_money
FROM store_returns
GROUP BY sr_customer_sk ) returned ON ss_customer_sk=sr_customer_sk'''


# Define the columns we wish to import.
column_info = {
    "customer": {"type": "integer"},
    "orderRatio": {"type": "integer"},
    "itemsRatio": {"type": "integer"},
    "frequency": {"type": "integer"}
}

Memuat data ke dalam bingkai data

Hasil dari kueri dikembalikan ke Python menggunakan fungsi Pandas read_sql . Sebagai bagian dari proses, Anda akan menggunakan informasi kolom yang Anda tentukan dalam skrip sebelumnya.

customer_data = pd.read_sql(input_query, conn_str)

Sekarang tampilkan awal bingkai data untuk memverifikasi bahwa bingkai tersebut terlihat benar.

print("Data frame:", customer_data.head(n=5))

Rows Read: 37336, Total Rows Processed: 37336, Total Chunk Time: 0.172 seconds
Data frame:     customer  orderRatio  itemsRatio  monetaryRatio  frequency
0    29727.0    0.000000    0.000000       0.000000          0
1    97643.0    0.068182    0.078176       0.037034          3
2    57247.0    0.000000    0.000000       0.000000          0
3    32549.0    0.086957    0.068657       0.031281          4
4     2040.0    0.000000    0.000000       0.000000          0

Membersihkan sumber daya

Jika Anda tidak akan melanjutkan tutorial ini, hapus database tpcxbb_1gb.

Langkah berikutnya

Di bagian dua seri tutorial ini, Anda menyelesaikan langkah-langkah berikut:

Memisahkan pelanggan di sepanjang dimensi yang berbeda menggunakan Python
Memuat data dari database ke dalam bingkai data Python

Untuk membuat model pembelajaran mesin yang menggunakan data pelanggan ini, ikuti bagian tiga dari seri tutorial ini:

Tutorial Python: Membuat model prediktif

Bagikan melalui