Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server 2016 – R i segmentacja klientów

Jedną z demonstracji na mojej sesji w czasie konferencji SQLDay Lite 2015 było demo segmentacji klientów za pomocą skryptu R uruchamianego z poziomu silnika baz danych SQL Server 2016. W poniższym wpisie pokażę, jak przygotować i przeprowadzić takie demo.

Przygotowanie demo

Aby móc korzystać z dobrodziejstw języka R w SQL Server 2016 CTP3, należy zainstalować kilka komponentów i przeprowadzić proces konfiguracji środowiska.

Pełen opis, co zainstalować i jak skonfigurować, jest dostępny w dokumentacji Books Online: https://msdn.microsoft.com/en-us/library/mt604883.aspx.

Wspomnę tylko, że do zainstalowania są:

Konfiguracja obejmuje między innymi ustawienie opcji external scripts enabled na poziomie instancji SQL Server:

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE;
GO

Na potrzeby demo potrzebna też będzie baza danych AdventureWorksDW (przykładowa baza danych – hurtownia), którą można pobrać z portalu CodePlex: http://msftdbprodsamples.codeplex.com/ (z punktu widzenia demo nie ma znaczenia wersja tej bazy).

Demo

W bazie danych AdentureWorksDW tworzymy procedurę składowaną:

USE AdventureWorksDW2012;
GO

DROP PROC IF EXISTS dbo.usp_R_KMeansCustomerSegmentation;
GO

CREATE PROCEDURE dbo.usp_R_KMeansCustomerSegmentation 
@cluster_count smallint = 6
AS
BEGIN
  DECLARE @r_code nvarchar(max);
  SET @r_code = N'clusterCount <- ' + CONVERT(nvarchar(5), @cluster_count) + N';
    df <- data.frame(Customers);
    clusterFeatures <- data.frame(df$YearlyIncome, df$TotalChildren, df$HouseOwnerFlag);
    clusterResult <- kmeans(clusterFeatures, centers=clusterCount, iter.max=8)$cluster;
    OutputDataSet <- data.frame(df, clusterResult);'
  EXECUTE sp_execute_external_script
    @language = N'R',
    @script = @r_code,
    @input_data_1 = N'
      SELECT 
        CustomerKey, 
        YearlyIncome, 
        TotalChildren, 
        CAST(HouseOwnerFlag as varchar(1)) AS HouseOwnerFlag 
      FROM dbo.DimCustomer;',
    @input_data_1_name = N'Customers'
  WITH RESULT SETS ((
    CustomerKey int NOT NULL,
    YearlyIncome float NOT NULL,
    TotalChildren int NOT NULL,
    HouseOwnerFlag varchar(1) NOT NULL,
    ClusterResult int NOT NULL
  ));
END;
GO

Procedura pobiera jeden parametr wejściowy – @cluster_count, który oznacza liczbę segmentów, na jaką chcemy podzielić klientów. W ciele procedury wywoływana jest procedura rozszerzona sp_execute_external_script, w której jako parametry przekazujemy:

  • @language – język programowania, w którym napisany jest uruchamiany skrypt – w tym wypadku język R, ale w przyszłości zapewne procedura będzie umożliwiała wykorzystanie także innych języków (np. Python),
  • @script – skrypt w języku R, tu “brzydko” sklejony z parametrem @cluster_count (nie znalazłem prostszej metody na przekazanie tego parametru do wnętrza skryptu), który wykorzystuje metodę k-średnich do segmentacji klientów w oparciu o przekazane atrybuty (YearlyIncome – roczny dochód klienta, TotalChildren – liczba posiadanych dzieci, HouseOwnerFlag – flaga posiadania domu), a wynik przekazuje w formie obiektu typu data frame na zewnątrz procedury (czyli de facto na wyjściu dostajemy tabelę do konsumowania w T-SQL),

    Uwaga: metoda kmeans wymaga pakietu stats. Upewnij się, że taki pakiet (jego katalog) znajduje się w bibliotece zainstalowanych pakietów w RRO (u mnie to był katalog: C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library). Ewentualnie użyj skryptu do przejrzenia listy zainstalowanych pakietów na potrzeby użycia w skryptach R uruchamianych z poziomu silnika SQL Server:

    EXEC sp_execute_external_script
      @language = N'R',
      @script = N'
        ip <- as.data.frame(installed.packages()[,c(1:5)])',
      @input_data_1 = N'',
      @output_data_1_name = N'ip'
    WITH RESULT SETS ((
      Package varchar(100), 
      LibPath varchar(50), 
      Version varchar(20), 
      Priority varchar(50), 
      Depends varchar(8000)
    ));
    GO

    Brakujące biblioteki najłatwiej zainstalować z poziomu Revolution R Enterprise for Windows (DevelopR) lub dowolnego innego klienta R (np. R Studio):

    image

    Po zainstalowaniu pakietu z poziomu narzędzia klienckiego wystarczy przekopiować bibliotekę spod wskazanego przez narzędzie folderu do w/w katalogu bibliotek RRO.

  • @input_data_1 – zapytanie T-SQL, którego wynik jest przekazywany do skryptu R, tu jest to pobranie atrybutów klientów,
  • @input_data_1_name – nazwa, pod jaką wynik zapytania z parametru @input_data_1 będzie widoczny w skrypcie R.

Procedura zwraca na wyjściu tabelę, która oprócz atrybutów klientów zawiera także dodatkową kolumnę informującą o numerze segmentu, do którego został przypisany klient.

Test procedury:

EXECUTE dbo.usp_R_KMeansCustomerSegmentation 6;
GO

Fragment wyniku:

image

I teraz można wykorzystać dowolne narzędzie do wizualizacji danych, by pokazać, w jaki sposób został dokonany podział na segmenty. W moim demo użyłem aplikacji Power BI Desktop, a efekt końcowy wygląda następująco:

image

Klienci zostali posegmentowani z wyraźnym podziałem wg dochodu. Pokazanie indywidualnych atrybutów na osobnych wizualizacjach (prawa strona raportu) pozwala to natychmiast zauważyć. Najliczniejszy segment to klienci zarabiający najmniej.

Pobierz pliki demo segmentacji klientów przy użyciu języka R w SQL Server 2016 (135 KB)

Podsumowanie

R wciąga :-) Możliwości tego języka są ogromne, a prostota kodu powalająca. Sądzę, że warto nabyć choć minimalne kompetencje w zakresie analityki z wykorzystaniem R, nawet będąc zatwardziałym programistą T-SQL, który świata poza swoim relacyjnym podwórkiem nie widzi. Jedno z drugim najwyraźniej się nie kłóci.

Jeśli uda Ci się zrobić coś ciekawego z wykorzystaniem R i SQL Server, daj znać – gwarantuję moje zainteresowanie!

2 thoughts on “SQL Server 2016 – R i segmentacja klientów

  1. Myślę, że osadzenie R-a wprost głęboko w bazie to zły kierunek, jak to debugować, jak utrzymywać? Zdziwię się, jeżeli to się przyjmie wśród analityków. Z zastosowań widzę jedynie produkcyjne wdrożenia zastosowań wytrenowanych już modeli, choć mając dostępny np: DeployR to to zastosowanie także stoi pod znakiem zapytania.

    IMO dużo bardziej przydatna byłaby transformacja w siss wykonująca skrypt R-owy, coś takiego ma chyba Pentaho (R script executor).

    1. Do debugowania masz oczywiście DevelopR lub dowolne inne IDE (R Studio). A osadzasz, jak napisałeś, wytrenowane modele lub coś, co wygeneruje sensowny produkt na wyjściu (np. wizualizacje). Co do SSIS – pewnie i tam się doczekamy :-)

Leave a Reply