Semantische Ansichten abfragen¶

Richtlinien für die Angabe der SEMANTIC_VIEW-Klausel¶

Bei Angabe der SEMANTIC_VIEW-Klausel befolgen Sie folgende Richtlinien:

• Sie müssen in der SEMANTIC_VIEW-Klausel mindestens eine der folgenden Klauseln angeben:

  • METRICS

  • DIMENSIONS

  • FACTS

  Sie können nicht alle diese Klauseln aus der SEMANTIC_VIEW-Klausel weglassen.

• Wenn Sie eine Kombination dieser Klauseln angeben, ist Folgendes zu beachten:

  • Sie können FACTS und METRICS nicht in der gleichenSEMANTIC_VIEW-Klausel angeben.

  • Sie können jedoch sowohl FACTS als auchDIMENSIONS in einer Abfrage angeben. Sie sollten dies nur tun, wenn die Dimensionen die Fakten eindeutig bestimmen können.

    Die Abfrage gruppiert die Ergebnisse nach Dimensionen: Wenn die Fakten nicht von den Dimensionen abhängen, können die Ergebnisse nicht-deterministisch sein.

  • Wenn Sie FACTS und DIMENSIONS angeben, müssen alle in der Abfrage verwendeten Fakten und Dimensionen (einschließlich diejenigen, die in der WHERE-Klausel angegeben sind), in der gleichen logischen Tabelle definiert sein.

  • Wenn Sie eine Dimension und eine Metrik angeben, muss die logische Tabelle für die Dimension mit der logischen Tabelle für die Metrik in Beziehung gesetzt werden.

    Außerdem muss die logische Tabelle für die Dimension eine gleiche oder geringere Granularität aufweisen als die logische Tabelle für die Metrik.

    Um festzustellen, welche Dimensionen diese Kriterien erfüllen, können Sie den SHOW SEMANTIC DIMENSIONS FOR METRIC-Befehl ausführen.

    Weitere Details dazu finden Sie unter Auswahl der Dimensionen, die Sie für eine bestimmte Metrik zurückgeben können.

• In der DIMENSIONS-Klausel können Sie einen Ausdruck angeben, der sich auf einen Fakt bezieht. Ähnlich verhält es sich mit der FACTS-Klausel, in der Sie einen Ausdruck angeben können, der sich auf eine Dimension bezieht. Beispiel:

  CopyExpand

  -- Dimension expression that refers to a fact
  DIMENSIONS my_table.my_fact
  
  -- Fact expression that refers to a dimension
  FACTS my_table.my_dimension
  

  Show lessSee more

  Scroll to top

  Einer der Hauptunterschiede zwischen der Verwendung von DIMENSIONS und FACTS besteht darin, dass die Abfrage die Ergebnisse nach den Dimensionen und Ausdrücken gruppiert, die in derDIMENSIONS-Klausel angegeben sind.

• In der METRICS-Klausel können Sie einen Ausdruck angeben, der Folgendes enthält:

  • Einen skalaren Ausdruck, der sich auf Metriken bezieht.

  • Eine Aggregation von Dimensionen oder Fakten.

• Geben Sie die METRICS-, DIMENSIONS- und FACTS-Klauseln in der Reihenfolge an, in der sie in den Ergebnissen erscheinen sollen.

  Wenn Sie möchten, dass die Abmessungen zuerst in den Ergebnissen erscheinen, geben Sie DIMENSIONS vor METRICS an. Andernfalls geben Sie zuerst METRICS an.

  Nehmen wir zum Beispiel an, dass Sie zuerst die METRICS-Klausel angeben:

  CopyExpand

  SELECT * FROM SEMANTIC_VIEW(
      tpch_analysis
      METRICS customer.customer_order_count
      DIMENSIONS customer.customer_name
    )
    ORDER BY customer_name
    LIMIT 5;
  

  Show lessSee more

  Scroll to top

  In der Ausgabe ist die erste Spalte die metrische Spalte (customer_order_count) und die zweite Spalte ist die Dimensionsspalte (customer_name):

  Expand

  +----------------------+--------------------+
  | CUSTOMER_ORDER_COUNT | CUSTOMER_NAME      |
  |----------------------+--------------------|
  |                    6 | Customer#000000001 |
  |                    7 | Customer#000000002 |
  |                    0 | Customer#000000003 |
  |                   20 | Customer#000000004 |
  |                    4 | Customer#000000005 |
  +----------------------+--------------------+
  

  Show lessSee more

  Scroll to top

  Wenn Sie stattdessen zuerst die DIMENSIONS-Klausel angeben:

  CopyExpand

  SELECT * FROM SEMANTIC_VIEW(
      tpch_analysis
      DIMENSIONS customer.customer_name
      METRICS customer.customer_order_count
    )
    ORDER BY customer_name
    LIMIT 5;
  

  Show lessSee more

  Scroll to top

  In der Ausgabe ist die erste Spalte die Dimensionsspalte (customer_name) und die zweite Spalte ist die metrische Spalte (customer_order_count):

  Expand

  +--------------------+----------------------+
  | CUSTOMER_NAME      | CUSTOMER_ORDER_COUNT |
  |--------------------+----------------------|
  | Customer#000000001 |                    6 |
  | Customer#000000002 |                    7 |
  | Customer#000000003 |                    0 |
  | Customer#000000004 |                   20 |
  | Customer#000000005 |                    4 |
  +--------------------+----------------------+
  

  Show lessSee more

  Scroll to top

