代码之家 › 专栏 › 技术社区 › Gage

Oracle SQL按日期和ID统计子级和父级

hierarchical-data join oracle sql

Gage · 技术社区 · 9 年前

背景: 我需要一种方法来计算我们将在归档过程中运行的记录数。我的查询低于我的设想,但我觉得必须有更好的方法(过去90天运行大约需要20分钟)

查询 :我们有一个名为table_Main(化名)的表,其中包含大量记录(3亿多条),并不断添加到(因此归档)。该表包含具有TR_ID的父记录,该记录将它们链接到另一个表,而子记录没有TR_ID。子对象通过Parent_ID连接到父对象。这里棘手的部分是,我需要根据Trans表中包含的值对记录进行计数,Trans表通过TR_ID链接。

 Select  B.Some_date, B.xx_ID, count(*)
From
(
select tr.Some_date, tr.xx_ID, p.H_ID
from Table_Main p, Trans tr
where p.TR_ID = tr.TR_ID 
 and tr.Some_date>sysdate-90
     --
UNION ALL
        --
Select Result.Some_date , Result.xx_ID, th.H_ID
from (
select tr.Some_date, tr.xx_ID, p.H_ID
from Table_Main p, Trans tr
where p.TR_ID = tr.TR_ID  and tr.Some_date>sysdate-90
) Result
  inner join Table_Main th on th.Parent_id = Result.H_ID and th.Parent_ID is not null
  ) B
  group by  B.Some_date, B.xx_ID
order by B.Some_date;

问题/想法 :是否有任何方法可以通过Table_Main与自身之间的一个连接来简化这一点?例如,一个连接保留了来自父级的第一条记录加上所有连接的子级?我正在尝试下面的查询,但没有得到任何结果。

Select a.Some_date, A.xx_ID, p.H_ID, c.*
from Table_Main p
inner join Trans a on p.TR_ID = a.TR_ID
left join Table_Main c on c.Parent_id = p.H_ID 
where a.Some_date> sysdate-20
order by p.H_ID, c.H_ID

表_主

-H_ID
-Parent_ID //Links child to parent
-TR_ID     //Links to Trans Table

变速器

-TR_ID     //Link to Table_Main
-xx_ID     //This is used to group on
-Some_Date //Used to group on

示例输入:

表_主

H_ID     Parent_ID     TR_ID
1        NULL          1
2        1             NULL
3        NULL          2
4        NULL          3
5        4             NULL
6        4             NULL
7        NULL          4
8        7             NULL
9        NULL          5
10       9             NULL
11       9             NULL
12       9             NULL
13       9             NULL
14       9             NULL
15       9             NULL
16       9             NULL

变速器

TR_ID    XX_ID    Some_Date
1        45       12/1/2015
2        4        12/1/2015
3        6        12/20/2015
4        45       12/1/2015
5        23       12/22/2015

期望输出:

Date      xx_ID Count
12/1/2015   4   1
12/1/2015   45  4
12/20/2015  6   3
12/22/2015  23  8

提前感谢您能提供的任何帮助。

2 回复 | 直到 9 年前

Boneist 9 年前

感谢您添加输入和预期输出数据-这使我们更容易确保我们得到您期望的答案!

这里有一种方法:

