Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Имеется три таблицы, основная и две подчинённые. При этом в приложении необходимо отображать данные из основной и несколько вычисляемых столбцов на основе подчинённых.

Примерно так:

Таблицы: A - основная, B и C - подчинённые.

    A    |    B    |    C   
ID | ID | ID
... | ID_A | ID_A
... | DATE | SOMEVAL
... | NUM1 | ...
... | NUM2 | ...
... | ... | ...

Нужно отобразить в сводной таблице для каждой записи из A:
  • все столбцы из A
  • две суммы по двум столбцам из B по некоторому условию (ключ и дата, например)
  • минимальное значение по ключу из C.
Результат:                                                                                                        
A.ID1, ..., SUM(NUM1), SUM(NUM2) [WHERE B.ID_A = A.ID1 AND DATE BETWEEN ....], MIN(SOMEVAL) [WHERE C.ID_A = A.ID1]
A.ID2, ..., SUM(NUM1), SUM(NUM2) [WHERE B.ID_A = A.ID2 AND DATE BETWEEN ....], MIN(SOMEVAL) [WHERE C.ID_A = A.ID2]
A.ID3, ..., SUM(NUM1), SUM(NUM2) [WHERE B.ID_A = A.ID3 AND DATE BETWEEN ....], MIN(SOMEVAL) [WHERE C.ID_A = A.ID3]
...
Вопрос: как это сделать правильно и чтобы работало максимально быстро?
30 май 20, 00:43    [22142551]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 50345

Ответ зависит от многих факторов, включая версию Firebird, размеры таблиц и т.д. и т.п.

Ну и было бы неплохо посмотреть на твои собственные попытки, их планы и статистику.

Posted via ActualForum NNTP Server 1.5

30 май 20, 01:12    [22142559]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Dimitry Sibiryakov

Ответ зависит от многих факторов, включая версию Firebird, размеры таблиц и т.д. и т.п.

Версия 3.0, таблицы... ну пока небольшие - до десятков тысяч записей.
Dimitry Sibiryakov

Ну и было бы неплохо посмотреть на твои собственные попытки, их планы и статистику.
Я пока ещё не делал ничего, т.к. жалко будет убить время на тупиковый путь. Мне не код нужен, а направление в котором двигаться.

Например в данный момент таблицы B ещё нет, а для таблицы C есть поле в A, которое обновляется по триггеру при изменении C.
Но мне кажется это не совсем правильным решением, особенно для новых условий.

Сообщение было отредактировано: 30 май 20, 01:15
30 май 20, 01:15    [22142561]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28878
alekcvp,

а чего не a join b join c ? left по вкусу, в зависимости от наличия записей в b и c соответствующих a.

в sql можно по всякому делать. "вложенные запросы" - это обычно от неумения join.
30 май 20, 01:34    [22142566]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 50345

alekcvp
Мне не код нужен, а направление в котором двигаться.

Все направления работоспособны при определённых условиях:
1) Простой JOIN + GROUP.
2) JOIN + DERIVED TABLE с GROUP внутри
3) SUBQUERY
4) Хранимые агрегаты.

Posted via ActualForum NNTP Server 1.5

30 май 20, 12:12    [22142644]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
Привет.

alekcvp
Имеется три таблицы, основная и две подчинённые
  • все столбцы из A
  • две суммы по двум столбцам из B по некоторому условию (ключ и дата, например)
  • минимальное значение по ключу из C.
Я бы для таблиц B и С агрегатные вычисления оформил бы в виде CTE, которые заджоинил к таблице A.
И все это оформил бы, как VIEW.
Основной вопрос - какой должен быть join (left или inner)?
Могут ли FK-поля в таблицах B и C принимать значение NULL?
Должны ли в результате запроса присутствовать записи из A, ссылки на которые отсутствуют в B и/или C?

С уважением, Polesov.
30 май 20, 13:41    [22142674]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 981
alekcvp,

