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) NULL,
  sepal_width DECIMAL(18,4) NULL,
  petal_length DECIMAL(18,4) NULL,
  petal_width DECIMAL(18,4) NULL,
  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 casuale 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 casuale*/
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

In questo articolo abbiamo implementato con il linguaggio SQL il Perceptron, uno dei più noti algoritmi di Machine Learning alla base di tutti i modelli più complessi attualmente utilizzati. Siamo partiti dal noto dataset Iris, abbiamo applicato le operazioni di Preprocessing e infine il Perceptron.

Torna in alto
Torna su