查询语义视图¶

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

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

备注

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

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

查询语义视图¶

要查询语义视图，请在 FROM 子句中使用 SEMANTIC_VIEW 子句。以下示例从您之前定义的 tpch_analysis 语义视图中选择 customer_market_segment 维度和 order_average_value 指标：

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

Copy

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

以下示例从 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;

Copy

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

请注意以下事项：

在 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
```
Copy
使用 DIMENSIONS 和 FACTS 的主要区别之一是，查询按 DIMENSIONS 子句中指定的维度和表达式对结果进行分组。

按照您希望 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;

Copy

在输出中，第一列是指标列 (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;

Copy

在输出中，第一列是维度列 (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.*
);

Copy

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

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

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

例如，假设你查询在使用 SQL 创建语义视图的示例中创建的 tpch_analysis 语义视图。假设您想返回 customer.customer_count 维度和 orders.order_date 指标：

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

Copy

此查询失败，因为 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;

Copy

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

基本示例¶

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

检索指标
按维度对指标数据进行分组
将 SEMANTIC_VIEW 分子句与其他结构一起使用
指定使用维度的标量表达式
指定 WHERE 子句

检索指标¶

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

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

Copy

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

Copy

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

Copy

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

Copy

+--------------------------------------+
| DATE_PART('YEAR', ORDERS.ORDER_DATE) |
|--------------------------------------|
|                                 1992 |
|                                 1997 |
|                                 1998 |
|                                 1993 |
|                                 1996 |
|                                 1994 |
|                                 1995 |
+--------------------------------------+

指定 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;

Copy

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

Copy

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

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

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

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

Copy

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

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

Copy

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

Copy

+----------------+-------------+
| 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( ... )
)
```
Copy
在此示例中，SUM 窗口函数以另一个指标 (table_1.metric_3) 作为实参。

以下指标也是一个窗口函数指标：
```
METRICS (
  table_1.metric_2 AS SUM(
    SUM(table_1.column_1)
  ) OVER( ... )
)
```
Copy
在此示例中，SUM 窗口函数以有效的指标表达式 (SUM(table_1.column_1)) 作为实参。
以下指标不是窗口函数指标：
```
METRICS (
  table_1.metric_1 AS SUM(
    SUM(table_1.column_1) OVER( ... )
  )
)
```
Copy
在此示例中，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')

  );

Copy

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

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

Copy

备注

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

查询窗口函数指标¶

当您查询语义视图且查询返回窗口函数指标时，必须同时返回在 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'),
    ...
  );

Copy

如果您在查询中返回 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
);

Copy

如果您省略 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;

Copy

在命令输出中，对于必须在查询中指定的维度，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
);

Copy

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

Copy