查询语义视图

To query a semantic view, you can use a standard SELECT statement. Within this statement, you can use one of the following approaches:

• Specify the SEMANTIC_VIEW clause in the FROM clause. For example:

  Copy codeExpand code block

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

  Show lessSee more

  Scroll to top

  For information, see Specifying the SEMANTIC_VIEW clause in the FROM clause.

• Specify the name of the semantic view in the FROM clause. For example:

  Copy codeExpand code block

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

  Show lessSee more

  Scroll to top

  For information, see Specifying the name of the semantic view in the FROM clause.

查询语义视图所需的权限

如果您使用的角色不拥有语义视图，则您必须获得授予该语义视图的 SELECT 权限才能查询该语义视图。

For information about granting privileges on semantic views, see Granting privileges on semantic views.

Specifying the SEMANTIC_VIEW clause in the FROM clause¶

To query a semantic view, you can specify the SEMANTIC_VIEW clause in the FROM clause.

The following example selects the customer_market_segment dimension and the order_average_value metric from the tpch_analysis semantic view, which you defined earlier:

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

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

Note that you can define an alias for a dimension or metric by specifying the alias after the dimension or metric name. You can also specify the optional keyword AS before the alias. The following example runs the same query but uses the aliases segment and average for the dimension and metric returned in the results.

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment AS segment
    METRICS orders.order_average_value average
  )
  ORDER BY segment;

+------------+-----------------+
| SEGMENT    |         AVERAGE |
|------------+-----------------|
| AUTOMOBILE | 142570.25947219 |
| BUILDING   | 142425.37987558 |
| FURNITURE  | 142563.63314267 |
| HOUSEHOLD  | 141659.94753445 |
| MACHINERY  | 142655.91550608 |
+------------+-----------------+

The following example selects the customer_name dimension and the c_customer_order_count fact from the tpch_analysis semantic view:

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

+--------------------+------------------------+
| CUSTOMER_NAME      | C_CUSTOMER_ORDER_COUNT |
|--------------------+------------------------|
| Customer#000000001 |                      9 |
| Customer#000000002 |                     11 |
| Customer#000000003 |                      0 |
| Customer#000000004 |                     20 |
| Customer#000000005 |                     10 |
+--------------------+------------------------+

Guidelines for specifying the SEMANTIC_VIEW clause¶

When specifying the SEMANTIC_VIEW clause, follow these guidelines:

To return all metrics or dimensions in a given logical table, use an asterisk as a wildcard, qualified by the name of the logical table. For example, to return all metrics and dimensions defined in the customer logical table:

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¶

The following examples use the tpch_analysis view defined in Example of using SQL to create a semantic view:

• Retrieving a metric
• Grouping metric data by a dimension
• Using the SEMANTIC_VIEW subclause with other constructs
• Specifying scalar expressions that use dimensions
• Specifying the WHERE clause
• Specifying facts in the WHERE clause

检索指标

以下语句通过查询指标来检索客户总数：

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

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

按维度对指标数据进行分组

The following statement groups metric data (order_average_value) by a dimension (customer_market_segment):

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

将 SEMANTIC_VIEW 分子句与其他结构一起使用¶

以下示例演示了如何在 SEMANTIC_VIEW 分子句中使用维度和指标，并结合其他 SQL 结构来筛选、排序和限制结果：

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

指定使用维度的标量表达式

以下示例使用标量表达式来引用 DIMENSIONS 分子句中的维度：

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

指定 WHERE 子句¶

以下示例指定 WHERE 子句来引用 DIMENSIONS 子句中的维度：

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

在 WHERE 子句中指定事实¶

The following example uses the region.r_name fact in a condition in the WHERE clause:

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

Specifying the name of the semantic view in the FROM clause¶

You can specify the name of the semantic view in the FROM clause of a SELECT statement, as you would when querying a standard SQL view:

