Consulta de exibições semânticas¶

Guidelines for specifying the SEMANTIC_VIEW clause¶

When specifying the SEMANTIC_VIEW clause, follow these guidelines:

• Na cláusula SEMANTIC_VIEW, você deve especificar pelo menos uma das seguintes cláusulas:

  • METRICS

  • DIMENSIONS

  • FACTS

  Não é possível omitir todas essas cláusulas da cláusula SEMANTIC_VIEW.

• Ao especificar uma combinação dessas cláusulas, observe o seguinte:

  • Não é possível especificar FACTS e METRICS na mesma cláusula SEMANTIC_VIEW.

  • Embora você possa especificar FACTS e DIMENSIONS em uma consulta, você deve fazê-lo somente se as dimensões puderem determinar os fatos de forma única.

    A consulta agrupa os resultados por dimensões. Se os fatos não dependem das dimensões, os resultados podem ser não determinísticos.

  • Se você especificar FACTS e DIMENSIONS, todos os fatos e as dimensões usados na consulta (incluindo aqueles especificados na cláusula WHERE) deverão ser definidos na mesma tabela lógica.

  • Se você especificar uma dimensão e uma métrica, a tabela lógica para a dimensão deverá estar relacionada à tabela lógica para a métrica.

    Além disso, a tabela lógica para a dimensão deve ter um nível igual ou inferior de granularidade do que a tabela lógica para a métrica.

    Para determinar quais dimensões atendem a esses critérios, você pode executar o comando SHOW SEMANTIC DIMENSIONS FOR METRIC.

    Para obter mais detalhes, consulte Escolha das dimensões que você pode retornar para uma determinada métrica.

• Na cláusula DIMENSIONS, você pode especificar uma expressão que se refere a um fato. Da mesma forma, na cláusula FACTS, você pode especificar uma expressão que se refere a uma dimensão. Por exemplo:

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

  Uma das principais diferenças entre o uso de DIMENSIONS e FACTS é que a consulta agrupa os resultados pelas dimensões e expressões especificadas na cláusula DIMENSIONS.

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

  • Uma expressão escalar que se refere a métricas;

  • Uma agregação de dimensões ou fatos.

• Especifique as cláusulas METRICS, DIMENSIONS e FACTS na ordem em que você deseja que apareçam nos resultados.

  Se você quiser que as dimensões apareçam primeiro nos resultados, especifique DIMENSIONS antes de METRICS. Caso contrário, especifique primeiro METRICS.

  Por exemplo, suponha que você especifique primeiro a cláusula METRICS:

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

  Copy

  Na saída, a primeira coluna é a coluna de métrica (customer_order_count) e a segunda coluna é a coluna de dimensão (customer_name):

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

  Se, em vez disso, você especificar primeiro a cláusula DIMENSIONS:

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

  Copy

  Na saída, a primeira coluna é a coluna de dimensão (customer_name) e a segunda coluna é a coluna de métrica (customer_order_count):

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

• Você pode usar a relação definida por uma cláusula SEMANTIC_VIEW em outras construções SQL, incluindo JOIN, PIVOT, UNPIVOT, GROUP BY e expressões de tabela comuns (CTEs).

• Os cabeçalhos das colunas de saída usam os nomes não qualificados das métricas e dimensões.

  Se você tiver várias métricas e dimensões com os mesmos nomes, use um alias de tabela para atribuir nomes diferentes aos cabeçalhos das colunas. Consulte Tratamento de nomes de colunas duplicados na saída.

Para retornar todas as métricas ou dimensões em uma determinada tabela lógica, use um asterisco como curinga, qualificado pelo nome da tabela lógica. Por exemplo, para retornar todas as métricas e dimensões definidas na tabela lógica 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¶

Os exemplos a seguir usam a visualização tpch_analysis definida em Exemplo de uso do SQL para criar uma exibição semântica:

• Recuperação de métrica

• Agrupamento de dados métricos por uma dimensão

• Usando a subcláusula SEMANTIC_VIEW com outras construções

• Especificar expressões escalares que usam dimensões

• Especificar a cláusula 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'
  );

Requisitos para dimensões e métricas em uma instrução SELECT¶

