Oracle 按级别划分的AVG() 分层查询

您好，我不是母语人士，所以我希望能正确解释自己，

我有一个带有标题的表三和一个带有数据的主体表，我需要得到每个级别的平均值，有5个级别，但将来可以修改，但实际上有一些条件的4级和5级有数据 (我知道这可能是奇怪的，但这是几年前的要求)，现在我需要做一个报告，得到每个级别的平均水平，但我不能得到预期的结果，我给你看一个例子:

WITH filtered AS (
    SELECT matriz_key,
           coalesce(paa,0) paa
      FROM totales
     WHERE period_key = 1
),groupeddata AS (
    SELECT mh.matriz_key,
           matriz_key_f,
           coalesce(paa,0) paa
      FROM matriz_h mh,
         filtered a1
     WHERE mh.matriz_key = a1.matriz_key (+)
)
SELECT matriz_key,
       matriz_key_f,
       paa,
       coalesce(round( (
           SELECT AVG(a2.paa)
             FROM groupeddata a2 START WITH
               a2.matriz_key_f = a1.matriz_key
           CONNECT BY
               PRIOR a2.matriz_key = a2.matriz_key_f
       ),2),0) avg_paa
  FROM groupeddata a1 START WITH
    matriz_key_f IS NULL
CONNECT BY
    PRIOR matriz_key = matriz_key_f;

MATRIZ_KEY MATRIZ_KEY_F LABEL LVL PAA AVG_PAA Avg_paa_man
1  -  1  1 0 22.06 31.88
2 1 1.1  2 0 23.44 31.88
3 2 1.1.1  3 0 25.00 20.00
4 3 1.1.1.1  4 0 25.00 20.00
5 4 1.1.1.1.1 5 10 0.00 0.00
6 4 1.1.1.1.2 5 0 0.00 0.00
7 4 1.1.1.1.3 5 50 0.00 0.00
8 4 1.1.1.1.4 5 40 0.00 0.00
9 3 1.1.1.2  4 20 20.00 20.00
10 9 1.1.1.2.1 5 0 0.00 0.00
11 9 1.1.1.2.2 5 35 0.00 0.00
12 9 1.1.1.2.3 5 0 0.00 0.00
13 9 1.1.1.2.4 5 45 0.00 0.00
14 3 1.1.2  3 0 43.75 43.75
15 14 1.1.2.1  4 35 46.67 43.75
16 15 1.1.2.1.1 5 100 0.00 0.00
17 15 1.1.2.1.2 5 20 0.00 0.00
18 15 1.1.2.1.3 5 20 0.00 0.00
19  -  2  1 0 26.82 36.88
20 19 2.1  2 0 29.50 36.88
21 20 2.1.1  3 0 25.00 25.00
22 21 2.1.1.1  4 0 33.33 25.00
23 22 2.1.1.1.1 5 30 0.00 0.00
24 22 2.1.1.1.2 5 0 0.00 0.00
25 22 2.1.1.1.3 5 70 0.00 0.00
26 20 2.1.2  3 0 48.75 48.75
27 26 2.1.2.1  4 55 46.67 48.75
28 27 2.1.2.1.1 5 65 0.00 0.00
28 27 2.1.2.1.1 5 75 0.00 0.00
29 27 2.1.2.1.2 5 0 0.00 0.00

在我的例子中，我在sql中计算了avg_paa，但在excel中我计算了avg_paa_man，avg_paa_man是我期望得到的结果，计算得到从4-5级的平均值，然后得到3级是所有4级的avg，毕竟要得到级别2是得到所有级别3的平均值，对于获取级别1我得到所有级别2的平均值，但在SQL中我得到所有行的平均值，并且计算变化，所以，我怎么能纠正我的查询得到avg_paa_man？

希望你能理解我，谢谢

-更多信息

在某些情况下，这似乎是树中所有较低行的平均值。在其他情况下，它包括当前行。<-没错，4级是所有行的平均值，甚至本身，因为4级有时有一些值，例如matriz_key = 9 paa = 20，所以4级需要得到树和本身中所有行的平均值，但是在1-3级只需要直接叶子的平均值

现在，avg_paa_man这是计算下一种方式:
树1.0的示例计算:
矩阵 _ 密钥 = 4 => 平均 (矩阵 _ 密钥 => 4-8 & lvl => 4,5) = (0 10 0 50 40)/ 5 = 20
矩阵 _ 密钥 = 9 => 平均 (矩阵 _ 密钥 =>9-13 & lvl = 4,5) = (20 0 35 0 45)/ 5 = 20
matriz_key = 3 => (matriz_key =>4,9 & lvl => 4) = (20 20)/2 = 20
matriz_key = 2 => (matriz_key =>3,14 & lvl => 3) = (20 43.75)/2 = 圆形 (31.875，2) = 31.88
matriz_key = 1 => (matriz_key => 2 & lvl => 2) = 31.88/1 = 31.88

树2.0的示例计算:
矩阵 _ 密钥 = 22 => 平均 (矩阵 _ 密钥 =>22-25 & lvl => 4,5) = (0 30 0 70)/ 4 = 25
矩阵 _ 密钥 = 27 => 平均 (矩阵 _ 密钥 =>27-29 & lvl => 4,5) = (55 65 75 0)/4 = 48.75
matriz_key = 21 => (matriz_key =>22 & lvl => 4) = (25)/1 = 25
matriz_key = 26 => (matriz_key =>27 & lvl => 4) = (48.75)/1 = 48.75
matriz_key = 20 => (matriz_key =>20,26 & lvl => 3) = (25 48.75)/2 = 圆形 (36.875，2)= 36.88
matriz_key = 19 => (matriz_key =>20 & lvl => 2) = (36.88)/1 = 36.88

