Machine Learning in SQL

machine learning con SQL

In questo articolo vedremo passo per passo come implementare in SQL uno dei più famosi algoritmi di Machine Learning: il Perceptron. Si tratta del primo modello di classificazione la cui origine si fa risalire solitamente al lavoro di Frank Rosenblatt del 1958. Il funzionamento del Perceptron è alla base di molti altri algoritmi più evoluti come la Regressione Logistica e le rete neurali, in molti casi l’accuratezza dei suoi risultati è in linea con essi. L’algoritmo è implementato in tutte le principali librerie di Machine Learning del linguaggio Python, in questo articolo vedremo invece la sua implementazione in SQL sul database Microsoft SQL Server nel caso di classificazione binaria.

 

Acquisizione dei dati

In questo articolo lavoreremo sul noto dataset Iris contenente informazioni su due sottospecie del fiore Iris: Iris setosa e Iris Versicolor. Il file .csv che potete scaricare qui è stato costruito a partire dai dati presenti a questo link https://archive.ics.uci.edu/ml/datasets/iris, contiene 100 record e le seguenti colonne:

  • rownumber che identifica il numero di riga;
  • sepal_length contenente la lunghezza del sepalo dell’iris;
  • sepal_width contenente la larghezza del sepalo;
  • petal_length contenente la lunghezza del petalo;
  • petal_width contenente la larghezza del petalo;
  • class contenente la sottospecie Iris setosa o Iris Versicolor.

Per acquisire i dati creiamo su un database SQL Server una tabella di nome Iris con questo codice SQL:

CREATE TABLE dbo.Iris
(Rownumber INT PRIMARY KEY 
           NOT NULL,
sepal_length DECIMAL(18,4),
sepal_width DECIMAL(18,4),
petal_length DECIMAL(18,4),
petal_width DECIMAL(18,4),
class VARCHAR(255) 
      NOT NULL);

e importiamo i dati provenienti dal file tramite l’istruzione Bulk Insert

BULK INSERT dbo.StagingIris
FROM 'C:\IrisDataset.csv'
WITH
( FIRSTROW = 2,
FIELDTERMINATOR = ';', 
ROWTERMINATOR = '\n');

 

Preprocessing dei dati

Prima di descrivere l’algoritmo vero è proprio, è necessario effettuare alcune operazioni propedeutiche sui dati. In particolare occorre:

Codificare le etichette: sostituiamo Iris Setosa e Iris Versicolor con 1 e -1.

UPDATE dbo.Iris
SET    class = 1
WHERE  class = 
 'Iris setosa';
UPDATE dbo.Iris
SET    class = -1
WHERE  class = 
'Iris versicolor';

Eseguire lo split in training e test: dividiamo il file in due sottoinsiemi in maniera randomica contenenti rispettivamente il 70% e il 30% dei dati. Chiameremo il primo insieme di dati “Training set” e il secondo insieme di dati “Test set”.

ALTER TABLE dbo.Iris 
ADD IsTraining INT;
UPDATE dbo.Iris 
SET    IsTraining = 1;
UPDATE dbo.Iris
SET    IsTraining = 0
WHERE  Rownumber IN (
 SELECT TOP 30 PERCENT 
    Rownumber
 FROM dbo.Iris
 ORDER BY 
   CHECKSUM(NEWID())
  );

Gestire i null: calcoliamo la media di ogni colonna numerica del Training Set (ad eccezione del Rownumber e della classe) e utilizziamola per sostituire i null con questi valori sia nel Training Set e sia nel Test set.

DECLARE 
@AVG_sepal_length DECIMAL(18,2), 
@AVG_sepal_width DECIMAL(18,2), 
@AVG_petal_length DECIMAL(18,2), 
@AVG_petal_width DECIMAL(18,2);
SELECT @AVG_sepal_length = AVG(sepal_length),
@AVG_sepal_width = AVG(sepal_width),
@AVG_petal_length = AVG(petal_length),
@AVG_petal_width = AVG(petal_width)
FROM  dbo.Iris
WHERE IsTraining = 1;
UPDATE dbo.Iris
SET    sepal_length = @AVG_sepal_length
WHERE  sepal_length IS NULL;
UPDATE dbo.Iris
SET    sepal_width = @AVG_sepal_width
WHERE  sepal_width IS NULL;
UPDATE dbo.Iris
SET    petal_length = @AVG_petal_length
WHERE  petal_length IS NULL;
UPDATE dbo.Iris
SET    petal_width = @AVG_petal_width
WHERE  petal_width IS NULL;