скорее всего обычным джойном такое не получится. Агрегаты с разными условиями так не сделать. Можно ещё рассмотреть оконные функции, в них агрегаты настраиваются по условию. Совсем просто - это подзапросы по таблицам B и С. Но может оказаться перепробег, т.е. по одним и тем же данным будет несколько проходов. На первый взгляд это Ваш случай - сумма по таблице B, частичная сумма по таблице B... По-моему, оконные для этого - полная выборка и агрегаты по разным условиям.
30 май 20, 15:07    [22142713]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov

Я бы для таблиц B и С агрегатные вычисления оформил бы в виде CTE, которые заджоинил к таблице A.

CTE?
Polesov

Могут ли FK-поля в таблицах B и C принимать значение NULL?
Не могут.
Polesov

Должны ли в результате запроса присутствовать записи из A, ссылки на которые отсутствуют в B и/или C?
Должны быть все записи из А.
30 май 20, 17:48    [22142774]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
kdv
а чего не a join b join c ? left по вкусу, в зависимости от наличия записей в b и c соответствующих a.
Я не знал что в обычных запросах можно делать аггрегатные join, причём с условиями...
30 май 20, 17:49    [22142775]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
zeon11
Member

Откуда: Сибирь, Кемерово
Сообщений: 1199
alekcvp,

Начни в лоб:
select 
   a.ID,
   a.Name,
   (select sum(b.summ) from TableB b where b.ID_A=a.ID and 1=1) SumB,
   (select count(b.ID)    from TableB b where b.ID_A=a.ID and 1=1) CountB,
   (select sum(c.summ) from TableC c where c.ID_A=a.ID and 1=1) SumC,
   (select count(c.ID)    from TableC c where c.ID_A=a.ID and 1=1) CountC

from TableA a 
where 1=1


на нескольких десятках тысяч записей будет отрабатываться очень быстро,
по крайней мере, прототип приложения отладишь, а там и новые идеи придут.

Сообщение было отредактировано: 30 май 20, 17:54
30 май 20, 17:52    [22142779]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov,

Что-то типа такого?
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
30 май 20, 18:04    [22142785]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov,

