<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Éric Hill ( EricHill ), Développeur principal de systèmes JMP, SAS
REMARQUE : Ceci n'est que le texte de l'article ; les captures d'écran ne sont pas incluses.Veuillez consulter le version ci-jointe du papier pour obtenir le tout.
Générateur de requêtes
Le nouvel outil JMP 12 pour importer vos données SQL dans JMP
(Mise à jour pour JMP 12.1)
Par Eric Hill, développeur de logiciels, JMP
Contenu
- Introduction . 2
Section 1 : Établir la connexion . 3
Section 2 : Sélection de tables pour la requête . 6
Section 3 : Construire la requête . 11
Ajout de colonnes . 13
Enregistrer la requête . 15
Exécuter la requête . 15
Répondre aux questions . 16
- Filtration . 19
Filtres d'invite . 23
- Échantillonnage . 25
Écrire votre propre SQL . 26
Section 4 : Gestion des requêtes en arrière-plan . 27
Panneau d'état de la requête 28
Fenêtre Exécuter des requêtes .. 28
Section 5 : Partage de requêtes . 28
Section 6 : Exécution de requêtes à partir du langage de script JMP (JSL) . 29
Quoi de neuf dans JMP 12.1 ? . 31
- Recherche . 31
Ajouter des tables associées . 32
Ouvrir comme modèle . 34
Modifier une formule de colonne calculée . 34
Corrections de bugs . 35
- Conclusion . 35
Introduction
JMP met à votre disposition une vaste gamme d'outils d'analyse, mais ces outils ne vous servent à rien si vous ne parvenez pas à obtenir les données dont vous avez besoin pour les analyser dans JMP de la manière dont vous en avez besoin.JMP prend en charge l'importation de données depuis des bases de données relationnelles vers JMP depuis un certain temps via Fichier > Base de données > Ouvrir la table . Table ouverte c'est bien dans la mesure où cela va, mais il a quelques limitations notables :
- La plupart des données des bases de données relationnelles sont stockées de manière normalisée, ce qui nécessite des jointures pour obtenir toutes les données dont vous avez besoin, mais Table ouverte ne vous aide pas avec les jointures ; vous devez écrire le SQL vous-même.
- Alors que Table ouverte fournit le Éditeur de formule pour créer une clause WHERE, elle ne vous montre pas, par exemple, les valeurs distinctes d'une variable catégorielle pour vous aider à la filtrer.
- Il peut être difficile de partager vos requêtes en toute sécurité avec d'autres, car Table ouverte stocke votre mot de passe dans la chaîne de connexion des scripts JSL qu'il génère.
- Table ouverte ne vous aide pas avec les étapes de post-traitement requises une fois les données importées, telles que le nettoyage des données, les types de modélisation et le formatage.
Générateur de requêtes a été conçu dès le départ pour répondre aux limites de Table ouverte , vous aidant à créer des requêtes multi-tables avec un filtrage interactif invité qui peuvent être partagées en toute sécurité avec vos collègues.
Dans cet article, j'utiliserai Générateur de requêtes avec un disponible gratuitement PostgreSQL exemple de base de données appelée dellstore2 . PostgreSQL est une base de données open source qui peut être téléchargée gratuitement ici :
http://www.postgresql.org/download/
Le dellstore2 un exemple de base de données peut être trouvé ici :
http://wiki.postgresql.org/wiki/Sample_Databases
Le dellstore2 Un exemple de base de données est également disponible pour d'autres plates-formes de bases de données (MySQL, Oracle et SQL Server).
Section 1 : Établir la connexion
Lancer Générateur de requêtes en utilisant Fichier > Base de données > Générateur de requêtes dans le menu :
Vous pouvez également le lancer depuis la première page de Démarreur JMP :
Enfin, sous Windows, vous pouvez également trouver un Générateur de requêtes outil sur le Fenêtre d'accueil barre d'outils :
Après le lancement Générateur de requêtes , vous serez accueilli avec Sélectionnez la connexion à la base de données boite de dialogue:
Le Sélectionnez la connexion à la base de données la boîte de dialogue est un sous-ensemble de Table ouverte fenêtre.Générateur de requêtes et Table ouverte utilisez les mêmes connexions ODBC et partagez en fait des connexions, de sorte que toutes les connexions que vous établissez à l'un ou l'autre endroit apparaîtront aux deux.
Cliquez sur Nouvelle connexion… pour vous connecter à votre base de données à l'aide d'un DSN existant ou pour créer un nouveau DSN.Vous serez invité à fournir toutes les informations d'identification requises :
Après la connexion, JMP récupérera les schémas de la connexion pour vous aider à vous assurer qu'il s'agit bien de la connexion souhaitée :
Vous pouvez également voir les tables disponibles dans le schéma sélectionné. Cliquez sur Suivant pour passer à l'étape suivante.
Nouveau dans JMP 12.2 : Remarquez les deux Recherche des cases dans le Schémas – Tableaux panneau.Ceux-ci sont nouveaux dans la mise à jour JMP 12.1 et effectuent un filtrage au fur et à mesure de la saisie dans le Schémas et les tables listes.Nous savons que beaucoup d'entre vous ont des centaines de schémas et des centaines de tables dans un schéma donné. Recherche les cases peuvent vous aider à affiner la liste afin que vous puissiez trouver ce que vous recherchez.
Section 2 : Sélection de tables pour la requête
Figure 1 : Sélection de tables pour la requête
Quand vous cliquez Suivant , le Sélectionner des tableaux une boîte de dialogue apparaîtra.Dans le Sélectionner des tableaux boîte de dialogue, vous sélectionnerez les tables que vous souhaitez inclure dans votre requête (voir Figure 1 au-dessus de).Sélectionnez des tables dans le Tableaux disponibles liste que vous souhaitez inclure dans la requête et ajoutez-les à la Primaire et Secondaire listes de tableaux en haut à droite.
- Le Primaire La table d'une requête est normalement la table à partir de laquelle toutes les lignes doivent être récupérées, qu'elles aient ou non une valeur correspondante dans une autre table.C'est ton tableau de faits , en termes d'entreposage de données.Il ne peut y avoir qu'une seule table principale dans une requête.
- Secondaire les tables sont normalement des tables de recherche.Par défaut, les lignes des tables secondaires ne sont incluses dans la requête que lorsque la valeur d'une colonne du primaire le tableau correspond à la valeur d'une colonne spécifiée dans le secondaire tableau.Ce sont vos tableaux de dimensions , en termes d'entreposage de données.Vous pouvez en ajouter autant secondaire tables à la requête comme vous le souhaitez.
Le panneau en bas à droite affiche des informations sur la table sélectionnée.Le Colonnes L'onglet affiche des informations sur chaque colonne du tableau, notamment s'il s'agit d'une clé quelconque :
Nouveau dans JMP 12.2 : Noter la Barre de recherche dans le Tableaux disponibles panneau, qui est nouveau dans JMP 12.1. Utilisez-le pour trouver la table que vous recherchez lorsque vos schémas contiennent de nombreuses tables.
Le Aperçu du tableau Le panneau affiche un instantané des données afin que vous puissiez vous assurer que le tableau contient les informations dont vous avez besoin :
Normalement, lorsque vous créez une requête, vous avez en tête une ou plusieurs questions auxquelles vous espérez répondre, et ces questions déterminent les décisions concernant les tables que votre requête doit inclure.Les données que nous explorons proviennent d’une société de location de DVD fictive.Les questions auxquelles j'aimerais répondre sont :
- Quels genres de films génèrent le plus de revenus ?
- Quels acteurs génèrent le plus de revenus ?
Le de location La table contient un enregistrement pour chaque événement de location de DVD au cours de la vie de cette entreprise. Ce sera donc la table principale pour cette requête.Je peux voir du Colonnes panneau qui de location comporte des colonnes avec des relations de clé étrangère avec le inventaire et client les tables:
Figure 2 : Clés étrangères
UN clé étrangère relation (également connue sous le nom de clé étrangère contrainte ) signifie que les valeurs de la colonne d'un tableau doivent être trouvées dans le clé primaire colonne de un autre tableau.Depuis Figure 2 , nous pouvons voir que les valeurs du inventaire_id colonne du locations table doit exister dans le clé primaire colonne du inventaire tableau, valeurs du N ° de client colonne du locations table doit exister dans le clé primaire colonne du client tableau et les valeurs du staff_id colonne du tableau des locations n'existe que dans la clé primaire colonne du personnel tableau.Pour cette requête, je recherche plus d'informations sur le film loué, plutôt que sur le client du membre du personnel impliqué dans la location, j'ajouterai donc le inventaire table à la requête en tant que table secondaire.
En regardant le Colonnes panneau pour le inventaire tableau, je peux voir plus loin un clé étrangère relation avec le film tableau:
Figure 3 : détails du tableau d'inventaire
Alors le film la table entre également dans la requête.En regardant les détails du film table, je vois un taux_location colonne, qui devrait être ce dont j'ai besoin pour calculer les revenus, mais je ne vois rien sur la catégorie du film ni sur les acteurs du film :
Figure 4 : Colonnes de la table film
Cependant, en regardant le Tableaux disponibles liste, je vois un Acteur de cinéma table et un catégorie_film tableau, qui semble mapper l'identifiant d'un film à ses acteurs et à sa catégorie, ainsi que acteur et catégorie des tableaux contenant plus d’informations sur les acteurs et les catégories.Je vais donc également vouloir ces tables dans la requête.Cependant, si j'ajoute simplement ces 4 tableaux, quelque chose ne va pas :
Figure 5 : Échec de la jointure automatique
Les icônes « panneau d'arrêt » avec le « X » au milieu signifient que JMP n'a pas pu joindre automatiquement ces deux tables, je devrai donc configurer les jointures manuellement.Si vous constatez que la fonction de jointure automatique de JMP ne fait souvent pas ce dont vous avez besoin, vous pouvez la désactiver à partir du triangle rouge à côté de Sélectionner les tables pour la requête .
Si je sélectionne public.film_actor (t14) et cliquez sur le Modifier Rejoindre bouton ( ), le Modifier la condition La boîte de dialogue apparaît, me permettant de configurer la jointure.Initialement, la boîte de dialogue ressemble à ceci :
Figure 6 : Modification de la condition de jointure
puisqu'aucune condition de jointure n'existe encore.Je veux joindre film_actor.film_id à inventaire.film_id .Pour ce faire, je sélectionne simplement le inventaire tableau du Colonne de gauche liste et sélectionnez film_id dans les deux listes :
Figure 7 : Modification de la condition de jointure
De même, je peux définir la condition de jointure pour public.film_category être inventaire.film_id = film_category.film_id .Après cela, les choses s'améliorent :
Figure 8 : Les jointures entre les tables sont valides
Tous les tableaux secondaires montrent le jointure externe gauche Icône du diagramme de Venn.En utilisant la jointure que nous venons de faire entre Acteur de cinéma et inventaire à titre d'exemple, une jointure externe gauche signifie que tous lignes du inventaire table, qui est la table de gauche dans ce cas, sera incluse dans la requête pour savoir s'il y a une correspondance dans le Acteur de cinéma table ou non, mais uniquement les lignes du Acteur de cinéma tableau qui correspond à une ligne du inventaire le tableau sera inclus.
D'ailleurs, le t1 , t2 , t3 , etc., entre parenthèses après les noms de table se trouvent les alias de table que JMP attribue aux tables lorsque vous les ajoutez à la requête.Les alias de table facilitent la génération du SQL correct et rendent le SQL généré plus lisible.Vous pouvez modifier l'alias d'une table en cliquant avec le bouton droit sur la table dans le Primaire ou Secondaire liste de tables et sélection Changer d'alias .Les alias de table ne peuvent pas contenir d'espaces ou de caractères spéciaux.
Si vous avez besoin d'autre chose qu'une jointure externe gauche, vous pouvez modifier le type de jointure à l'aide des cases à cocher sur le Modifier Rejoindre dialogue.Le type de jointure et l'icône du diagramme de Venn changeront pour refléter l'état des cases à cocher.
Figure 9 : Modification du type de jointure
Pour vérifier si les jointures sont correctement configurées, vous pouvez cliquer sur le bouton Aperçu Rejoindre bouton.Cela exécutera la requête et affichera un instantané des centaines de premières lignes de données afin que vous puissiez vérifier que tout semble correct.
Maintenant que nous avons toutes les tables dont nous avons besoin dans la requête, nous pouvons cliquer sur Construire une requête élever Générateur de requêtes lui-même, où nous pouvons spécifier les colonnes à inclure dans la requête, les critères de filtrage à utiliser, la manière dont les données résultantes doivent être classées, et bien plus encore.
Section 3 : Construire la requête
Quand le Générateur de requêtes La fenêtre apparaît initialement pour cette requête, elle ressemble à ceci :
- Le Nom de la requête La zone contient le nom de la requête, qui est par défaut le nom de la table principale.Le Nom de la requête est le nom attribué à la table de données JMP résultant de l'exécution de la requête et constitue également le nom de fichier par défaut lors de l'enregistrement de la requête dans un fichier.Le La source de données vous montre le nom de la source de données utilisée par la requête.
- Le les tables La boîte de dialogue vous montre les tables actuellement dans la requête.Pour ajouter ou supprimer des tables ou modifier des jointures, cliquez sur l'icône Changement… bouton.
- Le Colonnes disponibles list affiche toutes les colonnes de toutes les tables de la requête.Sélectionnez des colonnes dans cette liste et faites-les glisser vers le Colonnes incluses , Filtres , ou Commandé par panneaux.Ou, si vous préférez, il existe des options de clic droit pour accomplir la même chose.
Nouveau dans JMP 12.1 : Le Colonnes disponibles le panneau a maintenant un Recherche au cas où vous auriez besoin de trouver une colonne particulière dans une longue liste.Comme pour les autres Recherche cases, il filtre au fur et à mesure que vous tapez. - Le Colonnes incluses Le panneau affiche une grille de toutes les colonnes incluses par votre requête.Utilisez le Colonnes incluses grille pour définir des éléments tels que le format et le type de modélisation que les données de la colonne doivent avoir après avoir été importées dans JMP.Vous pouvez également spécifier des agrégations avec Par groupe pour les colonnes si vous souhaitez que vos données soient résumées avant qu'elles n'entrent dans JMP.
Il y a aussi Échantillon dans ce panneau pour générer des échantillons aléatoires de données sur le serveur, pour les bases de données prenant en charge l'échantillonnage. - Le Aperçu de la requête L'onglet de cette zone se met automatiquement à jour pour afficher un instantané des données que votre requête renverra lorsque vous l'exécuterez. Le SQL L'onglet affiche le SQL qui Générateur de requêtes génère en fonction des sélections actuelles.Le Script post-requête L'onglet vous permet de spécifier un script JSL qui sera exécuté une fois votre requête terminée.Le Statut de la requête L'onglet affiche l'état des requêtes exécutées en arrière-plan et vous permet de les annuler.
- Le Filtres Le panneau vous permet de spécifier des critères de filtrage des données (création de la clause WHERE) et de transformer le filtre en une invite qui s'exécute à chaque fois que vous exécutez la requête.
- Le Commandé par Le panneau vous permet de spécifier la ou les colonnes à utiliser pour trier les lignes renvoyées (clause ORDER BY).
Ajout de colonnes
Chaque requête nécessite quelques colonnes.Ajoutez des colonnes à la requête à l'aide de l'une de ces méthodes :
- Clique le Tout ajouter bouton pour ajouter toutes les colonnes disponibles de toutes les tables de la requête
- Sélectionnez des colonnes spécifiques dans le Colonnes disponibles liste et cliquez Ajouter ou faites-les glisser.
- Double-cliquez sur une colonne dans le Colonnes disponibles liste.
Pour cette requête, j'ajouterai film_id , titre , et taux_location du film tableau, id_catégorie et nom du catégorie tableau, et acteur_id , prénom , et nom de famille du acteur tableau.
Figure 10 : Après avoir initialement ajouté des colonnes
En utilisant le Nom JMP, type de modélisation et Format colonnes du Colonnes incluses , je peux contrôler le nom, le type de modélisation et le format de chaque colonne une fois les données importées dans JMP.
Figure 11 : Après avoir ajusté les détails de la colonne
Pour les besoins de cette analyse, je préférerais également que les noms des acteurs soient concaténés dans une seule colonne plutôt que divisés en colonnes distinctes. prénom et nom de famille Colonnes.Pour y parvenir, je peux ajouter un colonne calculée en sélectionnant Ajouter une colonne calculée dans le menu du triangle rouge sur le Colonnes disponibles panneau.
Cela ajoutera une colonne nommée Calcul1 au Colonnes disponibles liste, mais je peux cliquer dessus avec le bouton droit et utiliser Renommer la colonne pour le renommer en Nom de l'acteur , ajoutez-le à la requête et supprimez-le prénom et nom de famille .Voici à quoi ressemble l’aperçu de la requête :
Figure 12 : Aperçu de la requête avec colonne calculée
(Ces valeurs de nom d'acteur peuvent sembler un peu étranges, mais ce sont en fait les noms corrects de la base de données.Les noms des acteurs semblent avoir été générés en associant aléatoirement un prénom et un nom de famille.)
Enregistrer la requête
À ce stade, j'ai suffisamment de temps investi dans cette requête pour que je souhaite la sauvegarder afin de ne pas perdre mon travail.Cliquez simplement sur le Sauvegarder bouton en bas à droite du Générateur de requêtes fenêtre pour enregistrer la requête.Vous serez invité à fournir un nom de fichier, avec le texte du Nom de la requête boîte fournie par défaut.je nommerai la requête film_category_actor .Un message s'affichera en bas à gauche de l'écran. Générateur de requêtes fenêtre confirmant que la requête a été enregistrée :
L'enregistrement d'une requête crée un fichier avec un .jmpquery extension.De retour sur le Générateur de requêtes fenêtre, vous remarquerez que le Nom de la requête a été modifié pour correspondre au nom du fichier, il s'appelle donc maintenant film_category_actor .
Si vous devez créer une autre requête similaire à celle que vous avez ouverte, mais que vous souhaitez conserver l'original, vous pouvez utiliser l'option Enregistrer sous… pour enregistrer une copie sous un nom différent.
Exécuter la requête
Je peux également exécuter la requête à ce stade pour créer une table de données JMP contenant toutes les lignes de la base de données.Clique le Exécuter une requête bouton en bas du Générateur de requêtes fenêtre pour exécuter la requête. Par défaut, JMP exécute des requêtes en arrière-plan afin que vous puissiez continuer à travailler pendant l'exécution de vos requêtes.Utilisez le Statut de la requête dans la section inférieure centrale de la fenêtre pour voir l'état des requêtes encore en cours à partir de ce Générateur de requêtes fenêtre, ou utilisez Affichage > Exécution de requêtes pour voir l'état de toutes les requêtes actuellement exécutées en arrière-plan.Cette requête particulière ne prend qu'une seconde ou deux et renvoie 88 020 lignes :
Figure 13 : Résultats de la requête dans le tableau de données JMP
Notez que la table de données JMP résultante comporte 3 scripts et un SQL variable de table définie :
Répondre aux questions
À ce stade, je suis sur le point de pouvoir répondre aux questions initiales auxquelles je cherchais des réponses, mais j'ai une décision à prendre : est-ce que je veux que la base de données résume les résultats, ou est-ce que je veux intégrer les données brutes dans JMP et JMP fait-il le travail ?Si les données brutes ne sont pas trop volumineuses pour tenir en mémoire et ne prennent pas un temps prohibitif à importer, alors importer les données brutes dans JMP et y effectuer l'analyse présente de nombreux avantages - JMP est bien plus capable d'analyser des données qu'un système relationnel. base de données.
Pour la tâche à accomplir, il suffit d'utiliser le Distribution plateforme sur Nom de catégorie et Nom de l'acteur , en utilisant Tarif de location comme le Fréquence variable.Si je commande les histogrammes résultants par Compter par ordre croissant , j'obtiens un résultat qui répond à nos deux questions :
Figure 14 : Utilisation de la plateforme de distribution sur le résultat de la requête
Si, toutefois, l'apport de toutes les données brutes était prohibitif en termes de taille ou de temps écoulé, je peux utiliser comme alternative l'option Agrégation colonne dans la Colonnes incluses panneau pour que la base de données résume les données.Mais pour cela, je devrai répondre aux deux questions une par une.
Pour répondre à la question de Quels genres de films génèrent le plus de revenus ? , Je peux supprimer les colonnes liées aux informations sur les acteurs de la requête et demander à la base de données de faire la somme. Tarif de location regroupement par Nom de catégorie , et ensuite je peux trier le résultat par la somme Tarif de location .Voici les étapes :
- Clique le Agrégation colonne pour Tarif de location et sélectionnez Somme .
Remarquerez que Par groupe est vérifié pour toutes les variables qui n’ont pas d’agrégation spécifiée.Une fois qu'une colonne d'une requête spécifie une agrégation, toutes les colonnes de la requête qui le font pas spécifier une agrégation doivent être Par groupe Colonnes. - Faites un clic droit sur le Nom de variable colonne pour Tarif de location et sélectionnez Commandé par .
- Sélectionner SOMME(t4.rental_rate) dans le Commandé par panneau et cliquez sur le bouton fléché pointant vers le bas ( Descendant ).
Le résultat ressemble à ceci :
De même, en retirant les colonnes de catégories et en réinsérant les colonnes d'acteurs, je peux répondre à la question de Quels acteurs génèrent le plus de revenus ? :
Filtration
Revenons un instant à l'état où notre requête récupérait toutes les données brutes et où nous utilisions le Distribution plateforme pour répondre à nos questions sur les revenus.Une option dont nous disposons lorsque nous effectuons l'analyse dans JMP est de demander le script pour l'analyse JMP :
Je peux ensuite coller ce script dans le Script post-requête panneau de Générateur de requêtes :
Figure 15 : Ajout d'un script JSL post-requête
Ce script s'exécutera désormais chaque fois que j'exécuterai la requête.
Maintenant, j'aimerais affiner un peu mes questions.Plutôt que d'examiner les revenus des catégories et des acteurs pour toutes les locations dans la base de données, je souhaiterais peut-être les examiner en fonction du pays où le film a été loué, du magasin dans lequel il a été loué ou sur une certaine plage de dates.
Le de location la table a date_location , et N ° de client champs, donc je suis sur le point d'avoir ce dont j'ai besoin.Nous avons vu plus tôt que N ° de client est une référence de clé étrangère au client tableau.Du client table, nous pouvons obtenir le pays d'origine du client et le magasin dans lequel le DVD a été loué.
Si je clique sur le Changement… bouton dans le les tables panneau de Query Builder, je peux revenir en arrière et ajouter plus de tables à la requête.Pour obtenir des informations sur le pays, je devrai ajouter le client , adresse , ville , et pays tables à la requête.Heureusement, grâce aux relations clés définies par l'administrateur de cette base de données, toutes les tables se rejoignent automatiquement avec succès.
Après avoir ajouté les tables supplémentaires nécessaires, de retour dans Query Builder, je peux ajouter les colonnes dont j'ai besoin à la requête : date_location , identifiant_magasin , et pays .J'ai également défini des noms JMP conviviaux et corrigé le type de modélisation de identifiant_magasin :
Figure 16 : Après avoir ajouté les colonnes Date de location, ID de magasin et Pays
Commençons par filtrer selon le pays du client.Je peux faire un clic droit sur le t7.pays variable dans le Colonnes incluses panneau et sélectionnez Filtrer par .Après cela, un filtre par pays apparaît dans la Filtres panneau:
Figure 17 : Filtre pays
À ce stade, je peux simplement faire défiler la liste des pays et vérifier ceux qui m'intéressent, puis exécuter la requête, et seules les lignes de ces pays seront incluses.
De même, je peux ajouter des filtres pour identifiant_magasin et date_location , après quoi le panneau Filtres ressemble à ceci :
Quand j'ajoute le date_location filtre, puisqu'il s'agit d'une variable continue, j'obtiens un Comparaison simple filtrer par défaut.Ce que je voudrais à la place, c'est un Gamme filtre.En utilisant le menu triangulaire rouge à côté de t1.rental_date , je peux changer le type de filtre en Gamme :
Maintenant le filtre pour t1.rental_date ressemble à:
me permettant de spécifier une plage de dates.Ainsi, si je veux retrouver toutes les locations du second semestre 2005, je peux faire :
Figure 18 : Filtre de plage de dates
Tous les types de filtres dans Générateur de requêtes avoir un moyen de les désactiver efficacement, vous permettant d'ajouter plusieurs filtres à une requête même si tous ne sont pas actifs pour une requête donnée.Pour les filtres catégoriels affichant une liste de valeurs, sélectionnez l'option (Tous) l'élément en haut désactive le filtre.Pour les filtres demandant des valeurs numériques, définissez la valeur sur manquant (.) désactive le filtre (ou une partie du filtre).Donc, si je veux toutes les locations à partir du 1er janvier 2006, je peux faire ceci :
Maintenant le Filtres le panneau pour ma requête ressemble à Figure 19 ci-dessous.
| Figure 19 : Utilisation de Missing pour désactiver le filtre | Figure 20 : Filtre avec deux groupes OR |
Avec les sélections de filtres affichées dans Figure 19 , l'exécution de la requête renverra maintenant les lignes dans lesquelles le pays du client est la France ou l'Allemagne et le DVD a été loué dans le magasin 2 le 1er janvier 2006 ou après. Par défaut, plusieurs filtres sont combinés par ET, une ligne doit donc correspondre à tous filtres pour apparaître dans le résultat.Si vous avez besoin d'un filtre OU, cliquez sur le bouton OU bouton, qui créera un autre groupe de filtres, et les deux groupes de filtres seront OU ensemble.Par exemple, le filtre affiché dans Figure 20 ci-dessus renvoie les locations dont la catégorie est Drame ou le titre du film contient "Seau" .
Filtres d'invite
Bien qu'il soit agréable de pouvoir spécifier des critères de filtre dans Générateur de requêtes , vous souhaiterez peut-être également créer une requête destinée à être utilisée par d'autres personnes et qui n'a pas vraiment besoin de recevoir l'intégralité de la requête. Générateur de requêtes interface utilisateur, mais vous souhaitez qu'ils puissent saisir des critères de filtre différents à chaque fois qu'ils exécutent la requête.La façon la plus simple d'y parvenir est de sélectionner Toutes les invites lors de l'exécution dans le menu du triangle rouge à côté de Filtres :
Cela transforme tous les filtres de la requête en incitant filtres.Si vous souhaitez uniquement que certains filtres émettent des invites et pas d'autres, vous pouvez activer et désactiver les invites pour chaque filtre individuel à l'aide de son menu triangulaire rouge.
Après avoir réglé Toutes les invites lors de l'exécution , lorsque j'exécute cette requête, une invite me demande de définir les valeurs du filtre comme je le souhaite :
Les paramètres par défaut de l'invite seront ceux sélectionnés dans Générateur de requêtes (ou dans le fichier de requête enregistré si la requête est exécutée directement à partir du fichier) avant l'exécution de la requête.
Vous pouvez également personnaliser les étiquettes utilisées dans l'invite.Par exemple, supposons qu'au lieu de " Pays: "Je voulais que l'invite dise" Pays client : ».De retour dans Query Builder, en utilisant le menu triangle rouge pour le t7.pays filtrer, sélectionner Configurer l'invite .Cela ouvre une boîte de dialogue vous permettant de modifier l'étiquette de l'invite et également de modifier le tableau qui fournit les niveaux de catégorie pour l'invite :
Échantillonnage
Si votre base de données prend en charge l'échantillonnage aléatoire, le Échantillon onglet dans la section centrale supérieure de Générateur de requêtes affichera les options d'échantillonnage prises en charge par votre base de données. J'utilise PostgreSQL pour les exemples de cet article, qui prend en charge l'échantillonnage aléatoire simple d'un nombre spécifié de lignes :
SQL Server et Oracle prennent en charge différentes options d'échantillonnage. Échantillon L'onglet reflétera les différentes options :
| Figure 21 : options d'échantillonnage de SQL Server | Figure 22 : Options d'échantillonnage Oracle |
Le prélèvement est effectué par la base de données , avant que les données ne soient téléchargées vers JMP, c'est donc un bon moyen de limiter le nombre de lignes récupérées à partir de requêtes qui autrement récupéreraient un nombre prohibitif de lignes.
Écrire votre propre SQL
Nous reconnaissons que, autant de fonctionnalités que Générateur de requêtes a maintenant ou pourrait avoir dans le futur, il y a des moments où vous pourriez avoir besoin d'une requête qui fait des choses qui Générateur de requêtes ne prend pas directement en charge.De plus, vous avez déjà écrit et débogué minutieusement du code SQL qui exécute exactement la requête dont vous avez besoin pour que JMP exécute.Générateur de requêtes propose deux options pour fournir votre propre code SQL pour une requête :
- Expression de filtre personnalisée - Si Générateur de requêtes fait ce dont vous avez besoin en sélectionnant des tables et en spécifiant les colonnes à inclure, mais vous souhaitez simplement qu'une expression SQL personnalisée soit incluse dans le filtre, vous pouvez sélectionner Ajouter une expression personnalisée du Filtres menu triangle rouge :
| Figure 23 : Ajout d'une expression de filtre personnalisée | Figure 24 : Le filtre résultant |
Entrez le SQL de votre choix dans le Expression personnalisée boîte, à condition qu'il s'agisse d'un code SQL valide pour la requête.
Si un Expression personnalisée filter est défini comme filtre d'invite, l'expression personnalisée apparaîtra dans la boîte de dialogue d'invite de filtre et sera modifiable.
- Convertir l'intégralité de la requête en un Requête SQL personnalisée – Si vous souhaitez simplement écrire votre propre SQL pour une requête entière, alors, après le lancement Générateur de requêtes et en spécifiant la connexion de données, vous pouvez utiliser le menu triangle rouge en haut à gauche de Générateur de requêtes à côté de la Nom de la requête champ et sélectionnez Convertir en SQL personnalisé :
Figure 25 : Convertir en SQL personnalisé
Le Générateur de requêtes La fenêtre passera en mode SQL personnalisé, vous donnant une grande zone pour saisir votre propre SQL.Si vous aviez commencé à créer la requête de manière interactive, le SQL personnalisé La boîte sera initialisée avec le SQL généré en fonction des sélections que vous avez effectuées jusqu'à présent (voir Figure 26 ci-dessous).
Figure 26 : Requête SQL personnalisée
Vous pouvez modifier le code SQL selon vos besoins ou le coller à partir d'une autre source, puis cliquer sur le bouton Mise à jour bouton pour mettre à jour le Aperçu de la requête .Quelques mises en garde :
- Vous ne pouvez pas effectuer de filtrage par invite avec une requête SQL personnalisée.
- Une fois que vous avez converti une requête en SQL personnalisé et modifié le SQL, si vous rétablissez la requête en mode interactif, vous perdrez toutes les personnalisations que vous avez apportées au SQL. Query Builder ne tente pas d'interpréter le code SQL.
Section 4 : Gestion des requêtes en arrière-plan
Générateur de requêtes exécute des requêtes en arrière-plan par défaut, et il est également possible depuis JMP Scripting Language (JSL) d'exécuter des requêtes en arrière-plan (voir Article 6 ).JMP propose deux méthodes pour afficher la liste des requêtes en arrière-plan en cours d'exécution et arrêter l'exécution des requêtes si nécessaire.
Panneau d'état de la requête
Dans la section centrale inférieure de Query Builder, il y a un Statut de la requête languette.Toutes les requêtes en arrière-plan lancées à partir de cette fenêtre du générateur de requêtes apparaîtra dans cet onglet :
Si vous souhaitez arrêter une requête en cours d'exécution, sélectionnez-la simplement dans la liste et cliquez sur le bouton Arrêt bouton.Si certaines lignes ont été récupérées, JMP ouvrira une table de données contenant les lignes déjà récupérées.Le nom de la table de données indiquera que le résultat est partiel.
Fenêtre Exécuter des requêtes
Du Voir dans le menu de la fenêtre d'accueil ou dans une autre fenêtre JMP comprenant un menu, sélectionnez Exécution de requêtes .Cela ouvrira une fenêtre qui ressemble à celle du Statut de la requête onglet de Générateur de requêtes , mais le Exécution de requêtes la fenêtre contiendra tous requêtes qui s'exécutent actuellement en arrière-plan, quelle que soit la manière dont elles ont été invoquées (une fenêtre Query Builder ou le langage de script JMP) :
Là encore, sélectionnez une requête et cliquez Arrêt pour l'empêcher de fonctionner, ouvrant tout résultat partiel.
Section 5 : Partage de requêtes
Dans l'introduction, il a été mentionné que Table ouverte créé des scripts difficiles à partager, car le mot de passe de la base de données est inclus dans la chaîne de connexion.Générateur de requêtes fonctionne différemment. Si vous enregistrez un .jmpquery fichier pour une requête dont la connexion nécessitait un mot de passe, et que vous ouvrez ce fichier dans un éditeur de texte, vous verrez quelque chose comme ceci :
Vous pouvez voir que le nom d'utilisateur ( UID ) est présent dans la chaîne de connexion, mais vous voyez ensuite PWD=%_PWD_% .« %_PWD_% » n'est pas le mot de passe ; il s'agit d'un espace réservé placé par Query Builder.Lorsque Query Builder voit cet espace réservé, il sait qu'un mot de passe était requis à l'origine, donc si cette requête est ouverte à un moment où l'utilisateur qui l'ouvre n'a pas déjà de connexion au Location de DVD Postgres DSN, Query Builder saura demander à l'utilisateur le mot de passe.
Et qu’en est-il de la DSN elle-même ?Que se passe-t-il si vous envoyez ce fichier .jmpquery à un utilisateur JMP qui n'a pas de DSN nommé Location de DVD Postgres défini sur leur machine ?Table ouverte est incapable de procéder dans un tel scénario, mais Générateur de requêtes informera l'utilisateur de cette situation et lui donnera la possibilité de se connecter à un autre DSN (qui se connecte vraisemblablement à la même base de données) ou d'en créer un nouveau.
En raison de la possibilité que différents utilisateurs aient différents DSN définis sur leurs machines, certaines organisations ont opté pour Connexions sans DSN .Générateur de requêtes ne peut actuellement pas convertir une chaîne de connexion basée sur DSN en une chaîne sans DSN, mais si vous modifiez une chaîne de connexion dans le script pour une requête afin qu'au lieu de spécifier un DSN , il précise un CONDUCTEUR et SERVEUR , Générateur de requêtes propagera cette connexion sans DSN dans tous les scripts ou fichiers .jmpquery qu'il génère.
Section 6 : Exécution de requêtes à partir du langage de script JMP (JSL)
Les requêtes créées par Query Builder peuvent également être exécutées à partir de Langage de script JMP (JSL) scripts. La meilleure option consiste à enregistrer la requête en tant que .jmpquery fichier, ouvrez-le à partir de JSL et exécutez-le. Supposons, par exemple, qu'une requête soit enregistrée dans le c:\utilisateurs\public\temp dossier nommé pg_rentals.jmpquery .Voici quelques choses que vous pouvez faire avec ce fichier de requête de JSL :
- Ouvrez un fichier .jmpquery dans Query Builder :
ouvrir ( "c:\users\public\temp\pg_rentals.jmpquery" ) ;
requête = ouvrir ( "c:\users\public\temp\pg_rentals.jmpquery" , Privé ) ;
- Ouvrez la requête dans Générateur de requêtes pour l'édition :
requête << Modifier ;
requête << Courir () ;
- Exécutez la requête au premier plan. La table de données résultante s'ouvrira dans JMP une fois la requête terminée. dtResult sera une référence au tableau de données de résultat.
dtRésultat = requête << Exécuter au premier plan () ;
- Exécutez la requête au premier plan, mais faites pas ouvrez la table de données résultante dans JMP ; gardez-le privé.dtResult sera une référence au tableau de données de résultat.
dtRésultat = requête << Exécuter au premier plan ( Privé ) ;
- Exécutez la requête en arrière-plan. La table de données résultante s'ouvrira dans JMP une fois la requête terminée. Run Background ne peut pas renvoyer une référence à la table de données de résultat car la requête est simplement lancée en arrière-plan.Les exemples suivants montrent comment obtenir une référence à la table de données de résultat lors de l'exécution de requêtes en arrière-plan.
requête << Exécuter en arrière-plan () ;
- Exécutez la requête en arrière-plan. Si la requête réussit, appelez la plateforme de distribution sur la table de données de résultat ( résultat de la requête est une variable JSL spéciale dans un En cours d'exécution terminé script qui fait référence à la table de données de résultat). Si la requête est annulée par l'utilisateur ou comporte une erreur, écrivez un message dans le journal JMP.
requête << Exécuter en arrière-plan (
En cours d'exécution terminé (
résultat de la requête << Distribution ( Répartition nominale ( Colonne ( : titre ) ) ) ;
) ,
En exécution annulée ( Écrire ( "\!NLe requête a été annulée !") ) ,
En cas d'erreur ( Écrire ( "\!NErreur lors de l'exécution de la requête !") ; )
) ;
- Exécutez la requête en arrière-plan.Une fois la requête terminée, appelez la fonction MyFunc. La table de données du résultat de la requête est toujours transmise comme premier paramètre à la fonction.
:: MaFonction = Fonction ( { dt } , { Local par défaut } ,
Écrire ( "\!nNombre de locations : " , Lignes ( dt ) ) ;
dt << Distribution ( Répartition nominale ( Colonne ( : titre ) ) ) ;
) ;
requête << Exécuter en arrière-plan ( En cours d'exécution terminé ( :: MaFonction ) ) ;
Si l'ouverture d'une requête à partir d'un fichier .jmpquery distinct dans votre script JSL n'est pas satisfaisante, vous pouvez obtenir le script brut pour créer un objet scriptable de requête à partir du menu triangulaire rouge en haut à gauche de l'écran. Générateur de requêtes fenêtre:
Par exemple, invoquer Enregistrer le script d'exécution dans la fenêtre de script pour une simple requête ça me donne :
Nouvelle requête SQL renvoie un objet scriptable de requête SQL, tout comme Ouvrir fait lors de l’ouverture d’un fichier .jmpquery et peut être utilisé de la même manière.
Quoi de neuf dans JMP 12.1 ?
Recherche
De nombreuses personnes avec qui nous parlons disposent de montagnes de données réparties dans des centaines de schémas, de tables et de colonnes dans leurs bases de données.Dans la version JMP 12.1 de Query Builder, nous avons ajouté Recherche des encadrés vers les listes de schémas, de tables et de colonnes pour vous aider à trouver rapidement ceux que vous recherchez :
Tous ces Recherche les cases se mettent à jour au fur et à mesure que vous tapez et vous avez la possibilité d'utiliser expressions régulières pour affiner votre recherche.
Ajouter des tables associées
Dans le Sélectionner les tables pour la requête Dans la boîte de dialogue, si une table que vous avez ajoutée à la requête comporte des références de clé étrangère à d'autres tables, vous pouvez ajouter toutes ces tables associées à la requête en cliquant sur le bouton Ajouter des tables associées bouton.
Ouvrir comme modèle
Souvent, vous devez créer une requête très similaire à une autre requête que vous avez créée et enregistrée dans un fichier .jmpquery.Vous pouvez toujours ouvrir la requête précédemment enregistrée, y apporter des modifications et utiliser Enregistrer sous pour l'enregistrer dans un autre fichier .jmpquery, mais si vous appuyez accidentellement sur Sauvegarder par erreur, votre requête originale serait écrasée.Le Ouvrir comme modèle une fonctionnalité existe pour empêcher ce scénario.Ouvrir comme modèle est disponible dans le menu contextuel de la fenêtre d'accueil pour les fichiers .jmpquery sur Mac et Windows :
Lorsque vous ouvrez une requête .jmp en tant que modèle, vous ouvrez en réalité une copie de la requête d'origine qui n'a aucune association avec le fichier .jmpquery sur le disque.Si vous avez ouvert une requête en tant que modèle, apporté des modifications et appuyé accidentellement sur le bouton Sauvegarder , vous serez invité à fournir un fichier dans lequel enregistrer la requête.
Vous pouvez également ouvrir un fichier .jmpquery en tant que modèle à partir du langage de script JMP (JSL) :
ouvrir ( "C:\Utilisateurs\Public\Temp\rentals.jmpquery" , Comme modèle ) ;
Modifier une formule de colonne calculée
Dans JMP 12.0, vous pouvez ajouter une colonne calculée à votre requête qui serait calculée sur le serveur de base de données, avec les valeurs résultantes renvoyées dans la requête.Cependant, si vous deviez modifier la formule d'une colonne calculée que vous aviez créée précédemment, cela n'était pas possible.Au lieu de cela, vous avez dû supprimer la colonne calculée et en ajouter une nouvelle, ce qui vous a éventuellement obligé à recréer une formule complexe.Dans JMP 12.1, vous avez désormais la possibilité de modifier une colonne calculée.Cliquez simplement avec le bouton droit sur la colonne dans Colonnes disponibles et sélectionnez Modifier la formule :
Corrections de bugs
Comme vous vous en doutez, nous avons corrigé un certain nombre de bogues dans Query Builder dans JMP 12.1, dont certains ont été signalés par des clients.Si vous rencontrez un problème lors de l'utilisation de Query Builder, veuillez signaler le problème via le support technique SAS afin que nous puissions le résoudre.
Conclusion
Générateur de requêtes est une nouvelle fonctionnalité de JMP 12 (mise à jour dans JMP 12.1) qui vous aide à créer des requêtes SQL multi-tables hautement personnalisées sur vos bases de données relationnelles.L'objectif est de vous permettre d'introduire plus facilement vos données dans JMP sous une forme prête à être analysée plus rapidement qu'auparavant et de faciliter le partage de vos requêtes en toute sécurité.Nous espérons que vous l'essayerez et nous enverrez vos commentaires ( eric.hill@jmp.com ) sur ce qui fonctionne bien et ce qui ne fonctionne pas pour vous.
