Eric Hill ( EricCollina ), sviluppatore principale dei sistemi JMP, SAS
NOTA: Questo è solo il testo del documento; gli screenshot non sono inclusi. Si prega di consultare la versione allegata del documento per ottenere il testo completo.
Generatore di query
Il nuovo strumento JMP 12 per importare i dati SQL in JMP
(Aggiornato per JMP 12.1)
Di Eric Hill, sviluppatore software, JMP
Contenuto
- Introduzione. 2
Sezione 1: Creare la connessione. 3
Sezione 2: Selezione delle tabelle per la query. 6
Sezione 3: Creazione della query. 11
Aggiunta di colonne. 13
Salvataggio della query. 15
Esecuzione della query. 15
Rispondere alle domande. 16
- Filtraggio. 19
Filtri di richiesta. 23
- Campionamento. 25
Scrivere il proprio SQL. 26
Sezione 4: Gestione delle query in background. 27
Pannello di stato della query 28
Finestra delle query di Runninq..28
Sezione 5: Condivisione delle query. 28
Sezione 6: Esecuzione di query dal linguaggio di scripting JMP (JSL). 29
Quali sono le novità di JMP 12.1?. 31
- Ricerca. 31
Aggiungi tabelle correlate. 32
Apri come modello. 34
Modifica una formula di colonna calcolata. 34
Correzioni di bug. 35
- Conclusione. 35
Introduzione
JMP mette a tua disposizione una vasta gamma di strumenti analitici, ma questi strumenti non ti servono a nulla se non riesci a ottenere i dati che devi analizzare in JMP nel modo in cui ti servono. JMP supporta da parecchio tempo l'importazione di dati da database relazionali in JMP tramite File > Database > Open Table . Tavolo aperto va bene per quanto riguarda questo aspetto, ma presenta alcune limitazioni notevoli:
- La maggior parte dei dati nei database relazionali viene archiviata in modo normalizzato, il che richiede delle unioni per ottenere tutti i dati necessari. Tuttavia, Tavolo aperto non ti aiuta con le unioni: devi scrivere tu stesso il codice SQL.
- Sebbene Tavolo aperto fornisca l' editor di formule per creare una clausola WHERE, non mostra, ad esempio, i valori distinti di una variabile categoriale per aiutarti a filtrarla.
- Può essere difficile condividere in modo sicuro le tue query con altri, perché Tavolo aperto memorizza la tua password nella stringa di connessione degli script JSL che genera.
- Il Tavolo aperto non fornisce assistenza per i passaggi di post-elaborazione richiesti una volta importati i dati, come la pulizia dei dati, la modellazione dei tipi e la formattazione.
Generatore di query è stato progettato da zero per risolvere i limiti di Tavolo aperto , aiutandoti a creare query multi-tabella con filtri interattivi sollecitati che possono essere condivisi in modo sicuro con i colleghi.
In questo articolo, utilizzerò Generatore di query con un database di esempio PostgreSQL disponibile gratuitamente chiamato dellstore2 . PostgreSQL è un database open source che può essere scaricato gratuitamente da qui:
http://www.postgresql.org/download/
Il database di esempio dellstore2 può essere trovato qui:
http://wiki.postgresql.org/wiki/Sample_Databases
Il database di esempio dellstore2 è disponibile anche per altre piattaforme di database (MySQL, Oracle e SQL Server).
Sezione 1: Creare la connessione
Avviare Generatore di query utilizzando File > Database > Query Builder dal menu:
Puoi anche avviarlo dalla prima pagina di Avviamento JMP :
Infine, su Windows, puoi trovare anche uno strumento Generatore di query sulla barra degli strumenti della finestra Home :
Dopo aver avviato Generatore di query , verrà visualizzata la finestra di dialogo Seleziona connessione al database :
La finestra di dialogo Seleziona Connessione al database è un sottoinsieme della finestra Tavolo aperto . Generatore di query e Tavolo aperto utilizzano le stesse connessioni ODBC e di fatto condividono le connessioni, quindi qualsiasi connessione effettuata in uno dei due posti verrà visualizzata in entrambi.
Fai clic su Nuova connessione… per connetterti al tuo database utilizzando un DSN esistente o per crearne uno nuovo. Ti verrà chiesto di fornire le credenziali richieste:
Dopo la connessione, JMP recupererà gli schemi dalla connessione per aiutarti a verificare che questa sia la connessione desiderata:
Puoi anche vedere le tabelle disponibili nello schema selezionato. Fai clic su Avanti per procedere al passaggio successivo.
Novità di JMP 12.2: Notate le due caselle di ricerca nel pannello Schemi – Tabelle . Sono una novità dell'aggiornamento JMP 12.1 e filtrano durante la digitazione negli elenchi Schemi e Tabelle . Sappiamo che molti di voi hanno centinaia di schemi e centinaia di tabelle in un dato schema, quindi le nuove caselle di ricerca possono aiutarvi a restringere l'elenco in modo da trovare ciò che state cercando.
Sezione 2: Selezione delle tabelle per la query
Figura 1: Selezione delle tabelle per la query
Quando clicchi su Prossimo , apparirà la finestra di dialogo Seleziona tabelle . Nella finestra di dialogo Seleziona tabelle , selezionerai le tabelle che vuoi includere nella tua query (vedi Figura 1 sopra). Seleziona le tabelle nell'elenco Tabelle disponibili che vuoi includere nella query e aggiungile agli elenchi Tabella primaria e Tabella secondaria in alto a destra.
- La tabella primaria in una query è normalmente la tabella da cui tutte le righe devono essere recuperate, indipendentemente dal fatto che abbiano un valore corrispondente in qualche altra tabella. Questa è la tua tabella dei fatti , in termini di data warehousing. Ci può essere solo una tabella primaria in una query.
- Le tabelle secondarie sono normalmente tabelle di ricerca. Per impostazione predefinita, le righe delle tabelle secondarie vengono incluse nella query solo quando il valore di una colonna nella tabella primaria corrisponde al valore di una colonna specificata nella tabella secondaria . Queste sono le tabelle dimensionali , in termini di data warehousing. Puoi aggiungere alla query tutte le tabelle secondarie che desideri.
Il pannello in basso a destra mostra informazioni sulla tabella selezionata. La scheda Colonne mostra informazioni su ogni colonna nella tabella, incluso se si tratta di una chiave di qualche tipo:
Novità di JMP 12.2:Nota la casella di ricerca nel pannello Tavoli disponibili , una novità di JMP 12.1. Utilizzala per trovare la tabella che stai cercando quando i tuoi schemi contengono molte tabelle.
Il pannello Istantanea tabella mostra un'istantanea dei dati in modo da poter verificare che la tabella contenga le informazioni necessarie:
Normalmente, quando si crea una query, si ha in mente una o più domande a cui si spera di rispondere, e queste domande guidano le decisioni su quali tabelle la query deve includere. I dati che stiamo esplorando provengono da una fittizia società di noleggio DVD. Le domande a cui vorrei rispondere sono:
- Quali generi cinematografici generano i maggiori incassi?
- Quali attori stanno generando i maggiori guadagni?
La tabella affitto ha un record per ogni evento di noleggio DVD nel corso della vita di questa azienda, quindi sarà la tabella principale per questa query. Posso vedere dal pannello Colonne che affitto ha colonne con relazioni di chiave esterna con le tabelle inventario e cliente :
Figura 2: Chiavi esterne
Una relazione di chiave esterna (nota anche come vincolo di chiave esterna) significa che i valori nella colonna di una tabella devono essere trovati nella colonna chiave primaria di un'altra tabella. Dalla Figura 2 , possiamo vedere che i valori della colonna inventario_id della tabella Affitti devono esistere nella colonna chiave primaria della tabella inventario , i valori della colonna ID cliente della tabella Affitti devono esistere nella colonna chiave primaria della tabella cliente e i valori della colonna ID_personale della tabella rentals esistono solo nella colonna chiave primaria della tabella personale . Per questa query, sto cercando maggiori informazioni sul film noleggiato, piuttosto che sul cliente del membro dello staff coinvolto nel noleggio, quindi aggiungerò la tabella inventario alla query come tabella secondaria.
Osservando il pannello Colonne per la tabella inventario , posso inoltre notare una relazione di chiave esterna con la tabella film :
Figura 3: dettagli della tabella dell'inventario
Quindi anche la tabella film va nella query. Guardando i dettagli della tabella film , vedo una colonna tasso_di_affitto , che dovrebbe essere ciò di cui ho bisogno per calcolare i ricavi, ma non vedo nulla sulla categoria del film o sugli attori nel film:
Figura 4: Colonne nella tabella dei film
Tuttavia, guardando l'elenco Tavoli disponibili , vedo una tabella attore cinematografico e una tabella categoria_film , che sembrano mappare l'ID di un film ai suoi attori e alla sua categoria, così come tabelle attore e categoria che hanno più informazioni sugli attori e sulle categorie. Quindi vorrò anche queste tabelle nella query. Tuttavia, se aggiungo semplicemente quelle 4 tabelle, qualcosa sembra andare storto:
Figura 5: Unione automatica non riuscita
Le icone "stop sign" con la "X" al centro indicano che JMP non è riuscito a unire automaticamente quelle due tabelle, quindi dovrò impostare manualmente le unioni. Se scopri che la funzione di unione automatica di JMP spesso non fa ciò di cui hai bisogno, puoi disattivarla dal triangolo rosso accanto a Seleziona tabelle per query .
Se seleziono pubblico.film_attore (t14) e clicco sul pulsante Modifica Unisciti ( ), si apre la finestra di dialogo Modifica condizione , che mi consente di configurare il join. Inizialmente, la finestra di dialogo appare così:
Figura 6: Modifica della condizione di join
poiché non esiste ancora alcuna condizione di join. Voglio unire attore_del_film.id_del_film a inventario.film_id . Per farlo, seleziono semplicemente la tabella inventario dall'elenco della colonna di sinistra e seleziono ID film in entrambi gli elenchi:
Figura 7: Modifica della condizione di join
Allo stesso modo, posso impostare la condizione di join per categoria_film_pubblica su inventory.film_id = categoria_film.film_id . Dopo averlo fatto, le cose sembrano migliori:
Figura 8: Le unioni tra tabelle sono valide
Tutte le tabelle secondarie mostrano l'icona del diagramma di Venn join esterno sinistro . Utilizzando il join appena creato tra attore cinematografico e inventario come esempio, un left outer join significa che tutte le righe della tabella inventario , che in questo caso è la tabella di sinistra, saranno incluse nella query indipendentemente dal fatto che ci sia una corrispondenza nella tabella attore cinematografico o meno, ma saranno incluse solo le righe della tabella film_actor che corrispondono a una riga nella tabella inventory .
A proposito, t1 , t2 , t3 , ecc., tra parentesi dopo i nomi delle tabelle sono gli alias di tabella che JMP assegna alle tabelle quando le aggiungi alla query. Gli alias di tabella semplificano la generazione di SQL corretto e rendono più leggibile il codice SQL generato. Puoi modificare l'alias di tabella per una tabella facendo clic con il pulsante destro del mouse sulla tabella nell'elenco delle tabelle Primario o Secondario e selezionando Cambia alias . Gli alias di tabella non possono contenere spazi o caratteri speciali.
Se hai bisogno di qualcosa di diverso dal left outer join, puoi cambiare il tipo di join usando le caselle di controllo nella finestra di dialogo Modifica Unisciti . Il tipo di join e l'icona del diagramma di Venn cambieranno per riflettere lo stato delle caselle di controllo.
Figura 9: Modifica del tipo di join
Per verificare se hai impostato correttamente i join, puoi cliccare sul pulsante Anteprima Partecipa . Questo eseguirà la query e visualizzerà uno snapshot delle prime centinaia di righe di dati, così potrai controllare che tutto sia a posto.
Ora che abbiamo tutte le tabelle di cui abbiamo bisogno nella query, possiamo fare clic su Genera query per visualizzare il Generatore di query stesso, dove possiamo specificare quali colonne includere nella query, i criteri di filtro da utilizzare, come ordinare i dati risultanti e molto altro ancora.
Sezione 3: Creazione della query
Quando inizialmente viene visualizzata la finestra Generatore di query per questa query, appare così:
- La casella Nome query contiene il nome della query, che di default è il nome della tabella primaria. Nome query è il nome assegnato alla tabella dati JMP che risulta dall'esecuzione della query, ed è anche il nome file predefinito quando si salva la query in un file. Origine dati mostra il nome dell'origine dati che la query sta utilizzando.
- La casella Tabelle mostra le tabelle che sono attualmente nella query. Per aggiungere o rimuovere tabelle o modificare join, clicca sul pulsante Modifica… .
- L'elenco Colonne disponibili mostra tutte le colonne di tutte le tabelle nella query. Seleziona le colonne da questo elenco e trascinale nei pannelli Colonne incluse , Filtri o Ordina per . Oppure, se preferisci, ci sono opzioni di clic destro per ottenere lo stesso risultato.
Novità in JMP 12.1: il pannello Colonne disponibili ora ha una casella Ricerca nel caso in cui si debba trovare una colonna specifica in un lungo elenco. Come con le altre caselle di ricerca , filtra mentre si digita. - Il pannello Colonne incluse mostra una griglia di tutte le colonne incluse nella tua query. Utilizza la griglia Colonne incluse per impostare elementi come il formato e il tipo di modellazione che i dati per la colonna dovrebbero avere dopo essere stati importati in JMP. Puoi anche specificare aggregazioni insieme a Raggruppa per per le colonne se vuoi che i tuoi dati vengano riepilogati prima di essere inseriti in JMP.
In questo pannello è presente anche una scheda Campione per generare campioni casuali di dati sul server, per quei database che supportano il campionamento. - La scheda Anteprima della query in quest'area si aggiorna automaticamente per mostrare un'istantanea dei dati che la query restituirà quando la esegui. La scheda SQL mostra l'SQL che Generatore di query sta generando in base alle selezioni correnti. La scheda Script post-query consente di specificare uno script JSL che verrà eseguito dopo il completamento della query. La scheda Stato della query mostra lo stato delle query in esecuzione in background e consente di annullarle.
- Il pannello Filtri consente di specificare i criteri per filtrare i dati (creando la clausola WHERE) e di trasformare il filtro in un prompt che viene eseguito ogni volta che si esegue la query.
- Il pannello Ordina per consente di specificare la colonna o le colonne da utilizzare per ordinare le righe restituite (clausola ORDER BY).
Aggiunta di colonne
Ogni query necessita di alcune colonne. Aggiungi colonne alla query utilizzando uno di questi metodi:
- Fare clic sul pulsante Aggiungi tutto per aggiungere tutte le colonne disponibili da tutte le tabelle nella query
- Seleziona colonne specifiche dall'elenco Colonne disponibili e fai clic su Aggiungi oppure trascinale.
- Fare doppio clic su una colonna nell'elenco Colonne disponibili .
Per questa query aggiungerò ID film , titolo e tasso_di_affitto dalla tabella film , ID categoria e nome dalla tabella categoria e attore_id , nome di battesimo e cognome dalla tabella attore .
Figura 10: Dopo aver aggiunto inizialmente le colonne
Utilizzando le colonne JMP Nome, tipo di modellazione e Formato delle Colonne incluse , posso controllare quale sarà il nome, il tipo di modellazione e il formato di ogni colonna dopo che i dati saranno stati importati in JMP.
Figura 11: Dopo aver regolato i dettagli della colonna
Ai fini di questa analisi, preferirei anche che i nomi degli attori fossero concatenati in una singola colonna anziché divisi in colonne separate nome di battesimo e cognome . Per ottenere ciò, posso aggiungere una colonna calcolata selezionando Aggiungi colonna calcolata dal menu del triangolo rosso nel pannello Colonne disponibili .
Questo aggiungerà una colonna denominata Calcolo1 all'elenco Colonne disponibili , ma posso fare clic con il pulsante destro del mouse e usare Rinomina colonna per rinominarla in NomeAttore , aggiungerla alla query e rimuovere nome di battesimo e cognome . Ecco come appare l'anteprima della query dopo averlo fatto:
Figura 12: Anteprima della query con colonna calcolata
(Quei valori di Actor Name potrebbero sembrare un po' strani, ma in realtà sono i nomi corretti del database. I nomi degli attori sembrano essere stati generati abbinando casualmente un nome con un cognome.)
Salvataggio della query
A questo punto, ho investito abbastanza tempo in questa query che vorrei salvarla in modo da non perdere il mio lavoro. Basta fare clic sul pulsante Salva in basso a destra della finestra Generatore di query per salvare la query. Ti verrà chiesto di fornire un nome file, con il testo dalla casella Nome della query fornito come predefinito. Chiamerò la query categoria_film_attore . Verrà visualizzato un messaggio in basso a sinistra della finestra Generatore di query per confermare che la query è stata salvata:
Salvando una query si crea un file con estensione .interrogazione jmp . Tornando alla finestra Generatore di query , noterai che il Nome della query è stato modificato per corrispondere al nome del file, quindi ora è categoria_film_attore .
Se devi creare un'altra query simile a quella aperta, ma vuoi conservare l'originale, puoi usare il pulsante Salva con nome... per salvare una copia con un nome diverso.
Esecuzione della query
Posso anche eseguire la query a questo punto per creare una tabella dati JMP contenente tutte le righe del database. Fai clic sul pulsante Esegui query nella parte inferiore della finestra Generatore di query per eseguire la query. Per impostazione predefinita, JMP esegue le query in background in modo che tu possa continuare a lavorare mentre le tue query vengono eseguite. Utilizza la scheda Stato query nella sezione centrale inferiore della finestra per visualizzare lo stato delle query che sono ancora in corso da questa finestra Generatore di query , oppure utilizza Visualizza > Query in esecuzione per visualizzare lo stato di tutte le query che sono attualmente in esecuzione in background. Questa particolare query impiega solo uno o due secondi, restituendo 88.020 righe:
Figura 13: Risultati della query nella tabella dati JMP
Si noti che la tabella dati JMP risultante ha 3 script e una variabile di tabella SQL definita:
Rispondere alle domande
A questo punto, sono quasi in grado di rispondere alle domande originali a cui cercavo risposte, ma devo prendere una decisione: voglio che il database riassuma i risultati o voglio importare i dati grezzi in JMP e far fare il lavoro a JMP? Se i dati grezzi non sono troppo grandi per essere inseriti nella memoria e non richiedono un tempo proibitivo per l'importazione, allora importare i dati grezzi in JMP ed effettuare l'analisi lì ha molti vantaggi: JMP è molto più capace di analizzare i dati rispetto a un database relazionale.
Per il compito in questione, è una semplice questione di usare la piattaforma Distribuzione su Nome della categoria e Nome dell'attore , usando Tariffa di noleggio come variabile Frequenza . Se ordino gli istogrammi risultanti per Conteggio ascendente , ottengo un risultato che risponde a entrambe le nostre domande:
Figura 14: Utilizzo della piattaforma di distribuzione sul risultato della query
Se, tuttavia, importare tutti i dati grezzi fosse proibitivo in termini di dimensioni o tempo trascorso, posso in alternativa usare la colonna Aggregazione nel pannello Colonne incluse per far sì che il database riassuma i dati. Per questo, tuttavia, dovrò rispondere alle due domande una alla volta.
Per rispondere alla domanda Quali generi cinematografici generano i maggiori incassi? , posso rimuovere le colonne che hanno a che fare con le informazioni sugli attori dalla query e chiedere al database di sommare il raggruppamento Tariffa di noleggio per Nome della categoria , quindi posso ordinare il risultato in base alla Tariffa di noleggio sommata. Ecco i passaggi:
- Fare clic sulla colonna Aggregazione per Tariffa di noleggio e selezionare Somma .
Si noti che Raggruppa per diventa selezionato per tutte le variabili che non hanno un'aggregazione specificata. Una volta che una qualsiasi colonna in una query specifica un'aggregazione, tutte le colonne nella query che non specificano un'aggregazione devono essere colonne Raggruppa per . - Right-click on the Nome variabile column for Tariffa di noleggio and select Ordina per.
- Selezionare SOMMA(t4.tasso_di_affitto) nel pannello Ordina per e fare clic sul pulsante con la freccia rivolta verso il basso ( Decrescente ).
Il risultato appare così:
Allo stesso modo, rimuovendo di nuovo le colonne delle categorie e reinserendo le colonne degli attori, posso rispondere alla domanda Quali attori stanno generando i maggiori guadagni? :
Filtraggio
Torniamo un attimo indietro allo stato in cui la nostra query stava recuperando tutti i dati grezzi e stavamo usando la piattaforma Distribuzione per rispondere alle nostre domande sui ricavi. Un'opzione che abbiamo quando eseguiamo l'analisi in JMP è che possiamo richiedere lo script per l'analisi JMP:
Posso quindi incollare quello script nel pannello Script post-query del Generatore di query :
Figura 15: Aggiunta di uno script JSL post-query
Da ora in poi lo script verrà eseguito ogni volta che eseguirò la query.
Ora, vorrei affinare un po' le mie domande. Invece di guardare le entrate per categoria e attore per tutti i noleggi nel database, potrei volerle guardare in base al paese in cui il film è stato noleggiato, al negozio da cui è stato noleggiato o in un certo intervallo di date.
La tabella affitto ha i campi data_di_noleggio e ID cliente , quindi sono vicino ad avere ciò di cui ho bisogno. Abbiamo visto prima che ID cliente è un riferimento di chiave esterna alla tabella cliente . Dalla tabella cliente , possiamo ottenere il paese di provenienza del cliente e il negozio da cui è stato noleggiato il DVD.
Se clicco sul pulsante Modifica… nel pannello Tabelle di Query Builder, posso tornare indietro e aggiungere altre tabelle alla query. Per ottenere informazioni sul paese, dovrò aggiungere le tabelle cliente , indirizzo , città e Paese alla query. Fortunatamente, con le relazioni chiave che l'amministratore di questo database ha impostato, tutte le tabelle si uniscono automaticamente con successo.
Dopo aver aggiunto le tabelle aggiuntive necessarie, di nuovo in Query Builder, posso aggiungere le colonne di cui ho bisogno alla query: data_di_noleggio , ID_negozio e Paese . Ho anche impostato nomi JMP amichevoli e corretto il tipo di modellazione di ID_negozio :
Figura 16: Dopo aver aggiunto le colonne Data di noleggio, ID negozio e Paese
Per prima cosa, filtriamo in base al paese del cliente. Posso fare clic con il pulsante destro del mouse sulla variabile t7.paese nel pannello Colonne incluse e selezionare Filtra per . Dopo averlo fatto, appare un filtro per paese nel pannello Filtri :
Figura 17: Filtro Paese
A questo punto, posso semplicemente scorrere l'elenco dei paesi e selezionare quelli che mi interessano, quindi eseguire la query: verranno incluse solo le righe di quei paesi.
Allo stesso modo, posso aggiungere filtri per ID_negozio e data_di_noleggio , dopodiché il pannello Filtri apparirà così:
Quando aggiungo il filtro data_di_noleggio , poiché è una variabile continua, ottengo un filtro Confronto semplice di default. Quello che vorrei invece è un filtro Allineare . Utilizzando il menu a triangolo rosso accanto a t1.data_di_noleggio , posso cambiare il tipo di filtro in Allineare :
Ora il filtro per t1.data_di_noleggio appare come segue:
che mi consente di specificare un intervallo di date. Quindi, se voglio trovare tutti gli affitti della seconda metà del 2005, posso fare:
Figura 18: Filtro intervallo date
Tutti i tipi di filtro in Generatore di query hanno un modo per disattivarli in modo efficace, consentendo di aggiungere diversi filtri a una query anche se non tutti potrebbero essere attivi per una determinata query. Per i filtri categoriali che mostrano un elenco di valori, selezionando la voce (Tutti) in alto si disattiva il filtro. Per i filtri che richiedono valori numerici, impostando il valore su mancante (.) si disattiva il filtro (o parte del filtro). Quindi, se voglio tutti gli affitti dal 1° gennaio 2006 in poi, posso fare questo:
Ora il pannello Filtri per la mia query appare come nella Figura 19 qui sotto.
| Figura 19: Utilizzo di Missing per disattivare il filtro | Figura 20: Filtro con due gruppi OR |
Con le selezioni di filtro mostrate nella Figura 19 , l'esecuzione della query ora restituirà righe in cui il paese del cliente è Francia o Germania e il DVD è stato noleggiato presso il negozio 2 il 1° gennaio 2006 o in seguito. Per impostazione predefinita, più filtri vengono uniti tramite AND, quindi una riga deve corrispondere a tutti i filtri per apparire nel risultato. Se hai bisogno di un filtro OR, fai clic sul pulsante O , che creerà un altro gruppo di filtri e i due gruppi di filtri verranno uniti tramite OR. Ad esempio, il filtro mostrato nella Figura 20 sopra restituisce i noleggi in cui la categoria è Dramma o il titolo del film contiene "Bucket" .
Filtri di richiesta
Sebbene sia bello poter specificare criteri di filtro in Generatore di query , potresti anche voler creare una query da usare da altri che non hanno realmente bisogno di vedere l'intera interfaccia utente di Generatore di query , ma vuoi che siano in grado di immettere criteri di filtro diversi ogni volta che eseguono la query. Il modo più semplice per farlo è selezionare Tutti i prompt in esecuzione dal menu del triangolo rosso accanto a Filtri :
Ciò trasforma tutti i filtri nella query in filtri di richiesta . Se vuoi che solo alcuni filtri siano di richiesta e non altri, puoi attivare e disattivare la richiesta per ogni singolo filtro utilizzando il menu a triangolo rosso.
Dopo aver impostato Tutti i prompt in esecuzione , quando eseguo questa query, mi viene chiesto di impostare i valori del filtro nel modo desiderato:
Le impostazioni predefinite per il prompt saranno quelle selezionate nel Generatore di query (o nel file di query salvato se la query viene eseguita direttamente dal file) prima dell'esecuzione della query.
Puoi anche personalizzare le etichette utilizzate nel prompt. Ad esempio, supponiamo che invece di " Paese: " volessi che il prompt dicesse " Paese del cliente: ". Tornando a Query Builder, usando il menu del triangolo rosso per il filtro t7.paese , seleziona Configura richiesta . Questo apre una finestra di dialogo che ti consente di modificare l'etichetta per il prompt e anche di modificare la tabella che fornisce i livelli di categoria per il prompt:
Campionamento
Se il tuo database supporta il campionamento casuale, la scheda Campione nella sezione centrale in alto di Generatore di query mostrerà le opzioni di campionamento supportate dal tuo database. Ho utilizzato PostgreSQL per gli esempi in questo documento, che supporta il campionamento casuale semplice di un numero specificato di righe:
SQL Server e Oracle supportano diverse opzioni di campionamento, quindi la scheda Campione rifletterà le diverse opzioni:
| Figura 21: Opzioni di campionamento di SQL Server | Figura 22: Opzioni di campionamento Oracle |
Il campionamento viene eseguito dal database prima che i dati vengano scaricati su JMP, quindi questo è un buon modo per limitare il numero di righe recuperate da query che altrimenti recupererebbero un numero proibitivo di righe.
Scrivere il proprio SQL
Riconosciamo che, nonostante le numerose funzionalità che Generatore di query ha ora o potrebbe avere in futuro, ci sono momenti in cui potresti aver bisogno di una query che faccia cose che Generatore di query non supporta direttamente. Inoltre, potresti aver già scritto e faticosamente eseguito il debug di un po' di SQL che esegue esattamente la query che desideri che JMP esegua. Generatore di query ha due opzioni per fornire il tuo SQL per una query:
- Espressione filtro personalizzata : se Generatore di query sta facendo ciò di cui hai bisogno per quanto riguarda la selezione delle tabelle e la specificazione delle colonne da includere, ma vuoi solo includere un'espressione SQL personalizzata come parte del filtro, puoi selezionare Aggiungi espressione personalizzata dal menu a triangolo rosso Filtri :
| Figura 23: Aggiunta di un'espressione di filtro personalizzata | Figura 24: Il filtro risultante |
Inserisci il codice SQL desiderato nella casella Espressione personalizzata , purché sia un codice SQL valido per la query.
Se un filtro Espressione personalizzata è impostato come filtro di richiesta, l'espressione personalizzata verrà visualizzata nella finestra di dialogo di richiesta del filtro e sarà modificabile.
- Converti l'intera query in una query SQL personalizzata : se vuoi solo scrivere il tuo SQL per un'intera query, dopo aver avviato Generatore di query e specificato la connessione dati, puoi utilizzare il menu a forma di triangolo rosso in alto a sinistra di Generatore di query accanto al campo Nome della query e selezionare Converti in SQL personalizzato :
Figura 25: Converti in SQL personalizzato
La finestra Generatore di query passerà alla modalità SQL personalizzata, offrendoti un'ampia area per immettere il tuo SQL. Se hai iniziato a creare la query in modo interattivo, la casella SQL personalizzato verrà inizializzata con il SQL generato in base alle selezioni effettuate finora (vedi Figura 26 di seguito).
Figura 26: Query SQL personalizzata
Puoi modificare il codice SQL come necessario, o incollare il codice SQL da un'altra fonte, e poi puoi cliccare sul pulsante Aggiorna per aggiornare la query Anteprima della . Alcune avvertenze:
- Non è possibile applicare un filtro richiesto con una query SQL personalizzata.
- Dopo aver convertito una query in SQL personalizzato e modificato il codice SQL, se si ripristina la query in modalità interattiva, si perderanno tutte le personalizzazioni apportate al codice SQL. Query Builder non tenta di interpretare il codice SQL.
Sezione 4: Gestione delle query in background
Generatore di query esegue le query in background per impostazione predefinita, ed è anche possibile da JMP Scripting Language (JSL) eseguire query in background (vedere Sezione 6 ). JMP fornisce due modi per visualizzare l'elenco delle query in background attualmente in esecuzione e interrompere l'esecuzione delle query se necessario.
Pannello di stato della query
Nella sezione centrale inferiore di Query Builder, c'è una scheda Stato della query . Tutte le query in background avviate da quella finestra di Query Builder appariranno in quella scheda:
Se vuoi interrompere una query in esecuzione, selezionala semplicemente nell'elenco e fai clic sul pulsante Fermare . Se sono state recuperate alcune righe, JMP aprirà una tabella dati contenente tutte le righe che sono già state recuperate. Il nome della tabella dati rifletterà che il risultato è parziale.
Finestra delle query di Runninq
Dal menu Visualizza nella finestra Home o in un'altra finestra JMP che include un menu, seleziona Query in esecuzione . Si aprirà una finestra che ha lo stesso aspetto della scheda Stato della query di Generatore di query , ma la finestra Query in esecuzione conterrà le query Tutto attualmente in esecuzione in background, indipendentemente da come sono state richiamate (una finestra Query Builder o JMP Scripting Language):
Anche in questo caso, seleziona una query e clicca su Ferma per interromperne l'esecuzione, aprendo eventuali risultati parziali.
Sezione 5: Condivisione delle query
Nell'introduzione, è stato detto che Tavolo aperto creava script difficili da condividere, perché la password del database è inclusa nella stringa di connessione. Generatore di query funziona diversamente. Se salvi un file .interrogazione jmp per una query la cui connessione richiedeva una password e apri quel file in un editor di testo, vedrai qualcosa del genere:
Puoi vedere che il nome utente ( Identificativo utente ) è presente nella stringa di connessione, ma poi vedi PWD=%_PWD_% . "%_PWD_%" non è la password; è un segnaposto inserito lì da Query Builder. Quando Query Builder vede quel segnaposto, sa che in origine era richiesta una password, quindi se questa query viene aperta in un momento in cui l'utente che la apre non ha ancora una connessione al DSN di Postgres dvdrental , Query Builder saprà di dover chiedere all'utente la password.
E che dire del DSN stesso? Cosa succede se si invia questo file .jmpquery a un utente JMP che non ha un DSN denominato Postgres dvdrental definito sulla propria macchina? Tavolo aperto non è in grado di procedere in uno scenario del genere, ma Generatore di query informerà l'utente di questa situazione e gli darà la possibilità di connettersi a un DSN diverso (che presumibilmente si connette allo stesso database) o di crearne uno nuovo.
A causa del potenziale di utenti diversi con DSN diversi definiti sulle loro macchine, alcune organizzazioni sono passate a connessioni senza DSN . Generatore di query non può attualmente convertire una stringa di connessione basata su DSN in una senza DSN, ma se si modifica una stringa di connessione nello script per una query in modo che invece di specificare un Servizio di sicurezza informatica , specifichi un AUTISTA e un SERVER , Generatore di query propagherà questa connessione senza DSN in tutti gli script o file .jmpquery che genera.
Sezione 6: Esecuzione di query dal linguaggio di scripting JMP (JSL)
Le query create da Query Builder possono anche essere eseguite da script Linguaggio di scripting JMP (JSL) . L'opzione migliore è salvare la query come file jmp .interrogazione , aprirlo da JSL ed eseguirlo. Supponiamo, ad esempio, di avere una query salvata nella cartella c:\utenti\pubblico\temp denominata pg_affitti.jmpquery . Ecco alcune cose che puoi fare con quel file di query da JSL:
- Aprire un file .jmpquery in Query Builder:
aprire( "c:\utenti\pubblico\temp\pg_rentals.jmpquery" );
domanda= aprire( "c:\utenti\pubblico\temp\pg_rentals.jmpquery",Privato );
- Aprire la query in Generatore di query per modificarla:
domanda<< Modificare;
domanda<< Correre();
- Esegui la query in primo piano. La tabella dati risultante si aprirà in JMP al termine della query. dtResult sarà un riferimento alla tabella dati risultante.
dtRisultato=domanda<< Esegui in primo piano();
- Esegui la query in primo piano, ma non aprire la tabella dati risultante in JMP; mantienila privata. dtResult sarà un riferimento alla tabella dati risultante.
dtRisultato=domanda<< Esegui in primo piano(Privato );
- Esegui la query in background. La tabella dati risultante si aprirà in JMP al termine della query. Run Background non può restituire un riferimento alla tabella dati risultante perché la query viene semplicemente avviata in background. Gli esempi successivi a questo mostrano come ottenere un riferimento alla tabella dati risultante quando si eseguono query in background.
domanda<< Eseguire lo sfondo();
- Esegui la query in background. Se la query riesce, richiama la piattaforma Distribution sulla tabella dei dati dei risultati ( queryRisultato è una variabile JSL speciale all'interno di uno script In esecuzione completata che fa riferimento alla tabella dei dati dei risultati). Se la query viene annullata dall'utente o presenta un errore, scrivi un messaggio nel registro JMP.
domanda<< Eseguire lo sfondo(
Al completamento dell'esecuzione (
queryRisultato<< Distribuzione( Distribuzione nominale( Colonna( :titolo ) ) );
),
In esecuzione annullata (Scrivere( "\!NLa query è stata annullata!" ) ),
In caso di errore (Scrivere( "\!NErrore durante l'esecuzione della query!" ); )
);
- Esegui la query in background. Dopo il completamento con successo della query, richiama la funzione MyFunc. La tabella dati dei risultati della query viene sempre passata come primo parametro alla funzione.
::La mia funzione= Funzione({ in attesa },{ Predefinito Locale },
Scrivere( "\!nNumero di noleggi: ", Righe((dt ) );
dt<< Distribuzione( Distribuzione nominale( Colonna( :titolo ) ) );
);
domanda<< Eseguire lo sfondo(Al completamento dell'esecuzione (::La mia funzione ) );
Se l'apertura di una query da un file .jmpquery separato nello script JSL non è soddisfacente, è possibile ottenere lo script non elaborato per creare un oggetto scriptabile per la query dal menu a forma di triangolo rosso in alto a sinistra della finestra Query Builder :
Ad esempio, richiamando Salva script eseguito nella finestra script per una query semplice ottengo:
Nuova query SQL restituisce un oggetto scriptabile di query SQL, proprio come fa Aprire quando apre un file .jmpquery e può essere utilizzato negli stessi modi.
Quali sono le novità di JMP 12.1?
Ricerca
Molte persone con cui parliamo hanno montagne di dati in centinaia di schemi, tabelle e colonne nei loro database. Nella versione JMP 12.1 di Query Builder, abbiamo aggiunto le caselle Ricerca agli elenchi di schemi, tabelle e colonne per aiutarti a trovare rapidamente quelli che stai cercando:
Tutte queste caselle di Ricerca si aggiornano mentre digiti e hai la possibilità di utilizzare espressioni regolari per perfezionare la ricerca.
Aggiungi tabelle correlate
Nella finestra di dialogo Seleziona tabelle per query , se una tabella aggiunta alla query contiene riferimenti di chiave esterna ad altre tabelle, è possibile aggiungere tutte le tabelle correlate alla query facendo clic sul pulsante Aggiungi tabelle correlate .
Apri come modello
Spesso, devi creare una query che sia molto simile a un'altra query che hai creato e salvato in un file .jmpquery. Puoi sempre aprire la query salvata in precedenza, apportare modifiche e usare Salva con nome per salvarla in un file .jmpquery diverso, ma se dovessi accidentalmente premere Salva per errore, la tua query originale verrebbe sovrascritta. La funzionalità Apri come modello esiste per prevenire questo scenario. Apri come modello è disponibile dal menu contestuale della finestra Home per i file .jmpquery sia su Mac che su Windows:
Quando apri un .jmpquery come modello, stai in realtà aprendo una copia della query originale che non ha alcuna associazione con il file .jmpquery su disco. Se hai aperto una query come modello, hai apportato modifiche e hai premuto accidentalmente il pulsante Salva , ti verrà chiesto di fornire un file in cui salvare la query.
È anche possibile aprire un file .jmpquery come modello da JMP Scripting Language (JSL):
aprire( "C:\Utenti\Pubblico\Temp\rentals.jmpquery",Come modello );
Modifica una formula di colonna calcolata
In JMP 12.0, potevi aggiungere una colonna calcolata alla tua query che sarebbe stata calcolata sul server del database, con i valori risultanti restituiti nella query. Tuttavia, se dovevi modificare la formula di una colonna calcolata che avevi creato in precedenza, non era possibile farlo. Invece, dovevi eliminare la colonna calcolata e aggiungerne una nuova, il che potrebbe richiedere di ricreare una formula complessa. In JMP 12.1, ora hai la possibilità di modificare una colonna calcolata. Basta fare clic con il pulsante destro del mouse sulla colonna in Colonne disponibili e selezionare Modifica formula :
Correzioni di bug
Come ci si aspetterebbe, abbiamo corretto un certo numero di bug in Query Builder in JMP 12.1, alcuni dei quali segnalati dai clienti. Se si riscontra un problema con Query Builder, segnalarlo tramite SAS Technical Support in modo che possiamo risolverlo.
Conclusione
Generatore di query è una nuova funzionalità di JMP 12 (aggiornata in JMP 12.1) che ti aiuta a creare query SQL multi-tabella altamente personalizzate sui tuoi database relazionali. L'obiettivo è di semplificare l'inserimento dei tuoi dati in JMP in formato pronto per l'analisi più rapidamente di quanto fosse possibile prima e di semplificare la condivisione sicura delle tue query. Ci auguriamo che tu lo provi e ci invii feedback (eric.hill@jmp.com) su cosa funziona bene e cosa non funziona per te.