Что-то типа такого?
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 - уш-да-уш ... ((
30 май 20, 19:03    [22142816]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov

Агрегат по В можно делать в одной CTE.

Я в исходном посте упустил, что в B два ключа для A и суммы, соответственно, считаются по разным.
30 май 20, 19:06    [22142817]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov

Агрегат по В можно делать в одной CTE.

Я в исходном посте упустил...

Ну, это уже не столь существенно.
А вот если все-же требование на left join, я бы рассмотрел возможность изменения схемы выборки.
Уж больно left join в данной схеме тормозной.
30 май 20, 19:16    [22142823]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Должны быть все записи из А.

Возможна ли ситуация, когда в А есть записи, на которые нет ссылок из С и из B по обеим FK?
Если да, то требуется left join, а в моем варианте это, мягко говоря, не очень быстрый вариант.
30 май 20, 19:34    [22142825]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
alekcvp
Должны быть все записи из А.

Возможна ли ситуация, когда в А есть записи, на которые нет ссылок из С и из B по обеим FK?
Если да, то требуется left join, а в моем варианте это, мягко говоря, не очень быстрый вариант.

В C для каждой записи из A должна быть минимум одна запись, а вот в B - не обязательно, плюс там date between будет, под который может ни одна запись не попадать.

Сообщение было отредактировано: 30 май 20, 19:48
30 май 20, 19:49    [22142828]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Изначально предусмотрено что ...

Надеюсь, идея и ее плюсы и минусы, понятны.
Далее надо смотреть по месту.
На мой взгляд, странно иметь в таблице B 2 FK на таблицу A - это безусловное применение let join.
Впрочем, сложно что-то советовать, не зная предметной области.
30 май 20, 19:54    [22142830]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
плюс там date between будет, под который может ни одна запись не попадать.

Это условие на inner/left join не влияет.
30 май 20, 19:55    [22142831]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
На мой взгляд, странно иметь в таблице B 2 FK на таблицу A - это безусловное применение let join.
Впрочем, сложно что-то советовать, не зная предметной области.

Хм... чё-то я задумался... возможно стоит таблицу B разбить на две... но всё равно в этих B1 и B2 может не быть записей для A, т.е. никуда от left join не деться.
Может как-то индексами помочь?

Сообщение было отредактировано: 30 май 20, 20:14
30 май 20, 20:16    [22142837]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov
На мой взгляд, странно иметь в таблице B 2 FK на таблицу A - это безусловное применение let join.
Впрочем, сложно что-то советовать, не зная предметной области.

Хм... чё-то я задумался... возможно стоит таблицу B разбить на две... но всё равно в этих B1 и B2 может не быть записей для A, т.е. никуда от left join не деться.
Может как-то индексами помочь?

А обязательно данные получать в одном рекорд-сете?
Может быть, для отображения данных на клиенте, дешевле сделать master-detail?
30 май 20, 20:30    [22142841]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
А обязательно данные получать в одном рекорд-сете?
Может быть, для отображения данных на клиенте, дешевле сделать master-detail?

По ТЗ - обязательно... но в таблице A вряд ли будет больше 100000 записей.

Сообщение было отредактировано: 30 май 20, 20:42
30 май 20, 20:43    [22142848]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov
А обязательно данные получать в одном рекорд-сете?
Может быть, для отображения данных на клиенте, дешевле сделать master-detail?

По ТЗ - обязательно...

Для обеспечения inner join можно рассмотреть вариант создания в B и C фиктивных записей,
что бы каждой записи из A было соответствие в B и C, но это уже, на мой взгляд, костыль.
30 май 20, 20:54    [22142859]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
Для обеспечения inner join можно рассмотреть вариант создания в B и C фиктивных записей,
что бы каждой записи из A было соответствие в B и C, но это уже, на мой взгляд, костыль.

ИМХО проще тогда сделать эти поля в A и при изменении записей в B и C триггером это поле обновлять...
30 май 20, 21:25    [22142871]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp,
А связь A к B и A к C один к одному?
Если нет, то этот способ еще хуже.
30 май 20, 21:28    [22142873]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
alekcvp,
А связь A к B и A к C один к одному?
Если нет, то этот способ еще хуже.

Нет, один ко многим.
А почему хуже? По триггеру пересчитать одно поле одной записи вроде не должно занимать много времени?

Почему всё-таки Left Join так катастрофически влияет на производительность?

Сообщение было отредактировано: 30 май 20, 21:57
30 май 20, 21:58    [22142882]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov,

Это забавно.
+ Структура БД
CREATE TABLE A (
    ID INTEGER NOT NULL,
    VAL VARCHAR(32) CHARACTER SET UTF8);


CREATE TABLE B1 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE B2 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE C (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    FLAG SMALLINT);

ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID);
ALTER TABLE B1 ADD CONSTRAINT PK_B1 PRIMARY KEY (ID);
ALTER TABLE B2 ADD CONSTRAINT PK_B2 PRIMARY KEY (ID);
ALTER TABLE C ADD CONSTRAINT PK_C PRIMARY KEY (ID);

ALTER TABLE B1 ADD CONSTRAINT FK_B1_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE B2 ADD CONSTRAINT FK_B2_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE C ADD CONSTRAINT FK_C_A FOREIGN KEY (ID_A) REFERENCES A (ID);

CREATE INDEX IDX_B1_FIX ON B1 (FIX);
CREATE INDEX IDX_B2_FIX ON B2 (FIX);
CREATE INDEX IDX_C_FLAG ON C (FLAG);


Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

Как блин?.. И можно ли это сделать ещё быстрее? :)
30 май 20, 22:17    [22142887]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
В общем на данный момент самым оптимальным запросом под мои задачи, из тех что я смог придумать и протестировать, оказался такой:
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID

План:
PLAN (B2 INDEX (FK_B2_A, IDX_B2_FIX))
PLAN (B1 INDEX (FK_B1_A, IDX_B1_FIX))
PLAN JOIN (F C ORDER FK_C_A, A INDEX (PK_A))

