Python 教程：准备数据以通过 SQL 机器学习对客户进行聚类分析

项目
04/17/2023

适用于： SQL Server 2017 (14.x) 及更高版本 Azure SQL 托管实例

此教程包括四分部分，在第二部分中，你将使用 Python 从数据库还原和准备数据。在本系列的后续部分中，你将在 SQL Server 机器学习服务中或大数据群集上通过 Python 使用这些数据训练并部署聚类分析模型。

此教程包括四分部分，在第二部分中，你将使用 Python 从数据库还原和准备数据。在本系列的后续部分中，使用这些数据在 Python 中通过 SQL Server 机器学习服务来定型和部署聚类分析模型。

此教程包括四分部分，在第二部分中，你将使用 Python 从数据库还原和准备数据。在本系列的后面部分，将通过 Azure SQL 托管实例机器学习服务在 Python 中使用此数据训练和部署聚类分析模型。

本文将指导如何进行以下操作：

使用 Python 按不同维度对客户进行分隔
将数据库中的数据加载到 Python 数据帧中

在第一部分中，你安装了必备条件并还原了示例数据库。

第三部分介绍如何在 Python 中创建和定型 K-Means 群集模型。

在第四部分中，你将了解如何在数据库中创建存储过程，以便基于新数据在 Python 中执行聚类分析。

先决条件

本教程的第二部分假设你已满足第一部分的必备条件。

分隔客户

若要准备对客户进行聚类分析，首先要将客户按以下维度进行分隔：

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 教程：创建预测模型

通过