Блог

Я хотел сгруппировать по вложенному результату запроса select. Я не могу понять, как и почему этот запрос даже выполняется нормально.. Это правильный способ использования group by во вложенном столбце выбора? Мы находимся на Oracle 11g.

 select min(updated_at), max(updated_at), (select count(*) from domain_cdc where app_context_id = 1561 and domain_cdc_status = 'DONE') as done
from domain_cdc where app_context_id = 1561
group  by '';
 

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

Вложенный select не коррелирован, поэтому его нужно выполнить только один раз, что делает его результат фактически постоянным, и в документации говорится:

В запросе, содержащем предложение GROUP BY, элементами списка выбора могут быть агрегатные функции, выражения GROUP BY, константы или выражения, включающие одно из них.

Если бы вы заменили его фактическим значением count, вам не понадобилась бы group by:

 select min(updated_at), max(updated_at), 42 as done
from domain_cdc where app_context_id = 1561;
 

… который работает нормально и имеет смысл из того, что сказано в документах. Несоответствие заключается в том, что при вложенном выборе он будет жаловаться, если вы сделаете это вместо этого:

 select min(updated_at), max(updated_at),
  (select count(*) from domain_cdc where app_context_id = 1561
    and domain_cdc_status = 'DONE') as done
from domain_cdc where app_context_id = 1561;

SQL Error: ORA-00937: not a single-group group function
 

но не в том случае, если вы включаете избыточное group by null . Итак, с одной стороны, анализатор не знает, что вложенный select можно рассматривать как константу и ожидает group by предложение, но тогда он знает, что выражение на самом деле не обязательно должно быть в group by .

Если вложенный выбор был сопоставлен, то group by null он тоже не будет работать:

 select min(updated_at), max(updated_at),
  (select count(*) from domain_cdc dc2
    where dc2.app_context_id = dc1.app_context_id
    and domain_cdc_status = 'DONE') as done
from domain_cdc dc1
where app_context_id = 1561
group by null;

SQL Error: ORA-00979: not a GROUP BY expression
 

Так что ваш исходный запрос просто немного сбит с толку. Странно, но в основном безвредно, я думаю, и я понимаю, что это не совсем отвечает на вопрос… но это очень похоже на ошибку 18697654, если это поможет.

Однако здесь вам не нужен вложенный select, вы можете использовать оператор case:

 select min(updated_at), max(updated_at),
  count(case when domain_cdc_status = 'DONE' then updated_at end) as done
from domain_cdc where app_context_id = 1561;
 

count учитываются только ненулевые значения; случай делает все, что не равно 'DONE' нулю, поэтому они исключены из этого агрегата. И поскольку теперь у вас снова нет неагрегированных столбцов, и синтаксический анализ может понять, что происходит, вам не нужно group by .

Если у вас не очень большая таблица, вы можете попробовать что-то вроде:

 SELECT distinct MAX (updated_at) OVER () AS max_updated_at,
       MIN (updated_at) OVER () AS min_updated_at,
       (SELECT COUNT (*)
          FROM domain_cdc
         WHERE domain_cdc_status = 'DONE')
          AS cnt_domain_cdc
  FROM domain_cdc
  where app_context_id = 1561
 

Для большой таблицы лучшее решение (я считаю) может выглядеть так:

 SELECT max_updated_at, min_updated_at, cnt_domain_cdc
  FROM (SELECT 1 AS id, MAX (updated_at) AS max_updated_at, MIN (updated_at) AS min_updated_at
          FROM domain_cdc
         WHERE app_context_id = 1561) a
       JOIN (SELECT 1 AS id,
                    (SELECT COUNT (*)
                       FROM domain_cdc
                      WHERE domain_cdc_status = 'DONE' AND app_context_id = 1561)
                       AS cnt_domain_cdc
               FROM DUAL) b
          ON a.id = b.id