Python 教學課程：準備資料以使用 SQL 機器學習來分類客戶

適用於：SQL Server 2017 (14.x) 和更新版本Azure SQL 受控執行個體

在本教學課程系列的第二部分 (總共四個部分) 中，您將使用 Python 還原和準備資料庫的資料。 在本系列稍後，您將使用此資料搭配 SQL Server 機器學習服務，在 Python 中定型和部署叢集模型，或在巨量資料叢集上進行此定型和部署。

在本文中，您將學會如何：

• 使用 Python 劃分不同維度的客戶
• 將資料庫中的資料載入至 Python 資料框架

在第一部分中，您已安裝必要條件並還原範例資料庫。

在第三部分，您將了解如何在 Python 中建立和定型 K-Means 叢集模型。

在第四部分，您將了解如何在資料庫中建立預存程序，以根據新的資料在 Python 中執行叢集。

Prerequisites

• 本教學課程的第二部分假設您已滿足第一部分的必要條件。

劃分客戶

若要準備叢集客戶，您必須先依照下列維度來劃分客戶：

• orderRatio = 退貨訂單率 (部分退貨或全部退貨的訂單總數與訂單總數比較)
• itemsRatio = 退貨率 (退貨總數與購買項目數目比較)
• monetaryRatio = 退貨金額率 (退貨的貨幣金額總計與購買金額比較)
• frequency = 退貨頻率

在 Azure Data Studio 中開啟新的筆記本，並輸入以下指令碼。

在連接字串中，視需要取代連線詳細資料。

# 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"}
}

將資料載入資料框架

查詢的結果會使用 Pandas read_sql 函式傳回至 Python。 作為此流程的一部分，您將使用在先前指令碼中定義的資料行資訊。

customer_data = pd.read_sql(input_query, conn_str)

現在會顯示資料框架的開頭，以驗證它看起來正確。

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

清除資源

如果您不打算繼續進行本教學課程，請刪除 tpcxbb_1gb 資料庫。

後續步驟

在本教學課程系列的第二部分中，您已完成下列步驟：

• 使用 Python 劃分不同維度的客戶
• 將資料庫中的資料載入至 Python 資料框架

若要建立使用此客戶資料的機器學習模型，請遵循此教學課程系列的第三部分：

Python 教學課程：建立預測模型