Creazioni di query
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
0
Obiettivi
Definire la funzione del processo Filtro e ordinamento e del Costruttore di
query.
Confrontare le funzionalità disponibili in ciascun processo.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
1
Processo Filtro e ordinamento e Costruttore di query
Il processo Filtro e ordinamento e il Costruttore di query possono essere
usati per creare una nuova origine dati da una o più tabelle in base ai
criteri specificati dall’utente.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
2
Quiz
Fare doppio clic su un’origine dati nel progetto. Selezionare Filtro e
ordinamento ed esaminare le schede disponibili. Quali funzionalità sono
supportate da questo processo?
a.
Estrazione di righe
b.
Selezione di colonne
c.
Calcolo di nuove colonne
d.
Controllo dell’ordinamento delle righe
e.
Sommarizzazione dei dati
f.
Creazione di un data set SAS
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
3
Test a scelta multipla – Risposte corrette
Fare doppio clic su un’origine dati nel progetto. Selezionare Filtro e
ordinamento ed esaminare le schede disponibili. Quali funzionalità sono
supportate da questo processo?
a.
Estrazione di righe
b.
Selezione di colonne
c.
Calcolo di nuove colonne
d.
Controllo dell’ordinamento delle righe
e.
Sommarizzazione dei dati
f.
Creazione di un data set SAS
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
4
Processo Filtro e ordinamento
Il processo Filtro e ordinamento consente di creare una nuova tabella
SAS selezionando righe, colonne e una sequenza di ordinamento.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
5
Quiz
Chiudere il processo Filtro e ordinamento e ritornare alla griglia dei dati.
Selezionare Costruttore di query. Quali opzioni non presenti nel
processo Filtro e ordinamento sono disponibili?
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
6
Quiz – Risposta corretta
Chiudere il processo Filtro e ordinamento e ritornare alla griglia dei
dati. Selezionare Costruttore di query. Quali opzioni non presenti nel
processo Filtro e ordinamento sono disponibili?
Risposte possibili: Nome query, Nome output, Colonne
calcolate, Gestore dei prompt, Strumenti, Opzioni, Aggiungi
tabelle, Unisci tabelle
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
7
Costruttore di query
Il Costruttore di query consente di creare una nuova tabella SAS
selezionando righe, colonne e una sequenza di ordinamento. Permette
anche di calcolare nuove colonne, unire tabelle, raggruppare,
sommarizzare e modificare attributi delle colonne.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
8
Processo Filtro e ordinamento e Costruttore di query
Filtro e
ordinamento
Costruttore di
query
Ordinare dati
Sì
Sì
Filtrare righe e colonne
Sì
Sì
Creare un nuovo data set Sì
SAS
Sì
Definire nuove colonne
No
Sì
Unire tabelle
No
Sì
Raggruppare e
sommarizzare dati
No
Sì
Definire attributi delle
colonne
No
Sì
Rimuovere duplicati
No
Sì
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
9
Creazioni di query
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
10
Obiettivo
Applicare un filtro in una query.
Escludere colonne in una query.
Riordinare le righe in una query.
Scenario
Orion Star desidera analizzare le vendite in Internet dal 2008. Per preparare i dati da
fornire ai vari processi analitici, la società deve generare una nuova origine dati dalla
tabella orders, includendo solo gli ordini in Internet effettuati in data 01JAN2008 o
successiva.
Ordini in Internet
(Order_Type =3)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
11
Processo Filtro e ordinamento
Le schede Variabili, Filtro e Ordinamento del processo Filtro e ordinamento
permettono di selezionare righe e colonne in una sequenza specificata.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
12
Filtro e ordinamento: Filtro
È possibile creare semplici filtri utilizzando nomi di variabili, operatori
e valori dei dati. Selezionare Modifica avanzata… per creare filtri più
complessi.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
13
Costruttore di filtri avanzato
Il Costruttore di filtri avanzato consente di accedere a operatori e a
funzioni SAS avanzate per creare regole più complesse per estrarre
righe.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
14
Filtro e ordinamento: Ordinamento e Risultati
È possibile ordinare in base a più variabili e definire una sequenza
crescente o decrescente. È anche possibile indicare il nome del
processo e della tabella di output.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
15
Costruttore di query
Il Costruttore di query mette a disposizione schede analoghe per
selezionare colonne, applicare filtri sulle righe e ordinare i dati. Sono
disponibili altre funzionalità, incluse le seguenti:

modificare
proprietà
colonne

raggruppare
sommarizzare
dati

applicare formati

selezionare
specifiche

