<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Eric Hill ( EricHill ), sviluppatore principale di sistemi JMP, SAS
NOTA: questo è solo il testo dell'articolo; gli screenshot non sono inclusi.Si prega di consultare il versione allegata del foglio per ottenere il tutto.
Generatore di query
Il nuovo strumento JMP 12 per inserire i tuoi dati SQL in JMP
(Aggiornato per JMP 12.1)
Di Eric Hill, sviluppatore software, JMP
Contenuti
- introduzione . 2
Sezione 1: Effettuare 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 delle query 28
Finestra di esecuzione delle query .. 28
Sezione 5: Condivisione delle query . 28
Sezione 6: Esecuzione di query da JMP Scripting Language (JSL) . 29
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 trasferire i dati che devi analizzare in JMP nel modo in cui ne hai bisogno.JMP supporta da tempo l'importazione di dati da database relazionali in JMP tramite File > Database > Apri tabella . Tavolo aperto va bene fin dove va, ma ha alcune limitazioni notevoli:
- La maggior parte dei dati nei database relazionali viene archiviata in modo normalizzato, richiedendo join per ottenere tutti i dati necessari, ma Tavolo aperto non ti aiuta con i join; devi scrivere tu stesso l'SQL.
- Mentre Tavolo aperto fornisce il Redattore di formule per creare una clausola WHERE, ad esempio, non mostra i valori distinti di una variabile categoriale per aiutarti a filtrarla.
- Può essere difficile condividere in modo sicuro le tue domande con altri, perché Tavolo aperto memorizza la tua password nella stringa di connessione degli script JSL che genera.
- Tavolo aperto non ti aiuta con le fasi di post-elaborazione necessarie una volta importati i dati, come la pulizia dei dati, i tipi di modellazione e la formattazione.
Generatore di query è stato progettato da zero per affrontare i limiti di Tavolo aperto , aiutandoti a creare query multitabella con filtri interattivi guidati che possono essere condivisi in modo sicuro con i colleghi.
In questo articolo utilizzerò Generatore di query con un disponibile gratuitamente PostgreSQL database di esempio chiamato dellstore2 . PostgreSQL è un database open source che può essere scaricato gratuitamente da qui:
http://www.postgresql.org/download/
IL dellstore2 il database di esempio può essere trovato qui:
http://wiki.postgresql.org/wiki/Sample_Databases
IL dellstore2 il database di esempio è disponibile anche per altre piattaforme di database (MySQL, Oracle e SQL Server).
Sezione 1: Effettuare la connessione
Iniziato Generatore di query utilizzando File > Database > Generatore di query dal menù:
Puoi anche avviarlo dalla prima pagina di Avviatore JMP :
Infine, su Windows puoi trovare anche a Generatore di query strumento sul Finestra domestica barra degli strumenti:
Dopo il lancio Generatore di query , sarai accolto con Seleziona Connessione al database la finestra di dialogo:
IL Seleziona Connessione al database la finestra di dialogo è un sottoinsieme di Tavolo aperto finestra.Generatore di query E Tavolo aperto utilizza le stesse connessioni ODBC e di fatto condividi le connessioni, quindi qualsiasi connessione effettuata in entrambi i posti verrà visualizzata in entrambi.
Clic Nuova connessione... per connettersi al database utilizzando un DSN esistente o per creare un nuovo DSN.Ti verrà richiesto 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. Clic Prossimo per continuare con il passaggio successivo.
Novità in JMP 12.2: Nota i due Ricerca scatole nel Schemi – Tabelle pannello.Questi sono nuovi nell'aggiornamento JMP 12.1 ed eseguono il filtraggio durante la digitazione nel file Schemi E Tabelle elenchi.Sappiamo che molti di voi hanno centinaia di schemi e centinaia di tabelle in un dato schema, quindi il nuovo Ricerca le caselle possono aiutarti a restringere l'elenco in modo da poter trovare ciò che stai cercando.
Sezione 2: Selezione delle tabelle per la query
Figura 1: selezione delle tabelle per la query
Quando fai clic Prossimo , IL Seleziona Tabelle verrà visualizzata la finestra di dialogo.Nel Seleziona Tabelle finestra di dialogo, selezionerai quali tabelle desideri includere nella tua query (vedi Figura 1 Sopra).Seleziona le tabelle in Tabelle disponibili elenco che desideri includere nella query e aggiungerli al file Primario E Secondario elenchi di tabelle in alto a destra.
- IL Primario La tabella in una query è normalmente la tabella da cui devono essere recuperate tutte le righe, indipendentemente dal fatto che abbiano un valore corrispondente in qualche altra tabella.Questo è tuo tabella dei fatti , in termini di data warehousing.In una query può essere presente solo una tabella primaria.
- Secondario le tabelle sono normalmente tabelle di ricerca.Per impostazione predefinita, le righe delle tabelle secondarie vengono incluse nella query solo quando il valore di una colonna nel file primario tabella corrisponde al valore di una colonna specificata in secondario tavolo.Questi sono i tuoi tabelle delle dimensioni , in termini di data warehousing.Puoi aggiungerne tanti secondario tabelle alla query come desideri.
Il pannello in basso a destra mostra le informazioni sulla tabella selezionata.IL Colonne La scheda visualizza le informazioni su ciascuna colonna nella tabella, incluso se si tratta di una chiave di qualche tipo:
Novità in JMP 12.2: Notare la Casella di ricerca nel Tabelle disponibili pannello, una novità di JMP 12.1. Usalo per trovare la tabella che stai cercando quando i tuoi schemi contengono molte tabelle.
IL Istantanea della tabella il pannello mostra un'istantanea dei dati in modo che tu possa assicurarti che la tabella contenga le informazioni di cui hai bisogno:
Normalmente, quando si crea una query, si ha in mente una o più domande a cui si spera di rispondere e tali domande guidano le decisioni su quali tabelle la query deve includere.I dati che stiamo esplorando provengono da una società fittizia di noleggio di DVD.Le domande a cui vorrei rispondere sono:
- Quali generi cinematografici generano maggiori entrate?
- Quali attori stanno generando maggiori entrate?
IL noleggio La tabella ha un record per ogni evento di noleggio di DVD nel corso della vita di questa azienda, quindi quella sarà la tabella principale per questa query.Posso vedere da Colonne pannello quello noleggio ha colonne con relazioni di chiave esterna con inventario E cliente tabelle:
Figura 2: chiavi esterne
UN chiave esterna relazione (nota anche come chiave esterna vincolo ) significa che i valori nella colonna di una tabella devono essere trovati nel file chiave primaria colonna di un altro tavolo.Da figura 2 , possiamo vedere che i valori da inventario_id colonna del affitti la tabella deve esistere in chiave primaria colonna del inventario tabella, valori da identificativo del cliente colonna del affitti la tabella deve esistere in chiave primaria colonna del cliente tabella e valori da staff_id della tabella degli affitti esiste solo nel file chiave primaria colonna del personale tavolo.Per questa query, sto cercando maggiori informazioni sul film noleggiato, piuttosto che sul cliente del membro dello staff coinvolto nel noleggio, quindi aggiungerò il inventario tabella alla query come tabella secondaria.
Guardando il Colonne pannello per il inventario tabella, posso ulteriormente vedere a chiave esterna rapporto con il film tavolo:
Figura 3: dettagli della tabella dell'inventario
Così il film anche la tabella entra nella query.Guardando i dettagli del film tavolo, vedo a tariffa_noleggio colonna, che dovrebbe essere quella che mi serve per calcolare le entrate, ma non vedo nulla sulla categoria del film o sugli attori del film:
Figura 4: colonne nella tabella dei film
Tuttavia, guardando il Tabelle disponibili elenco, vedo a film_attore tavolo e a categoria_film tabella, che sembra mappare l'ID di un film ai suoi attori e alla sua categoria, nonché attore E categoria tabelle che contengono maggiori 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 del "segnale di stop" con la "X" al centro indicano che JMP non è riuscito a unire automaticamente queste due tabelle, quindi dovrò impostare le unioni manualmente.Se ti accorgi che la funzione di unione automatica di JMP spesso non fa ciò che ti serve, puoi disattivarla dal triangolo rosso accanto a Seleziona Tabelle per la query .
Se seleziono pubblico.film_attore (t14) e fare clic su Modifica Partecipa pulsante ( ), il Modifica condizione viene visualizzata la finestra di dialogo che mi consente di configurare l'unione.Inizialmente, la finestra di dialogo si presenta così:
Figura 6: modifica della condizione di unione
poiché non esiste ancora alcuna condizione di unione.voglio partecipare film_actor.film_id A inventario.film_id .Per ottenere ciò, seleziono semplicemente il file inventario tabella da Colonna di sinistra elencare e selezionare film_id in entrambi gli elenchi:
Figura 7: modifica della condizione di unione
Allo stesso modo, posso impostare la condizione di unione per public.film_category essere inventario.film_id = film_category.film_id .Dopo averlo fatto, le cose sembrano migliori:
Figura 8: i join tra tabelle sono validi
Tutte le tabelle secondarie mostrano il file unione esterna sinistra Icona del diagramma di Venn.Usando il join che abbiamo appena creato between film_attore E inventario ad esempio, un left external join significa questo Tutto righe da inventario table, che in questo caso è la tabella di sinistra, verrà inclusa nella query se esiste una corrispondenza in film_attore tabella o no, ma solo righe da film_attore tabella che corrisponde a una riga in inventario la tabella sarà inclusa.
A proposito, il t1 , t2 , t3 , ecc., tra parentesi dopo i nomi delle tabelle ci sono gli alias di tabella che JMP assegna alle tabelle quando le aggiungi alla query.Gli alias di tabella semplificano la generazione dell'SQL corretto e rendono più leggibile l'SQL generato.Puoi modificare l'alias di una tabella facendo clic con il pulsante destro del mouse sulla tabella nel file Primario O Secondario elenco delle tabelle e selezione Cambia alias .Gli alias di tabella non possono contenere spazi o caratteri speciali.
Se hai bisogno di qualcosa di diverso dal join esterno sinistro, puoi modificare il tipo di join utilizzando le caselle di controllo sul file Modifica Partecipa dialogo.Il tipo di unione e l'icona del diagramma di Venn cambieranno per riflettere lo stato delle caselle di controllo.
Figura 9: modifica del tipo di unione
Per verificare se i join sono impostati correttamente, puoi fare clic su Anteprima Partecipa pulsante.Ciò eseguirà la query e visualizzerà un'istantanea delle prime centinaia di righe di dati in modo da poter verificare che le cose siano corrette.
Ora che abbiamo tutte le tabelle nella query di cui abbiamo bisogno, possiamo fare clic Crea query per richiamare Generatore di query stesso, dove possiamo specificare quali colonne includere nella query, criteri di filtro da utilizzare, come dovrebbero essere ordinati i dati risultanti e molto altro.
Sezione 3: Creazione della query
Quando il Generatore di query la finestra viene visualizzata inizialmente per questa query, assomiglia a questa:
- IL Nome della domanda box contiene il nome della query, che per impostazione predefinita è il nome della tabella primaria.IL Nome della domanda è il nome assegnato alla tabella dati JMP risultante dall'esecuzione della query ed è anche il nome file predefinito quando si salva la query in un file.IL Fonte di dati mostra il nome dell'origine dati utilizzata dalla query.
- IL Tabelle La casella mostra le tabelle attualmente presenti nella query.Per aggiungere o rimuovere tabelle o modificare i join, fare clic su Modifica… pulsante.
- IL Colonne disponibili list mostra tutte le colonne di tutte le tabelle nella query.Seleziona le colonne da questo elenco e trascinale nel file Colonne incluse , Filtri , O Ordinato da pannelli.Oppure, se preferisci, ci sono opzioni del tasto destro per ottenere la stessa cosa.
Novità in JMP 12.1: IL Colonne disponibili il pannello ora ha un file Ricerca nel caso in cui sia necessario trovare una colonna particolare in un lungo elenco.Come con l'altro Ricerca caselle, filtra durante la digitazione. - IL Colonne incluse il pannello mostra una griglia di tutte le colonne incluse nella query.Usa il Colonne incluse griglia per impostare elementi quali il formato e il tipo di modellazione che i dati della colonna dovrebbero avere dopo essere stati importati in JMP.È inoltre possibile specificare le aggregazioni insieme a Raggruppa per per le colonne se si desidera che i dati vengano riepilogati prima che vengano inseriti in JMP.
C'è anche un Campione scheda in questo pannello per generare campioni casuali di dati sul server, per quei database che supportano il campionamento. - IL Anteprima della query La scheda in quest'area si aggiorna automaticamente per mostrare un'istantanea dei dati che la query restituirà quando la esegui. IL SQL la scheda mostra l'SQL che Generatore di query sta generando in base alle selezioni correnti.IL Script post-interrogazione La scheda ti consente di specificare uno script JSL che verrà eseguito al termine della query.IL Stato della domanda La scheda mostra lo stato delle query in esecuzione in background e consente di annullarle.
- IL Filtri Il pannello consente di specificare i criteri per filtrare i dati (creando la clausola WHERE) e trasformare il filtro in un prompt che viene eseguito ogni volta che si esegue la query.
- IL Ordinato da Il pannello 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:
- Clicca il Aggiungi tutto pulsante per aggiungere tutte le colonne disponibili da tutte le tabelle nella query
- Seleziona colonne specifiche da Colonne disponibili elenco e fare clic Aggiungere o trascinarli sopra.
- Fare doppio clic su una colonna nel file Colonne disponibili elenco.
Per questa domanda, aggiungerò film_id , titolo , E tariffa_noleggio dal film tavolo, categoria_id E nome dal categoria tavolo e attore_id , nome di battesimo , E cognome dal attore tavolo.
Figura 10: dopo aver inizialmente aggiunto le colonne
Usando il Nome JMP, tipo di modellazione e Formato colonne del Colonne incluse , posso controllare quale sarà il nome, il tipo di modello e il formato di ciascuna colonna dopo che i dati saranno importati in JMP.
Figura 11: dopo aver modificato i dettagli della colonna
Ai fini di questa analisi, preferirei anche che i nomi degli attori fossero concatenati in un'unica colonna piuttosto che suddivisi in colonne separate. nome di battesimo E cognome colonne.Per raggiungere questo obiettivo, posso aggiungere a colonna calcolata selezionando Aggiungi colonna calcolata dal menu del triangolo rosso sul Colonne disponibili pannello.
Questo aggiungerà una colonna denominata Calc1 al Colonne disponibili list, ma posso fare clic con il tasto destro del mouse e utilizzare Rinomina colonna per rinominarlo NomeAttore , aggiungilo alla query e rimuovi nome di battesimo E cognome .Ecco come appare l'anteprima della query dopo averlo fatto:
Figura 12: anteprima della query con colonna calcolata
(I valori del nome dell'attore potrebbero sembrare un po' strani, ma in realtà sono i nomi corretti dal database.I nomi degli attori sembrano essere stati generati accoppiando casualmente un nome con un cognome.)
Salvataggio della query
A questo punto, ho abbastanza tempo investito in questa query che vorrei salvarla in modo da non perdere il lavoro.Basta fare clic su Salva pulsante in basso a destra del Generatore di query finestra per salvare la query.Ti verrà richiesto di fornire un nome file, con il testo dal file Nome della domanda scatola fornita di default.Darò un nome alla query categoria_film_attore .Verrà visualizzato un messaggio in basso a sinistra Generatore di query finestra di conferma del salvataggio della query:
Il salvataggio di una query crea un file con estensione .jmpquery estensione.Di nuovo sul Generatore di query finestra, noterai che il Nome della domanda è stato modificato per corrispondere al nome del file, quindi è ora categoria_film_attore .
Se devi creare un'altra query simile a quella che hai aperto, ma vuoi preservare l'originale, puoi utilizzare il comando Salva come… pulsante per salvare una copia con un nome diverso.
Esecuzione della query
A questo punto posso anche eseguire la query per creare una tabella dati JMP contenente tutte le righe del database.Clicca il Esegui interrogazione pulsante nella parte inferiore del Generatore di query finestra per eseguire la query. Per impostazione predefinita, JMP esegue le query in background in modo che sia possibile continuare a lavorare durante l'esecuzione delle query.Usa il Stato della domanda nella sezione in basso al centro della finestra per visualizzare lo stato delle query ancora in elaborazione da questa Generatore di query finestra o utilizzare Visualizza > Esecuzione di query per visualizzare lo stato di tutte le query attualmente in esecuzione in background.Questa particolare query richiede solo un secondo o due, 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 a SQL variabile di tabella definita:
Rispondere alle domande
A questo punto, sono quasi in grado di rispondere alle domande iniziali a cui cercavo una risposta, ma devo prendere una decisione: voglio che il database riassuma i risultati o voglio inserire i dati grezzi in JMP e fare in modo che JMP esegua il lavoro?Se i dati grezzi non sono troppo grandi per stare in memoria e non richiedono un tempo proibitivo per l'importazione, importare i dati grezzi in JMP ed eseguire lì l'analisi presenta molti vantaggi: JMP è molto più in grado di analizzare i dati rispetto a un database relazionale. Banca dati.
Per il compito in questione, è semplice utilizzare il file Distribuzione piattaforma su Nome della categoria E Nome dell'attore , utilizzando Tariffa di noleggio come il Freq variabile.Se ordino gli istogrammi risultanti per Conteggio crescente , ottengo un risultato che risponde a entrambe le nostre domande:
Figura 14: utilizzo della piattaforma di distribuzione sul risultato della query
Se, tuttavia, l'inserimento di tutti i dati grezzi fosse proibitivo in termini di dimensioni o tempo trascorso, posso in alternativa utilizzare il comando Aggregazione colonna nel Colonne incluse pannello per fare in modo che il database riepiloghi i dati.Per questo, però, dovrò rispondere alle due domande una alla volta.
Per rispondere alla domanda di Quali generi cinematografici generano maggiori entrate? , Posso rimuovere dalla query le colonne che hanno a che fare con le informazioni sull'attore e chiedere al database di sommarle Tariffa di noleggio raggruppamento per Nome della categoria , e quindi posso ordinare il risultato in base alla somma Tariffa di noleggio .Ecco i passaggi:
- Clicca il Aggregazione colonna per Tariffa di noleggio e seleziona Somma .
Notare che Raggruppa per viene controllato per tutte le variabili che non hanno un'aggregazione specificata.Una volta che una colonna in una query specifica un'aggregazione, tutte le colonne nella query che lo fanno non specificare che è richiesta un'aggregazione Raggruppa per colonne. - Fare clic con il tasto destro su Nome variabile colonna per Tariffa di noleggio e seleziona Ordinato da .
- Selezionare SOMMA(t4.tasso_noleggio) nel Ordinato da pannello e fare clic sul pulsante freccia rivolta verso il basso ( Discendente ).
Il risultato è simile al seguente:
Allo stesso modo, rimuovendo le colonne delle categorie e inserendo nuovamente le colonne degli attori, posso rispondere alla domanda di Quali attori stanno generando maggiori entrate? :
Filtraggio
Torniamo un momento allo stato in cui la nostra query stava recuperando tutti i dati grezzi e stavamo utilizzando il metodo Distribuzione piattaforma per rispondere alle nostre domande sulle entrate.Un'opzione che abbiamo quando eseguiamo l'analisi in JMP è che possiamo richiedere lo script per l'analisi JMP:
Posso quindi incollare lo script nel file Script post-interrogazione pannello di Generatore di query :
Figura 15: aggiunta di uno script JSL post-query
Lo script verrà ora eseguito ogni volta che eseguo la query.
Ora vorrei perfezionare un po’ le mie domande.Piuttosto che esaminare le entrate delle categorie e degli attori per tutti i noleggi nel database, potrei voler esaminarli in base al paese in cui il film è stato noleggiato, al negozio da cui è stato noleggiato o in un determinato intervallo di date.
IL noleggio il tavolo ha data_noleggio , E identificativo del cliente campi, quindi sono vicino ad avere ciò di cui ho bisogno.Lo abbiamo visto prima identificativo del cliente è un riferimento in chiave esterna a cliente tavolo.Dal cliente tabella, possiamo ottenere il paese di provenienza del cliente e il negozio da cui è stato noleggiato il DVD.
Se clicco su Modifica… pulsante in Tabelle pannello di Query Builder, posso tornare indietro e aggiungere più tabelle alla query.Per ottenere informazioni sul paese, dovrò aggiungere il file cliente , indirizzo , città , E Paese tabelle alla query.Fortunatamente, con le relazioni chiave impostate dall'amministratore di questo database, tutte le tabelle si uniscono automaticamente con successo.
Dopo aver aggiunto le tabelle aggiuntive necessarie, tornando nel Query Builder, posso aggiungere le colonne di cui ho bisogno alla query: data_noleggio , negozio_id , E Paese .Ho anche impostato nomi JMP descrittivi e corretto il tipo di modellazione negozio_id :
Figura 16: dopo aver aggiunto le colonne Data di noleggio, ID negozio e Paese
Innanzitutto, filtriamo in base al paese del cliente.Posso fare clic con il pulsante destro del mouse su t7.paese variabile nel Colonne incluse pannello e selezionare Filtra per .Dopo averlo fatto, nel file verrà visualizzato un filtro per paese Filtri pannello:
Figura 17: filtro Paese
A questo punto, posso semplicemente scorrere l'elenco dei paesi e selezionare quelli che mi interessano, quindi eseguire la query e verranno incluse solo le righe di quei paesi.
Allo stesso modo, posso aggiungere filtri per negozio_id E data_noleggio , dopodiché il pannello Filtri avrà il seguente aspetto:
Quando aggiungo il data_noleggio filter, poiché è una variabile continua, ottengo a Confronto semplice filtrare per impostazione predefinita.Quello che vorrei invece è un Allineare filtro.Utilizzando il menu del triangolo rosso accanto t1.data_noleggio , posso cambiare il tipo di filtro in Allineare :
Ora il filtro per t1.data_noleggio sembra:
permettendomi di specificare un intervallo di date.Quindi, se voglio trovare tutti gli affitti della seconda metà del 2005, posso fare:
Figura 18: filtro intervallo di date
Tutti i tipi di filtro in Generatore di query hanno un modo per disabilitarli efficacemente, permettendoti 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 il file (Tutto) la voce in alto disabilita il filtro.Per i filtri che richiedono valori numerici, impostare il valore su mancante (.) disabilita il filtro (o parte del filtro).Quindi, se desidero tutti i noleggi dal 1 gennaio 2006 in poi, posso farlo:
Ora il Filtri appare il pannello per la mia query Figura 19 sotto.
| Figura 19: utilizzo di Missing per disattivare il filtro | Figura 20: filtro con due gruppi OR |
Con le selezioni del filtro mostrate in Figura 19 , eseguendo la query adesso verranno restituite le righe in cui il Paese del cliente è Francia o Germania e il DVD è stato noleggiato presso il negozio 2 a partire dal 1° gennaio 2006. Per impostazione predefinita, più filtri vengono combinati con AND, quindi una riga deve corrispondere a tutti filtri da visualizzare nel risultato.Se è necessario un filtro OR, fare clic su O pulsante , che creerà un altro gruppo di filtri e i due gruppi di filtri verranno combinati in OR.Ad esempio, il filtro mostrato in Figura 20 sopra restituisce gli affitti in cui si trova la categoria Dramma O contiene il titolo del film "Secchio" .
Filtri di richiesta
Anche se è bello poter specificare i criteri di filtro in Generatore di query , potresti anche voler creare una query che possa essere utilizzata da altri che non hanno realmente bisogno di presentare il file intero Generatore di query interfaccia utente, ma vuoi che siano in grado di inserire criteri di filtro diversi ogni volta che eseguono la query.Il modo più semplice per ottenere questo risultato è selezionare Tutto richiesto durante l'esecuzione dal menu del triangolo rosso accanto Filtri :
Ciò trasforma tutti i filtri nella query in sollecitazione filtri.Se desideri che vengano visualizzati solo alcuni filtri e non altri, puoi attivare e disattivare i suggerimenti per ogni singolo filtro utilizzando il menu del triangolo rosso.
Dopo l'impostazione Tutto richiesto durante l'esecuzione , quando eseguo questa query, mi viene presentato un messaggio per impostare i valori del filtro nel modo desiderato:
Le impostazioni predefinite per il prompt saranno quelle selezionate Generatore di query (o nel file di query salvato se la query viene eseguita direttamente dal file) prima dell'esecuzione della query.
È inoltre possibile personalizzare le etichette utilizzate nel prompt.Ad esempio, supponiamo invece di “ Paese: "Volevo che il suggerimento dicesse" Paese del cliente: ”.Torna in Query Builder, utilizzando il menu del triangolo rosso per t7.paese filtrare, selezionare Configura richiesta .Viene visualizzata una finestra di dialogo che 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, il file Campione scheda nella sezione centrale in alto di Generatore di query mostrerà le opzioni di campionamento supportate dal tuo database. Ho utilizzato PostgreSQL per gli esempi di questo articolo, che supporta il semplice campionamento casuale di un numero specificato di righe:
SQL Server e Oracle supportano diverse opzioni di campionamento, quindi Campione la scheda rifletterà le diverse opzioni:
| Figura 21: opzioni di campionamento di SQL Server | Figura 22: opzioni di campionamento Oracle |
Viene eseguito il campionamento dalla banca dati , 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 di righe proibitivamente elevato.
Scrivere il proprio SQL
Lo riconosciamo, tutte le funzionalità Generatore di query ha ora o potrebbe avere in futuro, ci sono momenti in cui potresti aver bisogno di una query che faccia le cose che Generatore di query non supporta direttamente.Inoltre, molti di voi hanno già scritto ed eseguito scrupolosamente il debug di alcuni SQL che eseguono esattamente la query che è necessario che JMP esegua.Generatore di query ha due opzioni per fornire il proprio 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 specifica delle colonne da includere, ma vuoi solo includere un'espressione SQL personalizzata come parte del filtro, puoi selezionare Aggiungi espressione personalizzata dal Filtri Menù triangolo rosso:
| Figura 23: aggiunta di un'espressione di filtro personalizzata | Figura 24: Il filtro risultante |
Inserisci l'SQL che desideri nel file Espressione personalizzata box, purché sia un SQL valido per la query.
Se un Espressione personalizzata filter è impostato come filtro di prompt, l'espressione personalizzata verrà visualizzata nella finestra di dialogo del prompt del filtro e sarà modificabile.
- Converti l'intera query in un file Query SQL personalizzata – Se vuoi semplicemente scrivere il tuo SQL per un'intera query, quindi, dopo il lancio Generatore di query e specificando la connessione dati, puoi utilizzare il menu del triangolo rosso in alto a sinistra Generatore di query accanto al Nome della domanda campo e selezionare Converti in SQL personalizzato :
Figura 25: conversione in SQL personalizzato
IL Generatore di query passerà alla modalità SQL personalizzata, offrendoti un'ampia area per inserire il tuo SQL.Se avessi iniziato a creare la query in modo interattivo, il file SQL personalizzato box verrà inizializzato con l'SQL generato in base alle selezioni effettuate finora (vedi Figura 26 sotto).
Figura 26: query SQL personalizzata
È possibile modificare l'SQL secondo necessità o incollarlo da un'altra origine, quindi fare clic su Aggiornamento pulsante per aggiornare il Anteprima della query .Alcuni avvertimenti:
- Non è possibile eseguire il filtraggio richiesto con una query SQL personalizzata.
- Dopo aver convertito una query in SQL personalizzato e modificato l'SQL, se riporti la query allo stato interattivo, perderai tutte le personalizzazioni apportate all'SQL. Il generatore di query non tenta di interpretare l'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 per interrompere l'esecuzione delle query, se necessario.
Pannello di stato delle query
Nella sezione centrale in basso di Query Builder, c'è a Stato della domanda scheda.Eventuali query in background avviate da quella finestra del generatore di query apparirà in quella scheda:
Se desideri interrompere una query in esecuzione, selezionala semplicemente nell'elenco e fai clic su Fermare pulsante.Se sono state recuperate alcune righe, JMP aprirà una tabella di dati contenente tutte le righe già recuperate.Il nome della tabella dati rifletterà che il risultato è parziale.
Finestra di esecuzione delle query
Dal Visualizzazione menu nella finestra principale o in un'altra finestra JMP che include un menu, selezionare Esecuzione di query .Si aprirà una finestra che avrà lo stesso aspetto del file Stato della domanda scheda di Generatore di query , ma il Esecuzione di query la finestra conterrà Tutto query attualmente in esecuzione in background, indipendentemente da come sono state richiamate (una finestra del generatore di query o un linguaggio di scripting JMP):
Anche in questo caso, seleziona una query e fai clic Fermare per interromperne l'esecuzione, aprendo qualsiasi risultato parziale.
Sezione 5: Condivisione delle query
Nell'introduzione si diceva di ciò Tavolo aperto creavano script difficili da condividere perché la password del database era inclusa nella stringa di connessione.Generatore di query funziona diversamente. Se salvi un .jmpquery file per una query la cui connessione richiedeva una password e apri quel file in un editor di testo, vedrai qualcosa di simile a questo:
Puoi vedere che il nome utente ( UID ) è presente nella stringa di connessione, ma poi vedi PWD=%_PWD_% .“%_PWD_%” non è la password; è un segnaposto inserito da Query Builder.Quando Query Builder vede quel segnaposto, sa che originariamente era richiesta una password, quindi se questa query viene aperta in un momento in cui l'utente che la apre non ha già una connessione al Noleggio dvd Postgres DSN, Query Builder saprà richiedere all'utente la password.
E che dire del DSN stesso?Cosa succede se invii questo file .jmpquery a un utente JMP che non ha un DSN denominato Noleggio dvd Postgres definito sulla loro 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 crearne uno nuovo.
A causa della possibilità che utenti diversi abbiano DSN diversi definiti sulle loro macchine, alcune organizzazioni sono passate a Connessioni senza DSN .Generatore di query al momento non è possibile 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 una DSN , specifica a AUTISTA E SERVER , Generatore di query propagherà questa connessione senza DSN in tutti gli script o file .jmpquery che genera.
Sezione 6: Esecuzione di query da JMP Scripting Language (JSL)
È anche possibile eseguire le query create da Query Builder Linguaggio di scripting JMP (JSL) script. L'opzione migliore è salvare la query come file .jmpquery file, aprilo da JSL ed eseguilo. Supponiamo, ad esempio, di avere una query salvata nel file c:\utenti\pubblico\temp cartella denominata pg_rentals.jmpquery .Ecco alcune cose che puoi fare con quel file di query da JSL:
- Apri un file .jmpquery nel Generatore di query:
aprire ( "c:\utenti\pubblico\temp\pg_rentals.jmpquery" ) ;
domanda = aprire ( "c:\utenti\pubblico\temp\pg_rentals.jmpquery" , Privato ) ;
- Apri la query in Generatore di query per la modifica:
domanda << Modificare ;
domanda << Correre () ;
- Esegui la query in primo piano. La tabella dati risultante verrà aperta in JMP al termine della query. dtResult sarà un riferimento alla tabella dei dati dei risultati.
dtRisultato = domanda << Corri in primo piano () ;
- Esegui la query in primo piano, ma fallo non aprire la tabella dati risultante in JMP; mantenerlo privato.dtResult sarà un riferimento alla tabella dei dati dei risultati.
dtRisultato = domanda << Corri in primo piano ( Privato ) ;
- Esegui la query in background. La tabella dati risultante verrà aperta in JMP al termine della query. Run Background non può restituire un riferimento alla tabella dei dati dei risultati perché la query viene semplicemente avviata in background.Gli esempi successivi a questo mostrano come ottenere un riferimento alla tabella dei dati dei risultati durante l'esecuzione di query in background.
domanda << Esegui lo sfondo () ;
- Esegui la query in background. Se la query ha esito positivo, richiamare la piattaforma di distribuzione nella tabella dei dati dei risultati ( queryResult è una variabile JSL speciale all'interno di un file In esecuzione completata script che fa riferimento alla tabella dei dati dei risultati). Se la query viene annullata dall'utente o presenta un errore, scrivere un messaggio nel registro JMP.
domanda << Esegui lo sfondo (
In esecuzione completata (
queryResult << Distribuzione ( Distribuzione nominale ( Colonna ( : titolo ) ) ) ;
) ,
In esecuzione annullato ( Scrivere ( "\!NLa query è stata annullata!") ) ,
In caso di errore ( Scrivere ( "\!NErrore durante l'esecuzione della query!") ; )
) ;
- Esegui la query in background.Una volta completata con successo la query, richiamare la funzione MyFunc. La tabella dei dati dei risultati della query viene sempre passata come primo parametro alla funzione.
:: MyFunc = Funzione ( { dt } , { Locale predefinito } ,
Scrivere ( "\!nNumero di noleggi: " , NRighe ( dt ) ) ;
dt << Distribuzione ( Distribuzione nominale ( Colonna ( : titolo ) ) ) ;
) ;
domanda << Esegui lo sfondo ( In esecuzione completata ( :: MyFunc ) ) ;
Se l'apertura di una query da un file .jmpquery separato nello script JSL non è soddisfacente, è possibile ottenere lo script grezzo per creare un oggetto scriptabile della query dal menu del triangolo rosso in alto a sinistra del Generatore di query finestra:
Ad esempio, invocando Salva Esegui script nella finestra Script per una semplice query mi dà:
Nuova query SQL restituisce un oggetto scriptabile SQL Query proprio come Aprire fa quando si apre un file .jmpquery e può essere utilizzato negli stessi modi.
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 Ricerca box agli elenchi di schemi, tabelle e colonne per aiutarti a trovare rapidamente quelli che stai cercando:
Tutti questi Ricerca le caselle si aggiornano durante la digitazione e hai la possibilità di utilizzarle espressioni regolari per affinare la tua ricerca
Aggiungi tabelle correlate
Nel Seleziona Tabelle per la query finestra di dialogo, se una tabella che hai aggiunto alla query ha riferimenti di chiavi esterne ad altre tabelle, puoi aggiungere tutte quelle tabelle correlate alla query facendo clic sul pulsante Aggiungi tabelle correlate pulsante.
Apri come modello
Spesso è necessario creare una query molto simile a un'altra query creata e salvata in un file .jmpquery.Puoi sempre aprire la query salvata in precedenza, apportarvi modifiche e utilizzare Salva come per salvarlo in un file .jmpquery diverso, ma se dovessi colpire accidentalmente Salva per errore, la tua query originale verrebbe sovrascritta.IL Apri come modello esiste una funzione per prevenire tale 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 realmente aprendo un file copia della query originale che non ha alcuna associazione con il file .jmpquery sul disco.Se hai aperto una query come modello, apportato modifiche e accidentalmente hai premuto il file Salva pulsante, ti verrà richiesto 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\Pubblica\Temp\rentals.jmpquery" , Come modello ) ;
Modifica una formula di colonna calcolata
In JMP 12.0, potresti aggiungere alla query una colonna calcolata che verrebbe calcolata sul server del database, con i valori risultanti restituiti nella query.Tuttavia, se avessi avuto bisogno di modificare la formula di una colonna calcolata creata in precedenza, non sarebbe stato possibile farlo.Invece, dovevi eliminare la colonna calcolata e aggiungerne una nuova, probabilmente richiedendo di ricreare una formula complessa.In JMP 12.1 ora hai la possibilità di modificare una colonna calcolata.È sufficiente fare clic con il pulsante destro del mouse sulla colonna Colonne disponibili e seleziona Modifica formula :
Correzioni di bug
Come prevedibile, abbiamo corretto una serie di bug nel Query Builder in JMP 12.1, alcuni segnalati dai clienti.Se riscontri un problema utilizzando Query Builder, segnala il problema tramite il supporto tecnico SAS in modo che possiamo risolverlo.
Conclusione
Generatore di query è una nuova funzionalità di JMP 12 (aggiornata in JMP 12.1) che consente di creare query SQL multitabella altamente personalizzate sui database relazionali.L'obiettivo è semplificare l'inserimento dei dati in JMP in un formato pronto per l'analisi più rapidamente di quanto fosse possibile prima e semplificare la condivisione sicura delle query.Ci auguriamo che lo proverai e ci invierai feedback ( eric.hill@jmp.com ) su cosa funziona bene e cosa non funziona per te.