Вопрос: добавил к B2 ещё один внешний ключ к B1 (т.к. они всё-таки взаимосвязаны). Запрос стал выполняться на 60% дольше (было 3.5, стало 5.2 секунды)... хотя этот ключ непосредственно в запросе не используется. Это можно как-то нивелировать?

Также приветствуются любые советы по оптимизации.

Если вдруг кому интересно, то бэкап тестовой базы с тестовыми данными тут (~10 Мб, сама база ~200 Мб).

Сообщение было отредактировано: 31 май 20, 01:55
31 май 20, 01:53    [22142932]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov
alekcvp,
А связь A к B и A к C один к одному?
Если нет, то этот способ еще хуже.

Нет, один ко многим.
А почему хуже? По триггеру пересчитать одно поле одной записи вроде не должно занимать много времени?

Я имел ввиду, что хуже потому, что усложнится логика.
20578934 )))
Например: добавлена запись в A. В таблице С соответствующих записей нет.
Что произойдет в триггере, если в таблицу С будет вставлена запись?
Что-то типа
update А set MIN_FLAG = ( select min( FLAG ) from C where ID_A = new.ID_A );
И аналогичные действия при апдейте и удалении записи из таблицы C?

Хотя, согласен - в принципе, схема работоспособная.
Ну, и не допускать ошибочной деактивации триггера )
31 май 20, 13:08    [22143033]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov,

Это забавно.
+ Структура БД
CREATE TABLE A (
    ID INTEGER NOT NULL,
    VAL VARCHAR(32) CHARACTER SET UTF8);


CREATE TABLE B1 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE B2 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE C (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    FLAG SMALLINT);

ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID);
ALTER TABLE B1 ADD CONSTRAINT PK_B1 PRIMARY KEY (ID);
ALTER TABLE B2 ADD CONSTRAINT PK_B2 PRIMARY KEY (ID);
ALTER TABLE C ADD CONSTRAINT PK_C PRIMARY KEY (ID);

ALTER TABLE B1 ADD CONSTRAINT FK_B1_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE B2 ADD CONSTRAINT FK_B2_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE C ADD CONSTRAINT FK_C_A FOREIGN KEY (ID_A) REFERENCES A (ID);

CREATE INDEX IDX_B1_FIX ON B1 (FIX);
CREATE INDEX IDX_B2_FIX ON B2 (FIX);
CREATE INDEX IDX_C_FLAG ON C (FLAG);


Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

Как блин?.. И можно ли это сделать ещё быстрее? :)

Я уже предлагал способ - обеспечить в таблице A наличие фиктивных записей, подзпаросы оформить в виде CTE и использовать inner join.
31 май 20, 13:12    [22143038]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
Polesov,
> обеспечить в таблице A наличие фиктивных записей
обеспечить для таблицы A наличие фиктивных записей
31 май 20, 13:15    [22143042]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov,
Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

А как измерял?
Я окончания полного фетча для этого запроса ждал 2 минуты - не дождался.
31 май 20, 14:16    [22143083]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

Откуда:
Сообщений: 10955
alekcvp
В общем на данный момент самым оптимальным запросом под мои задачи, из тех что я смог придумать и протестировать, оказался такой:
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID
1. Индекс по FIX в данном случае только мешает, его нужно либо отключить, либо добавить в него ID_A
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID


2. Т.к. полей из С в результате нет, то можно избавиться от агрегата с ней
SELECT
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM2
  FROM A
 WHERE EXISTS (SELECT * FROM C WHERE C.ID_A = A.ID)
31 май 20, 15:14    [22143104]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
hvlad

2. Т.к. полей из С в результате нет

Думаю, ТС, приводя запрос, просто ошибся.
В исходном посте сказано, что из таблицы C надо выводить min( FLAG ).

