查询语义视图¶

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:

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

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:
```
SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment
  ORDER BY customer_market_segment;
```
For information, see Specifying the name of the semantic view in the FROM clause.

查询语义视图所需的权限¶

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

备注

要查询语义视图，您不需要对语义视图中使用的表具有 SELECT 权限。您只需要对语义视图本身具有 SELECT 权限。

此行为与查询标准视图所需的权限一致。

有关授予语义视图权限的信息，请参阅授予语义视图的权限。

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

请注意，您可以通过在维度或指标名称后指定别名来定义维度或指标的别名。您还可以在别名前加上可选关键字 AS。以下示例执行了相同的查询，但在结果中对返回的维度和指标分别使用了别名 segment 和 average。

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

以下示例从 tpch_analysis 语义视图中选择 customer_name 维度和 c_customer_order_count 事实：

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:

在 SEMANTIC_VIEW 子句中，必须至少指定以下子句之一：
- METRICS
- DIMENSIONS
- FACTS
您不能在 SEMANTIC_VIEW 子句中省略所有这些子句。
在指定这些子句的组合时，请注意以下几点：
- 您不能在同一个 SEMANTIC_VIEW 子句中指定 FACTS 和 METRICS。
- 尽管可以在查询中同时指定 FACTS 和 DIMENSIONS，但只有当维度可以唯一地确定事实时才应这样做。
  
  查询按维度对结果进行分组。如果事实不依赖于维度，则结果可能是不确定的。
- 如果同时指定 FACTS 和 DIMENSIONS，则查询中使用的所有事实和维度（包括 WHERE 子句中指定的事实和维度）都必须在同一个逻辑表中定义。
- 如果您指定一个维度和一个指标，则该维度的逻辑表必须与该指标的逻辑表相关。
  
  此外，维度的逻辑表粒度必须等于或低于指标的逻辑表。
  
  要确定哪些维度符合此标准，可以运行 SHOW SEMANTIC DIMENSIONS FOR METRIC 命令。
  
  有关详细信息，请参阅为给定指标选择可以返回的维度。
在 DIMENSIONS 子句中，您可以指定一个引用事实的表达式。同样，在 FACTS 子句中，您可以指定一个引用维度的表达式。例如：
```
-- Dimension expression that refers to a fact
DIMENSIONS my_table.my_fact

-- Fact expression that refers to a dimension
FACTS my_table.my_dimension
```
使用 DIMENSIONS 和 FACTS 的主要区别之一是，查询按 DIMENSIONS 子句中指定的维度和表达式对结果进行分组。
In the METRICS clause, you can specify an expression that includes:
- A scalar expression referring to metrics.
- An aggregation of dimensions or facts.

按照您希望 METRICS、DIMENSIONS 和 FACTS 子句在结果中显示的顺序指定它们。

如果希望维度先在结果中显示，请在 METRICS 前面指定 DIMENSIONS。否则，请先指定 METRICS。

例如，假设您先指定 METRICS 子句：

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

在输出中，第一列是指标列 (customer_order_count)，第二列是维度列 (customer_name)：

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

如果您改为先指定 DIMENSIONS 子句：

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

在输出中，第一列是维度列 (customer_name)，第二列是指标列 (customer_order_count)：

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

您可以在其他 SQL 结构中使用由 SEMANTIC_VIEW 子句定义的关系，包括 JOIN、PIVOT、UNPIVOT、GROUP BY 和公用表表达式 (CTEs)。
输出列标题使用指标和维度的非限定名称。

如果您有多个具有相同名称的指标和维度，请使用表别名为列标题分配不同的名称。请参阅处理输出中重复的列名称。

要返回给定逻辑表中的所有指标或维度，可使用星号作为通配符，并加上逻辑表的名称限定。例如，要返回 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¶

以下示例使用使用 SQL 创建语义视图的示例中定义的 tpch_analysis 视图：

