Polesov,
Что-то типа такого?
1.
2.
3.
4.
5.
6.
with
t_in as (select id_a1 as id, sum(num1) as sum1 from b where date between .... group by id_a1),
t_out as (select id_a2 as id, sum(num2) as sum2 from b where date between .... group by id_a2)
select * from a
left join t_in on a.id = t_in.id
left join t_out on a.id = t_out.id
Агрегат по В можно делать в одной CTE.
Я тут интереса ради смакетировал свое предложение (скрипт под спойлером).
/* TABLE A ****************************************/
create table A (
ID integer not null,
VAL varchar(32)
);
alter table A
add constraint PK_A
primary key ( ID );
/* TABLE B ****************************************/
create table B (
ID integer not null,
ID_A integer not null,
SUM1 float,
SUM2 float,
FIX date
);
alter table B
add constraint PK_B
primary key ( ID );
alter table B
add constraint FK_B_A
foreign key ( ID_A )
references A ( ID );
create index IDX_B_FIX on B ( FIX );
/* TABLE C ****************************************/
create table C (
ID integer not null,
ID_A integer not null,
MIN1 float
);
alter table C
add constraint PK_C
primary key ( ID );
alter table C
add constraint FK_C_A
foreign key ( ID_A )
references A ( ID );
create index IDX_C_MIN1 on C ( MIN1 );
/* VIEW V_ABC *************************************/
create view V_ABC (
ID,
VAL,
SUM_1,
SUM_2,
MIN_1 )
as
with
CTE_B ( ID_A, SUM_1, SUM_2 ) as
( select ID_A,
sum( SUM1 ) as SUM_1,
sum( SUM2 ) as SUM_2
from B
where FIX between '01-JAN-2020' and '31-DEC-2020'
group by ID_A ),
CTE_C ( ID_A, MIN_1 ) as
( select ID_A,
min( MIN1 ) as MIN_1
from C
group by ID_A )
select a.ID,
a.VAL,
cte_b.SUM_1,
cte_b.SUM_2,
cte_c.MIN_1
from A
join CTE_B on ( cte_b.ID_A = a.ID )
join CTE_C on ( cte_c.ID_A = a.ID )
;
/* FILL DATA **************************************/
set term ^ ;
execute block
as
declare variable ID_A integer;
declare variable ID_B integer;
declare variable ID_C integer;
declare variable XX_N integer;
begin
ID_A = 0;
ID_B = 0;
ID_C = 0;
while ( :ID_A < 100000 ) do
begin
ID_A = :ID_A + 1;
insert into A (
ID,
VAL )
values (
:ID_A,
'VAL#' || :ID_A );
XX_N = 0;
while ( :XX_N < 5 ) do
begin
ID_B = :ID_B + 1;
insert into B (
ID,
ID_A,
SUM1,
SUM2,
FIX )
values (
:ID_B,
:ID_A,
:ID_B / 0.1,
:ID_B / 0.3,
'01-JUL-2020' );
XX_N = :XX_N + 1;
end
XX_N = 0;
while ( :XX_N < 7 ) do
begin
ID_C = :ID_C + 1;
insert into C (
ID,
ID_A,
MIN1 )
values (
:ID_C,
:ID_A,
:ID_C / 0.7 );
XX_N = :XX_N + 1;
end
end
end ^
set term ; ^
commit;
A - 100000 записей.
B - 500000 записей.
C - 700000 записей.
Ну, что сказать, как и ожидалось:
inner join - выполняется быстро (select count(*) from V_ABC с inner join ~0.9 sec на Cor-i7 3.6 HGz + SSD )
left join - уш-да-уш ... ((