Кстати, есть ли существенные минусы в добавлении фиктивных записей в дочерние таблицы для обеспечения использования inner join?
31 май 20, 16:19    [22143152]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
FROM
(
SELECT
    ID_A,
    SUM(NUM) SUM1,
    NULL SUM2,
    NULL FLAG
FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    SUM(NUM),
    NULL
FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    NULL,
    MIN(C.FLAG)
FROM C
GROUP BY ID_A
) B1B2C
LEFT JOIN A ON A.ID = B1B2C.ID_A
GROUP BY ID_A
31 май 20, 16:40    [22143162]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
alekcvp
Polesov,
Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

А как измерял?
Я окончания полного фетча для этого запроса ждал 2 минуты - не дождался.

А запрос во время выполнения разве не все данные считает, а тоже порциями? Я имел в виду время выполнения самого запроса.
Фетч-то на 100к записей может и сам по себе долго выполняться, нет?
31 май 20, 16:53    [22143172]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
hvlad

2. Т.к. полей из С в результате нет, то можно избавиться от агрегата с ней
Пропустил F.FLAG в запросе :(
31 май 20, 16:56    [22143176]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Flashpoke,

Охренеть. Спасибо!
Я так понимаю этот запрос разворачивает все три таблицы в одну, а потом тупо строит по ней аггрегатный запрос?

Сообщение было отредактировано: 31 май 20, 17:00
31 май 20, 17:01    [22143180]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

Откуда:
Сообщений: 10955
Flashpoke,

нужно ещё HAVING MAX(FLAG) IS NOT NULL добавить, иначе запрос не эквивалентен.

А вот так будет ещё немножко лучше

SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
  FROM
  (
    SELECT ID_A, SUM(NUM) SUM1, NULL SUM2, NULL FLAG
      FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
    GROUP BY ID_A
    
    UNION ALL
    
    SELECT ID_A, NULL, SUM(NUM), NULL
      FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
    GROUP BY ID_A
    
    UNION ALL
    
    SELECT ID_A, NULL, NULL, MIN(C.FLAG)
      FROM C
    GROUP BY ID_A
  ) B1B2C JOIN A ON A.ID +0= B1B2C.ID_A   -- заставим делать HASH JOIN
GROUP BY ID_A
HAVING MAX(FLAG) IS NOT NULL
31 май 20, 17:26    [22143195]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
alekcvp, таблицы агрегируются независимо друг от друга, чтобы избежать коррелированных подзапросов.
Потом результаты склеивается одну строку для каждого ID_A и берётся полный их список из A.

hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
HASH JOIN
31 май 20, 17:49    [22143210]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
А я эпично обломался. Решил начать от простого к сложному, хаха :(

Внезапно, даты в BETWEEN у меня не задаются в параметрах запроса, а вычисляются из поля таблицы A, причём сложно.
Т.е. там есть поле "начало учётного периода", которое содержит в себе число. И интервал Between - это либо прошлый, либо текущий месяц. Т.е. если там "20", а сегодня 10е мая, то надо просуммировать либо с 20 марта по 19 апреля (SUM2), либо с 20 апреля по сегодняшний день, включительно (SUM1). Блин.

Что-то я всё больше склоняюсь к кэшированию в A всех сумм и обновлению их по триггерам...

Сообщение было отредактировано: 31 май 20, 17:50
31 май 20, 17:51    [22143212]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
Flashpoke
SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
FROM
(
SELECT
    ID_A,
    SUM(NUM) SUM1,
    NULL SUM2,
    NULL FLAG
FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    SUM(NUM),
    NULL
FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    NULL,
    MIN(C.FLAG)
FROM C
GROUP BY ID_A
) B1B2C
LEFT JOIN A ON A.ID = B1B2C.ID_A
GROUP BY ID_A

Исходное условие - должны быть все записи из A.
В результат данного запроса не попадут записи из A, которых нет ни в B1, ни B2, ни в C.
Тогда уж
RIGHT JOIN A ON A.ID = B1B2C.ID_A
31 май 20, 18:15    [22143228]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov

Исходное условие - должны быть все записи из A.
В результат данного запроса не попадут записи из A, которых нет ни в B1, ни B2, ни в C.

Ну by design записи в C создаются одновременно с A, если запись есть в A, но нет ни одной записи в C - это нештатная ситуация, так что если такие записи не будет видно - не страшно. Вот отстутствие записей в B1, B2 - это нормально.

Сообщение было отредактировано: 31 май 20, 18:15
31 май 20, 18:17    [22143229]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Ну by design записи в C создаются одновременно с A

Аднака, паходу выявилась масса нюансоффф )))
31 май 20, 18:35    [22143245]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