在这个例子中，我只写了一些叶子，但是对于每个级别 = 2有大约2-4，对于级别 = 3有大约1-20对于级别 = 4有大约1-20，对于级别 = 5有大约1-20，所以树可能很大，但是计算就像我的例子一样，只是我需要包括每个叶子来计算它。

谢谢

要解决此问题，您需要遍历数据集两次。一旦从树上下来建立层次结构。并再次备份以计算平均值。

我在Twitter上与社区联系，寻求构建解决方案的帮助。几个人提交了查询，我们在本月的SQL Ask TOM Office Hours中进行了讨论。您可以在下面看到此记录:



破解此问题的关键是要注意，您正在计算具有相同父级的所有行的平均值。您可以使用流水线表函数，递归或model子句来执行此操作。

为简单起见，我只展示了Stew Ashton的模型解决方案。您可以在Live SQL上获取其他解决方案的代码:

https://livesql.oracle.com/apex/livesql/file/content_IOHVI2K5THQ213SZ6XOZPB402.html

我们还假设:

-只有底部两个级别的PAA值要包含在平均值中
-所有叶子都在同一深度

如果这些不是真的，事情会变得更加复杂;)

模型解决方案的核心是:

    rules iterate (999) until iteration_number >= l.lvl[1] 
    ( 
      avg_paa[l.lvl[1] - iteration_number, any, any] =  
      case iteration_number 
        when 0 then 0 
        when 1 then 
          (paa[cv(),cv(),cv()] + sum(paa)[cv(lvl)+1, cv(matriz_key), any]) / 
          (1 + count(*)[cv(lvl)+1, cv(matriz_key), any]) 
        else avg(avg_paa)[cv(lvl)+1, cv(matriz_key), any] 
      end 
    ) 

l.lvl[1] 存储树的最大深度。所以迭代循环通过这些级别，直到它达到这个级别。但是颠倒了级别值。所以叶子 (5级) 为零。

对于叶子，平均值只是零。所以这是第一个案例表达式。

接下来是最复杂的。它不仅必须对其子值求平均，还必须将自己添加到此计算中。发生在这里:

(paa[cv(),cv(),cv()] + sum(paa)[cv(lvl)+1, cv(matriz_key), any]) / 
(1 + count(*)[cv(lvl)+1, cv(matriz_key), any]) 

最后，该子句使用以下方法备份树:

avg(avg_paa)[cv(lvl)+1, cv(matriz_key), any] 

这会对当前以下所有具有当前matriz_key作为父项的行进行平均。

把它放在一起，你有:

with data as ( 
  select matriz_key, matriz_key_f, label, 
    coalesce(paa,0) paa, 0 avg_paa 
  from matriz_h mh 
  left join totales tot using(matriz_key) 
) 
, hier as ( 
  select matriz_key, matriz_key_f, label, level lvl, paa, avg_paa 
  from data s 
  start with matriz_key_f is null 
  connect by matriz_key_f = prior matriz_key 
) 
select matriz_key, matriz_key_f, label, lvl, paa, avg_paa 
from hier 
model 
  reference l on (select 1 dim, max(lvl) lvl from hier) 
    dimension by (dim) 
    measures (lvl) 
  main m 
    dimension by (lvl, matriz_key_f, matriz_key) 
    measures (label, paa, avg_paa) 
    rules iterate (999) until iteration_number >= l.lvl[1] 
    ( 
      avg_paa[l.lvl[1] - iteration_number, any, any] =  
      case iteration_number 
        when 0 then 0 
        when 1 then 
          (paa[cv(),cv(),cv()] + sum(paa)[cv(lvl)+1, cv(matriz_key), any]) / 
          (1 + count(*)[cv(lvl)+1, cv(matriz_key), any]) 
        else avg(avg_paa)[cv(lvl)+1, cv(matriz_key), any] 
      end 
    ) 
order by matriz_key;

MATRIZ_KEY    MATRIZ_KEY_F    LABEL        LVL    PAA    AVG_PAA   
            1           1                 1      0     31.875 
            2               1 1.1               2      0     31.875 
            3               2 1.1.1             3      0         20 
            4               3 1.1.1.1           4      0         20 
            5               4 1.1.1.1.1         5     10          0 
            6               4 1.1.1.1.2         5      0          0 
            7               4 1.1.1.1.3         5     50          0 
            8               4 1.1.1.1.4         5     40          0 
            9               3 1.1.1.2           4     20         20 
           10               9 1.1.1.2.1         5      0          0 
           11               9 1.1.1.2.2         5     35          0 
           12               9 1.1.1.2.3         5      0          0 
           13               9 1.1.1.2.4         5     45          0 
           14               2 1.1.2             3      0      43.75 
           15              14 1.1.2.1           4     35      43.75 
           16              15 1.1.2.1.1         5    100          0 
           17              15 1.1.2.1.2         5     20          0 
           18              15 1.1.2.1.3         5     20          0 
           19           2                 1      0     36.875 
           20              19 2.1               2      0     36.875 
           21              20 2.1.1             3      0         25 
           22              21 2.1.1.1           4      0         25 
           23              22 2.1.1.1.1         5     30          0 
           24              22 2.1.1.1.2         5      0          0 
           25              22 2.1.1.1.3         5     70          0 
           26              20 2.1.2             3      0      48.75 
           27              26 2.1.2.1           4     55      48.75 
           28              27 2.1.2.1.1         5     65          0 
           29              27 2.1.2.1.2         5     75          0 
           30              27 2.1.2.1.3         5      0          0 

PS-源数据中似乎有几个错误。

* matiz_key 14具有3而不是2作为父级。
* matiz_key 28在数据集中出现两次。

我假设这些都是错误，所以已经按摩了数据。