Агрегаты SQL OVER и PARTITION

Все,

Это мой первый пост в Stackoverflow, так что легко …

Я использую SQL Server 2008.

Я довольно новичок в написании SQL-запросов, и у меня есть проблема, которую я считал довольно простой, но я боролся в течение 2 дней. У меня есть набор данных, который выглядит так:

UserId Duration(Seconds) Month 1 45 January 1 90 January 1 50 February 1 42 February 2 80 January 2 110 February 3 45 January 3 62 January 3 56 January 3 60 February 

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

 UserId Duration(seconds) OrganizationDuration(Seconds) Month 1 67.5 63 January 1 46 65.5 February 

Я обманывал различные подзапросы и группировал по сценариям, и ничто никогда не работает. В последнее время я пытаюсь использовать OVER и PARTITION BY, но без успеха тоже. Мой последний запрос выглядит так:

 select Userid, AVG(duration) OVER () as OrgAverage, AVG(duration) as UserAverage, DATENAME(mm,MONTH(StartDate)) as Month from table.name where YEAR(StartDate)=2014 AND userid=119 GROUP BY MONTH(StartDate), UserId 

Этот запрос бомбит с «Длительностью», недействителен в списке выбора, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY ».

Пожалуйста, имейте в виду, что я имею дело с очень большим количеством данных. Я думаю, что я могу заставить его работать с операторами CASE, но я ищу более чистый и эффективный способ записи запроса, если это возможно.

Спасибо!

Здесь вы соединяете два вопроса:

  • Среднее количество пользователей за месяц
  • Вся организация в среднем за месяц

Если вы только собираетесь возвращать данные для одного пользователя за раз, то встроенный выбор может дать вам радость:

 SELECT AVG(a.duration) AS UserAvergage, (SELECT AVG(b.Duration) FROM tbl b WHERE MONTH(b.StartDate) = MONTH(a.StartDate)) AS OrgAverage ... FROM tbl a WHERE userid = 119 GROUP BY MONTH(StartDate), UserId 

Примечание. Использование сравнения в MONTH может быть медленным – вам может быть лучше иметь CTE (Common Table Expression)

Отсутствие раздела раздела в средней функции

 OVER ( Partition by MONTH(StartDate)) 
 Please try this. It works fine to me. WITH C1 AS ( SELECT AVG(Duration) AS TotalAvg, [Month] FROM [dbo].[Test] GROUP BY [Month] ), C2 AS ( SELECT Distinct UserID, AVG(Duration) OVER(PARTITION BY UserID, [Month] ORDER BY UserID) AS DetailedAvg, [Month] FROM [dbo].[Test] ) SELECT C2.*, C1.TotalAvg FROM C2 c2 INNER JOIN C1 c1 ON c1.[Month] = c2.[Month] ORDER BY c2.UserID, c2.[Month] desc; 

Я смог сделать это, используя самостоятельное соединение. Возможно, лучший способ.

 Select UserId, AVG(t1.Duration) as Duration, t2.duration as OrgDur, t1.Month from #temp t1 inner join (Select Distinct MONTH, AVG(Duration) over (partition by Month) as duration from #temp) t2 on t2.Month = t1.Month group by t1.Month, t1.UserId, t2.Duration order by t1.UserId, Month desc 

Здесь используется CTE, который, вероятно, является лучшим решением и, безусловно, легче читать

 With MonthlyAverage as ( Select MONTH, AVG(Duration) as OrgDur from #temp group by Month ) Select UserId, AVG(t1.Duration) as Duration, m.duration as OrgDur , t1.Month from #temp t1 inner join MonthlyAverage m on m.Month = t1.Month group by UserId, t1.Month, m.duration 

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

 SELECT Distinct UserID, AVG(Duration) OVER(PARTITION BY [Month]) AS TotalAvg, AVG(Duration) OVER(PARTITION BY UserID, [Month] ORDER BY UserID) AS DetailedAvg, [Month] FROM [dbo].[Test] 
  • Функция SQL Group By и window
  • SQL SERVER - сумма над разделом с помощью различных
  • Синтаксис SQL ROW_NUMBER OVER
  • Требуется ли рекурсивный CTE, просто творческие функции окна, цикл?
  • Выполнение общего использования над предложением
  • Обратитесь к другой таблице и возвращайте данные, смежные с результатом Max ()
  • Ранг SQL Server () над разделами с значениями Back и Forth
  • Выбор последнего элемента над SQL PARTITION
  • Предложение TSQL OVER: COUNT (*) OVER (ORDER BY a)
  • Ограничить количество строк на идентификатор
  • выберите случай с надписью «over partition by»
  • Interesting Posts

    Использование пространственного индекса

    Вопросы по собеседованию TSQL, которые вы задаете

    Отображение 1 до 0-1 в структуре объектов

    ошибка при подключении sql express 2005 по сети

    Можно ли отложить проверку ссылочной целостности до конца транзакции в SQL Server?

    SQL Server и TransactionScope (с MSDTC): спорадически не удается получить соединение

    sys эквивалент представления запроса INFORMATION_SCHEMA

    SQL SERVER: Экспорт запроса в качестве объекта JSON

    Работает ли полная резервная копия с простым режимом восстановления?

    Уникальная ключевая проблема в SQL Server

    Запрос выполняется быстро, но работает медленнее в хранимой процедуре

    Недопустимое имя объекта «dbo.TableName» при извлечении данных из сгенерированной таблицы

    Получение определенного дня с помощью MySql

    Какие изменения следует внести в архитектуру блокировки SQL Server, чтобы сделать ее более дружественной для разработчиков?

    Как выполнить sql-текст, переданный как параметр sp?

    Давайте будем гением компьютера.