Delen via

Python-zelfstudie: Gegevens voorbereiden om klanten te categoriseren met SQL Machine Learning

Van toepassing op: SQL Server 2017 (14.x) en latere versies van Azure SQL Managed Instance

In deel twee van deze vierdelige reeks zelfstudies herstelt en bereidt u de gegevens uit een database voor met behulp van Python. Verderop in deze reeks gebruikt u deze gegevens om een clusteringmodel te trainen en te implementeren in Python met SQL Server Machine Learning Services of op Big Data-clusters.

In deel twee van deze vierdelige reeks zelfstudies herstelt en bereidt u de gegevens uit een database voor met behulp van Python. Verderop in deze reeks gebruikt u deze gegevens om een clusteringmodel te trainen en te implementeren in Python met SQL Server Machine Learning Services.

In deel twee van deze vierdelige reeks zelfstudies herstelt en bereidt u de gegevens uit een database voor met behulp van Python. Verderop in deze reeks gebruikt u deze gegevens om een clusteringmodel te trainen en te implementeren in Python met Azure SQL Managed Instance Machine Learning Services.

In dit artikel leert u het volgende:

  • Klanten segmenteren langs verschillende dimensies met behulp van Python
  • De gegevens uit de database laden in een Python-gegevensframe

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel drie leert u hoe u een K-Means-clusteringmodel maakt en traint in Python.

In deel vier leert u hoe u een opgeslagen procedure maakt in een database die clustering in Python kan uitvoeren op basis van nieuwe gegevens.

Prerequisites

  • In deel twee van deze zelfstudie wordt ervan uitgegaan dat u aan de vereisten van deel 1 hebt voldaan.

Afzonderlijke klanten

Als u zich wilt voorbereiden op clusteringklanten, scheidt u eerst klanten langs de volgende dimensies:

  • orderRatio = retourorderverhouding (totaal aantal orders gedeeltelijk of volledig geretourneerd versus het totale aantal orders)
  • itemsRatio = verhouding retouritem (totaal aantal geretourneerde items versus het aantal gekochte artikelen)
  • monetaryRatio = retourbedragverhouding (totale monetaire hoeveelheid geretourneerde items versus het aangeschafte bedrag)
  • frequency = retourfrequentie

Open een nieuw notebook in Azure Data Studio en voer het volgende script in.

Vervang zo nodig de verbindingsgegevens in de verbindingsreeks.

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

De gegevens in een gegevensframe laden

Resultaten van de query worden geretourneerd naar Python met behulp van de functie Pandas read_sql . Als onderdeel van het proces gebruikt u de kolomgegevens die u in het vorige script hebt gedefinieerd.

customer_data = pd.read_sql(input_query, conn_str)

Geef nu het begin van het gegevensframe weer om te controleren of het er correct uitziet.

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

Hier is het resultatenoverzicht.

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

De hulpbronnen opschonen

Als u niet verdergaat met deze zelfstudie, verwijdert u de tpcxbb_1gb-database.

Volgende stappen

In deel twee van deze reeks zelfstudies hebt u deze stappen voltooid:

  • Klanten segmenteren langs verschillende dimensies met behulp van Python
  • De gegevens uit de database laden in een Python-gegevensframe

Als u een machine learning-model wilt maken dat gebruikmaakt van deze klantgegevens, volgt u deel drie van deze reeks zelfstudies:

Python-zelfstudie: Een voorspellend model maken