Аналитический подсчет по разделу с предложением ORDER BY и без него

Я не понимаю, почему при использовании ORDER BY оговорка в аналитическом COUNT функция.

Используя простой пример:

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls) as cnt from req;

дает следующий результат:

N   CLS CNT
2   A   2
1   A   2

Принимая во внимание, что при добавлении ORDER BY в аналитическом предложении результат другой!

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n) as cnt from req;

Столбец CNT изменен:

N   CLS CNT
1   A   1
2   A   2

Может кто-нибудь объяснить, пожалуйста?

Спасибо

Источник

3 ответа

Решение

Во-первых, ссылка на документы. Это несколько неясно, однако.

Аналитическая оговорка состоит из query_partition_clause, order_by_clause а также windowing_clause, И действительно важная вещь о windowing_clause является

Вы не можете указать этот пункт, если вы не указали order_by_clause, Некоторые границы окна определяются RANGE предложение позволяет указать только одно выражение в order_by_clause, Обратитесь к разделу "Ограничения по предложению ORDER BY".

Но вы не только не можете использовать windowing_clause без order_by_clause они связаны друг с другом.

Если вы полностью опустите windowing_clause, то по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,

Оконное предложение по умолчанию создает что-то вроде промежуточного итога. COUNT возвращается 1 для первой строки, поскольку есть только одна строка между верхней частью окна и текущей строкой, 2 для второго ряда и тд.

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

И вы можете смоделировать поведение первого запроса, указав полностью неограниченное окно.

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt from req;

Ага

N   CLS CNT
1   A   2
2   A   2
Источник

Самый простой способ думать об этом - оставить ORDER BY out эквивалентен "упорядочению" таким образом, что все строки в разделе "равны" друг другу. Действительно, вы можете получить тот же эффект, явно добавив ORDER BY пункт, как это: ORDER BY 0 (или "упорядочить" любым постоянным выражением), или даже, более решительно, ORDER BY NULL,

Почему вы получаете COUNT() или же SUM() и т. д. для всего раздела связано с предложением по умолчанию для окон: RANGE between unbounded preceding and current row, "Диапазон" (в отличие от "ROWS") означает, что все строки, "связанные" с текущей строкой, также включены, даже если они не предшествуют ей. Поскольку все строки связаны, это означает, что включен весь раздел, независимо от того, какая строка является "текущей".

Источник

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

Когда вы добавляете предложение в оконную функцию, она будет выполнять вычисления в следующем порядке в том же разделе и начинать заново с другого раздела (группы значений).

ценности, не различающиеся в  ORDER BY по порядку называются одноранговыми, в COUNT()они будут иметь тот же вычисленный результат, что и его последний одноранговый узел, что создаст промежутки, поддерживающие общее

Источник
Другие вопросы по тегам