Oracle 将汇总数据加载并更新到表中

你好，问一下汤姆团队。

我有两张表:

CREATE TABLE USER.DOC_DETAILS
(CLIENT_ID NUMBER NOT NULL, 
 DOC VARCHAR2(13 BYTE) NOT NULL, 
REGISTER_DATE DATE NOT NULL, 
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2),
TAX3_AMMOUNT NUMBER(18,2)
);

CREATE TABLE USER.DOC_SUMMARY
(CLIENT_ID NUMBER NOT NULL, 
PERIOD VARCHAR2(6 BYTE) NOT NULL, 
DOC_QUANTITY NUMBER, --number of docs
DOC_AMMOUNT NUMBER(18,2),
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2)
TAX3_AMMOUNT NUMBER(18,2)
);
复制

要求: 我需要读取USER.DOC_DETAILS表并每天在USER.DOC_SUMMARY表中按CLIENT_ID和句点 (YYYYMM从REGISTER_DATE) 汇总金额列进行分组。

USER.DOC_SUMMARY表中必须存在每个CLIENT_ID和句点的行。每天都会在USER.DOC_DETAILS中插入数据，因此该过程必须更新DOC_QUANTITY列和金额列。

注意: 1月的文档今天 (4月) 可以到达USER.DOC_DETAILS，因此，必须在USER.DOC_SUMMARY表中更新具有CLIENT_ID x和周期202001的行。

提前感谢。

问候，

我将创建一个具体化视图，而不是汇总表。刷新此功能将使用查询结果填充MV。

下面是一个例子:

create table periods (
  period date not null
    primary key
    check ( trunc ( period, 'mm' ) = period )
);

create table doc_details (
  client_id number not null, 
  doc varchar2(13 byte) not null, 
  register_date date not null, 
  tax1_ammount number(18,2),
  tax2_ammount number(18,2),
  tax3_ammount number(18,2),
  primary key ( client_id, doc )
);

create materialized view log on doc_details
  with rowid, primary key ( 
    register_date, tax1_ammount, tax2_ammount, tax3_ammount 
  )
  including new values;
    
create materialized view log on periods
  with rowid, primary key
  including new values;
  
insert into periods
  select add_months ( date'2020-01-01', level - 1 )
  from   dual
  connect by level <= 12;
  
create materialized view doc_summary
  enable query rewrite 
as
select p.period, client_id, count(*), 
       sum ( tax1_ammount ) tax1_ammount, 
       sum ( tax2_ammount ) tax2_ammount, 
       sum ( tax3_ammount ) tax3_ammount
from   periods p 
left join doc_details dd
on     p.period = trunc ( register_date, 'mm' )
group  by p.period, client_id;

exec dbms_mview.refresh ( 'doc_summary', 'C' );

select * from doc_summary
where  period between trunc ( sysdate, 'mm') 
              and add_months ( trunc ( sysdate, 'mm'), 1 );
              
PERIOD                 CLIENT_ID   COUNT(*)   TAX1_AMMOUNT   TAX2_AMMOUNT   TAX3_AMMOUNT   
01-APR-2020 00:00:00                1                            
01-MAY-2020 00:00:00                1                            

insert into doc_details 
  values ( 1, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details 
  values ( 1, 'doc2', sysdate + 30, 1, 2, 3 );
insert into doc_details 
  values ( 2, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details 
  values ( 2, 'doc2', sysdate + 30, 1, 2, 3 );
commit;

exec dbms_mview.refresh ( 'doc_summary', 'C' );

select * from doc_summary
where  period between trunc ( sysdate, 'mm') 
              and add_months ( trunc ( sysdate, 'mm'), 1 );
              
PERIOD                 CLIENT_ID   COUNT(*)   TAX1_AMMOUNT   TAX2_AMMOUNT   TAX3_AMMOUNT   
01-APR-2020 00:00:00           2          1              1              2              3 
01-MAY-2020 00:00:00           2          1              1              2              3 
01-APR-2020 00:00:00           1          1              1              2              3 
01-MAY-2020 00:00:00           1          1              1              2              3
复制

如果您可以更改查询，使其成为内部联接，则可以在提交时快速刷新MV。数据库将自动为您保持同步。

如果不能，请在每天结束时运行 (完成) 刷新。