SELECT [ DISTINCT ]
    {
      [<qualifiers>.]<dimension_or_fact>                          |
      <scalar_expression_over_dimension_or_fact>                  |
      AGG( [<qualifiers>.]<metric> )                              |
      <aggregate_function>( [<qualifiers>.]<dimension_for_fact> )
    }
    [ , ... ]
  FROM <semantic_view> [ AS <alias> ]
  [ WHERE <expr_using_dimensions_or_facts> ]
  [ GROUP BY <expr_using_dimensions_or_facts> [ , ... ] ]
  [ HAVING <expr_using_metrics> ]
  [ ORDER BY ... ]
  [ LIMIT ... ]

Internally, this statement is rewritten as a SELECT statement that uses the SEMANTIC_VIEW clause:

• The expressions that you specify in the GROUP BY clause are rewritten into the DIMENSIONS clause in the SEMANTIC_VIEW clause.

  In the SELECT statement, if you use an expression that is not in the GROUP BY clause (for example, a dimension expression in the SELECT list), the rewrite uses that expression in the FACTS clause in the SEMANTIC_VIEW clause.

• When you refer to a metric that is defined in a semantic view, you must pass the metric to the AGG function.

• You can select ad-hoc metrics by passing a dimension or fact to any aggregate function.

• Any other calculated values that don’t fall into the first two categories are considered to be fact references.

The next sections explain these requirements in more detail:

• Requirements for dimensions and metrics in a SELECT statement
• Selecting metrics
• Selecting dimensions
• Specifying the WHERE clause
• Specifying the HAVING clause
• Limitations with specifying the semantic view name in the FROM clause

Requirements for dimensions and metrics in a SELECT statement¶

In the SELECT statement, you can only refer to dimensions and metrics that have distinct names and that are not distinguished by their logical table name. For example, suppose that a semantic view has two dimensions that have the unqualified name name:

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

In the SELECT statement, when you specify the qualified name of a dimension or metric, the qualifier is interpreted as the name of the semantic view, not the name of a logical table:

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¶

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.

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;

You can also define and select ad-hoc metrics by passing a dimension or fact to any aggregate function. For example:

SELECT COUNT(customer_market_segment) FROM tpch_analysis;

Selecting dimensions¶

If the SELECT list includes dimensions, you must specify those dimensions in the GROUP BY clause. For example:

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

In the SELECT list and in the GROUP BY clause, you can specify a dimension or a scalar expression that uses a dimension or a fact. For example:

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

指定 WHERE 子句¶

In the WHERE clause, you can only use conditional expressions that refer to dimensions or facts. For example:

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

The dimensions must be reachable by every metric used in the query.

Specifying the HAVING clause¶

In the HAVING clause, you can only specify metrics, and you must pass them to one of the aggregate functions listed in Selecting metrics. For example:

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

Limitations with specifying the semantic view name in the FROM clause¶

You cannot specify the following in the SELECT statement:

• Extensions of the FROM clause, including:

  • PIVOT
  • UNPIVOT
  • MATCH_RECOGNIZE
  • LATERAL

• Joins

• Window function calls

• QUALIFY

• Subqueries

为给定指标选择可以返回的维度

当您指定要返回的维度和指标时，该维度的基表必须与该指标的基表相关。此外，维度的基表粒度必须等于或低于指标的基表。

For example, suppose that you query the tpch_analysis semantic view that you created in Example of using SQL to create a semantic view, and you want to return the orders.order_date dimension and the customer.customer_order_count metric:

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

This query fails because the orders table for the order_date dimension has a higher level of granularity than the customer table for the customer_order_count metric:

010234 (42601): SQL compilation error:
Invalid dimension specified: The dimension entity 'ORDERS' must be related to and
  have an equal or lower level of granularity compared to the base metric or dimension entity 'CUSTOMER'.

To list the dimensions that you can return with a specific metric, run the SHOW SEMANTIC DIMENSIONS FOR METRIC command. For example:

SHOW SEMANTIC DIMENSIONS IN tpch_analysis FOR METRIC customer_order_count;

+------------+-------------------------+-------------+----------+----------+---------+
| table_name | name                    | data_type   | required | synonyms | comment |
|------------+-------------------------+-------------+----------+----------+---------|
| CUSTOMER   | CUSTOMER_COUNTRY_CODE   | VARCHAR(15) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_MARKET_SEGMENT | VARCHAR(10) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_NAME           | VARCHAR(25) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_NATION_NAME    | VARCHAR(25) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_REGION_NAME    | VARCHAR(25) | false    | NULL     | NULL    |
| NATION     | NATION_NAME             | VARCHAR(25) | false    | NULL     | NULL    |
+------------+-------------------------+-------------+----------+----------+---------+

