查询语义视图¶

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
  

  Copy

  使用 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;
  

  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.*
);

+-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------+
| 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 视图：

• 检索指标

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

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

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

• 指定 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'
  );

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 one of the following functions:

• MIN

• MAX

• ANY_VALUE

• AGG (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

定义窗口函数指标¶

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

以下示例创建了一个语义视图，其中包含多个窗口函数指标的定义。该示例使用了 TPC-DS 示例数据库中的表。有关访问此数据库的信息，请参阅 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
)

查询窗口函数指标¶

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