Interrogation des vues sémantiques¶

Guidelines for specifying the SEMANTIC_VIEW clause¶

When specifying the SEMANTIC_VIEW clause, follow these guidelines:

• Dans la clause SEMANTIC_VIEW, vous devez spécifier au moins l’une des clauses suivantes :

  • METRICS

  • DIMENSIONS

  • FACTS

  Vous ne pouvez pas omettre toutes ces clauses dans la clause SEMANTIC_VIEW.

• Lorsque vous spécifiez une combinaison de ces clauses, notez ce qui suit :

  • Vous ne pouvez pas spécifier FACTS et METRICS dans la même clause SEMANTIC_VIEW.

  • Bien que vous puissiez spécifier à la fois FACTS et DIMENSIONS dans une requête, vous ne devez le faire que si les dimensions peuvent déterminer les faits de manière unique.

    La requête regroupe les résultats par dimensions. si les faits ne dépendent pas des dimensions, les résultats peuvent être non déterministes.

  • Si vous spécifiez à la fois FACTS et DIMENSIONS, tous les faits et dimensions utilisés dans la requête (y compris ceux spécifiés dans la clause WHERE) doivent être définis dans la même table logique.

  • Si vous spécifiez une dimension et une métrique, la table logique de la dimension doit être liée à la table logique de la métrique.

    En outre, la table logique de la dimension doit avoir un niveau de granularité égal ou inférieur à celui de la table logique de la métrique.

    Pour déterminer quelles dimensions répondent à ces critères, vous pouvez exécuter la commande SHOW SEMANTIC DIMENSIONS FOR METRIC.

    Pour plus de détails, voir Choix des dimensions que vous pouvez renvoyer pour une métrique donnée.

• Dans la clause DIMENSIONS, vous pouvez spécifier une expression qui fait référence à un fait. De même, dans la clause FACTS, vous pouvez spécifier une expression qui fait référence à une dimension. Par exemple :

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

  Copy

  Une des principales différences entre l’utilisation de DIMENSIONS et FACTS est que la requête regroupe les résultats selon les dimensions et les expressions spécifiées dans la clause DIMENSIONS.

• In the METRICS clause, you can specify an expression that includes:

  • Une expression scalaire faisant référence à des métriques.

  • Une agrégation de dimensions ou de faits.

• Spécifiez les clauses METRICS, DIMENSIONS et FACTS dans l’ordre dans lequel vous voulez qu’elles apparaissent dans les résultats.

  Si vous souhaitez que les dimensions apparaissent en premier dans les résultats, indiquez DIMENSIONS avant METRICS. Sinon, indiquez d’abord METRICS.

  Par exemple, supposons que vous indiquiez la clause METRICS en premier :

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

  Copy

  Dans la sortie, la première colonne est celle de la métrique (customer_order_count) et la deuxième colonne est celle de la dimension (customer_name) :

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

  Inversement, si vous spécifiez d’abord la clause DIMENSIONS :

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

  Copy

  Dans la sortie, la première colonne est celle de la dimension (customer_name) et la deuxième colonne est celle de la métrique (customer_order_count) :

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

• Vous pouvez utiliser la relation définie par une clause SEMANTIC_VIEW dans d’autres constructions SQL, notamment JOIN, PIVOT, UNPIVOT, GROUP BY et les expressions de table communes (CTE).

• Les en-têtes des colonnes de sortie utilisent les noms non qualifiés des métriques et des dimensions.

  Si plusieurs métriques et dimensions portent le même nom, utilisez un alias de table pour attribuer des noms différents aux en-têtes de colonne. Voir Gestion des noms de colonnes en double dans la sortie.

Pour renvoyer toutes les métriques ou dimensions dans une table logique donnée, utilisez un astérisque comme caractère générique, qualifié par le nom de la table logique. Par exemple, pour renvoyer toutes les métriques et dimensions définies dans la table logique customer :

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 |
...

Examples of specifying the SEMANTIC_VIEW clause¶

Les exemples suivants utilisent la vue tpch_analysis définie dans Exemple d’utilisation de SQL pour créer une vue sémantique :

• Récupération d’une métrique

• Grouper des données métriques par dimension

• Utiliser la sous-clause SEMANTIC_VIEW avec d’autres constructions

• Spécifier des expressions scalaires utilisant des dimensions

• Spécifier la clause WHERE

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'
  );

Exigences relatives aux dimensions et aux métriques dans une instruction SELECT¶

Dans l’instruction SELECT, vous ne pouvez faire référence qu’à des dimensions et à des métriques qui ont des noms distincts et qui ne se distinguent pas par leur nom de table logique. Par exemple, supposons qu’une vue sémantique possède deux dimensions ayant le nom non qualifié name :

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

