Capire i tipi di dati numerici in SQL

Lavorare con i database di qualsiasi tipo significa lavorare con i dati. Questi dati possono assumere dei diversi formati predefiniti. All'inizio del vostro percorso di apprendimento con LearnSQL.it, inizierete a comprendere i diversi tipi di dati di SQL. In questo articolo, verranno trattate le diverse varianti del tipo di dati numerici di SQL. Esamineremo anche alcune funzioni che convertono i dati da un tipo all'altro.

La creazione di tabelle è il primo passo di qualsiasi progetto di codifica SQL. Per farlo si utilizzano istruzioni DDL (Data Definition Language) come CREATE e DROP. Una volta impostata la tabella, si iniziano a elencare i nomi delle colonne e i tipi di dati in SQL. I tipi di dati indicano al database quali informazioni aspettarsi per quella colonna.

Il modo migliore per imparare a creare tabelle in SQL è il nostro corso interattivo Le basi della creazione di tabelle in SQL. Contiene 80 esercizi pratici che illustrano il comando CREATE TABLE, le chiavi primarie e straniere e come modificare una tabella in un database. In ogni esercizio si scrive del vero codice SQL che modifica la struttura del database. Ogni volta che si risolve un esercizio, aumenta la fiducia nelle proprie capacità SQL.

Supponiamo di avere una tabella di "utenti". Ogni utente ha informazioni in una colonna nome e in una colonna numero di telefono. I nomi sarebbero memorizzati in una colonna di caratteri. Il numero di telefono si trova in una colonna numerica. Quindi, le colonne numeriche memorizzano i numeri e tutti i numeri sono uguali, giusto? Non esattamente.

Esistono diversi tipi di numeri e di colonne numeriche. Chiunque abbia una formazione statistica sa che esistono quattro diverse scale di valori per i numeri: nominale, ordinale, intervallo e rapporto. A differenza di altri tipi di dati, i tipi numerici possono rappresentare tutte queste scale (ecco perché non si parla di tipo di dati numerici SQL, ma di tipi). Ma in cosa si differenziano queste scale le una dalle altre?

• I valori nominali si differenziano solo per il "nome". I valori nominali non sono trattati come numeri; non si possono sommare o sottrarre e non hanno un ordine intrinseco. Ad esempio, i numeri identificativi degli studenti e i numeri di telefono sono valori nominali. Sono più simili a un'etichetta che a un numero. Possiamo dire che i nostri numeri di telefono sono uguali (stessa importanza, stessa lunghezza), ma non possiamo confrontarli o ordinarli. Non possiamo sommarli e ottenere un numero di telefono significativo.
• I valori ordinali forniscono un ordine o un rango. Nei valori ordinali, l'ordine è l'elemento significativo. Supponiamo di avere una scala da 1 a 3 che valuta il nostro umore. Se vi sentite infelici otterrete un "1"; se siete soddisfatti otterrete un "2" e se siete felici otterrete un valore di "3". Si sa che felice ha un valore superiore a soddisfatto o infelice, ma questo è tutto. Non è possibile sommare "contento" e "infelice" per ottenere "felice".
• I valori di intervallo mostrano le differenze esatte. In una scala intervallare, ciò che conta sono le differenze tra i valori. Se si sottraggono 90 gradi centigradi da 100 gradi centigradi, si ottiene una differenza di 10 gradi. Gli anni, le date e la maggior parte delle misure di personalità sono misure di intervallo. I valori di intervallo sono numerici e vengono rappresentati come tali nel database.
• I valori di rapporto sono intervalli con un valore zero definito. Come una scala a intervalli, un rapporto presenta differenze misurabili tra i valori. In una scala di rapporti, tuttavia, un valore zero significa che non c'è nulla da misurare. Si pensi, ad esempio, alla massa, alla lunghezza e alla durata. Se uno di questi valori è pari a zero, non c'è nulla. Le scale di rapporto sono molto importanti nella scienza.

Ogni tipo di dati numerici in SQL viene utilizzato per rappresentare tutti i valori sopra citati, in particolare gli intervalli e i rapporti. In SQL è possibile confrontare i valori dei caratteri, quindi si potrebbe sostenere che anche i valori dei caratteri possono rappresentare i dati degli intervalli. Tuttavia, questo è un argomento per un altro articolo.

In SQL, i numeri sono definiti come esatti o approssimativi.

I tipi di dati numerici esatti sono SMALLINT, INTEGER, BIGINT, NUMERIC (p,s) e DECIMAL (p,s). Il tipo di dati numerici esatti in SQL significa che il valore viene memorizzato come rappresentazione letterale del valore del numero.

I tipi di dati numerici approssimativi sono FLOAT (p), REAL, e DOUBLE PRECISION. Rappresentano numeri reali, ma non sono rappresentati come numeri esatti nel database. Si tratta piuttosto di un'approssimazione del numero reale, a causa del modo in cui i sistemi informatici rappresentano i numeri. Se tutto questo vi sembra confuso, state certi che lo spiegheremo in dettaglio più avanti.

Iniziamo la nostra analisi dei tipi numerici SQL con i tipi di dati esatti o numerici.

Tipi di dati numerici SQL

I tipi di dati numerici esatti di SQL consistono nei sottotipi NUMERIC (p,s) e DECIMAL (p,s). Sono esatti e sono definiti dalla precisione (p) e dalla scala (s). La precisione è un numero intero che rappresenta il numero totale di cifre consentite in questa colonna. Queste cifre sono in un particolare radix, o base numerica, cioè binaria (base-2) o decimale (base-10). Di solito sono definite con un punto decimale. La scala, anch'essa un valore intero, rappresenta il numero di cifre decimali a destra (se positiva) o a sinistra (se negativa; è raramente utilizzata) della virgola decimale.

Vediamo un esempio. Supponendo di aver definito una colonna "equilibrio" come NUMERIC con una precisione di 8 e una scala di 2.

Il DDL avrebbe il seguente aspetto:

CREATE TABLE account (
accountNo integer,
equilibrio numeric(8,2)
);

La colonna "equilibrio" può memorizzare in modo sicuro il numero 173226,62.

P rappresenta il numero totale di tutte le cifre e s rappresenta le due cifre dopo il decimale.

C'è una piccola differenza tra i tipi di dati numerici di NUMERIC (p,s) e DECIMAL (p,s) SQL. NUMERIC determina la precisione e la scala esatta. DECIMAL specifica solo la scala esatta; la precisione è uguale o maggiore di quella specificata dal codificatore. Le colonne DECIMAL possono avere una precisione maggiore di quella specificata se ciò è più conveniente o efficiente per il sistema di database.

In altre parole, DECIMAL offre un certo margine di manovra.

Tenete presente che i dati finanziari, come i saldi dei conti, devono essere memorizzati come tipi di dati NUMERICI o DECIMALI.

Inoltre, occorre tenere presente che molti dei principali sistemi di gestione dei database hanno rappresentazioni specifiche dei tipi numerici (ad esempio, il tipo di dati NUMBER di Oracle). Queste implementazioni di solito non fanno distinzione tra i tipi NUMERIC e DECIMAL. (In Oracle, entrambi sono il tipo NUMBER).

Errori comuni nel tipo NUMERIC

Quando si inseriscono dati in una colonna NUMERIC, occorre tenere presente i limiti di precisione. Se si tenta di inserire un numero troppo grande, si potrebbe ottenere un errore. Ad esempio, si vuole inserire quanto segue:

INSERT INTO account(accountNo, equilibrio) VALUES(1313,12331411.23);

Questo produrrà un errore. Perché? Riproviamo con un numero simile:

INSERT INTO account(accountNo, equilibrio) VALUES(1313,123314.1123);

Il secondo tentativo funziona. Questo perché l'RDBMS arrotonda il numero inserito scartando le cifre "extra" a destra del punto decimale. In questo caso, ha mantenuto ".11" ma ha scartato le successive "23". Si noti che se la prima cifra scartata è un 5 o superiore, l'RDBMS arrotonderà per eccesso la cifra più a sinistra.

Nel nostro esempio, ciò significa che se si inserisce:

INSERT INTO account(accountNo, equilibrio) VALUES(1313,123314.1153);

... il saldo del database sarebbe 123314,12.

Per saperne di più sull'arrotondamento e sulle funzioni numeriche più comuni, consultate il corso SQL Basics.

I tipi di dati interi

I tipi di dati interi contengono numeri interi o senza punto decimale. ANSI SQL definisce SMALLINT, INTEGER e BIGINT come tipi di dati interi. La differenza tra questi tipi è la dimensione del numero che possono memorizzare.

Di seguito è riportata la definizione di Microsoft SQL dei vari tipi di dati interi:

Per questi tipi, la dimensione predefinita della colonna è importante. La definizione di una dimensione di colonna più piccola per i tipi interi più piccoli (se si conosce in anticipo la dimensione massima) può aiutare a mantenere le tabelle il più piccole possibile.

