Eine abgeleitete Tabelle ist eine Abfrage, deren Ergebnisse so verwendet werden, als wäre die abgeleitete Tabelle eine physische Tabelle in der Datenbank. Eine native abgeleitete Tabelle basiert auf einer Abfrage, die Sie mit LookML-Begriffen definieren. Dies unterscheidet sich von einer SQL-basierten abgeleiteten Tabelle, die auf einer Abfrage basiert, die Sie mit SQL-Begriffen definieren. Im Vergleich zu SQL-basierten abgeleiteten Tabellen sind native abgeleitete Tabellen bei der Modellierung Ihrer Daten wesentlich leichter zu lesen und zu verstehen. Weitere Informationen finden Sie auf der Dokumentationsseite Abgeleitete Tabellen in Looker im Abschnitt Native abgeleitete Tabellen und SQL-basierte abgeleitete Tabellen.
Sowohl native als auch SQL-basierte abgeleitete Tabellen werden in LookML mit dem Parameter derived_table auf Ansichtsebene definiert. Bei nativen abgeleiteten Tabellen müssen Sie jedoch keine SQL-Abfrage erstellen. Stattdessen geben Sie mit dem Parameter explore_source das Explore an, auf dem die abgeleitete Tabelle basieren soll, sowie die gewünschten Spalten und andere gewünschte Eigenschaften.
Sie können auch von Looker den LookML-Code der abgeleiteten Tabelle aus einer SQL Runner-Abfrage erstellen lassen, wie auf der Dokumentationsseite Mit SQL Runner zum Erstellen abgeleiteter Tabellen beschrieben.
Native abgeleitete Tabellen auf der Grundlage eines Explores definieren
Ausgehend von einem Explore kann Looker LookML für die gesamte abgeleitete Tabelle oder einen großen Teil davon generieren. Erstellen Sie einfach ein Explore, und wählen Sie alle Felder aus, die in der abgeleiteten Tabelle enthalten sein sollen. Führen Sie dann die folgenden Schritte aus, um den LookML-Code für die native abgeleitete Tabelle zu generieren:
Wählen Sie das Zahnradmenü Explore Actions (Aktionen ansehen) und dann Get LookML (LookML abrufen) aus.
Klicken Sie auf den Tab Abgeleitete Tabelle, um den LookML-Code zum Erstellen einer nativen abgeleiteten Tabelle für das Explore aufzurufen.
Kopieren Sie den LookML-Code.
Fügen Sie den kopierten LookML-Code in eine Ansichtsdatei ein:
Gehen Sie im Entwicklungsmodus zu Ihren Projektdateien.
Klicken Sie in der Looker-IDE oben in der Projektdateiliste auf das + und wählen Sie Ansicht erstellen aus. Alternativ können Sie auf das Menü eines Ordners klicken und darin Ansicht erstellen auswählen, um die Datei im Ordner zu erstellen.
Geben Sie der Ansicht einen aussagekräftigen Namen.
Optional können Sie Spaltennamen ändern, abgeleitete Spalten festlegen und Filter hinzufügen.
Wenn Sie in einem Explore den Wert
type: countals Messwert verwenden, werden die resultierenden Werte in der Visualisierung mit dem Ansichtsnamen und nicht mit dem Wort Anzahl gekennzeichnet. Geben Sie den Namen der Ansicht im Plural an, um Verwechslungen zu vermeiden. Sie können den Namen der Ansicht ändern. Wählen Sie dazu entweder in den Visualisierungseinstellungen unter Reihe die Option Vollständigen Feldnamen anzeigen aus oder verwenden Sie den Parameterview_labelmit einer Pluralversion des Ansichtsnamens.
Native abgeleitete Tabelle in LookML definieren
Unabhängig davon, ob Sie abgeleitete Tabellen verwenden, die in SQL oder nativem LookML deklariert wurden, wird eine derived_table-Abfrage als Tabelle mit einer Reihe von Spalten ausgegeben. Wenn die abgeleitete Tabelle in SQL ausgedrückt wird, werden die Spaltennamen der Ausgabe durch die SQL-Abfrage impliziert. Die folgende SQL-Abfrage enthält beispielsweise die Ausgabespalten user_id, lifetime_number_of_orders und lifetime_customer_value:
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
In Looker beruht eine Abfrage auf einem Explore, enthält Felder für Messwerte und Dimensionen, fügt ggf. Filter hinzu und kann auch eine Sortierreihenfolge vorgeben. Eine native abgeleitete Tabelle enthält all diese Elemente plus die Ausgabenamen für die Spalten.
Mit dem folgenden einfachen Beispiel wird eine abgeleitete Tabelle mit drei Spalten erstellt: user_id, lifetime_customer_value und lifetime_number_of_orders. Sie müssen die Abfrage nicht manuell in SQL schreiben. Stattdessen erstellt Looker die Abfrage für Sie, indem es das angegebene Explore-order_items und einige der Explore-Felder (order_items.user_id, order_items.total_revenue und order_items.order_count) verwendet.
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
include-Anweisungen verwenden, um Verweise auf Felder zu aktivieren
In der Ansichtsdatei der nativen abgeleiteten Tabelle verwenden Sie den Parameter explore_source, um auf ein Explore zu verweisen und die gewünschten Spalten und anderen gewünschten Merkmale für die native abgeleitete Tabelle zu definieren. Da Sie in der Ansichtsdatei der nativen abgeleiteten Tabelle auf ein Explore verweisen, müssen Sie auch die Datei mit der Explore-Definition einschließen. Explores werden normalerweise innerhalb einer Modelldatei definiert. Bei nativen abgeleiteten Tabellen ist es jedoch einfacher, eine separate Datei für das Explore mit der Dateiendung .explore.lkml zu erstellen, wie in der Dokumentation unter Explore-Dateien erstellen beschrieben. Auf diese Weise können Sie in der Ansichtsdatei der nativen abgeleiteten Tabelle eine einzelne Explore-Datei hinzufügen und nicht die gesamte Modelldatei. In diesem Fall gilt Folgendes:
- Die Ansichtsdatei der nativen abgeleiteten Tabelle sollte die Explore-Datei enthalten. Beispiel:
include: "/explores/order_items.explore.lkml"
- Die Explore-Datei sollte die benötigten Ansichtsdateien enthalten. Beispiel:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- Das Modell sollte die Explore-Datei enthalten. Beispiel:
include: "/explores/order_items.explore.lkml"
Explore-Dateien überwachen die Verbindung des Modells, in dem sie enthalten sind. Berücksichtigen Sie dies, wenn Sie Explore-Dateien in Modelle einbeziehen, die mit einer anderen Verbindung als das übergeordnete Modell der Explore-Datei konfiguriert sind. Wenn das Schema für die Verbindung des einschließenden Modells vom Schema für die Verbindung des übergeordneten Modells abweicht, kann dies zu Abfragefehlern führen.
Spalten nativer abgeleiteter Tabellen definieren
Wie im vorherigen Beispiel gezeigt, verwenden Sie column, um die Ausgabespalten der abgeleiteten Tabelle anzugeben.
Spaltennamen angeben
In der Spalte user_id entspricht der Spaltenname dem Namen des angegebenen Felds im ursprünglichen Explore.
Es wird häufiger vorkommen, dass der Spaltenname in der Ausgabetabelle anders lauten soll als der Name der Felder im ursprünglichen Explore. Im vorherigen Beispiel wurde mithilfe des Explores order_items eine Berechnung des Lifetime-Werts durch einen Nutzer erstellt. In der Ausgabetabelle ist total_revenue eigentlich lifetime_customer_value eines Kunden.
Mit der Deklaration column kann ein Ausgabename angegeben werden, der sich vom Eingabefeld unterscheidet. Mit dem folgenden Code wird Looker beispielsweise angewiesen, eine Ausgabespalte mit dem Namen lifetime_value aus dem Feld order_items.total_revenue zu erstellen:
column: lifetime_value {
field: order_items.total_revenue
}
Implizierte Spaltennamen
Wird der Parameter field in einer Spaltendeklaration ausgelassen, wird davon ausgegangen, dass er <explore_name>.<field_name> ist. Wenn Sie beispielsweise explore_source: order_items angegeben haben, dann
column: user_id {
field: order_items.user_id
}
entspricht
column: user_id {}
Abgeleitete Spalten für berechnete Werte erstellen
Sie können derived_column-Parameter hinzufügen, um Spalten anzugeben, die im Explore des Parameters explore_source nicht vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert erstellt wird.
Für die sql-Berechnung können alle Spalten verwendet werden, die Sie mit column-Parametern angegeben haben. Abgeleitete Spalten können zwar keine Summenfunktionen enthalten, aber Berechnungen, die an einer einzelnen Tabellenzeile durchgeführt werden können.
Im folgenden Beispiel wird dieselbe abgeleitete Tabelle wie im vorherigen Beispiel erstellt, mit dem Unterschied, dass eine berechnete Spalte average_customer_order hinzugefügt wird, die aus den Spalten lifetime_customer_value und lifetime_number_of_orders in der nativen abgeleiteten Tabelle berechnet wird.
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
SQL-Fensterfunktionen verwenden
Einige Datenbankdialekte unterstützen Fensterfunktionen, insbesondere zum Erstellen von Sequenznummern, Primärschlüsseln, laufenden und kumulativen Summen und anderen nützlichen mehrzeiligen Berechnungen. Nachdem die primäre Abfrage ausgeführt wurde, werden alle derived_column-Deklarationen in einer separaten Karte / einem separaten Ticket ausgeführt.
Sofern Ihr Datenbankdialekt Fensterfunktionen unterstützt, können Sie diese in Ihrer nativen abgeleiteten Tabelle nutzen. Erstellen Sie einen derived_column-Parameter mit einem sql-Parameter, der die gewünschte Fensterfunktion enthält. Beim Verweisen auf Werte sollten Sie den Spaltennamen verwenden, der in Ihrer nativen abgeleiteten Tabelle definiert wurde.
Im folgenden Beispiel wird eine native abgeleitete Tabelle erstellt, die die Spalten user_id, order_id und created_time enthält. Anschließend berechnet sie mithilfe einer abgeleiteten Spalte mit einer SQL ROW_NUMBER()-Fensterfunktion eine Spalte, die die Sequenznummer einer Kundenbestellung enthält.
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
Filter zu einer nativen abgeleiteten Tabelle hinzufügen
Angenommen, Sie möchten eine abgeleitete Tabelle mit dem Wert eines Kunden aus den letzten 90 Tagen erstellen. Sie möchten dieselben Berechnungen wie im vorherigen Beispiel, aber nur Käufe der letzten 90 Tage berücksichtigen.
Dazu fügen Sie einfach dem derived_table einen Filter hinzu, mit dem nach Transaktionen der letzten 90 Tage gefiltert wird. Der Parameter filters für eine abgeleitete Tabelle verwendet die gleiche Syntax, die Sie zum Erstellen eines gefilterten Messwerts verwenden.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Der WHERE-Klausel werden Filter hinzugefügt, wenn Looker den SQL-Code für die abgeleitete Tabelle schreibt.
Darüber hinaus können Sie den Unterparameter dev_filters von explore_source mit einer nativen abgeleiteten Tabelle verwenden. Mit dem Parameter dev_filters können Sie Filter angeben, die Looker nur auf Entwicklungsversionen der abgeleiteten Tabelle anwendet. So können Sie kleinere, gefilterte Versionen der Tabelle erstellen, um sie zu iterieren und zu testen, ohne nach jeder Änderung warten zu müssen, bis die vollständige Tabelle erstellt ist.
Der Parameter dev_filters fungiert in Verbindung mit dem Parameter filters, sodass alle Filter auf die Entwicklungsversion der Tabelle angewendet werden. Wenn sowohl dev_filters als auch filters Filter für dieselbe Spalte angeben, hat dev_filters in der Entwicklungsversion der Tabelle Vorrang.
Weitere Informationen finden Sie unter Im Entwicklungsmodus schneller arbeiten.
Filtervorlagen verwenden
Mit bind_filters können Sie Filtervorlagen hinzufügen:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Dies entspricht im Wesentlichen der Verwendung des folgenden Codes in einem sql-Block:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field ist das Feld, auf das der Filter angewendet wird. Der to_field muss ein Feld aus dem zugrunde liegenden explore_source sein.
from_field gibt das Feld an, aus dem der Filter abgerufen wird, wenn zur Laufzeit ein Filter vorhanden ist.
Im vorherigen bind_filters-Beispiel wendet Looker alle Filter, die auf das Feld filtered_lookml_dt.filter_date angewendet wurden, auf das Feld users.created_date an.
Sie können auch den Unterparameter bind_all_filters von explore_source verwenden, um alle Laufzeitfilter von einem Explore an eine Unterabfrage einer nativen abgeleiteten Tabelle zu übergeben. Weitere Informationen finden Sie auf der Dokumentationsseite zum Parameter explore_source.
Native abgeleitete Tabellen sortieren und begrenzen
Sie können die abgeleiteten Tabellen bei Bedarf auch sortieren und einschränken:
sorts: [order_items.count: desc]
limit: 10
Denken Sie daran, dass ein Explore die Zeilen möglicherweise in einer anderen Reihenfolge als die zugrunde liegende Sortierung anzeigt.
Native abgeleitete Tabellen in andere Zeitzonen konvertieren
Sie können die Zeitzone für Ihre native abgeleitete Tabelle mit dem Unterparameter timezone angeben:
timezone: "America/Los_Angeles"
Wenn Sie den Unterparameter timezone verwenden, werden alle zeitbasierten Daten in der nativen abgeleiteten Tabelle in die von Ihnen angegebene Zeitzone konvertiert. Eine Liste der unterstützten Zeitzonen finden Sie auf der Dokumentationsseite für timezone-Werte.
Wenn Sie in der Definition Ihrer nativen abgeleiteten Tabelle keine Zeitzone angeben, führt die native abgeleitete Tabelle keine Zeitzonenkonvertierung für zeitbasierte Daten durch. Stattdessen wird für zeitbasierte Daten standardmäßig die Zeitzone der Datenbank verwendet.
Wenn die native abgeleitete Tabelle nicht beständig ist, können Sie den Zeitzonenwert auf "query_timezone" festlegen, um automatisch die Zeitzone der aktuell ausgeführten Abfrage zu verwenden.