unire tabelle
le
delle
e
i
righe
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
16
Costruttore di query
Nella scheda Selezione dei dati del Costruttore di query, è possibile eseguire le
seguenti operazioni:
• selezionare le colonne per l’output
• assegnare alias ed etichette
• applicare formati
• raggruppare dati
• scegliere funzioni di riepilogo
• eliminare righe duplicate.
Analogamente alla scheda Filtro nel processo Filtro e ordinamento, la scheda Filtri sui
dati del Costruttore di query costruisce anche una clausola WHERE SQL che considera
ogni riga di dati e restituisce soltanto le righe che soddisfano le condizioni di filtro.
Tuttavia, la scheda Filtri sui dati del Costruttore di query fornisce un’ulteriore
funzionalità, che consente di applicare una condizione di filtro a dati sommarizzati.
Questo tipo di condizione crea una clausola HAVING SQL, che viene applicata solo
dopo il raggruppamento.
La scheda Ordinamento dei dati del Costruttore di query e il processo Filtro e
ordinamento offrono identiche funzionalità di ordinamento.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
17
Utilizzo dei risultati delle query nei processi
Le origini dati generate dalle query possono servire come dati di input per
processi successivi.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
18
Demo L4_A
Questa demo illustra come selezionare delle colonne e applicare un filtro
sulle righe.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
19
Esercizio
Creazione di una nuova tabella nel processo Filtro e ordinamento
con un filtro di base

Utilizzare il processo Filtro e ordinamento per creare una nuova
tabella con i dipendenti di San Diego ordinati per codice di
avviamento postale.

Iniziare un nuovo progetto.

Aggiungere la tabella employee_addresses al progetto.

Utilizzando il processo Filtro e ordinamento, creare una nuova tabella
per il responsabile dell’ufficio di San Diego. Questa tabella dovrebbe
includere Employee_ID, Employee_Name, Street_Number,
Street_Name e Postal_Code.

Creare un filtro per includere soltanto i dipendenti di San Diego nella
tabella di output.

Ordinare la tabella di output in sequenza crescente di codice postale.

Chiamare il processo e la tabella di output SanDiegoEmployees.

Sottomettere il processo per creare la nuova tabella.

Salvare il progetto come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
20
Esercizio
Creazione di una nuova tabella nel Costruttore di query con un
filtro di base

Utilizzare il Costruttore di query per creare una nuova tabella che
includa tutti i dipendenti con il termine Sales nelle rispettive figure
professionali.

Aggiungere la tabella employee_organization al progetto.

Utilizzare il Costruttore di query per creare una query denominata
Sales Employees Query e una tabella denominata sales_emps.
Includere tutti i dipendenti che contengono il termine Sales come
parte delle rispettive figure professionali.

Includere tutte le colonne e ordinare la tabella risultante per
Department.

Eseguire la query e verificare i risultati. Salvare il progetto come
Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
21
Esercizio
Creazione di una nuova tabella nel processo Filtro e ordinamento
con un filtro avanzato

Utilizzare il processo Filtro e ordinamento per creare una nuova
tabella con i dipendenti di San Diego nell’area del codice di
avviamento postale 920. Ordinare le righe per codice postale.

Aggiungere
necessario.

Utilizzare il processo Filtro e ordinamento per creare una nuova
tabella per il responsabile dell’ufficio di San Diego. La tabella
dovrebbe
includere
Employee_ID,
Employee_Name,
Street_Number, Street_Name e Postal_Code.

Includere soltanto i dipendenti che hanno un valore di Postal_Code
che inizia con i caratteri 920.

Accedere al Costruttore di filtri avanzato facendo clic su nella scheda
Filtro. Può essere utilizzata la funzione SUBSTR per creare il filtro.

Ordinare la tabella di output in sequenza crescente di codice postale.

Sottomettere il processo per creare la nuova tabella.

Salvare il progetto come Capitolo4.
la
tabella
employee_addresses
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
al
progetto
se
22
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con
un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
23
Obiettivo
Definire una nuova colonna di dati in una query creando un’espressione
Scenario
Orion Star desidera analizzare i metodi di spedizione determinando quanti
giorni intercorrono fra la data di ciascun ordine e la data di consegna. La
società desidera anche calcolare l’importo totale fatturato al cliente, che è la
somma del prezzo al dettaglio e delle spese di spedizione.
Delivery_Date - Order_Date
SUM(Total_Retail_Price, Shipping)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
24
Colonne calcolate
È possibile aggiungere nuove colonne sommarizzate, colonne
ricodificate o colonne basate su un’espressione di una query nel
Costruttore di query.
Selezionare per iniziare a
creare una nuova colonna.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
25
Procedura guidata Nuova colonna calcolata
Una procedura guidata mostra come creare la nuova colonna e
assegnare attributi come il nome della colonna, l’etichetta e il formato.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
26
Editor delle espressioni
L’Editor delle espressioni consente di creare espressioni in base a
variabili, operatori e funzioni.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
27
Funzioni SAS
Una funzione SAS è una routine che restituisce un valore che è determinato dagli argomenti specificati.
Forma generale di una funzione SAS:
nome-funzione(argomento1,argomento2, . . .)
Esempio:
sum(Salary,Bonus)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
28
Utilizzo delle funzioni SAS
Le funzioni SAS possono:

eseguire operazioni aritmetiche

calcolare statistiche campionarie (per esempio: somma, media e
deviazione standard)

manipolare date SAS

elaborare valori alfanumerici

eseguire molti altri processi
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
29
Quiz
Qual è il risultato dell’espressione dati i valori di Var1, Var2 e Var3?
a.
. (mancante)
b.
3
c.
9
d.
12
Var1
9
Var2
.
Var3
3
Var1+Var2+Var3
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
30
Quiz– Risposta corretta
Qual è il risultato dell’espressione dati i valori di Var1, Var2 e Var3?
a.
. (mancante)
b.
3
c.
9
d.
12
Var1
9
Var2
.
Var3
3
Var1+Var2+Var3
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
31
Quiz
Qual è il risultato dell’espressione dati i valori di Var1, Var2 e Var3?
a.
. (mancante)
b.
3
c.
9
d.
12
Var1
9
Var2
.
Var3
3
sum(Var1,Var2,Var3)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
32
Quiz – Risposta corretta
Qual è il risultato dell’espressione dati i valori di Var1, Var2 e Var3?
a.
. (mancante)
b.
3
c.
9
d.
12
Var1
9
Var2
.
Var3
3
sum(Var1,Var2,Var3)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
33
Colonne calcolate
Le colonne calcolate appaiono nel riquadro di sinistra e possono essere
usate in un filtro, per l’ordinamento o come input per un’altra colonna
calcolata.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
34
Demo L4_B
Questa demo illustra come utilizzare la procedura guidata Colonna
calcolata per definire nuove colonne in base a espressioni avanzate
Delivery_Date - Order_Date
SUM(Total_Retail_Price, Shipping)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
35
Esercizio
Utilizzo del Costruttore di query per creare una nuova tabella con
una colonna calcolata

Utilizzare il Costruttore di query per creare New_Salary, che è lo
stipendio corrente più un aumento del 2%.

Aggiungere la tabella SAS employee_payroll al progetto Capitolo4.

Utilizzare il Costruttore di query per creare una query denominata
New Salary Query. Chiamare la tabella di output New_Salary.

Includere Employee_ID, Employee_Gender, Salary, Birth_Date
e Employee_Hire_Date. Modificare le proprietà della colonna
Salary per cambiare il nome della colonna in Old_Salary.

Includere soltanto i dipendenti attivi che hanno un valore mancante
per Employee_Term_Date.

Creare una nuova colonna denominata New_Salary che è lo
stipendio corrente più un aumento del 2%. Formattare la colonna in
modo da includere segni di dollaro e due posizioni decimali.

Sottomettere la query e verificare i risultati.

Salvare il progetto come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
36
Esercizio
Creazione di una query con una colonna calcolata che utilizza una
funzione
 Utilizzare il Costruttore di query per creare una tabella dalla tabella
employee_donations con una nuova colonna che indichi i contributi totali
di ogni dipendente.
 Aggiungere la tabella employee_donations al progetto.
 Utilizzare il Costruttore di query per creare una query denominata
Donations Query e una tabella denominata Total_Donations.
 Includere le colonne Employee_ID, Recipients e Paid_By nella nuova
tabella.
 Creare una nuova colonna denominata Total_Donations, che calcoli il
totale dei valori per le quattro donazioni trimestrali. Formattare la nuova
colonna per visualizzare segni di dollaro, virgole e due posizioni decimali.
 Utilizzare una funzione per calcolare il totale e quindi ignorare i valori
mancanti che potrebbero essere presenti nelle colonne di input.
 Sottomettere la query e verificare che la nuova colonna sia stata calcolata
correttamente.
 Salvare il progetto come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
37
Esercizio
Creazione di una
assunzione
query
per calcolare
il numero di anni
di

Utilizzare il Costruttore di query per creare una tabella che includa i
dipendenti attivi (senza una data di fine contratto) e il numero di anni
di assunzione di ciascuno.

Utilizzare la tabella employee_payroll
denominata Years Employed Query e
Years_Employed.
Includere
Employee_Hire_Date. Includere soltanto
data di fine contratto.

Creare una nuova colonna denominata Years_Employed che calcoli
il numero di anni in cui ciascun dipendente ha lavorato in Orion Star,
in base alla data di assunzione e alla data odierna. Applicare un
formato che visualizzi i valori arrotondati a una posizione decimale.

Suggerimento: la funzione YRDIF può essere utilizzata per calcolare
il numero di anni fra due date. Inoltre, la funzione TODAY può essere
utilizzata per fornire la data corrente.

