Tutorial: Menyiapkan data untuk melakukan pengklusteran di R dengan pembelajaran mesin SQL

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Managed Instance yang lebih baru

Di bagian dua dari seri tutorial empat bagian ini, Anda akan menyiapkan data dari database untuk melakukan pengklusteran di R dengan SQL Server Pembelajaran Mesin Services atau di Kluster Big Data.

Dalam artikel ini, Anda akan mempelajari cara:

• Memisahkan pelanggan di sepanjang dimensi yang berbeda menggunakan R
• Memuat data dari database ke dalam bingkai data R

Di bagian satu, Anda menginstal prasyarat dan memulihkan database sampel.

Di bagian tiga, Anda akan mempelajari cara membuat dan melatih model pengklusteran K-Means di R.

Di bagian empat, Anda akan mempelajari cara membuat prosedur tersimpan dalam database yang dapat melakukan pengklusteran di R berdasarkan data baru.

Prasyarat

• Bagian kedua dari tutorial ini mengasumsikan Anda telah menyelesaikan bagian satu.

Memisahkan pelanggan

Buat file RScript baru di RStudio dan jalankan skrip berikut. Dalam kueri SQL, Anda memisahkan pelanggan di sepanjang dimensi berikut:

• orderRatio = mengembalikan rasio pesanan (jumlah total pesanan sebagian atau dikembalikan sepenuhnya versus jumlah total pesanan)
• itemsRatio = mengembalikan rasio item (jumlah total item yang dikembalikan versus jumlah item yang dibeli)
• moneterRatio = rasio jumlah pengembalian (jumlah total item moneter yang dikembalikan versus jumlah yang dibeli)
• frekuensi = frekuensi pengembalian

Dalam fungsi connStr, ganti ServerName dengan informasi koneksi Anda sendiri.

# Define the connection string to connect to the tpcxbb_1gb database

connStr <- "Driver=SQL Server;Server=ServerName;Database=tpcxbb_1gb;uid=Username;pwd=Password"

#Define the query to select data
input_query <- "
SELECT ss_customer_sk AS customer
    ,round(CASE 
            WHEN (
                       (orders_count = 0)
                    OR (returns_count IS NULL)
                    OR (orders_count IS NULL)
                    OR ((returns_count / orders_count) IS NULL)
                    )
                THEN 0.0
            ELSE (cast(returns_count AS NCHAR(10)) / orders_count)
            END, 7) AS orderRatio
    ,round(CASE 
            WHEN (
                     (orders_items = 0)
                  OR (returns_items IS NULL)
                  OR (orders_items IS NULL)
                  OR ((returns_items / orders_items) IS NULL)
                 )
            THEN 0.0
            ELSE (cast(returns_items AS NCHAR(10)) / orders_items)
            END, 7) AS itemsRatio
    ,round(CASE 
            WHEN (
                     (orders_money = 0)
                  OR (returns_money IS NULL)
                  OR (orders_money IS NULL)
                  OR ((returns_money / orders_money) IS NULL)
                 )
            THEN 0.0
            ELSE (cast(returns_money AS NCHAR(10)) / orders_money)
            END, 7) AS monetaryRatio
    ,round(CASE 
            WHEN (returns_count IS NULL)
            THEN 0.0
            ELSE returns_count
            END, 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";

Memuat data ke dalam bingkai data

Sekarang gunakan skrip berikut untuk mengembalikan hasil dari kueri ke bingkai data R.

# Query using input_query and get the results back
# to data frame customer_data

library(RODBC)

ch <- odbcDriverConnect(connStr)

customer_data <- sqlQuery(ch, input_query)

# Take a look at the data just loaded
head(customer_data, n = 5);

Anda akan melihat hasil yang mirip dengan yang berikut ini.

  customer orderRatio itemsRatio monetaryRatio frequency
1    29727          0          0      0.000000         0
2    26429          0          0      0.041979         1
3    60053          0          0      0.065762         3
4    97643          0          0      0.037034         3
5    32549          0          0      0.031281         4

Membersihkan sumber daya

Jika Anda tidak akan melanjutkan tutorial ini, hapus database tpcxbb_1gb.

Langkah berikutnya

Di bagian dua seri tutorial ini, Anda belajar cara:

• Memisahkan pelanggan di sepanjang dimensi yang berbeda menggunakan R
• Memuat data dari database ke dalam bingkai data R

Untuk membuat model pembelajaran mesin yang menggunakan data pelanggan ini, ikuti bagian tiga dari seri tutorial ini:

Membuat model prediktif di R dengan pembelajaran mesin SQL