Oracle SQL:标准分析函数与建模条款

Question

我想 1） 通过第二个 std 偏差的下边界来识别我的数据集中的差距，2） 评估给定组中剩余值的百分比份额的平均值/中位数/stddev。

作为Oracle SQL的初学者/中级，我想从专家那里获得一些关于实现所述目标的两种竞争查询方法的想法：

使用甲骨文的"著名"模型条款：

select intv, avg, stddev, med from
(
select * from test
MODEL
PARTITION BY (INTV)
DIMENSION BY (ROWN, CASE WHEN (VAL < AVG(VAL) OVER (PARTITION BY INTV) - 2*STDDEV(VAL) OVER (PARTITION BY INTV)) THEN 1 ELSE 0 END flag) 
MEASURES (VAL, val/sum(val) over (partition by rown) prt , 0 avg, 0 stddev, 0 med)
RULES
(
 avg[0,0] = AVG(PRT)[ANY, flag<>1]
 ,stddev[0,0] = STDDEV(PRT)[ANY, flag<>1] 
 ,med[0,0] = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY PRT)[ANY, flag<>1] 
)
) where rown = 0

与标准分析查询：

select
intv
,avg(prt) avg
,stddev(prt) stddev
,percentile_cont(0.5) WITHIN GROUP (ORDER BY prt desc) med
from
(
select 
a.*
, val/sum(val) over (partition by rown) prt
, case when avg(val) over (partition by intv) - 2*stddev(val) over (partition by intv) > val then 1 else 0 end flag
from
test a
)
where flag = 0
group by
intv

不幸的是，目前我无法访问我的大数据集，但通常计算这些平均值的表格包括数百万行。我通过以下方式在小提琴中设置了我的小数据库结构：

create table test (intv number, val number, rown number);
insert all 
into test (intv, val, rown) values (1,5,1)
into test (intv, val, rown) values (1,4,2)
into test (intv, val, rown) values (1,4,3)
into test (intv, val, rown) values (1,5,4)
into test (intv, val, rown) values (1,6,5)
into test (intv, val, rown) values (1,2,6)
into test (intv, val, rown) values (1,5,7)
into test (intv, val, rown) values (1,4,8)
into test (intv, val, rown) values (1,5,9)
into test (intv, val, rown) values (2,10,1)
into test (intv, val, rown) values (2,12,2)
into test (intv, val, rown) values (2,13,3)
into test (intv, val, rown) values (2,15,4)
into test (intv, val, rown) values (2,13,5)
into test (intv, val, rown) values (2,12,6)
into test (intv, val, rown) values (2,19,7)
into test (intv, val, rown) values (2,18,8)
into test (intv, val, rown) values (2,13,9)
select * from dual;

您认为出于什么原因更有效？哪种方法比另一种方法有什么优势？

我正在寻找您的答案和最好的问候！

Answer 1

我自己对 MODEL 子句的经验使我从不将其用于任何大型数据集。与我能找到的任何替代方案相比，我发现它非常慢。

然而，分析函数也可能有问题 - 我发现它们很容易拼写到磁盘，除非你注意它们使用的工作区大小，并且它们需要增加内存分配。

也就是说，你真的需要用你自己的数据和RDBMS版本对每一个进行基准测试。