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

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

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

Таблицы: 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

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

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

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

Posted via ActualForum NNTP Server 1.5

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

Откуда:
Сообщений: 2181
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
Сообщений: 28886
alekcvp,

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

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

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

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

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

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

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

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

CTE?
Polesov

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

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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

Откуда:
Сообщений: 2181
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

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

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

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

Сообщение было отредактировано: 30 май 20, 21:57
30 май 20, 21:58    [22142882]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Firebird, InterBase Ответить