Início / coding / Perguntas / 79039532
Accepted
Efgrafich
Asked: 2024-09-30 21:16:37 +0800 CST

Acumulação de dados de consulta hierárquica Oracle SQL

  • 772

Preciso acumular dados em uma consulta hierárquica como esta:

select 
id, 
prior id as parent_id,
count * prior count  --need to be accumulated through whole hierarchy like sys_connect_by_path 
from table
start with id = 1
connect by prior id = parent_id

Exceto que eu só posso acessar a linha anterior, então não vai funcionar corretamente. E eu não posso usar o alias definido na SELECTcláusula atual para a linha anterior:

select 
id, 
prior id as parent_id,
count,
(count * prior whole_count) as count_product --not allowed to do this :(
from table
start with id = 1
connect by prior id = parent_id

O que estou esquecendo? SYS_CONNECT_BY_PATHfazer exatamente o que preciso (exceto concatenação em vez de multiplicação), então deve ser possível. Posso fazer isso usando WITHcláusula, mas por algum motivo é extremamente lento, tipo meio segundo para CONNECT_BYvs ~20 para WITH.

Saída esperada:

EU IA ID_PARENTE CONTAR CONTAGEM_PRODUTO
1 nulo 1 1
2 1 2 2
3 2 3 6
4 3 4 24
5 4 5 120
6 5 6 720

Fiddle usando cláusula WITH (de MT0)

oracle

3 respostas

  1. Se você estiver tentando obter o produto de countvalores sucessivos por meio da hierarquia, use uma cláusula de fatoração de subconsulta recursiva:

    WITH rsqfc (id, count, count_product) AS (
  SELECT id, count, count FROM table_name WHERE parent_id IS NULL
UNION ALL
  SELECT t.id, t.count, t.count * r.count_product
  FROM   rsqfc r
         INNER JOIN table_name t
         ON (r.id = t.parent_id)
)
SELECT *
FROM   rsqfc;

    O que, para os dados da amostra:

    CREATE TABLE table_name (
  id        NUMBER(10,0) PRIMARY KEY,
  parent_id REFERENCES table_name (id),
  count     NUMBER(10,0)
)

INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;

    Saídas:

    EU IA CONTAR CONTAGEM_PRODUTO
    1 1 1
    2 2 2
    3 3 3
    4 4 8
    5 5 15
    6 6 90

    violino

    • 2
  2. Best Answer

    Supondo que seus dados sejam uma estrutura de árvore (sem loops) e que haja um único caminho de cada elemento descendente de volta à raiz da árvore, você pode descer a árvore usando uma consulta hierárquica e, então, encontrar todos os valores anteriores usando uma consulta hierárquica correlacionada na direção reversa e agregar seus logaritmos naturais para calcular o produto:

    SELECT id, parent_id, count,
       ( SELECT ROUND(EXP(SUM(LN(count))), 10)
         FROM   table_name s
         START WITH s.ROWID = t.ROWID
         CONNECT BY PRIOR parent_id = id ) AS count_product
FROM   table_name t
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id

    O que, para os dados da amostra:

    CREATE TABLE table_name (
  id        NUMBER(10,0) PRIMARY KEY,
  parent_id REFERENCES table_name (id),
  count     NUMBER(10,0)
)

INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;

    Saídas:

    EU IA ID_PARENTE CONTAR CONTAGEM_PRODUTO
    1 nulo 1 1
    2 1 2 2
    4 2 4 8
    3 1 3 3
    5 3 5 15
    6 5 6 90

    violino

    • 1

  3. Não que eu ache isso particularmente inteligente, mas se você insiste em usar CONNECT BY e SYS_CONNECT_BY_PATH:

    select id, parent_id, xmlquery(('1 ' || formula) returning content).getNumberVal() as res 
from (
    select 
    id, 
    parent_id,
    cnt,
    sys_connect_by_path(cnt,' * ') as formula
    from data
    start with id = 1
    connect by prior id = parent_id
)
;
    • 1

relate perguntas