处理输出中重复的列名称

输出列使用指标和维度的非限定名称。如果您有多个具有相同名称的指标和维度，则多个列将使用相同的名称。

要解决此问题，请使用表别名为列分配不同的名称。

For example, suppose that you define the following semantic view, which defines the dimensions nation.name and region.name:

CREATE OR REPLACE SEMANTIC VIEW duplicate_names

  TABLES (
    nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
    region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey)
  )

  RELATIONSHIPS (
    nation (n_regionkey) REFERENCES region
  )

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

If you query this view and select these two dimensions, the output includes two columns named name without any qualifiers:

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  );

+----------------+-------------+
| NAME           | NAME        |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

To disambiguate the columns, use a table alias to assign different column names (for example, nation_name and region_name):

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  ) AS table_alias(nation_name, region_name);

+----------------+-------------+
| NATION_NAME    | REGION_NAME |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

定义和查询窗口函数指标

You can define metrics that call window functions and pass in aggregated values. These metrics are called window function metrics.

以下示例说明了窗口函数指标与将行级表达式传递给窗口函数的指标之间的区别：

• 以下指标是一个窗口函数指标：

  Copy codeExpand code block

  METRICS (
    table_1.metric_1 AS SUM(table_1.metric_3) OVER( ... )
  )

  Show lessSee more

  Scroll to top

  In this example, the SUM window function takes another metric (table_1.metric_3) as an argument.

以下指标也是一个窗口函数指标：

METRICS (
  table_1.metric_2 AS SUM(
 SUM(table_1.column_1)
  ) OVER( ... )
)

In this example, the SUM window function takes a valid metric expression (SUM(table_1.column_1)) as an argument.

• The following metric is not a window function metric:

  Copy codeExpand code block

  METRICS (
    table_1.metric_1 AS SUM(
   SUM(table_1.column_1) OVER( ... )
    )
  )

  Show lessSee more

  Scroll to top

  In this example, the SUM window function takes a column (table_1.column_1) as an argument, and the result of that window function call is passed to a separate SUM aggregate function call.

以下部分说明如何定义和查询窗口函数指标：

• Defining window function metrics
• Querying window function metrics

定义窗口函数指标

When specifying a window function call, use this syntax, which is described in Parameters for window function metrics.

The following example creates a semantic view that includes the definitions of several window function metrics. The example uses tables from the TPC-DS sample database. For information on accessing this database, see 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')

  );

您还可以在指标定义中使用同一逻辑表中的其他指标。例如：

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

查询窗口函数指标

When you query a semantic view and the query returns a window function metric, you must also return the dimensions specified in PARTITION BY dimension, PARTITION BY EXCLUDING dimension, and ORDER BY dimension in the CREATE SEMANTIC VIEW statement for the semantic view.

For example, suppose that you specify the date.date and date.year dimensions in the PARTITION BY EXCLUDING and ORDER BY clauses in the definition of the store_sales.avg_7_days_sales_quantity metric:

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

If you return the store_sales.avg_7_days_sales_quantity metric in a query, you must also return the date.date and date.year dimensions:

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

If you omit the date.date and date.year dimensions, an error occurs.

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

To determine which dimensions you must specify in the query, execute the SHOW SEMANTIC DIMENSIONS FOR METRIC command. For example, to determine the dimensions that you must specify when retrieving the store_sales.avg_7_days_sales_quantity metric, run this command:

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

In the output of the command, the required column contains true for the dimensions that you must specify in the query.

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

The following additional examples query the window function metrics defined in 定义窗口函数指标. Note that the DIMENSIONS clause includes the dimensions specified in the PARTITION BY EXCLUDING and ORDER BY clauses of the metric definitions.

以下示例会返回 30 天前的销售数量：

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

以下示例会返回 30 天前总销售数量的 7 天滚动平均值：

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