此資訊描述明確指定報表總計聚總規則的方式,這有助於改善效能,且專為編寫 Oracle Analytics 報表的技術開發人員所設計。
在本範例中,報表定義為年度、月份以及客戶區域,且使用 'Count Distinct of Customers with Orders'
度量,其定義為計算訂單的相異客戶。
邏輯查詢如下:
SELECT 0 s_0, "A - Sample Sales"."Cust Regions"."C50 Region" s_1, "A - Sample Sales"."Counts"."32 # of Cust with Orders (Cnt Distinct)" s_2, REPORT_AGGREGATE("A - Sample Sales"."Counts"."32 # of Cust with Orders (Cnt Distinct)" BY ) s_3 FROM "A - Sample Sales" WHERE ("Time"."T02 Per Name Month" = '2011 / 11') ORDER BY 2 ASC NULLS LAST FETCH FIRST 500001 ROWS ONLY
產生的實體查詢如下:
WITH SAWITH0 AS (select count(distinct T42433.Cust_Key) as c1, T42430.Region as c2 from BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ , BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ , BISAMPLE.SAMP_TIME_MTH_D T42405 /* D02 Time Month Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42405.Mth_Key = T42433.Bill_Mth_Key and T42405.Per_Name_Month = '2011 / 11' and T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key ) group by T42430.Region), SAWITH1 AS (select count(distinct T42433.Cust_Key) as c1 from BISAMPLE.SAMP_TIME_MTH_D T42405 /* D02 Time Month Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42405.Per_Name_Month = '2011 / 11' and T42405.Mth_Key = T42433.Bill_Mth_Key ) ) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from (select 0 as c1, D1.c2 as c2, D1.c1 as c3, D2.c1 as c4, ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c5 from SAWITH0 D1, SAWITH1 D2 ) D1 where ( D1.c5 = 1 ) order by c2 ) D1 where rownum <= 500001
總計值 480 為成員值的總和 (182 + 113 + 185)。計算總計的邏輯查詢表示式為:“REPORT_AGGREGATE("A - Sample Sales"."Counts"."32 # of Cust with Orders (Cnt Distinct)" BY ) s_3”
。
使用 “REPORT_AGGREGATE”
時,總計將獨立於成員值以外計算。但對於此報表設計,我們認為正確的總計可以從成員計算而得。
編輯資料欄公式,將 “Aggregation Rule (Totals Row)”
從 “Server Complex Aggregate”
變更為 “Sum”
。這會變更邏輯和實體 SQL。
原始聚總:
修改後的聚總:
修改後的邏輯 SQL 現在包含 “REPORT_SUM”
。
SELECT 0 s_0, "A - Sample Sales"."Cust Regions"."C50 Region" s_1,"A - Sample Sales"."Counts"."32 # of Cust with Orders (Cnt Distinct)" s_2, REPORT_SUM("A - Sample Sales"."Counts"."32 # of Cust with Orders (Cnt Distinct)" BY ) s_3 FROM "A - Sample Sales" WHERE ("Time"."T02 Per Name Month" = '2011 / 11') ORDER BY 2 ASC NULLS LAST FETCH FIRST 500001 ROWS ONLY
產生的實體查詢如下:
WITH SAWITH0 AS (select count(distinct T42433.Cust_Key) as c1, T42430.Region as c2 from BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ , BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ , BISAMPLE.SAMP_TIME_MTH_D T42405 /* D02 Time Month Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42405.Mth_Key = T42433.Bill_Mth_Key and T42405.Per_Name_Month = '2011 / 11' and T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key ) group by T42430.Region), SAWITH1 AS (select 0 as c1, D1.c2 as c2, D1.c1 as c3 from SAWITH0 D1) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, sum(D1.c3) over () as c4 from SAWITH1 D1 order by c2 ) D1 where rownum <= 500001
工作簿中提供可明確設定聚總的相同選項:
請複查您的報表,確認報表使用最佳聚總規則。當報表設計允許時,請使用明確聚總規則。
如需報表的詳細資訊,請參閱建立分析。