Normalizzare i dati: calcoliamo la media e la deviazione standard di ogni colonna numerica del Training Set (ad eccezione del Rownumber e della classe) e sostituiamo i valori delle colonne sia nel Training Set e sia nel Test Set con questa formula

(valore – media)/deviazione standard

DECLARE 
@AVG_sepal_length DECIMAL(18,2),
 @AVG_sepal_width DECIMAL(18,2), 
@AVG_petal_length DECIMAL(18,2), 
@AVG_petal_width DECIMAL(18,2), 
@DV_sepal_length DECIMAL(18,2), 
@DV_sepal_width DECIMAL(18,2), 
@DV_petal_length DECIMAL(18,2), 
@DV_petal_width DECIMAL(18,2)
SELECT 
@AVG_sepal_length = AVG(sepal_length),
@AVG_sepal_width = AVG(sepal_width),
@AVG_petal_length = AVG(petal_length),
@AVG_petal_width = AVG(petal_width),
@DV_sepal_length = STDEVP(sepal_length),
@DV_sepal_width = STDEVP(sepal_width),
@DV_petal_length = STDEVP(petal_length),
@DV_petal_width = STDEVP(petal_width)
FROM  dbo.Iris
WHERE IsTraining = 1;
UPDATE dbo.Iris
SET    
sepal_length = (sepal_length - @AVG_sepal_length) 
                      / @DV_sepal_length,
sepal_width= (sepal_width- @AVG_sepal_width) 
                      / @DV_sepal_width,
petal_length = (petal_length - @AVG_sepal_length) 
                      / @DV_petal_length,
petal_width = (petal_width - @AVG_petal_width) 
                      / @DV_petal_width

Aggiungere una colonna con tutti 1: l’algoritmo del Perceptron ha bisogno di un’ulteriore colonna valorizzata sempre con il valore 1

ALTER TABLE dbo.Iris 
ADD Weight DECIMAL(18,2);
UPDATE dbo.Iris 
SET    Weight = 1;

 

Descrizione del Perceptron

Il Perceptron si pone l’obiettivo di trovare una formula matematica che, a partire dai valori contenuti nelle colonne sepal_length, sepal_width, petal_length, petal_width, sia capace di predire il valore presente nella colonna class. Si tratta di un classico problema di classificazione binaria. La formula matematica che cerca il Perceptron è di una tipologia ben precisa: valutare se il prodotto scalare tra una singola riga di input (comprensiva della nuova colonna con il valore uno) e un vettore w sia maggiore o minore di zero. L’algoritmo si riduce quindi a cercare iterativamente tale vettore w, che nel nostro caso avrà 5 componenti. Creiamo dunque una matrice con 5 colonne

CREATE TABLE w (
w0 DECIMAL(18,2),
w1 DECIMAL(18,2),
w2 DECIMAL(18,2),
w3 DECIMAL(18,2),
w4 DECIMAL(18,2));

e inizializziamola con una riga di tutti zero.

INSERT INTO w
(w0, w1, w2, w3, w4)
VALUES (0,0,0,0,0);

Ad ogni passo dell’algoritmo faremo le seguenti operazioni:

  • useremo il vettore w per effettuare il prodotto scalare tra esso e una riga del dataset di training;
  • se tale prodotto scalare è maggiore o uguale di 0 prediremo 1 (iris-setosa) altrimenti -1 (iris versicolor);
  • aggiorneremo il vettore w tramite questa formula

w = (w0, w1, w2,w3,w4) + 0.1*(class – predizione)*(sepal_length, sepal_width, petal_length, petal_width , 1)

Iteriamo questo procedimento per ogni riga del dataset di Training e ripetiamo l’intero procedimento un certo numero di volte detto “numero di epoche” (ad esempio 10). Il vettore w ottenuto sarà quello che utilizzeremo per tutte le classificazioni future.

 

