Подтвердить что ты не робот

Оконные функции могут отображаться только в предложениях SELECT или ORDER BY

Может кто-нибудь объяснить, почему мы не можем использовать оконные функции в предложении group by и почему это разрешено только в SELECT и ORDER BY

Я пытался сгруппировать записи на основе row_number() и столбца на SQL Server следующим образом:

SELECT Invoice
from table1
group by row_number() over(order by Invoice),Invoice

Я получаю сообщение об ошибке

Оконечные функции могут отображаться только в SELECT или ORDER BY

Я могу выбрать этот row_number() в предложении SELECT, но я хочу знать, почему мы не можем использовать его в группе?

Ответ 1

Оконечные функции определены в спецификации ANSI для логического выполнения после обработки GROUP BY, HAVING, WHERE.

Чтобы быть более конкретными, они разрешены на этапах 5.1 и 6 в блок-схеме обработки логических запросов здесь.

Я полагаю, что они могли бы определить его другим способом и разрешили GROUP BY, WHERE, HAVING использовать функции окна, а окно было логическим результатом, установленным в начале этой фазы, но предположим, что они были и мы были разрешено создавать запросы, такие как

SELECT a, 
       b, 
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
  FROM YourTable
  WHERE NTILE(2) OVER (PARTITION BY a ORDER BY b) > 1
  GROUP BY a, 
           b, 
           NTILE(2) OVER (PARTITION BY a ORDER BY b)
  HAVING NTILE(2) OVER (PARTITION BY a ORDER BY b) = 1

С четырьмя различными логическими окнами в игре удачи, выясняя, каков будет результат этого! Кроме того, что, если в HAVING вы действительно хотели отфильтровать выражение из уровня GROUP BY выше, а не с окном строк, являющимся результатом после GROUP BY?

Версия CTE более подробная, но более ясная и удобная в использовании.

WITH T1 AS
(
SELECT a, 
       b, 
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForWhere
  FROM YourTable
), T2 AS
(
SELECT a,
       b,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForGroupBy
FROM T1
WHERE NtileForWhere > 1
), T3 AS
(
SELECT a,
       b,
       NtileForGroupBy,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForHaving
FROM T2
GROUP BY a,b, NtileForGroupBy
)
SELECT a,
       b,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
FROM T3
WHERE NtileForHaving = 1

Поскольку все они определены в инструкции SELECT и сглажены, это легко достижимо для устранения неоднозначности результатов с разных уровней, например. просто переключив WHERE NtileForHaving = 1 в NtileForGroupBy = 1

Ответ 2

Вы можете обойти это, поместив функцию окна в подзапрос:

select  invoice
,       rn
from    (
        select  Invoice
        ,       row_number() over(order by Invoice) as rn
        from    Table1
        ) as SubQueryAlias
group by
        invoice
,       rn