跳到主要內容 跳至補充內容

Direct Query 的自訂 SQL 範例

下列範例顯示對 Direct Query 使用自訂 SQL 時的一些選項。

範例:將自訂 SQL 定義新增至 Direct Query 模型

在此範例中,表格 CRN 的自訂 SQL 定義已新增至 Direct Query 應用程式中的資料模型以及兩個表格之間關係。Coalesce 函數用來檢查 N_NATIONKEY 的欄位值是等於 C_NATIONKEY 的值,或是兩者皆等於 Null。

提示備註

建議以 Coalesce 方式納入檢查,以查看欄位值是否等於 Null。如需詳細資訊,請參閱coalesce - 指令碼與圖表函數

LIB CONNECT TO 'CustomSQL:Snowflake_example.com'; Section DirectQuery; C: Select C_NATIONKEY, Sum(C_ACCTBAL) as C_SUM from tpch1.customer group by C_NATIONKEY; R: SELECT R_REGIONKEY, R_NAME from tpch1.region; N: SELECT N_REGIONKEY, N_NAME, N_NATIONKEY from tpch1.nation; Create Relationship Between R, N Matching R_REGIONKEY With N_REGIONKEY; Create Relationship Between Outer Joined N, Inner Joined C On (coalesce([N_NATIONKEY], -1) = coalesce([C_NATIONKEY], -1));

範例: 將具有自訂 SQL 的表格新增至以 資料模型管理員 製作的資料模型

在此範例中,先以 DirectQueryModel.main 物件中的內容建構 Direct Query 資料模型,這透過 資料模型管理員 建立。然後其他表格,即具有欄位 C_CUSTKEYC_ACCTBALC,會新增至資料模型。會在 ORDERS 中的欄位 O_CUSTKEYC 中的欄位 C_CUSTKEY 之間產生關係。

LIB CONNECT TO 'CustomSQL:Snowflake_example.com'; // DMM created model portion includes Orders table. IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main'; Section DirectQuery; C: Select C_CUSTKEY, C_ACCTBAL from tpch1.customer; Create Relationship Between ORDERS, C on (O_CUSTKEY = C_CUSTKEY);

範例: 透過自訂 SQL 表格建構 Direct Query 資料模型

在此範例中,透過自訂 SQL 表格 C 建構 Direct Query 資料模型,這具有重新命名的欄位 C_KEYC_BAL。接下來,IMPORT LIVE 會從 DirectQueryModel.main 新增在 資料模型管理員 中建立的資料模型。此資料模型包含表格「訂單」,並在該模型中有 OrdersCustomer 之間的現有關係。然後,會捨棄 CustomersOrders 之間的現有關係,並定義 OrdersC 之間的新關係。

LIB CONNECT TO 'CustomSQL:Snowflake_example.com'; Section DirectQuery; C: Select C_CUSTKEY as C_KEY, C_ACCTBAL as C_BAL from tpch1.customer; // DMM created model portion includes Orders table. IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main'; // This relationship was defined using DMM between ORDERS and CUSTOMER, but I want to define my own. Drop Relationship Between ORDERS, CUSTOMER; Create Relationship Between ORDERS, C on (O_CUSTKEY = C_KEY);

範例:建構包括變數的資料模型

在此範例中,首先透過值 100 定義變數 MULT。然後,這會乘以 PS_Supplycost 的總和,以計算欄位 S

LIB CONNECT TO 'CustomSQL:Snowflake_example.com'; Section DirectQuery; LET MULT=100; T1: SELECT PS_AVAILQTY AS C, SUM(PS_SUPPLYCOST) * $(MULT) AS S FROM "TPCH.01"."PARTSUPP" GROUP BY C;

範例:透過 Qlik 運算式建構資料模型

在此範例中,兩個變數和運算式會用來建置資料模型。在表格 ORDERS_AGGREGATIONS 中,運算式用來定義 ORDERS_GROUP_TOTAL_PRICE 中的值。若在會影響 ORDERS_GROUP_TOTAL_PRICE 值的工作表中進行選取,將會對資料庫查詢傳回的資料重新評估運算式。

IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main'; section DirectQuery; let Aggr1 = 1; let Aggr2 = 0; [GROUPS]: SELECT 0 as GROUP_CODE, 'NONE' as "GROUP" UNION ALL SELECT 1 as GROUP_CODE, 'ORDERSTATUS' as "GROUP" UNION ALL SELECT 2 as GROUP_CODE, 'ORDERPRIORITY' as "GROUP" ; [ORDERS_AGGREGATIONS]: SELECT 'BY ORDERSTATUS' as "GROUP_NAME", "O_ORDERSTATUS" as "ORDERS_GROUP", sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE" FROM "TEST1"."TPCH.01"."ORDERS" WHERE '$(=min({<GROUP_CODE={0,1}>}[GROUP_CODE]))' = '1' GROUP BY 1,2 UNION ALL SELECT 'BY ORDERPRIORITY' as "GROUP_NAME", "O_ORDERPRIORITY" as "ORDERS_GROUP", sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE" FROM "TEST1"."TPCH.01"."ORDERS" WHERE '$(=min({<GROUP_CODE={0,2}>}[GROUP_CODE]))' = '2' GROUP BY 1,2; ; [ORDERS_AGGREGATIONS_VARS]: SELECT 'BY ORDERSTATUS' as "GROUP_NAME_V", "O_ORDERSTATUS" as "ORDERS_GROUP_V", sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V" FROM "TEST1"."TPCH.01"."ORDERS" WHERE $(=Aggr1) = 1 GROUP BY 1,2 UNION ALL SELECT 'BY ORDERPRIORITY' as "GROUP_NAME_V", "O_ORDERPRIORITY" as "ORDERS_GROUP_V", sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V" FROM "TEST1"."TPCH.01"."ORDERS" WHERE $(=Aggr2) = 1 GROUP BY 1,2; ;

此頁面是否對您有幫助?

若您發現此頁面或其內容有任何問題——錯字、遺漏步驟或技術錯誤——請告知我們可以如何改善!