Eseguire la query, verificare i risultati e salvare il progetto come
Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
per creare una query
una tabella denominata
Employee_ID
e
i dipendenti senza una
38
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati
in una query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
39
Scenario
Obiettivo
Assegnare
una
variabile
raggruppamento in una query.
di
Selezionare la variabile di analisi e la
statistica di riepilogo da calcolare.
Applicare
un
raggruppati.
filtro
sui
dati
Scenario
Orion Star intende avviare una
campagna promozionale che evidenzi
i prodotti maggiormente redditizi. La
società desidera un elenco di tutti i
prodotti
con
un
ricavo
totale
superiore a $500.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
40
Raggruppamento dei dati
Il Costruttore di query può essere
usato
per
raggruppare
e
sommarizzare i dati.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
41
Raggruppamento dei dati
I dati possono essere raggruppati e sommarizzati utilizzando la scheda
Selezione dei dati.
Scegliere una statistica per le
colonne da sommarizzare.
Le colonne senza una
statistica definiscono
automaticamente i gruppi.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
42
Raggruppamento dei dati
L’ordine delle colonne nella scheda Selezione dei dati determina la gerarchia di
raggruppamento iniziale, che si può modificare nel riquadro Riepilogo di gruppi. Per
modificare la struttura di raggruppamento dei dati, deselezionare la casella di controllo
Seleziona gruppi in automatico e scegliere Modifica gruppi….
Le statistiche includono:
•
AVG, MEAN
•
COUNT, FREQ, N (numero di valori non mancanti)
•
CSS (somma dei quadrati corretta)
•
CV (coefficiente di variazione)
•
MAX o MIN
•
NMISS (numero di valori mancanti)
•
PRT (p-value a due code per la statistica t di
•
Student)
•
RANGE (range di valori)
•
STD (deviazione standard)
•
STDERR (errore standard della media)
•
SUM
•
SUMWGT (somma dei valori della variabile
•
WEIGHT)
•
T (valore t di Student durante il test dell’ipotesi
•
che la media della popolazione sia zero)
•
USS (somma dei quadrati non corretta)
•
VAR (varianza)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
43
Raggruppamento dei dati
Alcune funzioni di riepilogo consentono di selezionare DISTINCT come
parte della funzione. Per esempio, la statistica di riepilogo AVG calcola
la media per tutte le osservazioni all’interno di ogni gruppo. Tuttavia,
la funzione di riepilogo AVG DISTINCT esclude dal calcolo i valori
duplicati, in modo che la statistica calcolata sia la media solo dei valori
distinti all’interno di ogni gruppo.
Si può inoltre creare una colonna sommarizzata utilizzando la
procedura guidata Nuova colonna calcolata.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
44
Raggruppamento in base ai valori di colonne
Il risultato della query include una riga per ogni valore univoco delle
colonne del gruppo e una statistica calcolata per le colonne
sommarizzate.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
45
Quiz
1.
Aprire il Costruttore di query e utilizzare un’origine dati nel progetto
corrente.
2.
Selezionare la scheda Filtri sui dati e osservare il layout.
3.
Ritornare alla scheda Selezione dei dati e aggiungere due colonne.
4.
Per una delle colonne nella scheda Selezione dei dati, selezionare
Conteggio nel campo Riepilogo.
5.
Ritornare alla scheda Filtri sui dati.
Come cambia la scheda Filtri sui dati quando una query include dati
raggruppati?
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
46
Quiz – Risposta corretta
Come cambia la scheda Filtri sui dati quando una query include dati
raggruppati?
Un riquadro aggiuntivo denominato “Applicazione di filtri a dati
sommarizzati” viene aggiunto alla scheda Filtri sui dati.
Con
raggruppamento
Senza
raggruppamento
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
47
Filtri sui dati
La scheda Filtri sui dati può essere usata per applicare filtri su dati
grezzi e su dati sommarizzati.
Per prima cosa, la query estrae dalle
tabelle tutte le righe che soddisfano le
condizioni specificate nel riquadro
Applicazione di filtri a dati grezzi. Quindi
i dati risultanti sono raggruppati per la
variabile di raggruppamento assegnata e
le statistiche di riepilogo sono calcolate
per ogni gruppo univoco nella scheda
Selezione dei dati. L’ulteriore estrazione
definita nel riquadro Applicazione di filtri
a dati sommarizzati è poi applicata ai
valori sommarizzati.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
48
Demo L4_C
Questa demo illustra come raggruppare, sommarizzare e applicare filtri
su dati raggruppati.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
49
Esercizio
Utilizzo del Costruttore di query per raggruppare e sommarizzare
dati

Utilizzare il Costruttore di query per creare una tabella di output con
lo stipendio medio per città.

Aggiungere la tabella employee_master al progetto Capitolo4.

Utilizzare il Costruttore di query per creare una query denominata
Average Salary per City Query e una tabella di output denominata
AvgSal_City.

Includere soltanto le colonne City e Salary nella query.

Selezionare la statistica AVG (media aritmetica) per Salary.