检索指标¶

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

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

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

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

以下语句按维度 (customer_market_segment) 对指标数据 (order_average_value) 进行分组：

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 子句中指定事实¶

以下示例在 WHERE 子句的条件中使用了``region.r_name`` 事实：

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¶

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;

备注

The AGG function has no effect on the metric because the function evaluates one value of the metric.

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 使用 SQL 创建语义视图的示例, 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
);

此查询失败，因为 order_date 维度对应的 orders 表的粒度高于 customer_order_count 指标对应的 customer 表：

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

要列出可以与特定指标一起返回的维度，请执行 SHOW SEMANTIC DIMENSIONS FOR METRIC 命令。例如：

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

处理输出中重复的列名称¶

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

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

例如，假设您定义了以下语义视图，该视图定义了维度 nation.name 和 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
  );

如果您查询此视图并选择这两个维度，则输出将包括名为 name 且不带任何限定符的两个列：

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

要消除列的歧义，请使用表别名来分配不同的列名（例如，nation_name 和 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      |
| ...            | ...         |
+----------------+-------------+

定义和查询窗口函数指标¶

您可以定义调用窗口函数并传入聚合值的指标。这些指标称为 窗口函数指标。

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

以下指标是一个窗口函数指标：
```
METRICS (
  table_1.metric_1 AS SUM(table_1.metric_3) OVER( ... )
)
```
在此示例中，SUM 窗口函数以另一个指标 (table_1.metric_3) 作为实参。

以下指标也是一个窗口函数指标：
```
METRICS (
  table_1.metric_2 AS SUM(
    SUM(table_1.column_1)
  ) OVER( ... )
)
```
在此示例中，SUM 窗口函数以有效的指标表达式 (SUM(table_1.column_1)) 作为实参。
以下指标不是窗口函数指标：
```
METRICS (
  table_1.metric_1 AS SUM(
    SUM(table_1.column_1) OVER( ... )
  )
)
```
在此示例中，SUM 窗口函数以列 (table_1.column_1) 作为实参，并将该窗口函数调用的结果传递给一个单独的 SUM 聚合函数调用。

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

定义窗口函数指标¶

在指定窗口函数调用时，请使用此语法，如窗口函数指标的参数中所述。

以下示例创建了一个语义视图，其中包含多个窗口函数指标的定义。该示例使用了 TPC-DS 示例数据库中的表。有关访问此数据库的信息，请参阅将 TPC-DS 数据集添加到您的账户。

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
)

备注

您不能在行级计算（数值和维度）或其他指标的定义中使用窗口函数指标。

查询窗口函数指标¶

当您查询语义视图且查询返回窗口函数指标时，必须同时返回在 CREATE SEMANTIC VIEW 语句中为该语义视图的 PARTITION BY dimension、PARTITION BY EXCLUDING dimension 和 ORDER BY dimension 指定的维度。

例如，假设您在 store_sales.avg_7_days_sales_quantity 指标的定义中，在 PARTITION BY EXCLUDING 和 ORDER BY 子句中指定了``date.date`` 和 date.year 维度：

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

如果您在查询中返回 store_sales.avg_7_days_sales_quantity 指标，则还必须返回 date.date 和``date.year`` 维度：

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

如果您省略 date.date 和 date.year 维度，则会出现错误。

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

要确定必须在查询中指定哪些维度，请执行 SHOW SEMANTIC DIMENSIONS FOR METRIC 命令。例如，要确定检索 store_sales.avg_7_days_sales_quantity 指标时必须指定的维度，请运行以下命令：

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

在命令输出中，对于必须在查询中指定的维度，required 列包含 true。

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

以下其他示例查询在定义窗口函数指标中定义的窗口函数指标。请注意，该 DIMENSIONS 条款包括在指标定义的 PARTITION BY EXCLUDING 和 ORDER BY 子句中指定的维度。

以下示例会返回 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
);