セマンティックビューのクエリ¶
セマンティックビューのクエリに必要な権限¶
セマンティック・ビューを所有していないロールを使用している場合、そのセマンティック・ビューをクエリするには、そのセマンティック・ビューに SELECT 権限を付与する必要があります。
注釈
セマンティックビューをクエリには、SELECT セマンティックビューで使用されるテーブルに対するSELECT 権限は必要ありません。 セマンティックビュー自体に対する SELECT 権限のみが必要です。
この動作は 標準クエリに必要な権限 と一致しています。
セマンティック・ビューの権限付与に関する情報は、 セマンティックビューの権限付与 を参照してください。
セマンティックビューのクエリ¶
セマンティックビューをクエリには、SEMANTIC_VIEW 句 をFROM句内で使用します。次の例では、tpch_analysis``セマンティックビューから ``customer_market_segment ディメンションと order_average_value メトリックを選択します。このビューは、:doc:`以前に定義した </user-guide/views-semantic/example>`です:
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 |
+-------------------------+---------------------+
次の例では、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 |
+--------------------+------------------------+
次の点に注意してください。
内 SEMANTIC_VIEW 句は、次の句の少なくとも1つを指定する必要があります。
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 を使用した場合の主な違いの1つは、クエリが 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;
出力では、最初の列がメートル法の列（
customer_order_count）で、2番目の列がディメンションの列（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）、2番目の列がメトリクスの列（customer_order_count）です：+--------------------+----------------------+ | CUSTOMER_NAME | CUSTOMER_ORDER_COUNT | |--------------------+----------------------| | Customer#000000001 | 6 | | Customer#000000002 | 7 | | Customer#000000003 | 0 | | Customer#000000004 | 20 | | Customer#000000005 | 4 | +--------------------+----------------------+
JOIN, PIVOT, UNPIVOT, GROUP BY, 共通表式 (CTEs) などの他の SQL 構文で、 SEMANTIC_VIEW 句で定義された関係を使用することができます。
出力列ヘッダーには、メトリクスとディメンションの修飾されていない名前が使用されます。
同じ名前の複数のメトリクスやディメンションがある場合は、テーブル・エイリアスを使用して列ヘッダーに異なる名前を割り当てます。出力中の重複列名の処理 をご参照ください。
指定された論理テーブルのすべてのメトリックまたはディメンションを返すには、論理テーブルの名前で修飾されたワイルドカードとしてアスタリスクを使用します。たとえば、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 |
...
指定したメトリックに対して返すことができるディメンションの選択¶
返すディメンションとメトリックを指定する場合、ディメンションのベーステーブルは、メトリックのベーステーブルに関連している必要があります。さらに、ディメンションのベーステーブルは、メトリックのベーステーブルと同じレベルの粒度にする必要があります。
たとえば、tpch_analysis で作成したセマンティックビュー SQL を使用してセマンティック表示を作成する例 をクエリとします。customer.customer_count ディメンションと orders.order_date メトリックを返すとします：
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 |
+------------+-------------------------+-------------+----------+----------+---------+
基本的な例¶
以下の例では、 SQL を使用してセマンティック表示を作成する例 で定義された tpch_analysis ビューを使用しています：
メトリックの取得¶
以下のステートメントは、メトリクスをクエリして、顧客の総カウントを取得します。
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
METRICS customer.customer_count
);
+----------------+
| CUSTOMER_COUNT |
+----------------+
| 15000 |
+----------------+
ディメンションによるメトリクスデータのグループ化¶
次のステートメントは、メトリクスデータ (order_average_value) をディメンション (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)
);
+--------------------------------------+
| DATE_PART('YEAR', ORDERS.ORDER_DATE) |
|--------------------------------------|
| 1992 |
| 1997 |
| 1998 |
| 1993 |
| 1996 |
| 1994 |
| 1995 |
+--------------------------------------+
WHERE 句の指定¶
以下の例では、 DIMENSIONS 句のディメンションを参照する WHERE 句を指定しています。
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 句でのファクトの指定¶
次の例では、region.r_name の条件内のファクト WHERE 句を使用します：
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
FACTS customer.c_customer_order_count
WHERE orders.order_date < '2021-01-01' AND region.r_name = 'AMERICA'
);
出力中の重複列名の処理¶
出力列は、メトリクスとディメンションの修飾されていない名前を使用します。同じ名前を持つ複数のメトリクスやディメンションがある場合、複数の列が同じ名前を使用します。
これを回避するには、テーブルエイリアスを使用して列に異なる名前を割り当てます。
例えば、次のようなセマンティック・ビューを定義し、ディメンション 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
);
このビューにクエリを実行し、これら 2 つのディメンションを選択すると、 name という 2 つの列が出力されます (修飾子はありません)。
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 ウィンドウ関数は有効なメトリック式（:code:`SUM(table_1.column_1)`を引数として受け取ります。
次のメトリックは not ウィンドウ関数のメトリックです。
METRICS ( table_1.metric_1 AS SUM( SUM(table_1.column_1) OVER( ... ) ) )
この例では、SUM ウィンドウ関数は列（
table_1.column_1）を引数として取得し、そのウィンドウ関数の呼び出しの結果は別の SUM 集計関数呼び出しに渡されます。
次のセクションでは、ウィンドウ関数のメトリックを定義およびクエリ方法について説明します。
ウィンドウ関数のメトリックの定義¶
ウィンドウ関数の呼び出しを指定する場合は、 この構文 <label-create_semantic_view_window_function_syntax>`で説明されている :ref:`label-create_semantic_view_window_function を使用します。
次の例では、いくつかのウィンドウ関数メトリックの定義を含むセマンティックビューを作成します。この例では、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
)
注釈
ウィンドウ関数のメトリックは、行レベルの計算（ファクトとディメンション）や他のメトリックの定義では使用できません。
ウィンドウ関数のメトリックのクエリ¶
セマンティックビューをクエリし、クエリがウィンドウ関数メトリックを返す場合は、セマンティックビューの:doc:/sql-reference/sql/create-semantic-view ステートメントで指定された PARTITIONBY:samp:{dimension}, PARTITIONBYEXCLUDING:samp:{dimension}`および ORDERBY:samp:`{dimension} 内のディメンションも返す必要があります。
たとえば、store_sales.avg_7_days_sales_quantity メトリックの定義において、 PARTITIONBYEXCLUDING 句および ORDERBY 句で 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 句には、メトリック定義の PARTITIONBYEXCLUDING およびORDERBY 句で指定されたディメンションが含まれます。
次の例では、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
);