Dopo avere scelto una statistica di riepilogo, la sezione Gruppi
riepilogo si apre nella parte inferiore della scheda Selezione dei dati.
La casella di controllo Seleziona gruppi in automatico è
selezionata.

Ordinare la tabella in sequenza decrescente di AVG_of_Salary.

Sottomettere la query e visualizzare i risultati. Salvare il progetto
come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
50
Esercizio
Utilizzo del Costruttore di query per raggruppare, sommarizzare e
applicare filtri ai dati

Aggiungere la tabella employee_master al progetto Capitolo4, se
necessario.

Creare una nuova query denominata Salary Summary by Dept
Query e una tabella di output denominata salary_summary.

Includere Department, Employee_ID e Salary (due volte) nella
scheda Selezione dei dati. Selezionare le statistiche appropriate per
calcolare il numero di dipendenti, lo stipendio medio e lo stipendio
totale per ogni reparto.

Modificare i formati applicati alle colonne AVG_of_Salary
SUM_of_Salary per arrotondare i valori al dollaro più vicino.

Applicare un filtro alla query per includere soltanto i reparti con più di
10 dipendenti.

Ordinare i dati in sequenza decrescente di dimensioni del reparto, in
base al numero di dipendenti.

Sottomettere la query e visualizzare i risultati. Salvare il progetto
come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
e
51
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
52
Scenario
Obiettivi
Unire più tabelle in base a colonne comuni.
Includere soltanto le righe corrispondenti
Scenario
In una precedente query, sono stati identificati i prodotti con ricavi totali
superiori a $500. Gli analisti richiedevano maggiori dettagli su tali prodotti,
inclusi categoria, prodotto, fornitore e nome del paese. Le colonne da
includere provengono da tre tabelle diverse.
Top products
products
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
country
_lookup
53
Scenario
Per includere le colonne necessarie, la tabella SAS topproducts deve
essere unita alla tabella SAS products e al foglio di lavoro Excel
country_lookup.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
54
Unione di tabelle
L’unione di tabelle consente di estrarre e contemporaneamente elaborare i
dati provenienti da più tabelle.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
55
Unione di tabelle
Per impostazione predefinita, il Costruttore di query include solo le righe
corrispondenti nei risultati.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
56
Quiz
Quali clienti restituisce il Costruttore di query se queste tabelle vengono
combinate utilizzando il tipo di unione predefinito?
a.
Smith, John (00001)
b.
Anderson, Tim (00002)
c.
Jones, Betsy (00003)
d.
Customer 00004
e.
Rigsbee, Marilyn (00005)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
57
Quiz– Risposte corrette
Quali clienti restituisce il Costruttore di query se queste tabelle vengono
combinate utilizzando il tipo di unione predefinito?
a.
Smith, John (00001)
b.
Anderson, Tim (00002)
c.
Jones, Betsy (00003)
d.
Customer 00004
e.
Rigsbee, Marilyn (00005)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
58
Aggiunta Tabelle
Selezionare Aggiungi tabelle per includere altre origini dati dal
computer locale, dal server SAS o dal progetto.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
59
Finestra Tabelle e unioni
Selezionare Unisci tabelle per accedere alla finestra Tabelle e unioni,
che consente di aggiugnere altre tabelle e verificare o cambiare i
criteri usati per unire le tabelle.
Il Costruttore di query cerca di unire le tabelle
tramite colonne che hanno lo stesso nome e tipo. Se
non vi sono corrispondenze tra nomi di colonne e
tipi, un messaggio di avvertimento indica di unire le
colonne manualmente.
Per impedire a SAS Enterprise Guide di cercare di
unire tabelle tramite colonne corrispondenti,
selezionare Strumenti – Opzioni - Query e
deselezionare Cerca automaticamente di unire
le tabelle nella query
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
60
Proprietà dell’unione
La finestra Proprietà dell’unione consente di modificare il tipo di unione o
condizione. Un diverso tipo di unione può essere usato per identificare o
eliminare righe non corrispondenti.
Per modificare il tipo di join o applicare
una condizione, fare clic con il pulsante
destro del mouse sull’indicatore di unione
nella linea di unione e selezionare
Proprietà….
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
61
Proprietà dell’unione
Sono disponibili i seguenti tipi di join:

Soltanto righe corrispondenti data una condizione (predefinito) Inner join

Tutte le righe della tabella a sinistra data una condizione Left join

Tutte le righe della tabella a destra data una condizione Right join

Tutte le righe di entrambe le tabelle data una condizione Full outer join

Prodotto cartesiano Cross join

Soltanto righe corrispondenti con colonne comuni uguali Natural inner join

Tutte le righe della tabella a sinistra con colonne comuni uguali Natural left
join

Tutte le righe della tabella a destra con colonne comuni uguali Natural
right join

