分配用于报表总计的聚合规则
本主题介绍如何明确分配用于报表总计的聚合规则（这有助于提高性能），专门面向使用 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
工作簿中具有明确设置聚合的相同选项。请参见设置可视化的数值格式。在此示例中，对于 Count Distinct Customers with Orders 列，将聚合方法更改为总和。
检查报表以查看最佳聚合规则在用于报表。在报表设计允许的情况下使用明确的聚合规则。
有关报表的更多信息，请参见创建分析。