Tutorial de Python: Preparación de datos para clasificar clientes por categorías con aprendizaje automático de SQL

Artículo
04/17/2023

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL Managed Instance

En la parte dos de esta serie de tutoriales de cuatro partes, restaurará y preparará los datos de una base de datos mediante Python. Más adelante en esta serie, usará estos datos para entrenar e implementar un modelo de agrupación en clústeres en Python con SQL Server Machine Learning Services o en clústeres de macrodatos.

En la parte dos de esta serie de tutoriales de cuatro partes, restaurará y preparará los datos de una base de datos mediante Python. Más adelante en esta serie, usará estos datos para entrenar e implementar un modelo de agrupación en clústeres en Python con Machine Learning Services en Azure SQL Managed Instance.

En este artículo, aprenderá a:

Separación de los clientes en distintas dimensiones mediante Python
Carga de datos de la base de datos en una trama de datos de Python

En la parte uno, ha instalado los requisitos previos y ha restaurado la base de datos de ejemplo.

En la parte tres, aprenderá a crear y entrenar un modelo de agrupación en clústeres k-means en Python.

En la parte cuatro, aprenderá a crear un procedimiento almacenado en una base de datos que pueda realizar la agrupación en clústeres en Python basándose en datos nuevos.

Prerrequisitos

En la parte dos de este tutorial, se da por hecho que ha completado los requisitos previos de la parte uno.

Separación de clientes

Para preparar la agrupación en clústeres de los clientes, primero separe los clientes en las dimensiones siguientes:

orderRatio = índice de devolución de pedidos (número total de pedidos con una devolución total o parcial comparado con el número total de pedidos)
itemsRatio = índice de artículos devueltos (número total de artículos devueltos comparado con el número de artículos comprados)
monetaryRatio = índice de importes de devoluciones (total de importes monetarios de los artículos devueltos comparado con el importe de las compras)
frequency = frecuencia de devolución

Abra un nuevo cuaderno en Azure Data Studio y escriba el script siguiente.

En la cadena de conexión, reemplace los detalles de conexión según corresponda.

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

Carga de los datos en una trama de datos

Los resultados de la consulta se devuelven a Python mediante la función read_sql de Pandas. Como parte del proceso, usará la información sobre columnas que ha definido en el script anterior.

customer_data = pd.read_sql(input_query, conn_str)

Ahora, muestre el principio de la trama de datos para asegurarse de que sea correcta.

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

Limpieza de recursos

Si no quiere continuar con este tutorial, elimine la base de datos tpcxbb_1gb.

Pasos siguientes

En la parte dos de esta serie de tutoriales, ha completado estos pasos:

Separación de los clientes en distintas dimensiones mediante Python
Carga de datos de la base de datos en una trama de datos de Python

Para crear un modelo de aprendizaje automático que use estos datos de clientes, siga la parte tres de esta serie de tutoriales:

Tutorial de Python: Creación de un modelo predictivo

Compartir a través de