• Sie können die durch eine SEMANTIC_VIEW-Klausel definierte Beziehung in anderen SQL-Konstrukten verwenden, einschließlich JOIN, PIVOT, UNPIVOT, GROUP BY und allgemeinen Tabellenausdrücken (CTEs).

• Die Spaltenüberschriften der Ausgabe verwenden die unqualifizierten Namen der Metriken und Dimensionen.

  Wenn Sie mehrere Metriken und Dimensionen mit denselben Namen haben, verwenden Sie einen Tabellenalias, um den Spaltenüberschriften unterschiedliche Namen zuzuweisen. Siehe Behandlung doppelter Spaltennamen in der Ausgabe.

Um alle Metriken oder Dimensionen in einer gegebenen logischen Tabelle zurückzugeben, verwenden Sie ein Sternchen als Platzhalter, qualifiziert durch den Namen der logischen Tabelle. Beispielsweise, um alle in der logischen Tabelle``customer`` definierten Metriken und Dimensionen zurückzugeben:

SELECT * FROM SEMANTIC_VIEW(
  tpch_analysis
  DIMENSIONS customer.*
  METRICS customer.*
);

+-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------+
| CUSTOMER_COUNTRY_CODE | CUSTOMER_MARKET_SEGMENT | CUSTOMER_NAME      | CUSTOMER_NATION_NAME | CUSTOMER_REGION_NAME | CUSTOMER_COUNT | CUSTOMER_ORDER_COUNT |
|-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------|
| 18                    | BUILDING                | Customer#000034857 | INDIA                | ASIA                 |              1 |                    0 |
| 14                    | AUTOMOBILE              | Customer#000145116 | EGYPT                | MIDDLE EAST          |              1 |                    0 |
...

Beispiele für die Angabe der SEMANTIC_VIEW-Klausel¶

Die folgenden Beispiele verwenden die Ansicht tpch_analysis, die in Beispiel für die Verwendung von SQL zur Erstellung einer semantischen Ansicht definiert ist:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    METRICS customer.customer_count
  );

+----------------+
| CUSTOMER_COUNT |
+----------------+
|          15000 |
+----------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  );

+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE              |     142570.25947219 |
| FURNITURE               |     142563.63314267 |
| MACHINERY               |     142655.91550608 |
| HOUSEHOLD               |     141659.94753445 |
| BUILDING                |     142425.37987558 |
+-------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_name
    METRICS orders.average_line_items_per_order,
            orders.order_average_value
  )
  WHERE average_line_items_per_order > 4
  ORDER BY average_line_items_per_order DESC
  LIMIT 5;

+--------------------+------------------------------+---------------------+
| CUSTOMER_NAME      | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
+--------------------+------------------------------+---------------------+
| Customer#000045678 |                         6.87 |           175432.21 |
| Customer#000067890 |                         6.42 |           182376.58 |
| Customer#000012345 |                         5.93 |           169847.42 |
| Customer#000034567 |                         5.76 |           178952.36 |
| Customer#000056789 |                         5.64 |           171248.75 |
+--------------------+------------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS DATE_PART('year', orders.order_date) AS year
  )
  ORDER BY year;

+------+
| YEAR |
|------|
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
+------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS orders.order_date
    METRICS orders.average_line_items_per_order,
            orders.order_average_value
    WHERE orders.order_date > '1995-01-01'
  )
  ORDER BY order_date ASC
  LIMIT 5;