with table_main as (select 1 H_ID, null Parent_ID, 1 TR_ID from dual union all
                    select 2 H_ID, 1 Parent_ID, NULL TR_ID from dual union all
                    select 3 H_ID, NULL Parent_ID, 2 TR_ID from dual union all
                    select 4 H_ID, NULL Parent_ID, 3 TR_ID from dual union all
                    select 5 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 6 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 7 H_ID, NULL Parent_ID, 4 TR_ID from dual union all
                    select 8 H_ID, 7 Parent_ID, NULL TR_ID from dual union all
                    select 9 H_ID, NULL Parent_ID, 5 TR_ID from dual union all
                    select 10 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 11 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 12 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 13 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 14 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 15 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 16 H_ID, 9 Parent_ID, NULL TR_ID from dual),
          trans as (select 1 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 2 TR_ID, 4 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 3 TR_ID, 6 XX_ID, to_date('12/20/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 4 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 5 TR_ID, 23 XX_ID, to_date('12/22/2015', 'mm/dd/yyyy') Some_Date from dual)
-- end of mimicking your tables with data in them. See SQL below:                    
select   tr1.some_date,
         tr1.xx_id,
         count(*) cnt
from     (select     h_id,
                     parent_id,
                     max(tr_id) over (partition by connect_by_root(h_id)) tr_id
          from       table_main tm
          connect by prior h_id = parent_id
          start with parent_id is null) tm1
         inner join trans tr1 on (tm1.tr_id = tr1.tr_id)
group by tr1.some_date,
         tr1.xx_id
order by tr1.some_date,
         tr1.xx_id;

SOME_DATE       XX_ID        CNT
---------- ---------- ----------
12/01/2015          4          1
12/01/2015         45          4
12/20/2015          6          3
12/22/2015         23          8

基本上,这首先执行分层查询( connect by... )以链接父行和子行。

然后我们使用 connect_by_root 函数来标识所有父子行的顶层h_id。

一旦我们有了这些,我们就可以使用分析函数来返回每个顶层h_id的所有父行和子行的tr_id(我在这里使用了max(),因为看起来只有父行才会有tr_id)。

然后,连接到转换表并进行聚合计数就很简单了。

这里有一个(希望更快!)考虑到层次结构只有两个可能的层次,修改了做同样事情的方法:

with table_main as (select 1 H_ID, null Parent_ID, 1 TR_ID from dual union all
                    select 2 H_ID, 1 Parent_ID, NULL TR_ID from dual union all
                    select 3 H_ID, NULL Parent_ID, 2 TR_ID from dual union all
                    select 4 H_ID, NULL Parent_ID, 3 TR_ID from dual union all
                    select 5 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 6 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 7 H_ID, NULL Parent_ID, 4 TR_ID from dual union all
                    select 8 H_ID, 7 Parent_ID, NULL TR_ID from dual union all
                    select 9 H_ID, NULL Parent_ID, 5 TR_ID from dual union all
                    select 10 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 11 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 12 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 13 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 14 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 15 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 16 H_ID, 9 Parent_ID, NULL TR_ID from dual),
          trans as (select 1 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 2 TR_ID, 4 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 3 TR_ID, 6 XX_ID, to_date('12/20/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 4 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 5 TR_ID, 23 XX_ID, to_date('12/22/2015', 'mm/dd/yyyy') Some_Date from dual)
-- end of mimicking your tables with data in them. See SQL below:                    
select   tr.some_date,
         tr.xx_id,
         count(*) cnt
from     table_main tm1
         left join table_main tm2 on (tm1.h_id = coalesce(tm2.parent_id, tm2.h_id) and tm1.parent_id is null)
         inner join trans tr on (tm1.tr_id = tr.tr_id)
group by tr.some_date,
         tr.xx_id
order by tr.some_date,
         tr.xx_id;

SOME_DATE       XX_ID        CNT
---------- ---------- ----------
12/01/2015          4          1
12/01/2015         45          4
12/20/2015          6          3
12/22/2015         23          8

另一个可能的答案不涉及自连接,但依赖于分析函数:

with table_main as (select 1 H_ID, null Parent_ID, 1 TR_ID from dual union all
                    select 2 H_ID, 1 Parent_ID, NULL TR_ID from dual union all
                    select 3 H_ID, NULL Parent_ID, 2 TR_ID from dual union all
                    select 4 H_ID, NULL Parent_ID, 3 TR_ID from dual union all
                    select 5 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 6 H_ID, 4 Parent_ID, NULL TR_ID from dual union all
                    select 7 H_ID, NULL Parent_ID, 4 TR_ID from dual union all
                    select 8 H_ID, 7 Parent_ID, NULL TR_ID from dual union all
                    select 9 H_ID, NULL Parent_ID, 5 TR_ID from dual union all
                    select 10 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 11 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 12 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 13 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 14 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 15 H_ID, 9 Parent_ID, NULL TR_ID from dual union all
                    select 16 H_ID, 9 Parent_ID, NULL TR_ID from dual),
          trans as (select 1 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 2 TR_ID, 4 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 3 TR_ID, 6 XX_ID, to_date('12/20/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 4 TR_ID, 45 XX_ID, to_date('12/01/2015', 'mm/dd/yyyy') Some_Date from dual union all
                    select 5 TR_ID, 23 XX_ID, to_date('12/22/2015', 'mm/dd/yyyy') Some_Date from dual)
-- end of mimicking your tables with data in them. See SQL below:                    
select   tr.some_date,
         tr.xx_id,
         count(*) cnt
from     (select max(tr_id) over (partition by coalesce(parent_id, h_id)) tr_id
          from   table_main) tm1
         inner join trans tr on (tm1.tr_id = tr.tr_id)
group by tr.some_date,
         tr.xx_id
order by tr.some_date,
         tr.xx_id;

SOME_DATE       XX_ID        CNT
---------- ---------- ----------
12/01/2015          4          1
12/01/2015         45          4
12/20/2015          6          3
12/22/2015         23          8

MT0 9 年前

Oracle设置 :

CREATE TABLE TRANS(
  TR_ID     NUMBER(10,0) PRIMARY KEY,
  XX_ID     NUMBER(10,0),
  Some_Date DATE
);

CREATE TABLE TABLE_MAIN (
  H_ID      NUMBER(10,0) PRIMARY KEY,
  PARENT_ID NUMBER(10,0) REFERENCES TABLE_MAIN( H_ID ),
  TR_ID     NUMBER(10,0) REFERENCES TRANS( TR_ID )
);

INSERT INTO TRANS
SELECT 1, 45, DATE '2015-12-01' FROM DUAL UNION ALL
SELECT 2,  4, DATE '2015-12-01' FROM DUAL UNION ALL
SELECT 3,  6, DATE '2015-12-20' FROM DUAL UNION ALL
SELECT 4, 45, DATE '2015-12-01' FROM DUAL UNION ALL
SELECT 5, 23, DATE '2015-12-22' FROM DUAL;

INSERT INTO TABLE_MAIN
SELECT  1, NULL, 1 FROM DUAL UNION ALL
SELECT  2, 1, NULL FROM DUAL UNION ALL
SELECT  3, NULL, 2 FROM DUAL UNION ALL
SELECT  4, NULL, 3 FROM DUAL UNION ALL
SELECT  5, 4, NULL FROM DUAL UNION ALL
SELECT  6, 4, NULL FROM DUAL UNION ALL
SELECT  7, NULL, 4 FROM DUAL UNION ALL
SELECT  8, 7, NULL FROM DUAL UNION ALL
SELECT  9, NULL, 5 FROM DUAL UNION ALL
SELECT 10, 9, NULL FROM DUAL UNION ALL
SELECT 11, 9, NULL FROM DUAL UNION ALL
SELECT 12, 9, NULL FROM DUAL UNION ALL
SELECT 13, 9, NULL FROM DUAL UNION ALL
SELECT 14, 9, NULL FROM DUAL UNION ALL
SELECT 15, 9, NULL FROM DUAL UNION ALL
SELECT 16, 9, NULL FROM DUAL;

查询 :

SELECT some_date,
       xx_id,
       COUNT(*)
FROM   Trans t
       INNER JOIN
       (
        SELECT CONNECT_BY_ROOT( TR_ID ) AS TR_ID
        FROM   Table_Main
        START WITH H_ID IS NOT NULL
        CONNECT BY PRIOR H_ID = PARENT_ID
       ) m
       ON ( t.TR_ID = m.TR_ID )
WHERE  some_date > SYSDATE - 90
GROUP BY some_date,
         xx_id;

后果 :

SOME_DATE      XX_ID   COUNT(*)
--------- ---------- ----------
01-DEC-15          4          1 
01-DEC-15         45          4 
22-DEC-15         23          8 
20-DEC-15          6          3