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

Откуда:
Сообщений: 146
 		Помогите, плиз поправить запрос
;with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by CustomerType,[CustomerName],ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
avg(SaleCount) over (Partition by CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear,datePart(WEEKDAY,Dt), IsPromo) as AVG_WeekDay
From promo_data_copy)
Update a
Set SaleCount = cte.AVG_WeekDay
From CTE
join promo_data_copy a
on a.Dt = cte.dt
and a.ItemRelation=cte.ItemRelation
and a.CustomerName=cte.CustomerName
and a.DocumentNum = cte.DocumentNum
and a.DocumentYear = cte.DocumentYear
and a.CustomerType = cte.CustomerType
and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 7
and CTE.ispromo = 0 ;

как вместо avg() сделать median()
там такой функции нет
запрос сейчас выбросы заменяет средними значениями по тому или иному дню, но лучше это делать по медиане
но как в экселе там нет такой функции.
2 июл 18, 19:23    [21537600]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Kontox
Member

Откуда:
Сообщений: 146
 		так делать не помогает

;with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by CustomerType,[CustomerName],ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SaleCount) over (Partition by CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear,
datePart(WEEKDAY,Dt), IsPromo) as median

From promo_data_copy2)
Update a
Set SaleCount = cte.median
From CTE
join promo_data_copy a
on a.Dt = cte.dt
and a.ItemRelation=cte.ItemRelation
and a.CustomerName=cte.CustomerName
and a.DocumentNum = cte.DocumentNum
and a.DocumentYear = cte.DocumentYear
and a.CustomerType = cte.CustomerType
and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 7
and CTE.ispromo = 0 ;

он не заменяет выбросы на медиану дня
2 июл 18, 19:36    [21537620]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Kontox
Member

Откуда:
Сообщений: 146
 		так сообразил в чем дело, заработало.:)
Как удалить этот пост?
2 июл 18, 19:44    [21537634]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
court
Member

Откуда:
Сообщений: 2438
Kontox
заработало.:)
мададца, конечно, только это:
автор
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SaleCount) over (Partition by CustomerType, [CustomerName], ItemRelation, DocumentNum, DocumentYear,
datePart(WEEKDAY,Dt), IsPromo) as median

ниразу не медиана ...
set nocount on
--
declare @t table (v float)
insert into @t values(1.0),(2.0),(3.0),(4.0) --,(5.0)

--	
select
	PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY v) over(partition by (select 1)) as [Это не медиана !]
from @t

;with cte as (
	select
		v
		,rnAsc	=row_number()over(order by v)
		,rnDesc	=row_number()over(order by v desc)
	from @t)

select
	avg(v) as [Это медиана]
from cte 
where rnAsc in (rnDesc,rnDesc-1,rnDesc+1)


Это не медиана !
----------------------
2
2
2
2

Это медиана
----------------------
2,5
3 июл 18, 10:00    [21538493]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
 		court,

зависит от требований. По вашей притензии PERCENTILE_CONT
3 июл 18, 10:18    [21538540]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Kontox
Member

Откуда:
Сообщений: 146
 		court , интересное замечание, а как мне ваш код в свой интегрировать?
3 июл 18, 12:07    [21538882]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
court
Member

Откуда:
Сообщений: 2438
Kontox
court , интересное замечание, а как мне ваш код в свой интегрировать?
не нужно ничего интегрировать

TaPaK, тут 21538540 лучше вариант предложил, - просто измените PERCENTILE_DISC на PERCENTILE_CONT и результат будет правильный
3 июл 18, 12:10    [21538893]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Kontox
court , интересное замечание, а как мне ваш код в свой интегрировать?

т.е. описание что же делает команда мы не будем... впрочем ничего нового
3 июл 18, 12:21    [21538938]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Kontox
Member

Откуда:
Сообщений: 146
 		Как просил модератор, не буду плодить темы, все в одной.
все подпилиываю этот код

;with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_cont(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by CustomerType,[CustomerName],ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SaleCount) over (Partition by CustomerType, [CustomerName],
ItemRelation, DocumentNum, DocumentYear,
datePart(WEEKDAY,Dt), IsPromo) as median
From promo_data_copy2)
Update a
Set SaleCount = cte.median
From CTE
join promo_data_copy2 a
on a.Dt = cte.dt
and a.ItemRelation=cte.ItemRelation
and a.CustomerName=cte.CustomerName
and a.DocumentNum = cte.DocumentNum
and a.DocumentYear = cte.DocumentYear
and a.CustomerType = cte.CustomerType
and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 7
and CTE.ispromo = 0 ;

его шаги
* есть переменная ispromo, она принимает или 1 или 0
код должен работать с нулевой категорией

1. высчитать 75 процентиль по всем наблюдениям нулевой категории ispromo по переменной SaleCount
2. все что выше 75 процентиля код должен заменять медианой выбросы тех дней, на который он пришолся

например выброс пришелся на четверг
значит смотрим медиану всех четвергов и заменяем ею выбросный четверг
3. и все это код делает для групп сustomerType+[CustomerName]+
ItemRelation+DocumentNum+DocumentYear
т.е. раздельно для каждой такой группы


но сейчас он что-то не отрабатывает
вот набор данных

желтые исходные данные

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

К сообщению приложен файл (perc.xlsx - 12Kb) cкачать
3 июл 18, 18:49    [21540337]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Модератор: Может, вы еще и тег src освоите наконец?
3 июл 18, 18:53    [21540340]     Ответить | Цитировать Сообщить модератору
 Re: медиана за место avg()  [new]
Kontox
Member

Откуда:
Сообщений: 146
 		iiyama, дал мне совет через этот запрос
проверить
 ;with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_cont(0.75) WITHIN GROUP (ORDER BY SaleCount) Over (partition by CustomerType,[CustomerName],ItemRelation, DocumentNum, DocumentYear) as PERCENTILE,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SaleCount)  over (Partition by CustomerType, [CustomerName], 
ItemRelation, DocumentNum, DocumentYear,
datePart(WEEKDAY,Dt), IsPromo)  as median
 From promo_data_copy2)

   select 
	a.SaleCount , cte.median
	, CTE.PERCENTILE , CTE.SaleCount -- проверить CTE.PERCENTILE < CTE.SaleCount
	,datePart(WEEKDAY,CTE.Dt)  -- проверить datePart(WEEKDAY,CTE.Dt) < 7
	,CTE.ispromo -- проверить CTE.ispromo = 0 ;
From CTE
join promo_data_copy2 a 
on a.Dt = cte.dt
and a.ItemRelation=cte.ItemRelation 
and a.CustomerName=cte.CustomerName
and a.DocumentNum = cte.DocumentNum 
and a.DocumentYear = cte.DocumentYear 
and a.CustomerType = cte.CustomerType 
and a.ispromo = cte.ispromo
and CTE.ispromo = 0 
where cte.[DocumentNum]=000000999 and cte.[DocumentYear]=2018
  and cte.[ItemRelation]=11683
  order by cte.Dt



Спасибо, разобрался:)
3 июл 18, 19:16    [21540383]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить