Eric Hill ( EricHill ), JMP Principal Systems Developer, SAS
HINWEIS: Dies ist nur der Text des Dokuments; die Screenshots sind nicht enthalten. Den vollständigen Text finden Sie in der beigefügten Version des Dokuments.
Abfrage-Generator
Das neue JMP 12-Tool zum Importieren Ihrer SQL-Daten in JMP
(Aktualisiert für JMP 12.1)
Von Eric Hill, Softwareentwickler, JMP
Inhalt
- Einführung. 2
Abschnitt 1: Herstellen der Verbindung. 3
Abschnitt 2: Auswählen von Tabellen für die Abfrage. 6
Abschnitt 3: Erstellen der Abfrage. 11
Spalten hinzufügen. 13
Speichern der Abfrage. 15
Ausführen der Abfrage. 15
Beantwortung der Fragen. 16
- Filtern. 19
Eingabeaufforderungsfilter. 23
- Probenahme. 25
Schreiben Ihres eigenen SQL. 26
Abschnitt 4: Verwalten von Hintergrundabfragen. 27
Abfragestatusbereich 28
Fenster „Runninq-Abfragen“..28
Abschnitt 5: Abfragen teilen. 28
Abschnitt 6: Ausführen von Abfragen aus der JMP-Skriptsprache (JSL). 29
Was ist neu in JMP 12.1?. 31
- Suchen. 31
Hinzufügen verknüpfter Tabellen. 32
Als Vorlage öffnen. 34
Bearbeiten einer berechneten Spaltenformel. 34
Fehlerbehebungen. 35
- Abschluss. 35
Einführung
JMP bietet Ihnen eine Vielzahl von Analysewerkzeugen. Diese nützen Ihnen jedoch nichts, wenn Sie die zu analysierenden Daten nicht bedarfsgerecht in JMP importieren können. JMP unterstützt bereits seit längerem den Import von Daten aus relationalen Datenbanken über Datei > Datenbank > Tabelle öffnen . Die Funktion „Tabelle öffnen“ ist zwar in Ordnung, weist aber einige nennenswerte Einschränkungen auf:
- Die meisten Daten in relationalen Datenbanken werden normalisiert gespeichert und erfordern Verknüpfungen, um alle benötigten Daten zu erhalten. Der Offene Tisch hilft Ihnen jedoch nicht bei Verknüpfungen. Sie müssen das SQL selbst schreiben.
- Während Offener Tisch den Formel-Editor zum Erstellen einer WHERE-Klausel bereitstellt, werden Ihnen beispielsweise nicht die unterschiedlichen Werte einer kategorialen Variable angezeigt, um Ihnen beim Filtern danach zu helfen.
- Es kann schwierig sein, Ihre Abfragen sicher mit anderen zu teilen, da Offener Tisch Ihr Kennwort in der Verbindungszeichenfolge der generierten JSL-Skripte speichert.
- Der Offene Tisch unterstützt Sie nicht bei den Nachbearbeitungsschritten, die nach dem Importieren der Daten erforderlich sind, wie etwa Datenbereinigung, Modellierungstypen und Formatierung.
Abfrage-Generator wurde von Grund auf entwickelt, um die Einschränkungen des Offenen Tischs zu berücksichtigen und Sie bei der Erstellung von Abfragen mit mehreren Tabellen und interaktiver Filterung mit Eingabeaufforderung zu unterstützen, die sicher mit Kollegen geteilt werden können.
In diesem Artikel verwende ich den Abfrage-Generator mit einer frei verfügbaren PostgreSQL- Beispieldatenbank namens dellstore2 . PostgreSQL ist eine Open-Source-Datenbank, die hier kostenlos heruntergeladen werden kann:
http://www.postgresql.org/download/
Die Beispieldatenbank dellstore2 finden Sie hier:
http://wiki.postgresql.org/wiki/Sample_Databases
Die Beispieldatenbank dellstore2 ist auch für andere Datenbankplattformen (MySQL, Oracle und SQL Server) verfügbar.
Abschnitt 1: Herstellen der Verbindung
Starten Sie den Abfrage-Generator über Datei > Datenbank > Abfrage-Generator aus dem Menü:
Sie können es auch von der ersten Seite von JMP Starter aus starten:
Unter Windows finden Sie außerdem in der Symbolleiste des Startfensters ein Abfragegenerator- Tool:
Nach dem Starten des Abfrage-Generators wird Ihnen das Dialogfeld „Datenbankverbindung auswählen“ angezeigt:
Das Dialogfeld „Datenbankverbindung auswählen“ ist eine Teilmenge des Fensters „Offener Tisch“ . Abfrage-Generator und Offener Tisch verwenden dieselben ODBC-Verbindungen und teilen sich diese sogar. Alle Verbindungen, die Sie an einer der beiden Stellen herstellen, werden daher in beiden angezeigt.
Klicken Sie auf „Neue Verbindung…“ , um eine Verbindung zu Ihrer Datenbank über einen vorhandenen DSN herzustellen oder einen neuen DSN zu erstellen. Sie werden aufgefordert, alle erforderlichen Anmeldeinformationen einzugeben:
Nach dem Herstellen der Verbindung ruft JMP Schemata aus der Verbindung ab, damit Sie sicherstellen können, dass dies die gewünschte Verbindung ist:
Sie können auch die im ausgewählten Schema verfügbaren Tabellen anzeigen. Klicken Sie auf Weiter , um mit dem nächsten Schritt fortzufahren.
Neu in JMP 12.2: Beachten Sie die beiden Suchfelder im Bereich „Schemas – Tabellen “. Diese sind neu im JMP 12.1-Update und ermöglichen die Filterung während der Eingabe in den Schema- und Tabellenlisten . Wir wissen, dass viele von Ihnen Hunderte von Schemas und Tabellen in einem bestimmten Schema haben. Die neuen Suchfelder helfen Ihnen daher, die Liste einzugrenzen und das Gesuchte zu finden.
Abschnitt 2: Auswählen von Tabellen für die Abfrage
Abbildung 1: Auswählen von Tabellen für die Abfrage
Wenn Sie auf Nächste klicken, wird das Dialogfeld Tabellen auswählen angezeigt. Wählen Sie im Dialogfeld Tabellen auswählen die Tabellen aus, die Sie in Ihre Abfrage einbeziehen möchten (siehe Abbildung 1 oben). Wählen Sie in der Liste Verfügbare Tabellen die Tabellen aus, die Sie in die Abfrage einbeziehen möchten, und fügen Sie sie den Listen Primäre und Sekundäre Tabellen oben rechts hinzu.
- Die Primärtabelle einer Abfrage ist normalerweise die Tabelle, aus der alle Zeilen abgerufen werden sollen, unabhängig davon, ob sie in einer anderen Tabelle einen entsprechenden Wert haben. Im Data Warehousing ist dies Ihre Faktentabelle . Eine Abfrage kann nur eine Primärtabelle enthalten.
- Sekundärtabellen sind in der Regel Nachschlagetabellen. Standardmäßig werden Zeilen aus Sekundärtabellen nur dann in die Abfrage einbezogen, wenn der Wert einer Spalte in der Primärtabelle mit dem Wert einer angegebenen Spalte in der Sekundärtabelle übereinstimmt. Im Data Warehousing sind dies Ihre Dimensionstabellen . Sie können der Abfrage beliebig viele Sekundärtabellen hinzufügen.
Im unteren rechten Bereich werden Informationen zur ausgewählten Tabelle angezeigt. Auf der Registerkarte „ Spalten “ werden Informationen zu jeder Spalte in der Tabelle angezeigt, einschließlich der Angabe, ob es sich um einen Schlüssel handelt:
Neu in JMP 12.2:Beachten Sie das Suchfeld im Bereich „Verfügbare Tabellen“ , das in JMP 12.1 neu ist. Verwenden Sie es, um die gewünschte Tabelle zu finden, wenn Ihre Schemata viele Tabellen enthalten.
Im Bereich „Tabellen-Snapshot “ wird ein Snapshot der Daten angezeigt, sodass Sie sicherstellen können, dass die Tabelle die benötigten Informationen enthält:
Normalerweise hat man beim Erstellen einer Abfrage eine oder mehrere Fragen im Kopf, die man beantworten möchte. Diese Fragen bestimmen die Entscheidung darüber, welche Tabellen in die Abfrage einbezogen werden müssen. Die Daten, die wir untersuchen, stammen von einem fiktiven DVD-Verleih. Ich möchte folgende Fragen beantworten:
- Welche Filmgenres bringen die meisten Einnahmen?
- Welche Akteure erzielen die meisten Einnahmen?
Die Tabelle „Rental“ enthält einen Datensatz für jeden DVD-Verleihvorgang während der Unternehmensgeschichte und ist daher die primäre Tabelle für diese Abfrage. Im Spaltenbereich erkenne ich, dass „ Rental “ Spalten mit Fremdschlüsselbeziehungen zu den Tabellen „Inventar“ und „Kunden“ enthält:
Abbildung 2: Fremdschlüssel
Eine Fremdschlüsselbeziehung (auch Fremdschlüsseleinschränkung genannt) bedeutet, dass die Werte in der Spalte einer Tabelle in der Primärschlüsselspalte einer anderen Tabelle zu finden sein müssen. In Abbildung 2 können wir erkennen, dass Werte aus der Spalte Bestands-ID der Tabelle Mieten in der Primärschlüsselspalte der Tabelle Inventar vorhanden sein müssen, Werte aus der Spalte Kundennummer der Tabelle Mieten in der Primärschlüsselspalte der Tabelle Kunde und Werte aus der Spalte Mitarbeiter-ID der Tabelle rentals nur in der Primärschlüsselspalte der Tabelle Personal vorhanden sein müssen. Für diese Abfrage suche ich nach weiteren Informationen über den ausgeliehenen Film und nicht nach dem Kunden des an der Ausleihe beteiligten Mitarbeiters, daher füge ich der Abfrage die Tabelle Inventar als sekundäre Tabelle hinzu.
Wenn ich mir das Spaltenfeld für die Inventartabelle anschaue, kann ich außerdem eine Fremdschlüsselbeziehung mit der Filmtabelle erkennen:
Abbildung 3: Details der Inventartabelle
Daher wird auch die Tabelle „Film“ in die Abfrage einbezogen. In den Details der Tabelle „Film“ sehe ich eine Spalte mit dem Wert „rental_rate “, die ich eigentlich für die Berechnung des Umsatzes benötigen sollte. Ich sehe jedoch nichts über die Kategorie des Films oder die Schauspieler im Film:
Abbildung 4: Spalten in der Filmtabelle
In der Liste der verfügbaren Tabellen sehe ich jedoch eine Tabelle „film_actor“ und eine Tabelle „film_category“ , die die ID eines Films seinen Schauspielern und seiner Kategorie zuzuordnen scheinen, sowie Tabellen „actor“ und „category“ , die weitere Informationen zu den Schauspielern und Kategorien enthalten. Daher möchte ich diese Tabellen auch in der Abfrage verwenden. Füge ich jedoch einfach diese vier Tabellen hinzu, scheint etwas schief zu laufen:
Abbildung 5: Automatischer Beitritt fehlgeschlagen
Die Stoppschild-Symbole mit dem „X“ in der Mitte zeigen an, dass JMP die beiden Tabellen nicht automatisch verknüpfen konnte. Daher muss ich die Verknüpfungen manuell einrichten. Sollte die automatische Verknüpfungsfunktion von JMP häufig nicht die gewünschte Funktion erfüllen, können Sie sie über das rote Dreieck neben „Tabellen für Abfrage auswählen“ deaktivieren.
Wenn ich public.film_actor (t14) auswähle und auf die Schaltfläche „Join bearbeiten“ ( ) klicke, wird das Dialogfeld „Bedingung bearbeiten“ angezeigt, in dem ich den Join konfigurieren kann. Das Dialogfeld sieht zunächst wie folgt aus:
Abbildung 6: Bearbeiten der Join-Bedingung
da noch keine Join-Bedingung existiert, möchte ich film_actor.film_id mit Bestandsaufnahme.Film-ID verknüpfen. Dazu wähle ich einfach die Tabelle Inventar aus der linken Spalte aus und wähle film_id in beiden Listen aus:
Abbildung 7: Bearbeiten der Join-Bedingung
Analog kann ich die Join-Bedingung für öffentliche Filmkategorie auf Bestand.Film-ID = Filmkategorie.Film-ID setzen. Danach sieht alles besser aus:
Abbildung 8: Verknüpfungen zwischen Tabellen sind gültig
Alle sekundären Tabellen zeigen das Venn-Diagrammsymbol „ Left Outer Join“ . Am Beispiel des Joins zwischen „film_actor“ und „Inventar“ bedeutet ein „Left Outer Join“, dass alle Zeilen der Tabelle „Inventar“ , in diesem Fall die linke Tabelle, in die Abfrage einbezogen werden, unabhängig davon, ob eine Übereinstimmung in der Tabelle „film_actor“ vorliegt oder nicht. Es werden jedoch nur Zeilen der Tabelle „film_actor“ einbezogen, die mit einer Zeile in der Tabelle „Inventar “ übereinstimmen.
Die in Klammern hinter den Tabellennamen angegebenen t1 , t2 , t3 usw. sind die Tabellenaliase, die JMP den Tabellen beim Hinzufügen zur Abfrage zuweist. Tabellenaliase erleichtern die Generierung korrekten SQL-Codes und verbessern die Lesbarkeit des generierten SQL-Codes. Sie können den Tabellenalias für eine Tabelle ändern, indem Sie in der Liste der primären oder sekundären Tabellen mit der rechten Maustaste auf die Tabelle klicken und „Alias ändern“ auswählen. Tabellenaliase dürfen keine Leerzeichen oder Sonderzeichen enthalten.
Wenn Sie einen anderen Join als den Left Outer Join benötigen, können Sie den Join-Typ mithilfe der Kontrollkästchen im Dialogfeld „Verbinden bearbeiten“ ändern. Der Join-Typ und das Venn-Diagramm-Symbol ändern sich entsprechend dem Status der Kontrollkästchen.
Abbildung 9: Ändern des Join-Typs
Um zu überprüfen, ob die Verknüpfungen korrekt eingerichtet sind, klicken Sie auf die Schaltfläche „Verknüpfung in der Vorschau anzeigen“ . Dadurch wird die Abfrage ausgeführt und ein Snapshot der ersten paar hundert Datenzeilen angezeigt, sodass Sie die Richtigkeit überprüfen können.
Nachdem wir nun alle Tabellen in der Abfrage haben, die wir benötigen, können wir auf „Abfrage erstellen“ klicken, um den Abfrage-Generator selbst aufzurufen. Dort können wir angeben, welche Spalten in die Abfrage aufgenommen werden sollen, welche Filterkriterien verwendet werden sollen, wie die resultierenden Daten sortiert werden sollen und vieles mehr.
Abschnitt 3: Erstellen der Abfrage
Wenn das Abfrage-Generator -Fenster für diese Abfrage zum ersten Mal angezeigt wird, sieht es folgendermaßen aus:
- Das Feld „Abfragename“ enthält den Namen der Abfrage, der standardmäßig dem Namen der Primärtabelle entspricht. Der Abfragename ist der Name der JMP-Datentabelle, die sich aus der Ausführung der Abfrage ergibt, und gleichzeitig der Standarddateiname beim Speichern der Abfrage. Das Feld „Datenquelle“ zeigt den Namen der von der Abfrage verwendeten Datenquelle an.
- Im Feld Tabellen werden die Tabellen angezeigt, die aktuell in der Abfrage enthalten sind. Um Tabellen hinzuzufügen, zu entfernen oder Verknüpfungen zu ändern, klicken Sie auf die Schaltfläche Ändern… .
- Die Liste „Verfügbare Spalten“ zeigt alle Spalten aus allen Tabellen der Abfrage. Wählen Sie Spalten aus dieser Liste aus und ziehen Sie sie in die Bereiche „Eingeschlossene Spalten“ , „Filter“ oder „ Sortieren nach “. Alternativ können Sie mit der rechten Maustaste klicken, um dasselbe zu erreichen.
Neu in JMP 12.1: Das Fenster „Verfügbare Spalten“ verfügt jetzt über ein Suchfeld , falls Sie eine bestimmte Spalte in einer langen Liste suchen müssen. Wie bei den anderen Suchfeldern wird während der Eingabe gefiltert. - Das Fenster „Eingeschlossene Spalten“ zeigt ein Raster aller Spalten Ihrer Abfrage. Verwenden Sie das Raster „Eingeschlossene Spalten“ , um beispielsweise das Format und den Modellierungstyp festzulegen, den die Daten der Spalte nach dem Import in JMP haben sollen. Sie können außerdem Aggregationen und „Gruppieren nach“ für Spalten angeben, wenn Ihre Daten vor der Übertragung in JMP zusammengefasst werden sollen.
In diesem Bereich gibt es außerdem eine Registerkarte „Beispiel“ zum Generieren von zufälligen Datenbeispielen auf dem Server für Datenbanken, die Stichproben unterstützen. - Die Registerkarte „Abfragevorschau“ in diesem Bereich aktualisiert sich automatisch und zeigt eine Momentaufnahme der Daten an, die Ihre Abfrage beim Ausführen zurückgibt. Die Registerkarte „SQL“ zeigt das SQL, das der Abfrage-Generator basierend auf den aktuellen Auswahlen generiert. Auf der Registerkarte „Post-Query-Skript“ können Sie ein JSL-Skript angeben, das nach Abschluss Ihrer Abfrage ausgeführt wird. Die Registerkarte „Abfragestatus“ zeigt den Status von Abfragen an, die im Hintergrund ausgeführt werden, und ermöglicht Ihnen, diese abzubrechen.
- Im Filterbereich können Sie Kriterien zum Filtern der Daten angeben (Erstellen der WHERE-Klausel) und den Filter in eine Eingabeaufforderung umwandeln, die bei jeder Ausführung der Abfrage ausgeführt wird.
- Im Bereich „Sortieren nach “ können Sie die Spalte(n) angeben, die zum Sortieren der zurückgegebenen Zeilen verwendet werden sollen (ORDER BY-Klausel).
Spalten hinzufügen
Jede Abfrage benötigt Spalten. Fügen Sie der Abfrage Spalten mit einer der folgenden Methoden hinzu:
- Klicken Sie auf die Schaltfläche „Alle hinzufügen“ , um alle verfügbaren Spalten aus allen Tabellen in der Abfrage hinzuzufügen
- Wählen Sie bestimmte Spalten aus der Liste „Verfügbare Spalten“ aus und klicken Sie auf „Hinzufügen“ oder ziehen Sie sie herüber.
- Doppelklicken Sie auf eine Spalte in der Liste „Verfügbare Spalten“ .
Für diese Abfrage füge ich film_id , Titel und Mietpreis aus der Tabelle „Film“ , Kategorie-ID und Name aus der Tabelle „Kategorie“ und Schauspieler-ID , Vorname und Nachname aus der Tabelle „Schauspieler“ hinzu.
Abbildung 10: Nach dem ersten Hinzufügen von Spalten
Mithilfe der JMP-Spalten „Name“, „Modellierungstyp“ und „Format “ der geschlossenen Spalten kann ich steuern, wie Name, Modellierungstyp und Format jeder Spalte lauten, nachdem die Daten in JMP importiert wurden.
Abbildung 11: Nach dem Anpassen der Spaltendetails
Für diese Analyse wäre es mir außerdem lieber, wenn die Schauspielernamen in einer einzigen Spalte zusammengefasst wären, anstatt in separate Spalten für Vorname und Nachname aufgeteilt zu werden. Um dies zu erreichen, kann ich eine berechnete Spalte hinzufügen, indem ich im roten Dreieckmenü im Bereich „ Verfügbare Spalten “ die Option „Berechnete Spalte hinzufügen“ auswähle.
Dadurch wird der Liste „Verfügbare Spalten“ eine Spalte mit dem Namen „Calc1“ hinzugefügt. Ich kann sie aber auch mit der rechten Maustaste anklicken und mit „Spalte umbenennen “ in „ActorName“ umbenennen, sie der Abfrage hinzufügen und Vorname und Nachname entfernen. So sieht die Vorschau der Abfrage danach aus:
Abbildung 12: Abfragevorschau mit berechneter Spalte
(Diese Werte für die Schauspielernamen sehen vielleicht etwas seltsam aus, es handelt sich jedoch tatsächlich um die korrekten Namen aus der Datenbank. Die Schauspielernamen scheinen durch die zufällige Kombination eines Vornamens mit einem Nachnamen generiert worden zu sein.)
Speichern der Abfrage
Ich habe mittlerweile so viel Zeit in diese Abfrage investiert, dass ich sie speichern möchte, um meine Arbeit nicht zu verlieren. Klicken Sie dazu einfach unten rechts im Abfrage-Generator- Fenster auf die Schaltfläche Speichern . Sie werden aufgefordert, einen Dateinamen einzugeben. Der Text aus dem Feld Abfragename ist der Standard. Ich nenne die Abfrage Filmkategorie_Schauspieler . Unten links im Abfrage-Generator- Fenster wird eine Meldung angezeigt, die das Speichern der Abfrage bestätigt:
Beim Speichern einer Abfrage wird eine Datei mit der Erweiterung .jmpquery erstellt. Im Fenster des Abfrage-Generators sehen Sie, dass der Abfragename an den Dateinamen angepasst wurde und nun Filmkategorie_Schauspieler lautet.
Wenn Sie eine weitere Abfrage erstellen müssen, die der geöffneten Abfrage ähnelt, Sie aber das Original beibehalten möchten, können Sie mit der Schaltfläche „Speichern unter…“ eine Kopie unter einem anderen Namen speichern.
Ausführen der Abfrage
Ich kann die Abfrage jetzt auch ausführen, um eine JMP-Datentabelle mit allen Zeilen der Datenbank zu erstellen. Klicken Sie dazu unten im Abfrage-Generator- Fenster auf die Schaltfläche „Abfrage ausführen“. JMP führt Abfragen standardmäßig im Hintergrund aus, sodass Sie während der Ausführung Ihrer Abfragen weiterarbeiten können. Über die Registerkarte „Abfragestatus“ unten in der Mitte des Fensters können Sie den Status der noch laufenden Abfragen in diesem Abfrage-Generator- Fenster einsehen. Alternativ können Sie über „Ansicht > Laufende Abfragen“ den Status aller aktuell im Hintergrund laufenden Abfragen anzeigen. Diese Abfrage dauert nur ein bis zwei Sekunden und liefert 88.020 Zeilen:
Abbildung 13: Ergebnisse der Abfrage in der JMP-Datentabelle
Beachten Sie, dass in der resultierenden JMP-Datentabelle drei Skripte und eine SQL- Tabellenvariable definiert sind:
Beantwortung der Fragen
Ich bin nun fast in der Lage, die ursprünglichen Fragen zu beantworten, muss aber eine Entscheidung treffen: Soll die Datenbank die Ergebnisse zusammenfassen oder soll ich die Rohdaten in JMP importieren und JMP die Arbeit überlassen? Wenn die Rohdaten nicht zu groß für den Speicher sind und der Import nicht übermäßig lange dauert, bietet der Import der Rohdaten in JMP und die dortige Analyse viele Vorteile – JMP bietet deutlich bessere Analysemöglichkeiten als eine relationale Datenbank.
Für die vorliegende Aufgabe genügt es, die Distributionsplattform auf Kategoriename und Schauspielername anzuwenden und den Mietpreis als Häufigkeitsvariable zu verwenden. Wenn ich die resultierenden Histogramme nach Anzahl aufsteigend sortiere, erhalte ich ein Ergebnis, das beide Fragen beantwortet:
Abbildung 14: Anwendung der Distributionsplattform auf das Abfrageergebnis
Wenn die Erfassung aller Rohdaten aufgrund der Größe oder des Zeitaufwands jedoch zu groß wäre, kann ich alternativ die Spalte „Aggregation“ im Bereich „Eingeschlossene Spalten “ verwenden, um die Daten von der Datenbank zusammenfassen zu lassen. Dazu muss ich jedoch die beiden Fragen einzeln beantworten.
Um die Frage „Welche Filmgenres bringen die meisten Einnahmen?“ zu beantworten, kann ich die Spalten mit den Schauspielerinformationen aus der Abfrage entfernen und die Datenbank bitten, den Mietpreis nach Kategoriename zu summieren. Anschließend kann ich das Ergebnis nach dem summierten Mietpreis sortieren. So gehen Sie vor:
- Klicken Sie auf die Spalte „Aggregation“ für „Mietpreis“ und wählen Sie „Summe“ aus.
Beachten Sie, dass „Gruppieren nach“ für alle Variablen aktiviert ist, für die keine Aggregation angegeben ist. Sobald eine Spalte in einer Abfrage eine Aggregation angibt, müssen alle Spalten in der Abfrage, die keine Aggregation angeben, Gruppieren nach -Spalten sein. - Right-click on the Variablenname column for Mietpreis and select Sortieren nach.
- Wählen Sie im Bereich „Sortieren nach“ SUMME(t4.Mietpreis) aus und klicken Sie auf die Schaltfläche mit dem nach unten zeigenden Pfeil ( Absteigend ).
Das Ergebnis sieht folgendermaßen aus:
Wenn ich die Kategoriespalten wieder herausnehme und die Schauspielerspalten wieder einfüge, kann ich die Frage „ Welche Akteure erzielen die meisten Einnahmen?“ wie folgt beantworten:
Filtern
Gehen wir noch einmal zurück zu dem Zustand, als unsere Abfrage alle Rohdaten abrief und wir die Verteilungsplattform zur Beantwortung unserer Umsatzfragen nutzten. Bei der Analyse in JMP haben wir die Möglichkeit, das Skript für die JMP-Analyse anzufordern:
Ich kann dieses Skript dann in das Post-Query-Skript- Bedienfeld des Abfrage-Generators einfügen:
Abbildung 15: Hinzufügen eines Post-Query-JSL-Skripts
Dieses Skript wird jetzt jedes Mal ausgeführt, wenn ich die Abfrage ausführe.
Nun möchte ich meine Fragen etwas präzisieren. Anstatt die Einnahmen nach Kategorie und Schauspieler für alle Verleihe in der Datenbank zu betrachten, möchte ich sie vielleicht nach dem Land, in dem der Film ausgeliehen wurde, dem Geschäft, in dem er ausgeliehen wurde, oder nach einem bestimmten Zeitraum betrachten.
Die Tabelle Vermietung enthält die Felder Mietdatum und Kundennummer , sodass ich fast das habe, was ich brauche. Wir haben bereits gesehen, dass Kundennummer eine Fremdschlüsselreferenz zur Tabelle Kunde ist. Aus der Tabelle Kunde können wir das Herkunftsland des Kunden und das Geschäft, in dem die DVD ausgeliehen wurde, ermitteln.
Wenn ich im Tabellenbereich des Abfrage-Generators auf die Schaltfläche „Ändern… “ klicke, kann ich zurückgehen und weitere Tabellen zur Abfrage hinzufügen. Um Länderinformationen zu erhalten, muss ich der Abfrage die Tabellen „Kunde“ , „Adresse“ , „Stadt“ und „Land“ hinzufügen. Dank der vom Administrator dieser Datenbank eingerichteten Schlüsselbeziehungen werden alle Tabellen erfolgreich automatisch verknüpft.
Nachdem ich die erforderlichen zusätzlichen Tabellen hinzugefügt habe, kann ich im Abfrage-Generator die benötigten Spalten zur Abfrage hinzufügen: Mietdatum , Geschäfts-ID und Land . Ich habe außerdem benutzerfreundliche JMP-Namen festgelegt und den Modellierungstyp von Geschäfts-ID korrigiert:
Abbildung 16: Nach dem Hinzufügen der Spalten „Mietdatum“, „Geschäfts-ID“ und „Land“
Filtern wir zunächst nach dem Land des Kunden. Dazu klicke ich mit der rechten Maustaste auf die Variable t7.Land im Bereich „Eingeschlossene Spalten“ und wähle „Filtern nach“ . Anschließend wird im Bereich „Filter“ ein Filter für das Land angezeigt:
Abbildung 17: Länderfilter
An diesem Punkt kann ich einfach durch die Liste der Länder scrollen und die Länder markieren, die mich interessieren. Anschließend kann ich die Abfrage ausführen. Es werden dann nur Zeilen aus diesen Ländern einbezogen.
Auf ähnliche Weise kann ich Filter für Geschäfts-ID und Mietdatum hinzufügen, woraufhin das Filterfenster folgendermaßen aussieht:
Wenn ich den Filter „Mietdatum“ hinzufüge, erhalte ich standardmäßig einen einfachen Vergleichsfilter , da es sich um eine kontinuierliche Variable handelt. Stattdessen hätte ich gerne einen Bereichsfilter . Über das rote Dreieckmenü neben „t1.rental_date“ kann ich den Filtertyp in „Bereich“ ändern:
Nun sieht der Filter für t1.Mietdatum folgendermaßen aus:
Ich kann einen Zeitraum angeben. Wenn ich also alle Mietobjekte aus der zweiten Hälfte des Jahres 2005 finden möchte, kann ich Folgendes tun:
Abbildung 18: Datumsbereichsfilter
Alle Filtertypen im Abfrage-Generator lassen sich effektiv deaktivieren. So können Sie einer Abfrage mehrere Filter hinzufügen, auch wenn nicht alle für die jeweilige Abfrage aktiv sind. Bei kategorialen Filtern mit Wertelisten deaktiviert die Auswahl von (Alle) oben den Filter. Bei Filtern mit numerischen Werten deaktiviert die Angabe von „ fehlend “ (.) den Filter (oder einen Teil davon). Wenn ich also alle Mieten ab dem 1. Januar 2006 abfragen möchte, gehe ich wie folgt vor:
Jetzt sieht das Filterfeld für meine Abfrage wie in Abbildung 19 unten aus.
| Abbildung 19: Filter „Fehlend“ deaktivieren | Abbildung 20: Filter mit zwei ODER-Gruppen |
Mit den in Abbildung 19 gezeigten Filtern liefert die Abfrage nun Zeilen, bei denen das Land des Kunden entweder Frankreich oder Deutschland ist und die DVD am oder nach dem 1. Januar 2006 in Filiale 2 ausgeliehen wurde. Standardmäßig werden mehrere Filter mit UND verknüpft, sodass eine Zeile alle Filter erfüllen muss, um im Ergebnis zu erscheinen. Wenn Sie einen ODER-Filter benötigen, klicken Sie auf die ODER- Schaltfläche. Dadurch wird eine weitere Filtergruppe erstellt, und die beiden Filtergruppen werden mit ODER verknüpft. Beispielsweise liefert der in Abbildung 20 oben gezeigte Filter Leihfilme, deren Kategorie Drama ist oder deren Filmtitel „Bucket“ enthält.
Eingabeaufforderungsfilter
Obwohl es praktisch ist, Filterkriterien im Abfrage-Generator angeben zu können, möchten Sie möglicherweise auch eine Abfrage für andere Benutzer erstellen, die nicht unbedingt die gesamte Benutzeroberfläche des Abfrage-Generators benötigen, sondern bei jeder Ausführung der Abfrage unterschiedliche Filterkriterien eingeben können. Am einfachsten erreichen Sie dies, indem Sie im roten Dreieckmenü neben Filter die Option Alle Abfragen beim Ausführen auswählen:
Dadurch werden alle Filter in der Abfrage zu Eingabeaufforderungsfiltern . Wenn Sie nur einige Filter als Eingabeaufforderungen verwenden möchten, können Sie die Eingabeaufforderung für jeden einzelnen Filter über das rote Dreieckmenü ein- und ausschalten.
Nachdem ich „All Prompt On Run“ eingestellt habe, werde ich beim Ausführen dieser Abfrage aufgefordert, die Filterwerte wie gewünscht festzulegen:
Die Standardeinstellungen für die Eingabeaufforderung entsprechen den Einstellungen, die vor der Ausführung der Abfrage im Abfrage-Generator (oder in der gespeicherten Abfragedatei, wenn die Abfrage direkt aus der Datei ausgeführt wird) ausgewählt wurden.
Sie können auch die in der Eingabeaufforderung verwendeten Beschriftungen anpassen. Angenommen, ich möchte beispielsweise statt „ Land: “ „ Land des Kunden: “ verwenden. Wählen Sie im Abfrage-Generator über das rote Dreieckmenü für den Filter „t7.Land“ die Option „Eingabeaufforderung konfigurieren“ aus. Daraufhin wird ein Dialogfeld geöffnet, in dem Sie die Beschriftung der Eingabeaufforderung sowie die Tabelle mit den Kategorieebenen ändern können:
Probenahme
Wenn Ihre Datenbank Zufallsstichproben unterstützt, werden auf der Registerkarte „Probe“ im oberen mittleren Bereich des Abfrage-Generators die von Ihrer Datenbank unterstützten Stichprobenoptionen angezeigt. Für die Beispiele in diesem Dokument habe ich PostgreSQL verwendet, das einfache Zufallsstichproben einer bestimmten Zeilenanzahl unterstützt:
SQL Server und Oracle unterstützen unterschiedliche Sampling-Optionen, daher werden auf der Registerkarte „Probe“ die unterschiedlichen Optionen angezeigt:
| Abbildung 21: SQL Server-Sampling-Optionen | Abbildung 22: Oracle-Sampling-Optionen |
Die Stichprobenentnahme wird von der Datenbank durchgeführt, bevor die Daten in JMP heruntergeladen werden. Dies ist also eine gute Möglichkeit, die Anzahl der aus Abfragen abgerufenen Zeilen zu begrenzen, die andernfalls eine untragbar große Anzahl von Zeilen abrufen würden.
Schreiben Ihres eigenen SQL
Wir sind uns bewusst, dass trotz der vielen Funktionen, die der Abfrage-Generator jetzt und in Zukunft bieten wird, es vorkommen kann, dass Sie eine Abfrage benötigen, die Funktionen ausführt, die der Abfrage-Generator nicht direkt unterstützt. Möglicherweise haben Sie bereits SQL geschrieben und sorgfältig debuggt, das genau die Abfrage ausführt, die Sie von JMP benötigen. Der Abfrage-Generator bietet zwei Möglichkeiten, Ihr eigenes SQL für eine Abfrage bereitzustellen:
- Benutzerdefinierter Filterausdruck – Wenn der Abfrage-Generator die gewünschten Tabellen auswählt und die einzuschließenden Spalten angibt, Sie aber nur einen benutzerdefinierten SQL-Ausdruck als Teil des Filters einschließen möchten, können Sie „Benutzerdefinierten Ausdruck hinzufügen“ aus dem roten Dreieckmenü „ Filter “ auswählen:
| Abbildung 23: Hinzufügen eines benutzerdefinierten Filterausdrucks | Abbildung 24: Der resultierende Filter |
Geben Sie den gewünschten SQL-Code in das Feld „Benutzerdefinierter Ausdruck“ ein, solange es sich um gültiges SQL für die Abfrage handelt.
Wenn ein benutzerdefinierter Ausdrucksfilter als Eingabeaufforderungsfilter festgelegt ist, wird der benutzerdefinierte Ausdruck im Dialogfeld „Filteraufforderung“ angezeigt und kann bearbeitet werden.
- Konvertieren Sie die gesamte Abfrage in eine benutzerdefinierte SQL-Abfrage . Wenn Sie nur Ihr eigenes SQL für eine gesamte Abfrage schreiben möchten, können Sie nach dem Starten des Abfrage-Generators und der Angabe der Datenverbindung das rote Dreieckmenü oben links im Abfrage-Generator neben dem Feld Abfragename verwenden und „In benutzerdefiniertes SQL konvertieren“ auswählen:
Abbildung 25: In benutzerdefiniertes SQL konvertieren
Das Fenster „Abfrage-Generator“ wechselt in den benutzerdefinierten SQL-Modus und bietet Ihnen einen großen Bereich für die Eingabe Ihres eigenen SQL-Codes. Wenn Sie die Abfrage interaktiv erstellt haben, wird das Feld „Benutzerdefinierter SQL-Code“ mit dem basierend auf Ihren bisherigen Auswahlen generierten SQL initialisiert (siehe Abbildung 26 unten).
Abbildung 26: Benutzerdefinierte SQL-Abfrage
Sie können das SQL nach Bedarf bearbeiten oder SQL aus einer anderen Quelle einfügen und anschließend auf die Schaltfläche „Aktualisieren“ klicken, um die Abfragevorschau zu aktualisieren. Einige Einschränkungen:
- Mit einer benutzerdefinierten SQL-Abfrage können Sie keine Filterung mit Eingabeaufforderung durchführen.
- Wenn Sie eine Abfrage in benutzerdefiniertes SQL konvertieren und das SQL bearbeiten, gehen beim Zurücksetzen auf interaktiv alle am SQL vorgenommenen Anpassungen verloren. Der Abfrage-Generator versucht nicht, das SQL zu interpretieren.
Abschnitt 4: Verwalten von Hintergrundabfragen
Der Abfrage-Generator führt Abfragen standardmäßig im Hintergrund aus. Darüber hinaus ist es auch möglich, Abfragen über die JMP Scripting Language (JSL) im Hintergrund auszuführen (siehe Abschnitt 6 ). JMP bietet Ihnen zwei Möglichkeiten, die Liste der aktuell laufenden Hintergrundabfragen anzuzeigen und diese bei Bedarf zu stoppen.
Abfragestatusbereich
Im unteren mittleren Bereich des Abfrage-Generators befindet sich die Registerkarte „Abfragestatus“ . Alle Hintergrundabfragen, die aus diesem Fenster gestartet wurden, werden dort angezeigt:
Wenn Sie eine laufende Abfrage abbrechen möchten, wählen Sie sie einfach in der Liste aus und klicken Sie auf die Schaltfläche „ Stoppen“ . Wenn bereits Zeilen abgerufen wurden, öffnet JMP eine Datentabelle mit den bereits abgerufenen Zeilen. Der Name der Datentabelle weist darauf hin, dass es sich um ein partielles Ergebnis handelt.
Fenster „Runninq-Abfragen“
Wählen Sie im Menü Ansicht im Startfenster oder in einem anderen JMP-Fenster mit entsprechendem Menü die Option Laufende Abfragen aus. Daraufhin öffnet sich ein Fenster, das dem Tab Abfragestatus des Abfrage-Generators entspricht. Das Fenster „Laufende Abfragen“ enthält jedoch alle Abfragen, die aktuell im Hintergrund ausgeführt werden, unabhängig davon, wie sie aufgerufen wurden (über ein Fenster des Abfrage-Generators oder über die JMP-Skriptsprache):
Wählen Sie auch hier eine Abfrage aus und klicken Sie auf „Stoppen“ , um die Ausführung zu beenden und alle Teilergebnisse zu öffnen.
Abschnitt 5: Abfragen teilen
In der Einleitung wurde erwähnt, dass Offener Tisch Skripte erstellte, die schwer zu teilen waren, da das Datenbankkennwort im Verbindungsstring enthalten war. Der Abfrage-Generator funktioniert anders. Wenn Sie eine .jmpquery- Datei für eine Abfrage speichern, deren Verbindung ein Kennwort erforderte, und diese Datei in einem Texteditor öffnen, sehen Sie etwa Folgendes:
Sie sehen, dass der Benutzername ( UID ) in der Verbindungszeichenfolge vorhanden ist, sehen dann aber PWD=%_PWD_% . „%_PWD_%“ ist nicht das Kennwort, sondern ein Platzhalter, der vom Query Builder eingefügt wurde. Wenn der Query Builder diesen Platzhalter erkennt, weiß er, dass ursprünglich ein Kennwort erforderlich war. Wenn diese Abfrage also zu einem Zeitpunkt geöffnet wird, an dem der öffnende Benutzer noch keine Verbindung zum Postgres-Dvdrental- DSN hat, muss der Query Builder den Benutzer zur Kennworteingabe auffordern.
Und was ist mit dem DSN selbst? Was passiert, wenn Sie diese .jmpquery-Datei an einen JMP-Benutzer senden, der keinen DSN namens „Postgres dvdrental“ auf seinem Rechner definiert hat? Offener Tisch kann in einem solchen Szenario nicht weiterarbeiten, aber Abfrage-Generator informiert den Benutzer darüber und gibt ihm die Möglichkeit, eine Verbindung zu einem anderen DSN herzustellen (der vermutlich mit derselben Datenbank verbunden ist) oder einen neuen zu erstellen.
Da für verschiedene Benutzer möglicherweise unterschiedliche DSNs auf ihren Rechnern definiert sind, sind einige Organisationen zu DSN-losen Verbindungen übergegangen. Der Abfrage-Generator kann derzeit eine DSN-basierte Verbindungszeichenfolge nicht in eine DSN-lose konvertieren. Wenn Sie jedoch eine Verbindungszeichenfolge im Skript für eine Abfrage so ändern, dass anstelle eines DSN ein TREIBER und ein SERVER angegeben werden, propagiert der Abfrage-Generator diese DSN-lose Verbindung in allen Skripten oder .jmpquery-Dateien, die er generiert.
Abschnitt 6: Ausführen von Abfragen aus der JMP-Skriptsprache (JSL)
Mit dem Abfrage-Generator erstellte Abfragen können auch über JSL-Skripte (JMP Scripting Language) ausgeführt werden. Am besten speichern Sie die Abfrage als .jmpquery- Datei, öffnen sie in JSL und führen sie aus. Angenommen, Sie haben beispielsweise eine Abfrage mit dem Namen pg_rentals.jmpquery im Ordner c:\Benutzer\öffentlich\temp gespeichert. Mit dieser Abfragedatei in JSL können Sie Folgendes tun:
- Öffnen Sie eine .jmpquery-Datei im Query Builder:
offen( "c:\Benutzer\öffentlich\temp\pg_rentals.jmpquery" );
Abfrage= offen( "c:\Benutzer\öffentlich\temp\pg_rentals.jmpquery",Privat );
- Öffnen Sie die Abfrage im Abfrage-Generator zur Bearbeitung:
Abfrage<< Ändern;
Abfrage<< Laufen();
- Führen Sie die Abfrage im Vordergrund aus. Die resultierende Datentabelle wird nach Abschluss der Abfrage in JMP geöffnet. dtResult ist ein Verweis auf die Ergebnisdatentabelle.
dtErgebnis=Abfrage<< Vordergrund ausführen();
- Führen Sie die Abfrage im Vordergrund aus, öffnen Sie die resultierende Datentabelle jedoch nicht in JMP. Halten Sie sie privat. „dtResult“ ist ein Verweis auf die Ergebnisdatentabelle.
dtErgebnis=Abfrage<< Vordergrund ausführen(Privat );
- Führen Sie die Abfrage im Hintergrund aus. Die resultierende Datentabelle wird nach Abschluss der Abfrage in JMP geöffnet. „Hintergrund ausführen“ kann keinen Verweis auf die Ergebnisdatentabelle zurückgeben, da die Abfrage lediglich im Hintergrund ausgeführt wird. Die folgenden Beispiele zeigen, wie Sie bei der Ausführung von Abfragen im Hintergrund einen Verweis auf die Ergebnisdatentabelle erhalten.
Abfrage<< Hintergrund ausführen();
- Führen Sie die Abfrage im Hintergrund aus. Ist die Abfrage erfolgreich, rufen Sie die Distributionsplattform für die Ergebnisdatentabelle auf ( Abfrageergebnis ist eine spezielle JSL-Variable innerhalb eines Bei Ausführung abgeschlossen -Skripts, die auf die Ergebnisdatentabelle verweist). Wird die Abfrage vom Benutzer abgebrochen oder tritt ein Fehler auf, wird eine Meldung in das JMP-Protokoll geschrieben.
Abfrage<< Hintergrund ausführen(
Bei Ausführung abgeschlossen (
Abfrageergebnis<< Verteilung( Nominale Verteilung( Spalte( :Titel ) ) );
),
Bei Ausführung abgebrochen (Schreiben( "\!NDie Abfrage wurde abgebrochen!" ) ),
Bei einem Fehler (Schreiben( "\!NFehler beim Ausführen der Abfrage!" ); )
);
- Führen Sie die Abfrage im Hintergrund aus. Rufen Sie nach erfolgreichem Abschluss der Abfrage die Funktion MyFunc auf. Die Datentabelle mit den Abfrageergebnissen wird immer als erster Parameter an die Funktion übergeben.
::MeineFunktion= Funktion({ dt },{ Standardmäßig lokal },
Schreiben( "\!nAnzahl der Anmietungen: ", NRows(dt ) );
dt<< Verteilung( Nominale Verteilung( Spalte( :Titel ) ) );
);
Abfrage<< Hintergrund ausführen(Bei Ausführung abgeschlossen (::MeineFunktion ) );
Wenn das Öffnen einer Abfrage aus einer separaten .jmpquery-Datei in Ihrem JSL-Skript nicht zufriedenstellend ist, können Sie das Rohskript zum Erstellen eines abfrageskriptfähigen Objekts aus dem roten Dreieckmenü oben links im Fenster „Abfrage-Generator“ abrufen:
Wenn ich beispielsweise „Skript im Skriptfenster speichern und ausführen“ für eine einfache Abfrage aufrufe, erhalte ich Folgendes:
„New SQL Query“ gibt ein skriptfähiges SQL-Abfrageobjekt zurück, genau wie „Open “ beim Öffnen einer .jmpquery-Datei, und kann auf die gleiche Weise verwendet werden.
Was ist neu in JMP 12.1?
Suchen
Viele unserer Kunden verfügen über riesige Datenmengen in Hunderten von Schemata, Tabellen und Spalten in ihren Datenbanken. In der JMP 12.1-Version des Abfrage-Generators haben wir die Listen mit Schemata, Tabellen und Spalten um Suchfelder erweitert, damit Sie die gesuchten Daten schnell finden können:
Alle diese Suchen- Felder werden während der Eingabe aktualisiert und Sie haben die Möglichkeit, Ihre Suche mithilfe regulärer Ausdrücke zu verfeinern.
Hinzufügen verknüpfter Tabellen
Wenn eine Tabelle, die Sie der Abfrage hinzugefügt haben, im Dialogfeld „ Tabellen für die Abfrage auswählen“ Fremdschlüsselverweise auf andere Tabellen enthält, können Sie alle diese verknüpften Tabellen der Abfrage hinzufügen, indem Sie auf die Schaltfläche „Verknüpfte Tabellen hinzufügen“ klicken.
Als Vorlage öffnen
Häufig müssen Sie eine Abfrage erstellen, die einer anderen Abfrage, die Sie erstellt und in einer .jmpquery-Datei gespeichert haben, sehr ähnlich ist. Sie können die zuvor gespeicherte Abfrage jederzeit öffnen, Änderungen vornehmen und sie mit „Speichern unter“ in einer anderen .jmpquery-Datei speichern . Sollten Sie jedoch versehentlich auf „Speichern“ klicken, wird Ihre ursprüngliche Abfrage überschrieben. Um dies zu verhindern, gibt es die Funktion „Als Vorlage öffnen“ . „Als Vorlage öffnen“ ist im Kontextmenü des Startfensters für .jmpquery-Dateien sowohl auf Mac als auch unter Windows verfügbar:
Wenn Sie eine JMP-Abfrage als Vorlage öffnen, öffnen Sie tatsächlich eine Kopie der Originalabfrage, die nicht mit der JMP-Abfragedatei auf der Festplatte verknüpft ist. Wenn Sie eine Abfrage als Vorlage geöffnet, Änderungen vorgenommen und versehentlich auf die Schaltfläche „Speichern“ geklickt haben, werden Sie aufgefordert, eine Datei zum Speichern der Abfrage anzugeben.
Sie können auch eine .jmpquery-Datei als Vorlage aus der JMP Scripting Language (JSL) öffnen:
offen( "C:\Benutzer\Öffentlich\Temp\rentals.jmpquery",Als Vorlage );
Bearbeiten einer berechneten Spaltenformel
In JMP 12.0 konnten Sie Ihrer Abfrage eine berechnete Spalte hinzufügen, die auf dem Datenbankserver berechnet wurde. Die resultierenden Werte wurden in der Abfrage zurückgegeben. Wenn Sie jedoch die Formel einer zuvor erstellten berechneten Spalte bearbeiten wollten, war dies nicht möglich. Stattdessen mussten Sie die berechnete Spalte löschen und eine neue hinzufügen, was möglicherweise die Neuerstellung einer komplexen Formel erforderte. In JMP 12.1 können Sie nun eine berechnete Spalte bearbeiten. Klicken Sie einfach mit der rechten Maustaste auf die Spalte unter „Verfügbare Spalten“ und wählen Sie „Formel bearbeiten“ :
Fehlerbehebungen
Wie erwartet haben wir in JMP 12.1 eine Reihe von Fehlern im Query Builder behoben, darunter auch einige von Kunden gemeldete. Sollten Sie bei der Verwendung des Query Builders auf ein Problem stoßen, melden Sie es bitte dem technischen Support von SAS, damit wir es beheben können.
Abschluss
Der Abfrage-Generator ist eine neue Funktion in JMP 12 (aktualisiert in JMP 12.1), mit der Sie hochgradig individuelle Multi-Table-SQL-Abfragen für Ihre relationalen Datenbanken erstellen können. Ziel ist es, Ihnen die schnellere und analysebereite Übertragung Ihrer Daten in JMP zu erleichtern und die sichere Weitergabe Ihrer Abfragen zu vereinfachen. Wir freuen uns auf Ihr Feedback und testen Sie die Funktion.eric.hill@jmp.com) darüber, was für Sie gut funktioniert und was nicht.
