СГРУППИРОВАТЬ ПО в ПЕРЕКРЕСТНОМ ПРИМЕНЕНИИ

Пусть у нас есть две таблицы

create table A (
  fkb int,
  groupby int
);

create table B (
  id int,
  search int
);

insert into A values (1, 1);
insert into B values (1, 1);
insert into B values (2, 1);

то следующий запрос

select B.id, t.max_groupby - B.search diff
from B
cross apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
) t

вернуть ожидаемый результат следующим образом

id  diff
---------
1   0
2   NULL

Однако, когда я добавляю group by A.fkb в перекрестное применение, строка B, где соответствующий A.fkb не существует, исчезает.

select B.id, t.max_groupby - B.search diff
from B
cross apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
  group by A.fkb
) t

Я тестировал на SQL Server, а также на PostgreSQL (с cross join lateral вместо cross apply). Почему group by заставляет строку исчезнуть? Кажется, что cross apply ведет себя как внешнее соединение в первом случае и как внутреннее соединение во втором случае. Однако мне непонятно, почему.


postgresql sql sql-server lateral-join
person Radim Bača    schedule 11.04.2019    source источник
comment
select B.id, (select max(A.groupby) from A where A.fkb = B.id) - B.search diff from B Думаю, вы ожидаете результат этого запроса.   -  person Ahmet Remzi EKMEKCI    schedule 11.04.2019

Ответы (2)


arrow_upward
3
arrow_downward

Вы можете увидеть это, если посмотрите на результат внутреннего запроса отдельно:

select max(A.groupby) max_groupby
from A 
where A.fkb = 2;

возвращает одну строку с max_groupby = null:

max_groupby
-----------
     (null)

Однако, поскольку нет строки с группировкой A.fkb = 2, она дает пустой результат, который вы можете увидеть при запуске:

select max(A.groupby) max_groupby
from A 
where A.fkb = 2
group by A.fkb

и, таким образом, перекрестное соединение не возвращает строки возврата для fkb = 2

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

В Postgres вам нужно было бы написать это как:

select B.id, t.max_groupby - B.search diff
from B
  left join lateral ( 
    select max(A.groupby) max_groupby
    from A 
    where A.fkb = B.id 
    group by A.fkb
  ) t on true

Я не знаю, что будет эквивалентно left join lateral в SQL Server.
on true нужно будет записать как on 1=1.

person a_horse_with_no_name    schedule 11.04.2019
comment
Отличный ответ, спасибо! Эквивалентом левого бокового соединения в SQL Server является outer apply. - person Radim Bača; 11.04.2019

arrow_upward
1
arrow_downward

Это происходит потому, что:

  • GROUP BY ничего не возвращает, когда A.fkb = 2
  • без GROUP BY возвращает NULL

Таким образом, ваш запрос CROSS APPLY возвращает разные результаты.

select B.id, t.max_groupby - B.search diff
from B
outer apply ( 
  select max(A.groupby) max_groupby
  from A 
  where A.fkb = B.id 
  group by A.fkb
) t

ВЫХОД:

id  diff
1   0
2   NULL
person StepUp    schedule 11.04.2019
comment
Спасибо за Ваш ответ. Мой вопрос был: почему group by в cross apply имеет значение в результате? Другими словами, почему введение group by меняет семантику соединения? - person Radim Bača; 11.04.2019