+------------+------------------------------+---------------------+
| ORDER_DATE | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
|------------+------------------------------+---------------------|
| 1995-01-02 |                     3.884547 |     151237.54900533 |
| 1995-01-03 |                     3.894819 |     145751.84384615 |
| 1995-01-04 |                     3.838863 |     145331.39167457 |
| 1995-01-05 |                     4.040689 |     150723.67353678 |
| 1995-01-06 |                     3.990755 |     152786.54109399 |
+------------+------------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    FACTS customer.c_customer_order_count
    WHERE orders.order_date < '2021-01-01' AND region.r_name = 'AMERICA'
  );

Anforderungen an Dimensionen und Kennzahlen in einer SELECT-Anweisung¶

In der SELECT-Anweisung können Sie nur auf Dimensionen und Kennzahlen verweisen, die eindeutige Namen haben und sich nicht durch ihren logischen Tabellennamen unterscheiden. Angenommen, eine semantische Ansicht hat zwei Dimensionen mit nicht qualifiziertem Namen name:

DIMENSIONS (
  nation.name AS nation.n_name,
  region.name AS region.r_name
);

In der SELECT-Anweisung wird bei Angabe des qualifizierten Namens einer Dimension oder Kennzahl der Qualifizierer als Name der semantischen Ansicht interpretiert, nicht als Name einer logischen Tabelle:

SELECT nation.name, region.name
  FROM duplicate_names
  GROUP BY nation.name, region.name;

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'NATION.NAME'

Auswählen von Metriken¶

If you want to select a metric that is defined in a semantic view, you must pass the metric to the AGG function, which is a special aggregate function for metrics in semantic views.

Beispiel:

SELECT AGG(order_average_value) FROM tpch_analysis;

in der SELECT-Liste können Sie einen Ausdruck angeben, der eine Metrik verwendet. Beispiel:

SELECT AGG(order_average_value) * 10 FROM tpch_analysis;

Sie können auch Ad-hoc-Kennzahlen definieren und auswählen, indem Sie eine Dimension oder eine Fakten- an eine beliebige Aggregatfunktion übergeben. Beispiel:

SELECT COUNT(customer_market_segment) FROM tpch_analysis;

Auswählen von Dimensionen¶

Wenn die SELECT-Liste Dimensionen enthält, müssen Sie diese Dimensionen in der GROUP BY-Klausel angeben. Beispiel:

SELECT customer_market_segment, customer_nation_name, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment, customer_nation_name;

In der SELECT-Liste und in der GROUP BY-Klausel können Sie eine Dimension oder einen skalaren Ausdruck angeben, der eine Dimension oder einen Fakt verwendet. Beispiel:

SELECT LOWER(customer_nation_name), AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_nation_name;

Angabe der WHERE-Klausel¶

In der WHERE-Klausel können Sie nur bedingte Ausdrücke verwenden, die sich auf Dimensionen oder Fakten beziehen. Beispiel:

SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  WHERE customer_market_segment = 'BUILDING'
  GROUP BY customer_market_segment;

Die Dimensionen müssen für jede in der Abfrage verwendete Metrik erreichbar sein.

Angabe der HAVING-Klausel¶

In der HAVING-Klausel können Sie nur Kennzahlen angeben und müssen diese an eine der unter Auswählen von Metriken aufgeführten Aggregatfunktionen übergeben. Beispiel:

SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment
  HAVING AGG(order_average_value) > 142500;

Einschränkungen bei der Angabe des Namens der semantischen Ansicht in der FROM-Klausel¶

Folgendes kann in der SELECT-Anweisung nicht angegeben werden:

• Erweiterungen der FROM-Klausel, einschließlich:

  • PIVOT

  • UNPIVOT

  • MATCH_RECOGNIZE

  • LATERAL

• Joins

• Aufrufe von Fensterfunktionen

• QUALIFY

• Unterabfragen

Fensterfunktions-Metriken definieren¶

Verwenden Sie bei der Angabe eines Fensterfunktionsaufrufs diese Syntax, die in Parameter für Fensterfunktionskennzahlen beschrieben wird.

Das folgende Beispiel erstellt eine semantische Ansicht, die die Definitionen mehrerer Fensterfunktions-Metriken enthält. Das Beispiel verwendet Tabellen aus der TPC-DS-Beispieldatenbank. Weitere Informationen zum Zugriff auf diese Datenbank finden Sie unter Den TPC-DS-Datensatz zu Ihrem Konto hinzufügen.

CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
  TABLES (
    store_sales AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.store_sales,
    date AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.date_dim PRIMARY KEY (d_date_sk)
  )
  RELATIONSHIPS (
    sales_to_date AS store_sales(ss_sold_date_sk) REFERENCES date(d_date_sk)
  )
  DIMENSIONS (
    date.date AS d_date,
    date.d_date_sk AS d_date_sk,
    date.year AS d_year
  )
  METRICS (
    store_sales.total_sales_quantity AS SUM(ss_quantity)
      WITH SYNONYMS = ('Total sales quantity'),

    store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity'),

    store_sales.total_sales_quantity_30_days_ago AS LAG(total_sales_quantity, 30)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date)
      WITH SYNONYMS = ('Sales quantity 30 days ago'),

    store_sales.avg_7_days_sales_quantity_30_days_ago AS AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '36 days' PRECEDING AND INTERVAL '30 days' PRECEDING)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity 30 days ago')

  );

Sie können in der Metrikdefinition auch andere Metriken aus derselben logischen Tabelle verwenden. Beispiel:

METRICS (
  orders.m3 AS SUM(m2) OVER (PARTITION BY m1 ORDER BY m2),
  orders.m4 AS ((SUM(m2) OVER (..)) / m1) + 1
)

Fensterfunktionsmetriken abfragen¶

Wenn Sie eine semantische Ansicht abfragen und die Abfrage eine Fensterfunktionsmetrik zurückgibt, müssen Sie auch die Dimensionen zurückgeben, die in PARTITION BY dimension, PARTITION BY EXCLUDING dimension und ORDER BY dimension in der CREATE SEMANTIC VIEW-Anweisung für die semantische Ansicht angegeben werden.

Angenommen, Sie geben die Dimensionen date.date und date.year in den Klauseln PARTITION BY EXCLUDING und ORDER BY in der Definition der Metrik store_sales.avg_7_days_sales_quantity an:

CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
  ...
  DIMENSIONS (
    ...
    date.date AS d_date,
    ...
    date.year AS d_year
    ...
  )
  METRICS (
    ...
    store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity'),
    ...
  );

Wenn Sie die Metrik store_sales.avg_7_days_sales_quantity in einer Abfrage zurückgeben, müssen Sie auch die Dimensionen date.date und date.year zurückgeben:

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.avg_7_days_sales_quantity
);

Wenn Sie die Dimensionen date.date und date.year auslassen, tritt ein Fehler auf.

010260 (42601): SQL compilation error:
Invalid semantic view query: Dimension 'DATE.DATE' used in a
   window function metric must be requested in the query.

Um festzustellen, welche Dimensionen Sie in der Abfrage angeben müssen, führen Sie den Befehl SHOW SEMANTIC DIMENSIONS FOR METRIC aus. Um beispielsweise die Dimensionen zu bestimmen, die Sie beim Abrufen der Metrik store_sales.avg_7_days_sales_quantity angeben müssen, führen Sie diesen Befehl aus:

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

In der Ausgabe des Befehls steht in der Spalte required true für die Dimensionen, die Sie in der Abfrage angeben müssen.

+------------+-----------+--------------+----------+----------+---------+
| table_name | name      | data_type    | required | synonyms | comment |
|------------+-----------+--------------+----------+----------+---------|
| DATE       | DATE      | DATE         | true     | NULL     | NULL    |
| DATE       | D_DATE_SK | NUMBER(38,0) | false    | NULL     | NULL    |
| DATE       | YEAR      | NUMBER(38,0) | true     | NULL     | NULL    |
+------------+-----------+--------------+----------+----------+---------+

Die weiteren folgenden Beispiele fragen die Fensterfunktionsmetriken ab, die in Fensterfunktions-Metriken definieren definiert sind. Beachten Sie, dass die DIMENSIONS-Klausel die Dimensionen umfasst, die in den Klauseln PARTITION BY EXCLUDING und ORDER BY in den Metrikdefinitionen angegeben sind.

Das folgende Beispiel gibt die Umsatzmenge vor 30 Tagen zurück:

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.total_sales_quantity_30_days_ago
);

Das folgende Beispiel gibt den laufenden 7-Tage-Durchschnitt des Gesamtumsatzes von 30 Tagen zurück:

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.avg_7_days_sales_quantity_30_days_ago
);