Implementazione del Perceptron

Creiamo la stored procedure che esegue tutte le operazioni necessarie

CREATE PROCEDURE dbo.Perceptron
AS
BEGIN
--inizializziamo il vettore w
UPDATE W
SET 
w0 = 0, 
w1 = 0, 
w2 = 0, 
w3 = 0, 
w4 = 0;
--creiamo una variabile I per iterare il
--procedimento un certo numero di volte
DECLARE @I INT = 1
--decidiamo di ripetere il procedimento
--per un totale di 10 volte
WHILE @I < 10
BEGIN
--dichiariamo una variabile 
--per salvare la predizione effettuata
DECLARE @predizione DECIMAL(18,2)
--dichiariamo una variabile
-- per salvare la classe reale
DECLARE @y DECIMAL(18,2)
--dichiariamo una variabile per salvare
-- la riga in lavorazione ad ogni iterazione
DECLARE @RowNumber INT
--dichiariamo un cursore contenente le righe
-- del dataset di Training ordinate 
--in modo randomico
DECLARE cursore CURSOR
FOR   SELECT RowNumber
FROM  dbo.Iris
WHERE IsTraining = 1
ORDER BY CHECKSUM(NEWID())
--apriamo il cursore
OPEN cursore
--inseriamo il valore corrente 
--del cursore nella variabile @RowNumber
FETCH NEXT FROM cursore 
INTO @RowNumber;
--ripetiamo il procedimento 
--finché ci sono righe nel cursore
WHILE (@@FETCH_STATUS = 0)
BEGIN
--calcoliamo la predizione valutando 
--se il prodotto scalere è maggiore 
--o minore di zero, salviamo inoltre 
-- il valore reale della classe nella
--variabile @y
SELECT 
@Predizione = CASE WHEN
       sepal_length*w0+
       sepal_width*w1+
       petal_length*w2+
       petal_width*w3+
       weight*w4 >= 0
               THEN 1
               ELSE -1
               END,
@y = class
FROM dbo.Iris
CROSS JOIN w
WHERE RowNumber = 
       @RowNumber;
--aggiornamento il vettore w
UPDATE w
SET 
w0 = w0 + 0.1*(@y-@predizione)
             *sepal_length,
w1 = w1 + 0.1*(@y-@predizione)
             *sepal_width,
w2 = w2 + 0.1*(@y-@predizione)
             *petal_length,
w3 = w3 + 0.1*(@y-@predizione)
             *petal_width,
w4 = w4 + 0.1*(@y-@predizione)
             *weight
FROM w AS w
CROSS JOIN (SELECT *
            FROM dbo.Iris
            WHERE RowNumber = 
            @RowNumber) AS A
--valorizziamo @RowNumber 
--con la riga seguente del cursore
FETCH NEXT FROM cursore 
INTO @RowNumber;
END
--chiudiamo e deallochiamo il cursore
CLOSE cursore
DEALLOCATE cursore
--incrementiamo il numero di iterazioni
SET @I = @I + 1
END
SELECT * FROM dbo.w;
END

 

Valutazione dei risultati

Eseguiamo la procedura appena creata

EXEC dbo.Perceptron;

L’effetto sarà quello di valorizzare il vettore w.

w0w1w2w3w4
-0.620.48-0.18-0.88-0.40

A questo punto potremo verificare i risultati delle predizioni sul dataset di Test tramite la seguente query

SELECT 
 Iris.RowNumber,
 CASE WHEN Iris.class = 1 
      THEN 'Iris Setosa' 
      ELSE 'Iris Versicolor' 
 END AS classe,
 CASE WHEN sepal_length*w0+
           sepal_width*w1+
           petal_length*w2+
           petal_width*w3+
           weight*w4 >= 0
      THEN 'Iris Setosa' 
      ELSE 'Iris Versicolor' 
 END AS Predizione
FROM dbo.Iris
CROSS JOIN w
WHERE IsTraining = 0;

La predizione risulta corretta per tutte e trenta le righe!

Continua a imparare

Torna su