Tutte le righe di entrambe le tabelle con colonne comuni uguali Natural full
outer join
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
62
Opzioni della query
Selezionare Opzioni per personalizzare la query, incluso il tipo di risultato
prodotto, i limiti della query e il server SAS che la eseguirà.
63
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
63
Quiz
1.
Fare clic con il pulsante destro del mouse su un’origine dati nel progetto e
selezionare Costruttore di query….
2.
Selezionare Opzioni  Server e leggere attentamente l’avvertimento
relativo al server SAS per la query.
Supponiamo che SAS sia installato sia sulla macchina locale sia su un
server remoto. Se si desidera unire un foglio di lavoro Excel sul PC a una
tabella di ampie dimensioni sul server, che cosa occorre fare?
a. Niente. Consentire a SAS Enterprise Guide di scegliere dove elaborare la
query.
b. Modificare le opzioni della query per fare in modo che la query esegua
l’elaborazione sul server locale.
c. Modificare le opzioni della query per fare in modo che la query esegua
l’elaborazione sul server SAS remoto.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
64
Quiz– Risposta corretta
Supponiamo che SAS sia installato sia sulla macchina locale sia su un
server remoto. Se si desidera unire un foglio di lavoro Excel sul PC a una
tabella di ampie dimensioni sul server, che cosa occorre fare?
a. Niente. Consentire a SAS Enterprise Guide di scegliere dove elaborare la
query.
b. Modificare le opzioni della query per fare in modo che la query esegua
l’elaborazione sul server locale.
c. Modificare le opzioni della query per fare in modo che la query esegua
l’elaborazione sul server SAS remoto.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
65
Risultati dell’unione
Quando si uniscono tabelle nel Costruttore della query, è anche possibile
applicare filtri o effettuare l’ordinamento delle colonne delle tabelle di
input, calcolare nuove colonne o raggruppare e sommarizzare.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
66
Demo L4_D
Questa demo illustra come unire più tabelle e memorizzare il risultato in
una tabella di dati.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
67
Esercizio
Unione dei dati di due tabelle
 Utilizzare il Costruttore di query per creare una nuova tabella i cui dati siano il
risultato di un’unione di due tabelle. Creare una nuova colonna.
 Nel
progetto Capitolo4, utilizzare il Costruttore di query per unire
employee_payroll con employee_addresses e creare una tabella chiamata
employee_payroll_location. Etichettare la query Payroll Location Join Query.
 Aprire la finestra Tabelle e unioni per verificare che l’unione fra le due tabelle sia
sulla colonna Employee_ID.
 Includere le seguenti colonne nella scheda Selezione dei dati: Employee_ID,
Employee_Name, Employee_Gender, Birth_Date, Salary, Street_Number,
Street_Name, City, State e Country.
 Formattare la colonna Salary con un segno di dollaro, la virgola e due posizioni
decimali. Formattare la colonna Birth_Date nella forma 01JAN2009.
 Creare una nuova colonna denominata Bonus che rappresenti l’1,5% della colonna
Salary. Formattare i valori con un segno di dollaro, la virgola e due posizioni
decimali.
 Includere nella tabella di output soltanto i dipendenti attivi o quelli che non hanno un
valore per Employee_Term_Date.
 Sottomettere la query e visualizzare i risultati. Salvare il progetto come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
68
Esercizio
Unione dei dati di tre tabelle

Completare l’esercizio del Livello 1.

Aggiungere al progetto il file Excel country_lookup.xls. Specificare
che la prima riga del foglio di lavoro contiene i nomi delle colonne.

Modificare Payroll Location Join Query. Unire manualmente
country_lookup e employee_addresses per la colonna comune.

La colonna comune potrebbe avere un nome diverso nelle due tabelle.

Rimuovere la colonna Country nella scheda Selezione dei dati e
sostituirla con una colonna che rappresenti il nome completo dello
stato nella tabella country_lookup.

Risottomettere la query e visualizzare i risultati. Salvare il progetto
come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
69
Esercizio







Unione di dati utilizzando una condizione non-equijoin
Utilizzare il Costruttore di query per unire i dati di due origini in cui la condizione di unione
implica una condizione non-equijoin.
Due
colonne
del
foglio
di
lavoro
bonus_schedule,
Employed_After
e
Employed_Before, definiscono il range temporale per una data percentuale di bonus. Per
trovare la percentuale di bonus corretta per ogni dipendente, unire la colonna
Employee_Hire_Date e la colonna Employed_After con un operatore Maggiore o
uguale. Impostare una seconda unione con Employee_Hire_Date e questa volta
utilizzare la colonna Employed_Before con l’operatore Minore o uguale.
Unire la tabella employee_payroll con il file Excel bonus_schedule. Poiché i dati nel
foglio di lavoro Bonus Schedule non sono una tabella SAS, utilizzare il processo Importa
dati per aggiungere i dati al progetto e quindi unire le due tabelle.
Poiché non vi sono colonne corrispondenti fra queste due tabelle, è necessaria un’unione
manuale. Unire le tabelle in modo che Employee_Hire_Date corrisponda o segua la
colonna Employed_After e corrisponda o preceda la colonna Employed_Before.
Aggiungere Employee_ID, Employee_Hire_Date, Salary e Bonus_Percent alla
query.
Creare una nuova colonna denominata Bonus_Amount che moltiplichi Salary per il
valore percentuale nella colonna Bonus_Percent. Visualizzare Bonus_Amount con un
segno di dollaro, la virgola e due posizioni decimali.
Sottomettere la query e verificare i risultati. Salvare il progetto come Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
70
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non
corrispondenti (studio autonomo)
4.7 Creazione di nuove colonne ricodificando i valori
(studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
71
Scenario
Obiettivo
Eseguire diversi tipi di unione.
Scenario
Nel tentativo di conservare il più possibile i clienti acquisiti, il reparto
Marketing di Orion Star desidera individuare i clienti presenti nel database
che non hanno effettuato ordini di recente.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
72
Unione di tabelle
Tipi di unione:

Soltanto righe corrispondenti (valore predefinito di SAS
Enterprise Guide)

produce risultati in cui vengono restituite solo le righe di
una tabella che hanno una corrispondenza in tutte le altre
tabelle.

Tutte le righe da una o da entrambe le tabelle

produce risultati in cui vengono restituite tutte le righe
corrispondenti di entrambe le tabelle e le righe non
corrispondenti di almeno una tabella.
A
B
Tutte le righe da A
A
B
Tutte le righe da A e B
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
A
B
Tutte le righe da B
73
Ripasso: Solo righe corrispondenti
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
74
Ripasso: Inclusione di righe non corrispondenti
Tutte le righe da
customerdatabase
e itemsordered
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
75
Inclusione di righe non corrispondenti
Tutte le righe da
customerdatabase
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
76
Inclusione di righe non corrispondenti
Tutte le righe da
itemsordered
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
77
Proprietà dell’unione (Ripasso)
La finestra Proprietà dell’unione consente di modificare il tipo di unione o
condizione. Un diverso tipo di unione può essere usato per identificare o
eliminare righe non corrispondenti.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
78
Isolamento delle righe non corrispondenti
La query può anche includere un filtro per isolare le righe
non corrispondenti da una o da entrambe le tabelle.
Filtro per includere solo
le righe in cui
Customer_ID
è mancante dalla tabella
orders
Clienti nella tabella
CustomerDatabase che non
hanno effettuato ordini
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
79
Quiz
Qual è il tipo di unione più appropriato per iniziare a isolare gli ordini di
prodotti che non sono più inclusi nella tabella products?
a. Solo righe corrispondenti
b. Tutte le righe da products
c. Tutte le righe da orders
d. Tutte le righe da products e orders
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
80
Quiz – Risposta corretta
Qual è il tipo di unione più appropriato per iniziare a isolare gli ordini di
prodotti che non sono più inclusi nella tabella products?
a. Solo righe corrispondenti
b. Tutte le righe da products
c. Tutte le righe da orders
d. Tutte le righe da products e orders
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
81
Demo L4_E
Questa demo illustra come cambiare il tipo di unione per includere
righe non corrispondenti in una query.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
82
Esercizio
Unione di due tabelle utilizzando una outer join

Utilizzare il Costruttore di query per individuare i dipendenti che non
hanno compiuto donazioni. L’elenco verrà utilizzato per informare tali
dipendenti del programma di donazioni.

Nel
progetto
Capitolo4,
aggiungere
employee_donations, se necessario.

Costruire una query per unire la tabella employee_addresses con
employee_donations. Chiamare la query Employees Without
Donations Query e la tabella no_donations.

Modificare le proprietà dell’unione per includere tutte le righe della
tabella employee_addresses.

Includere tutte le colonne della tabella employee_addresses.

Applicare un filtro alla query per includere soltanto i valori mancanti
di Employee_ID dalla tabella employee_donations. Questo isola i
dipendenti che non compaiono nella tabella employee_donations.

Sottomettere la query e visualizzare i risultati. Salvare il progetto
come Capitolo4
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
la
tabella
83
4.1 Introduzione al processo di query
4.2 Applicazione di un filtro e ordinamento dei dati
4.3 Creazione di nuove colonne con un’espressione
4.4 Raggruppamento e sommarizzazione dei dati in una
query
4.5 Unione di tabelle
4.6 Unione di tabelle includendo le righe non corrispondenti
(studio autonomo)
4.7 Creazione di nuove colonne ricodificando i
valori (studio autonomo)
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
84
Scenario
Obiettivo
Ricodificare singoli valori o un range di valori in una colonna.
Scenario
Per analizzare ulteriormente i ricavi per ordine, il management desidera
classificare ogni ordine nei seguenti range:

da $0 a $100

da $100 a $500

$500 e oltre
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
85
Colonne ricodificate
È anche possibile derivare nuove colonne ricodificando i valori di una
colonna esistente.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
86
Valori ricodificati
La ricodifica di una colonna consente di assegnare un valore a una nuova
colonna in base al valore di una colonna esistente.
VERO
Quando Order_Type=1
Allora Order_Type_Detail
= 'Retail Sale'
FALSO
VERO
Allora Order_Type_Detail
= 'Catalog Sale'
Quando Order_Type=2
FALSO
VERO
Quando Order_Type=3
Allora Order_Type_Detail
= 'Internet Sale'
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
87
Quiz
Che cosa si dovrebbe assegnare alla nuova colonna se Order_Type =
999?
???
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
88
Quiz – Risposta corretta
Che cosa si dovrebbe assegnare alla nuova colonna se Order_Type = 999?
Rispose possibili:
Assegnare un valore mancante.
Assegnare ‘999’.
Assegnare ‘Altro’.
???
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
89
Ricodifica di una colonna
La procedura guidata Nuova colonna calcolata permette di ricodificare
i valori di una colonna esistente nella tabella di input.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
90
Sostituzione
La procedura guidata permette di specificare sostituzioni in base a
valori distinti, range o condizioni.
Determinare un valore per i
dati a cui non è assegnata
una sostituzione.
Selezionare il tipo
della nuova colonna
prima di definire
valori di sostituzione.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
91
Demo L4_F
Questa demo illustra come ricodificare i valori in una query per creare
una nuova colonna in base a una colonna esistente
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
92
Esercizio
Creazione di una nuova colonna ricodificando i valori distinti
 La colonna Country nella tabella employee_addresses include i codici
degli stati, come US o AU.
 Creare una query che includa una nuova colonna chiamata anch’essa
Country che ricodifichi i valori di US e AU nel nome dello stato per
esteso.
 Nel progetto Capitolo4, aggiungere la tabella employee_addresses al
progetto, se necessario.
 Utilizzare il Costruttore di query per creare una nuova query denominata
Country
Name
Values
Query
e
una
tabella
denominata
country_name.
 Aggiungere tutte le colonne alla scheda Selezione dei dati eccetto la
colonna Country.
 Utilizzare la procedura guidata Nuova colonna calcolata per creare una
nuova colonna chiamata Country. Creare i nuovi valori ricodificando la
colonna Country della tabella di input.
 Accertarsi di esaminare tutti i valori della colonna Country di input per
assegnare un valore ricodificato per ogni possibile codice. Ricordarsi che i
valori dei dati riconoscono le maiuscole.
 Sottomettere la query e visualizzare i risultati. Salvare il progetto come
Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
93
Esercizio
Creazione di una nuova colonna ricodificando i range di valori
 Utilizzare il Costruttore di query per creare una nuova colonna che
raggruppi i valori di retribuzione in quattro categorie. Chiamare la nuova
colonna Salary_Range.
 Aggiungere la tabella employee_payroll al progetto, se necessario.
 Utilizzare il Costruttore di query per creare una nuova query denominata
Salary Range Query e una tabella denominata Salary_Range.
 Includere soltanto le colonne Employee_ID e Employee_Hire_Date.
Formattare Employee_Hire_Date con il formato DDMMYYDw. in modo
che le date appaiano come 20-10-2008.
 Utilizzare la procedura guidata Nuova colonna calcolata per creare una
nuova colonna chiamata Salary_Range basata sui valori nella colonna
Salary.
0 – 24999.99
Below $25K

25000 – 49999.99
$25K to $50K
50000 – 99999.99
$50K to $100K
100000 and above
Over $100K
Sottomettere la query e visualizzare i risultati. Salvare il progetto
Capitolo4.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
94
Ripasso del capitolo
1.
Nominare almeno tre processi che possono essere eseguiti nel
Costruttore di query ma non nel processo Filtro e ordinamento.
2.
È possibile applicare un filtro o ordinare una colonna calcolata?
3.
Qual è il tipo di unione predefinito?
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
95
Risposte del ripasso del capitolo
1.
Nominare almeno tre processi che possono essere eseguiti
Costruttore di query ma non nel processo Filtro e ordinamento.
Definire nuove colonne.
Unire tabelle.
Raggruppare e sommarizzare dati.
Definire attributi delle colonne.
Rimuovere righe duplicate.
nel
2.
È possibile applicare un filtro o ordinare una colonna calcolata?
Sì, si può applicare un filtro o ordinare una colonna i cui valori
sono creati durante l’elaborazione.
3.
Qual è il tipo di unione predefinito?
Il tipo di unione predefinito è la inner join.
Laboratorio Informatica - SAS – Anno Accademico 2015-2016 LIUC
96