Откуда:
Сообщений: 10955
Flashpoke
hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
Вот здесь 22142932 - не все, только те, для которых есть запись в C.
Всю ветку не читал, скучно :)
31 май 20, 19:18    [22143272]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
hvlad
Flashpoke
hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
Вот здесь 22142932 - не все, только те, для которых есть запись в C.

22143229
Выяснилось, что в C есть все, что есть в А.
31 май 20, 22:35    [22143345]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
А вот этого монстра можно как-нибудь оптимизировать?..
-- RD1, RD2 - день месяца (1..31)
SELECT ID_A, RD1, RD2, SUM(SNUM1), SUM(SNUM2), MIN(FLAG) AS FLAG
  FROM
  (
    SELECT ID_A, FIX FIX1, NULL FIX2, SUM(NUM) SNUM1, NULL SNUM2, NULL FLAG FROM B1
    GROUP BY ID_A, FIX
    
    UNION ALL
    
    SELECT ID_A, NULL, FIX, NULL, SUM(NUM), NULL FROM B2
    GROUP BY ID_A, FIX
    
    UNION ALL
    
    SELECT ID_A, NULL, NULL, NULL, NULL, MIN(C.FLAG) FROM C
    GROUP BY ID_A
  ) B1B2C JOIN A ON A.ID = B1B2C.ID_A
WHERE
  (FIX1 BETWEEN DFST(A.RD1) AND DLST(A.RD1) OR FIX1 IS NULL) AND
  (FIX2 BETWEEN DFST(A.RD2) AND DLST(A.RD2) OR FIX2 IS NULL)
GROUP BY ID_A, RD1, RD2
-- HAVING MIN(FLAG) IS NOT NULL -- наличие этого условия не обязательно

+ DFST/DLST
SET TERM ^ ;

create or alter function DFST (
    RDAY smallint not null)
returns date not null
as
declare variable RDIFF smallint;
begin
  RDIFF = RDAY - EXTRACT(DAY FROM CURRENT_DATE);
  return IIF(RDIFF < 0,  DATEADD(DAY, RDIFF, CURRENT_DATE), DATEADD(MONTH, -1, DATEADD(DAY, RDIFF, CURRENT_DATE)));
end
^

create or alter function DLST (
    RDAY smallint not null)
returns date not null
as
declare variable RDIFF smallint;
begin
  RDIFF = RDAY - EXTRACT(DAY FROM CURRENT_DATE);
  return IIF(RDIFF < 0,  DATEADD(MONTH, 1, DATEADD(DAY, RDIFF, CURRENT_DATE)), DATEADD(DAY, RDIFF, CURRENT_DATE)) - 1;
end
^

SET TERM ; ^

Polesov
Выяснилось, что в C есть все, что есть в А.
Предполагается что есть, судьба записей для которых нет - не важна (показывать или нет без разницы).

Сообщение было отредактировано: 31 май 20, 22:47
31 май 20, 22:48    [22143357]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
alekcvp
А вот этого монстра можно как-нибудь оптимизировать?..
За 8 секунд отрабатывает, вроде нормально.
1 июн 20, 02:11    [22143411]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Flashpoke
alekcvp
А вот этого монстра можно как-нибудь оптимизировать?..
За 8 секунд отрабатывает, вроде нормально.

Ну исходный-то за секунду срабатывал, пока я в него кривыми ручками не залез :)
1 июн 20, 02:13    [22143414]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Firebird, InterBase Ответить