主页 / coding / 问题 / 79039532
Accepted
Efgrafich
Asked: 2024-09-30 21:16:37 +0800 CST

Oracle SQL分层查询数据积累

  • 772

我需要在分层查询中累积数据，如下所示：

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

除此之外，我只能访问前一行，因此它无法正常工作。而且我无法SELECT对前一行使用当前子句中定义的别名：

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

我缺少什么？SYS_CONNECT_BY_PATH完全按照我的需要进行操作（除了连接而不是乘法），所以应该是可以的。我可以使用WITH子句来执行此操作，但出于某种原因，它非常慢，例如 需要半秒，CONNECT_BY而 需要约 20 秒WITH。

预期输出：

ID 父代 ID 数数 COUNT_PRODUCT
1 无效的 1 1
2 1 2 2
3 2 3 6
4 3 4 24
5 4 5 120
6 5 6 720

使用 WITH 子句（来自 MT0）

oracle

3 个回答

  1. 如果您尝试count通过层次结构获取连续值的乘积，则使用递归子查询分解子句：

    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;

    对于样本数据来说：

    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;

    输出：

    ID 数数 COUNT_PRODUCT
    1 1 1
    2 2 2
    3 3 3
    4 4 8
    5 5 15
    6 6 90

    小提琴

    • 2
  2. Best Answer

    假设您的数据是树结构（无循环），并且从每个后代元素到树的根都有一条路径，那么您可以使用分层查询下降树，然后使用反向相关分层查询找到所有先前的值，并聚合它们的自然对数来计算乘积：

    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

    对于样本数据来说：

    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;

    输出：

    ID 父代 ID 数数 COUNT_PRODUCT
    1 无效的 1 1
    2 1 2 2
    4 2 4 8
    3 1 3 3
    5 3 5 15
    6 5 6 90

    小提琴

    • 1

  3. 我并不认为它特别聪明，但如果你坚持使用 CONNECT BY 和 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

相关问题