Dans l’instruction SELECT, lorsque vous spécifiez le nom qualifié d’une dimension ou d’une métrique, le qualificatif est interprété comme le nom de la vue sémantique, et non comme le nom d’une table logique :

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'

Selecting metrics¶

Si vous souhaitez sélectionner une métrique définie dans une vue sémantique, vous devez transmettre cette métrique à l’une des fonctions suivantes :

• MIN

• MAX

• ANY_VALUE

• AGG (une fonction d’agrégation spéciale pour les métriques dans les vues sémantiques)

For example:

SELECT AGG(order_average_value) FROM tpch_analysis;

In the SELECT list, you can specify an expression that uses a metric. For example:

SELECT AGG(order_average_value) * 10 FROM tpch_analysis;

Vous pouvez également définir et sélectionner des métriques ad hoc en transmettant une dimension ou un fait à n’importe quelle fonction d’agrégation. Par exemple :

SELECT COUNT(customer_market_segment) FROM tpch_analysis;

Selecting dimensions¶

Si la liste SELECT comprend des dimensions, vous devez spécifier ces dimensions dans la clause GROUP BY. Par exemple :

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

Dans la liste SELECT et dans la clause GROUP BY, vous pouvez spécifier une dimension ou une expression scalaire qui utilise une dimension ou un fait. Par exemple :

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

Spécifier la clause WHERE¶

Dans la clause WHERE, vous ne pouvez utiliser que des expressions conditionnelles faisant référence à des dimensions ou à des faits. Par exemple :

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

Les dimensions doivent être atteignables par chaque métrique utilisée dans la requête.

Specifying the HAVING clause¶

Dans la clause HAVING, vous ne pouvez spécifier que des métriques, et vous devez les transmettre à l’une des fonctions d’agrégation répertoriées dans Selecting metrics. Par exemple :

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

Limites de la spécification du nom de la vue sémantique dans la clause FROM¶

Vous ne pouvez pas spécifier ce qui suit dans l’instruction SELECT :

• Extensions de la clause FROM, dont :

  • PIVOT

  • UNPIVOT

  • MATCH_RECOGNIZE

  • LATERAL

• Jointures

• Window function calls

• QUALIFY

• Sous-requêtes

Définition des métriques de la fonction de fenêtre¶

Lorsque vous spécifiez un appel de fonction de fenêtre, utilisez cette syntaxe, qui est décrit dans Paramètres pour les métriques de la fonction de fenêtre.

L’exemple suivant crée une vue sémantique qui inclut les définitions de plusieurs métriques de fonction de fenêtre. L’exemple utilise des tables de la base de données d’exemple TPC-DS. Pour obtenir des informations sur l’accès à cette base de données, voir Add the TPC-DS data set to your account.

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')

  );

Vous pouvez également utiliser d’autres métriques de la même table logique dans la définition de métrique. Par exemple :

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

Interrogation des métriques de la fonction de fenêtre¶

Lorsque vous interrogez une vue sémantique et que la requête renvoie une métrique de fonction de fenêtre, vous devez également renvoyer les dimensions spécifiées dans PARTITION BY dimension, PARTITION BY EXCLUDING dimension et ORDER BY dimension dans l’instruction CREATE SEMANTIC VIEW pour la vue sémantique.

Par exemple, supposons que vous spécifiez les dimensions date.date et date.year des clauses PARTITION BY EXCLUDING et ORDER BY dans la définition de la métrique store_sales.avg_7_days_sales_quantity :

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'),
    ...
  );

Si vous renvoyez la métrique store_sales.avg_7_days_sales_quantity dans une requête, vous devez également renvoyer les dimensions date.date et date.year :

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

Si vous omettez les dimensions date.date et date.year, une erreur se produit.

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

Pour déterminer les dimensions que vous devez spécifier dans la requête, exécutez la commande SHOW SEMANTIC DIMENSIONS FOR METRIC. Par exemple, pour déterminer les dimensions que vous devez spécifier lors de la récupération de la métrique``store_sales.avg_7_days_sales_quantity``, exécutez cette commande :

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

Dans la sortie de la commande, la colonne required contient true pour les dimensions que vous devez spécifier dans la requête.

+------------+-----------+--------------+----------+----------+---------+
| 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    |
+------------+-----------+--------------+----------+----------+---------+

Les exemples supplémentaires suivants interrogent les métriques de la fonction de fenêtre définies dans Définition des métriques de la fonction de fenêtre. Notez que la clause DIMENSIONS inclut les dimensions spécifiées dans les clauses PARTITION BY EXCLUDING et ORDER BY des définitions de métriques.

L’exemple suivant renvoie la quantité des ventes il y a 30 jours :

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

L’exemple suivant renvoie la moyenne sur 7 jours de la quantité totale des ventes il y a 30 jours :

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