Python のチュートリアル:SQL 機械学習を使用して顧客を分類するデータを準備する

Applies to: SQL Server 2017 (14.x) and later Azure SQL Managed Instance

この記事では、次の方法について学習します。

In part one, you installed the prerequisites and restored the sample database.

In part three, you'll learn how to create and train a K-Means clustering model in Python.

In part four, you'll learn how to create a stored procedure in a database that can perform clustering in Python based on new data.

Prerequisites

• Part two of this tutorial assumes you have fulfilled the prerequisites of part one.

Separate customers

顧客のクラスタリングを準備するには、まず次のディメンションに従って顧客を分離します。

• orderRatio = return order ratio (total number of orders partially or fully returned versus the total number of orders)
• itemsRatio = return item ratio (total number of items returned versus the number of items purchased)
• monetaryRatio = return amount ratio (total monetary amount of items returned versus the amount purchased)
• frequency = return 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"}
}

データをデータ フレームに読み込む

Results from the query are returned to Python using the Pandas read_sql function. プロセスの一部として、前のスクリプトで定義した列情報を使用します。

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 データベースを削除してください。

Next steps

このチュートリアル シリーズの第 2 部では、次の手順を完了しました。

• Python を使用して異なるディメンションに沿って顧客を分離する
• データベースから Python データ フレームにデータを読み込む

顧客データを使用する機械学習モデルをトレーニングするには、このチュートリアル シリーズの第 3 部に従ってください。