Как выполнить ссылку на оконную функцию внутри текущей таблицы?

Question

У меня есть эта часть в большем запросе, который потребляет много оперативной памяти:

TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id (The most common close Reason)
   from ReasonsPerPost
   where Name is NOT NULL and TopPerPost.seq=1 -- Remove useless results here, instead of doing it later
)

но я получил идентификатор из нескольких частей "TopPerPost.seq" не может быть связан.
Последняя деталь... Я использую толькоNameколонка позжеINNER JOINэтой таблицы.

Answer 1

Вы не можете ссылаться на оконную функцию в месте того же запроса. Просто создайте второй cte.

with TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
)
, OnlyTheTop as
(
    select *
    from TopPerPost
    where seq = 1
)

Или вы можете сделать это так.

select * from 
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
) s
where seq = 1

Вот еще один вариант, который должен устранить необходимость в возвращении такого количества строк.

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   cross apply
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        order by TotalByCloseReason desc
   ) s
   where Name is NOT NULL 

Попытка № 4... это было бы намного проще с SQL скрипкой работать с.

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   inner join
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        and Name is NOT NULL
        order by TotalByCloseReason desc
   ) s on s.Id = rpp.Id
   where Name is NOT NULL

Answer 2

Ниже может работать для ваших нужд. Но, не глядя на данные, сложно сказать, будет это или нет.

;with t as
(
  Select Id, max(totalbyclosereason) TC from reasonsperpost where name is not null group by id
)
Select T.id,t.tc,c.closereasontypeid,c.name 
From t join reasonsperpost c on t.id = c.id and t.tc = c.totalbyclosereason