Errori comuni nei tipi interi

Le colonne intere arrotondano i punti decimali. Per spiegarlo, modifichiamo un po' la nostra tabella:

CREATE TABLE account (
accountNo integer,
equilibrio integer
);

Se eseguiamo questa istruzione:

INSERT INTO account(accountNo, equilibrio) VALUES(1313,123314.3153);

... i valori inseriti vengono arrotondati alla prima cifra prima della virgola. Supponiamo di inserire 123314,5 nella colonna del saldo:

INSERT INTO account(accountNo, equilibrio) VALUES(1313,123314.5);

Poiché abbiamo cambiato il valore dietro la virgola decimale in un valore uguale o superiore a 5, otterremo 123315.

Se si utilizzano tipi di dati interi nelle formule, è bene sapere che l'arrotondamento può causare incongruenze nelle formule. Se si sottrae 123314,3153+123314,3153:

INSERT INTO account(accountNo, equilibrio)    VALUES(1313,123314.3153+123314.3153);

.. il valore inserito sarebbe 123314. Se si sottraggono gli stessi valori aggiunti prima dal saldo:

SELECT BALANCE - 123314.3153 FROM ACCOUNT; 

... il risultato, ora un numero decimale, sarebbe 123314,3147. Si tratta di una chiara incoerenza. È possibile evitarla definendo i tipi di dati di colonna appropriati, a seconda delle operazioni che verranno eseguite sulle colonne.

I tipi di dati float

I tipi di dati numerici float e i tipi di dati numerici SQL correlati a float contengono valori numerici approssimativi. Sono costituiti da un significativo (un valore numerico firmato) e da un esponente (un intero firmato che specifica la grandezza del significativo). Questi tipi di dati hanno una precisione, ovvero un numero intero positivo che definisce il numero di cifre significative (esponente della base del numero).

Questo tipo di rappresentazione dei dati è comunemente chiamato rappresentazione in virgola mobile.

Se dovessimo rappresentare 173226,62 in questa notazione (con base 10), il risultato sarebbe il seguente.

Il valore di un valore numerico approssimato è il suo significativo moltiplicato per 10 all'esponente.

Per capire veramente il tipo di dati numerici SQL in virgola mobile, è necessario scavare un po' nell'informatica. Può essere divertente, ma in questa fase del vostro viaggio in SQL credo che sia eccessivo. Per il momento, è sufficiente ricordare che esistono tre tipi approssimativi SQL standard ANSI: FLOAT (p), REAL, e DOUBLE PRECISION.

La differenza tra FLOAT (p) e REAL è che FLOAT ha una precisione binaria (non decimale) uguale o superiore al valore definito. REAL ha una precisione predefinita in base al database implementato. Nella normale vita lavorativa, FLOAT viene usato raramente; REAL e DOUBLE PRECISION sono legati a particolari implementazioni del sistema e i programmatori tendono a passare il lavoro di implementazione del sistema ai DBA e ai Sysadmin.

La differenza tra REAL e DOUBLE PRECISION è che REAL rappresenta numeri a 34 bit e DOUBLE PRECISION a 64 bit.

Lavorare con i tipi approssimativi

È molto importante ricordare che questo tipo di dati numerici SQL sacrifica la precisione per l'intervallo, da cui il nome approssimativo.

Nei calcoli, i tipi approssimativi possono dare risultati strani, come 204,000000056, mentre il risultato esatto dovrebbe essere 204. Se state costruendo il vostro database per un'applicazione ingegneristica o scientifica, i tipi di dati float dovrebbero andare bene. C'è anche una differenza di velocità: se si esegue un numero eccezionalmente elevato di calcoli complicati (ad esempio, funzioni trigonometriche, ecc.) i tipi float dovrebbero essere molto più veloci di altri tipi di dati numerici. D'altra parte, se si lavora su un'applicazione finanziaria, bancaria o di altro tipo, la rappresentazione decimale è più appropriata.

Il catalogo dei tipi di dati numerici di SQL non si limita ai tipi interi e decimali. Essi riflettono la necessità di memorizzare i dati in modo sicuro, prevedibile e utilizzabile. Come in ogni linguaggio di programmazione, ci ricordano l'aspetto informatico dei database e di SQL.

Per esercitarsi su ciascun tipo di dati numerici in SQL e conoscerne le possibilità, l'uso, i vincoli e gli errori più comuni, andate su LearnSQL.it e divertitevi!