Na instrução SELECT, você só pode se referir a dimensões e métricas que tenham nomes distintos e que não sejam distinguidas pelo próprio nome da tabela lógica. Por exemplo, suponha que uma exibição semântica tenha duas dimensões com o nome não qualificado name:

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

Na instrução SELECT, quando você especifica o nome qualificado de uma dimensão ou métrica, o qualificador é interpretado como o nome da exibição semântica, não o nome de uma tabela lógica:

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¶

Se quiser selecionar uma métrica definida em uma exibição semântica, é necessário passar a métrica para uma das seguintes funções:

• MIN

• MAX

• ANY_VALUE

• AGG (uma função de agregação especial para métricas em exibições semânticas)

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;

Você também pode definir e selecionar métricas especiais passando uma dimensão ou um fato para qualquer função de agregação. Por exemplo:

SELECT COUNT(customer_market_segment) FROM tpch_analysis;

Selecting dimensions¶

Se a lista SELECT inclui dimensões, você deve especificá-las na cláusula GROUP BY. Por exemplo:

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

Na lista SELECT e na cláusula GROUP BY, você pode especificar uma dimensão ou uma expressão escalar que usa uma dimensão ou um fato. Por exemplo:

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

Especificar a cláusula WHERE¶

Na cláusula WHERE, você só pode usar expressões condicionais que se refiram a dimensões ou fatos. Por exemplo:

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

As dimensões devem ser acessíveis por cada métrica usada na consulta.

Specifying the HAVING clause¶

Na cláusula HAVING, você só pode especificar métricas e deve passá-las para uma das funções de agregação listadas em Selecting metrics. Por exemplo:

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

Limitações com a especificação do nome da exibição semântica na cláusula FROM¶

Não é possível especificar o seguinte na instrução SELECT:

• Extensões da cláusula FROM, incluindo:

  • PIVOT

  • UNPIVOT

  • MATCH_RECOGNIZE

  • LATERAL

• Junções

• Window function calls

• QUALIFY

• Subconsultas

Definição de métricas de função de janela¶

Ao especificar uma chamada de função de janela, use esta sintaxe, que está descrita em Parâmetros para métricas de função de janela.

O exemplo a seguir cria uma exibição semântica que inclui as definições de várias métricas de função de janela. O exemplo usa tabelas do banco de dados de amostra TPC-DS. Para obter informações sobre como acessar esse banco de dados, consulte 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')

  );

Você também pode usar outras métricas da mesma tabela lógica na definição da métrica. Por exemplo:

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

Consulta de métricas de função de janela¶

Quando você consulta uma exibição semântica e a consulta retorna uma métrica de função de janela, você também deve retornar as dimensões especificadas em PARTITION BY dimension, PARTITION BY EXCLUDING dimension e ORDER BY dimension na instrução CREATE SEMANTIC VIEW para a exibição semântica.

Por exemplo, suponha que você especifique as dimensões date.date e date.year nas cláusulas PARTITION BY EXCLUDING e ORDER BY na definição da métrica 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'),
    ...
  );

Se você retornar a métrica store_sales.avg_7_days_sales_quantity em uma consulta, também deverá retornar as dimensões date.date e date.year:

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

Se você omitir as dimensões date.date e date.year, ocorrerá um erro.

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

Para determinar quais dimensões você deve especificar na consulta, execute o comando SHOW SEMANTIC DIMENSIONS FOR METRIC. Por exemplo, para determinar as dimensões que você deve especificar ao recuperar a métrica store_sales.avg_7_days_sales_quantity, execute este comando:

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

Na saída do comando, a coluna required contém true para as dimensões que você deve especificar na consulta.

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

Os outros exemplos a seguir consultam as métricas de função de janela definidas em Definição de métricas de função de janela. Observe que a cláusula DIMENSIONS inclui as dimensões especificadas nas cláusulas PARTITION BY EXCLUDING e ORDER BY das definições das métricas.

O seguinte exemplo retorna a quantidade de vendas dos 30 dias anteriores:

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

O seguinte exemplo retorna a média por período consecutivo de 7 dias da quantidade total de vendas dos 30 dias anteriores:

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