Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Накопительный итог для агрегатной функции  [new]
AlexandrAVG
Member

Откуда:
Сообщений: 17
Доброго времени суток. Опишу проблему.
Есть таблица столбцами [modify_date], [response_1], [response_2], [response_3],[incident_id].
Таблица отображает дату опроса, оценки поставленные по 3 критериям и номер заявки по которой были поставлены оценки.
modify_date - дата опроса
response_1-3 - оценки
incident_id - номер заявки.

Задача состоит в следующем:
1. Посчитать среднее значение всех оценок по неделям за квартал.
2. Вывести историю изменения оценок по неделям за квартал.
Например, имеем средние оценки за:
первую неделю квартала: 4.3
вторую неделю квартала: 5
третью неделю квартала: 3.2
Тогда должен получится результат
первая неделя квартала: 4.3
вторая неделя квартала: 4.65 ((4.3+5)/2))
третья неделя квартала: 4.16 ((4.3+5+3.2)/3));

Вывел номер недели и номер месяца, так же вывел общую среднюю оценку из 3 столбцов.

SELECT TOP (500) concat (datepart (ww, surv_req.modify_date) , ',',MONTH (surv_req.modify_date)) as [Неделя/Месяц],
(avg (surv_req.response_1*1.00)+avg (surv_req.response_2*1.00)+avg (surv_req.response_3*1.00))/3 as 'Оценка'
FROM surv_req INNER JOIN
incident ON surv_req.incident_id = incident.incident_id INNER JOIN
assyst_usr ON incident.inc_resolve_usr = assyst_usr.assyst_usr_id
where response_1 <>'0' and
surv_req.modify_date between (select dateadd(qq, datediff(qq, 0, GETDATE()), 0)) and GETDATE() and concat(datepart(ww, surv_req.modify_date) , ',',MONTH(surv_req.modify_date))=concat(datepart(ww, surv_req.modify_date) , ',',MONTH(surv_req.modify_date))
GROUP BY month(surv_req.modify_date),datepart(ww, surv_req.modify_date)



У меня такие вопросы: как мне вывести накопительный итог для агрегатной функции avg? Накопительный итог складывает значения, как мне потом разделить полученной число на количество записей?
20 май 19, 09:08    [21888550]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
AlexandrAVG
Например, имеем средние оценки за:
первую неделю квартала: 4.3
вторую неделю квартала: 5
третью неделю квартала: 3.2
Тогда должен получится результат
первая неделя квартала: 4.3
вторая неделя квартала: 4.65 ((4.3+5)/2))
третья неделя квартала: 4.16 ((4.3+5+3.2)/3));
В CTE посчитать среднее. В основном запросе посчитать среднее среднего как оконную функцию.
20 май 19, 09:24    [21888555]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
declare @t table (modify_date date, response_1 int, response_2 int, response_3 int, incident_id int);

insert into @t
values
 ('20190101', 1, 2, 3, 1),
 ('20190102', 2, 1, 2, 2),
 ('20190401', 3, 3, 3, 3),
 ('20190402', 1, 2, 2, 4);

select
 year(a.modify_date), datepart(qq, a.modify_date),
 sum(b.sum_response) / (count(*) * 3.),
 avg(sum(b.sum_response) / (count(*) * 3.)) over (order by year(a.modify_date), datepart(qq, a.modify_date))
from
 @t a cross apply
 (select a.response_1 + a.response_2 + a.response_3) b(sum_response)
group by
 year(a.modify_date), datepart(qq, a.modify_date)
order by
 year(a.modify_date), datepart(qq, a.modify_date);
20 май 19, 10:02    [21888583]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
AlexandrAVG
Member

Откуда:
Сообщений: 17
invm
declare @t table (modify_date date, response_1 int, response_2 int, response_3 int, incident_id int);

insert into @t
values
 ('20190101', 1, 2, 3, 1),
 ('20190102', 2, 1, 2, 2),
 ('20190401', 3, 3, 3, 3),
 ('20190402', 1, 2, 2, 4);

select
 year(a.modify_date), datepart(qq, a.modify_date),
 sum(b.sum_response) / (count(*) * 3.),
 avg(sum(b.sum_response) / (count(*) * 3.)) over (order by year(a.modify_date), datepart(qq, a.modify_date))
from
 @t a cross apply
 (select a.response_1 + a.response_2 + a.response_3) b(sum_response)
group by
 year(a.modify_date), datepart(qq, a.modify_date)
order by
 year(a.modify_date), datepart(qq, a.modify_date);


Спасибо большое!

Теперь пытаюсь привести результат в формат 4,93, сейчас он в таком виде 4.9300000000000. Пытался воспользоваться SUBSTRING и CONVERT выдает ошибки
Argument data type numeric is invalid for argument 1 of substring function.
Arithmetic overflow error converting numeric to data type varchar.
как можно преобразовать в char, ограничить количество символов до сотрых и произвести замену с точки на запятую?
20 май 19, 11:27    [21888678]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
AlexandrAVG
Member

Откуда:
Сообщений: 17
round( avg(sum(b.sum_response) / (count(*) * 3.)) over (order by month(surv_req.modify_date),datepart(ww, surv_req.modify_date)),2) as 'Оценка'

всё что смог - это округлить значения до сотых
20 май 19, 11:34    [21888684]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
AlexandrAVG
Member

Откуда:
Сообщений: 17
round( avg(sum(b.sum_response) / (count(*) * 3.)) over (order by  month(surv_req.modify_date),datepart(ww, surv_req.modify_date)),2) as 'Оценка'


так будет понятней
20 май 19, 11:36    [21888687]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог для агрегатной функции  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
AlexandrAVG
Теперь пытаюсь привести результат в формат 4,93
На клиенте такие вещи нужно делать.
Но если очень хочется, то:
replace(cast(cast(avg(sum(b.sum_response) / (count(*) * 3.)) over (order by year(a.modify_date), datepart(qq, a.modify_date)) as numeric(18,2)) as varchar(30)), '.', ',')
20 май 19, 11:47    [21888697]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить