Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
помогите, пожалуйста доработать запрос для чистки данных от выбросов
SELECT t.*
FROM
( select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType FROM [Action].[dbo].[promo_data]
where [IsPromo] = 1 group by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType having count(*) >1 ) as x
left outer join [Action].[dbo].[promo_data] as t on x.ItemRelation = t.[ItemRelation] and x.[DocumentNum] = t.[DocumentNum]
and x.[DocumentYear] = t.[DocumentYear]
and x.[CustomerName] = t.[CustomerName]
and x.[CustomerType] = t.[CustomerType]ъ

в чем суть. выбросы детектируются только для нулевой категории переменной ispromo (всего она имеет две категории 1 и 0)
детектирование простое, все что больше 75% процентиля, то выброс
таким образом для каждой страты(группы) CustomerName+DocumentNum+ItemRelation+DocumentYear нужно
1. найти значение 75% процентиля, и все те, что выше него , то
заменить средними значениями своего дня недели. То есть, вот строчка.

Dt ItemRelation SaleCount DocumentNum DocumentYear
2017-10-30 11511 1096 5 2017


Предположим, что 1096 является выбросом(по 75 процентилю), а 2017-10-30 - понедельник, т.е. выброс следует заменить на средние значения по всем понедельникам в таблице. Ниже простой пример, часть таблицы В этом примере понедельники

23.10.2017
30.10.2017
06.11.2017
13.11.2017
20.11.2017

(ispromo = 1, мы не трогаем) среднее значение для понедельников = 276, поэтому 1096 заменить на 276.

также мы делаем с остальными выбросами к дням.

Если значение выброс был во вторник, тогда его следует заменить средними значениями для всего вторника в наборе данных.

Как я могу это сделать sql?

пример данных
Dt ItemRelation SaleCount DocumentNum DocumentYear ispromo
20.10.2017 11511 245 5 2017 0
21.10.2017 11511 232 5 2017 0
22.10.2017 11511 138 5 2017 0
23.10.2017 11511 143 5 2017 0
24.10.2017 11511 245 5 2017 0
25.10.2017 11511 120 5 2017 0
26.10.2017 11511 196 5 2017 0
27.10.2017 11511 364 5 2017 0
28.10.2017 11511 416 5 2017 0
29.10.2017 11511 252 5 2017 0
30.10.2017 11511 1096 5 2017 0
31.10.2017 11511 1333 5 2017 0
01.11.2017 11511 999 5 2017 0
02.11.2017 11511 683 5 2017 0
03.11.2017 11511 429 5 2017 0
04.11.2017 11511 1935 5 2017 0
05.11.2017 11511 50 5 2017 0
06.11.2017 11511 617 5 2017 0
07.11.2017 11511 415 5 2017 0
08.11.2017 11511 91 5 2017 0
09.11.2017 11511 129 5 2017 0
10.11.2017 11511 238 5 2017 0
11.11.2017 11511 156 5 2017 0
12.11.2017 11511 20 5 2017 0
13.11.2017 11511 122 5 2017 0
14.11.2017 11511 -7 5 2017 0
15.11.2017 11511 85 5 2017 0
16.11.2017 11511 162 5 2017 0
17.11.2017 11511 184 5 2017 0
18.11.2017 11511 239 5 2017 0
19.11.2017 11511 0 5 2017 0
20.11.2017 11511 222 5 2017 0
21.11.2017 11511 116 5 2017 0
22.11.2017 11511 107 5 2017 0
23.11.2017 11511 178 5 2017 0
24.11.2017 11511 258 5 2017 1
25.11.2017 11511 343 5 2017 1
26.11.2017 11511 63 5 2017 1
27.11.2017 11511 667 5 2017 1
28.11.2017 11511 1708 5 2017 1
29.11.2017 11511 1108 5 2017 1
30.11.2017 11511 841 5 2017 1
01.12.2017 11511 1256 5 2017 1
02.12.2017 11511 1037 5 2017 1
03.12.2017 11511 169 5 2017 1
04.12.2017 11511 698 5 2017 1
05.12.2017 11511 532 5 2017 1
25 июн 18, 23:00    [21520130]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Kontox,
Убедись, что у тебя неделя начинается с понедельника, иначе принудительно задай перед запросом.

Declare @t table
(Dt date,
ItemRelation int,
SaleCount int,
DocumentNum int,
DocumentYear int,
ispromo int);
insert into @t values
('20.10.2017', 11511, 245, 5, 2017, 0),
('21.10.2017', 11511, 232, 5, 2017, 0), 
('22.10.2017', 11511, 138, 5, 2017, 0), 
('23.10.2017', 11511, 143, 5, 2017, 0), 
('24.10.2017', 11511, 245, 5, 2017, 0), 
('25.10.2017', 11511, 120, 5, 2017, 0), 
('26.10.2017', 11511, 196, 5, 2017, 0), 
('27.10.2017', 11511, 364, 5, 2017, 0), 
('28.10.2017', 11511, 416, 5, 2017, 0), 
('29.10.2017', 11511, 252, 5, 2017, 0), 
('30.10.2017', 11511, 1096, 5, 2017, 0), 
('31.10.2017', 11511, 1333, 5, 2017, 0), 
('01.11.2017', 11511, 999, 5, 2017, 0), 
('02.11.2017', 11511, 683, 5, 2017, 0), 
('03.11.2017', 11511, 429, 5, 2017, 0), 
('04.11.2017', 11511, 1935, 5, 2017, 0), 
('05.11.2017', 11511, 50, 5, 2017, 0), 
('06.11.2017', 11511, 617, 5, 2017, 0), 
('07.11.2017', 11511, 415, 5, 2017, 0), 
('08.11.2017', 11511, 91, 5, 2017, 0), 
('09.11.2017', 11511, 129, 5, 2017, 0), 
('10.11.2017', 11511, 238, 5, 2017, 0), 
('11.11.2017', 11511, 156, 5, 2017, 0), 
('12.11.2017', 11511, 20, 5, 2017, 0), 
('13.11.2017', 11511, 122, 5, 2017, 0), 
('14.11.2017', 11511, -7, 5, 2017, 0), 
('15.11.2017', 11511, 85, 5, 2017, 0), 
('16.11.2017', 11511, 162, 5, 2017, 0), 
('17.11.2017', 11511, 184, 5, 2017, 0), 
('18.11.2017', 11511, 239, 5, 2017, 0), 
('19.11.2017', 11511, 0, 5, 2017, 0), 
('20.11.2017', 11511, 222, 5, 2017, 0), 
('21.11.2017', 11511, 116, 5, 2017, 0), 
('22.11.2017', 11511, 107, 5, 2017, 0), 
('23.11.2017', 11511, 178, 5, 2017, 0), 
('24.11.2017', 11511, 258, 5, 2017, 1), 
('25.11.2017', 11511, 343, 5, 2017, 1), 
('26.11.2017', 11511, 63, 5, 2017, 1), 
('27.11.2017', 11511, 667, 5, 2017, 1), 
('28.11.2017', 11511, 1708, 5, 2017, 1), 
('29.11.2017', 11511, 1108, 5, 2017, 1), 
('30.11.2017', 11511, 841, 5, 2017, 1), 
('01.12.2017', 11511, 1256, 5, 2017, 1), 
('02.12.2017', 11511, 1037, 5, 2017, 1), 
('03.12.2017', 11511, 169, 5, 2017, 1), 
('04.12.2017', 11511, 698, 5, 2017, 1), 
('05.12.2017', 11511, 532, 5, 2017, 1);

with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over () as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Select * From CTE
Where  PERCENTILE < SaleCount
and datePart(WEEKDAY,Dt) < 5
and ispromo = 0;


with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over () as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Update a 
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0	;
26 июн 18, 05:13    [21520409]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
Kopelly , благодарю Вас
но есть вопрос
делаю как вы, сначала объявляю табличную переменную

Declare @t table
(Dt date,
ItemRelation int,
SaleCount int,
DocumentNum int,
DocumentYear int,
ispromo int);


select * from @t

Сообщение 1087, уровень 15, состояние 2, строка 58
Необходимо объявить табличную переменную "@t".
не понимаю в чем дело, она же объявлена как табличная, а значится локальной
что тут сделать нужно?
26 июн 18, 14:14    [21522020]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
TaPaK
Member

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

Сообщение 1087, уровень 15, состояние 2, строка 58
Необходимо объявить табличную переменную "@t".
не понимаю в чем дело, она же объявлена как табличная, а значится локальной
что тут сделать нужно?

выполнять одним пакетом
26 июн 18, 14:18    [21522037]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
TaPaK , вы имеете ввиду выполнить весь запрос сразу?
я пробывал

итак запускаем скрипт весь, что Kopelly предоставил, за что я ему сильно благодарен

тут такие ошибки
Сообщение 319, уровень 15, состояние 1, строка 60
Неправильный синтаксис около ключевого слова "with". Если эта инструкция является обобщенным табличным выражением, предложением xmlnamespaces или предложением в контексте отслеживания изменений, предыдущую инструкцию необходимо завершить точкой с запятой.
Сообщение 10762, уровень 15, состояние 1, строка 62
Функция PERCENTILE_CONT запрещена в текущем режиме совместимости. Она разрешена только в режиме 110 и выше.
Сообщение 10762, уровень 15, состояние 1, строка 73
Функция PERCENTILE_CONT запрещена в текущем режиме совместимости. Она разрешена только в режиме 110 и выше.

что я не правильно делаю?
26 июн 18, 14:23    [21522072]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1723
Kontox,

или между declare @t и select * from @t стоит go,
или Вы выделили мышкой только часть скрипта - как раз ту, что с select, и нажали F5.
26 июн 18, 14:25    [21522090]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1723
Kontox,

;with - перед with точка с запятой должна стоять в обязательном порядке.
26 июн 18, 14:27    [21522102]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
точка с запятой около with решила проблему первой строчки

вторые две маячат

Сообщение 10762, уровень 15, состояние 1, строка 62
Функция PERCENTILE_CONT запрещена в текущем режиме совместимости. Она разрешена только в режиме 110 и выше.
Сообщение 10762, уровень 15, состояние 1, строка 73
Функция PERCENTILE_CONT запрещена в текущем режиме совместимости. Она разрешена только в режиме 110 и выше.

логично предположить, что тут дело в каком -то режиме
Как узнать что у меня за режим?
как сделать так .чтобы в нем все работало?
Если никак , то как перевести в 110 режим
я не админю sql сервер
но работаюьт ssms 17
26 июн 18, 14:35    [21522158]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
TaPaK
Member

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

автор
я не админю sql сервер
но работаюьт ssms 17

нет, вы ничего не делаете. Позовите взрослых.
26 июн 18, 14:38    [21522177]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
дождешься тут взрослых...
тогда пару вопросов
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 120

это безопасная процедура, мир не взорвется, если я попробую сам это сделать?


если небезопасная, есть ли альтернатива

PERCENTILE_CONT function
26 июн 18, 15:01    [21522303]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
set dateformat dmy;
Declare @t table
(Dt date,
ItemRelation int,
SaleCount int,
DocumentNum int,
DocumentYear int,
ispromo int);
insert into @t values
('20.10.2017', 11511, 245, 5, 2017, 0),
('21.10.2017', 11511, 232, 5, 2017, 0), 
('22.10.2017', 11511, 138, 5, 2017, 0), 
('23.10.2017', 11511, 143, 5, 2017, 0), 
('24.10.2017', 11511, 245, 5, 2017, 0), 
('25.10.2017', 11511, 120, 5, 2017, 0), 
('26.10.2017', 11511, 196, 5, 2017, 0), 
('27.10.2017', 11511, 364, 5, 2017, 0), 
('28.10.2017', 11511, 416, 5, 2017, 0), 
('29.10.2017', 11511, 252, 5, 2017, 0), 
('30.10.2017', 11511, 1096, 5, 2017, 0), 
('31.10.2017', 11511, 1333, 5, 2017, 0), 
('01.11.2017', 11511, 999, 5, 2017, 0), 
('02.11.2017', 11511, 683, 5, 2017, 0), 
('03.11.2017', 11511, 429, 5, 2017, 0), 
('04.11.2017', 11511, 1935, 5, 2017, 0), 
('05.11.2017', 11511, 50, 5, 2017, 0), 
('06.11.2017', 11511, 617, 5, 2017, 0), 
('07.11.2017', 11511, 415, 5, 2017, 0), 
('08.11.2017', 11511, 91, 5, 2017, 0), 
('09.11.2017', 11511, 129, 5, 2017, 0), 
('10.11.2017', 11511, 238, 5, 2017, 0), 
('11.11.2017', 11511, 156, 5, 2017, 0), 
('12.11.2017', 11511, 20, 5, 2017, 0), 
('13.11.2017', 11511, 122, 5, 2017, 0), 
('14.11.2017', 11511, -7, 5, 2017, 0), 
('15.11.2017', 11511, 85, 5, 2017, 0), 
('16.11.2017', 11511, 162, 5, 2017, 0), 
('17.11.2017', 11511, 184, 5, 2017, 0), 
('18.11.2017', 11511, 239, 5, 2017, 0), 
('19.11.2017', 11511, 0, 5, 2017, 0), 
('20.11.2017', 11511, 222, 5, 2017, 0), 
('21.11.2017', 11511, 116, 5, 2017, 0), 
('22.11.2017', 11511, 107, 5, 2017, 0), 
('23.11.2017', 11511, 178, 5, 2017, 0), 
('24.11.2017', 11511, 258, 5, 2017, 1), 
('25.11.2017', 11511, 343, 5, 2017, 1), 
('26.11.2017', 11511, 63, 5, 2017, 1), 
('27.11.2017', 11511, 667, 5, 2017, 1), 
('28.11.2017', 11511, 1708, 5, 2017, 1), 
('29.11.2017', 11511, 1108, 5, 2017, 1), 
('30.11.2017', 11511, 841, 5, 2017, 1), 
('01.12.2017', 11511, 1256, 5, 2017, 1), 
('02.12.2017', 11511, 1037, 5, 2017, 1), 
('03.12.2017', 11511, 169, 5, 2017, 1), 
('04.12.2017', 11511, 698, 5, 2017, 1), 
('05.12.2017', 11511, 532, 5, 2017, 1);


with Numbered as  
(
Select 1.0*row_number() over(Order by SaleCount)/count(*) over() as Num,* From @t
),
Procentile as (
Select top 1 SaleCount From Numbered Where Num <=0.75 Order by SaleCount desc 
 union all
Select top 1 SaleCount From Numbered Where Num >=0.75 Order by SaleCount ),
cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
(Select avg(SaleCount) From Procentile) as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Select * From CTE
Where  PERCENTILE < SaleCount
and datePart(WEEKDAY,Dt) < 5
and ispromo = 0;

with Numbered as  
(
Select 1.0*row_number() over(Order by SaleCount)/count(*) over() as Num,* From @t
),
Procentile as (
Select top 1 SaleCount From Numbered Where Num <=0.75 Order by SaleCount desc 
 union all
Select top 1 SaleCount From Numbered Where Num >=0.75 Order by SaleCount ),
cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
(Select avg(SaleCount) From Procentile) as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Update a 
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0	;
26 июн 18, 15:09    [21522335]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
Kopelly , очень и очень круто, читаю твой код и учусь , хотя до тебя еще далеко.
хочу уточнить только
SELECT t.*
FROM
( select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType FROM [Action].[dbo].[promo_data]
where [IsPromo] = 1 group by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType having count(*) >=5 ) as x
left outer join [Action].[dbo].[promo_data] as t on x.ItemRelation = t.[ItemRelation] and x.[DocumentNum] = t.[DocumentNum]
and x.[DocumentYear] = t.[DocumentYear]
and x.[CustomerName] = t.[CustomerName]
and x.[CustomerType] = t.[CustomerType]

это код выбирает страты, только где больше или равно 5 наблюдений по ispromo=1 (Прости меня пожалуйста, я забыл правильно написать
CustomerType having count(*)[b] >=5 [/b]) )
пример
магазин-рога-и-копыта+кока-кола+11+2018+розница имеет пять наблюдений по ispromo =1
для наглядности

магзин товар номер год тип магаза salecount ispromo
магазин-рога-и-копыта кока-кола 11 2018 розница 24 0
магазин-рога-и-копыта кока-кола 11 2018 розница 25 0
магазин-рога-и-копыта кока-кола 11 2018 розница 26 0
магазин-рога-и-копыта кока-кола 11 2018 розница 27 0
магазин-рога-и-копыта кока-кола 11 2018 розница 28 0
магазин-рога-и-копыта кока-кола 11 2018 розница 29 0
магазин-рога-и-копыта кока-кола 11 2018 розница 547 1
магазин-рога-и-копыта кока-кола 11 2018 розница 457 1
магазин-рога-и-копыта кока-кола 11 2018 розница 43 1
магазин-рога-и-копыта кока-кола 11 2018 розница 347 1
магазин-рога-и-копыта кока-кола 11 2018 розница 236 1



тут у нас страта где есть 5 наблюдений по salecount по ispromo=1

с ней мы работаем, (по этой страте для нулей по ispromo заменяем выбросы)

но с такой стратой мы не работаем
магзин товар номер год тип магаза salecount ispromo
магазин-рога-и-копыта кока-кола 11 2018 розница 24 0
магазин-рога-и-копыта кока-кола 11 2018 розница 25 0
магазин-рога-и-копыта кока-кола 11 2018 розница 26 0
магазин-рога-и-копыта кока-кола 11 2018 розница 27 0
магазин-рога-и-копыта кока-кола 11 2018 розница 28 0
магазин-рога-и-копыта кока-кола 11 2018 розница 29 0
магазин-рога-и-копыта кока-кола 11 2018 розница 547 1
магазин-рога-и-копыта кока-кола 11 2018 розница 457 1

тут всего два наблюдения по ispromo=1, следовательно выбросы по ispromo=0 не заменяем согласно тому условию, которое я писал

Как сделать мне замену выбросов только для страт более 5 наблюдений по ispromo=1,

Я моя вина, что я сразу не обратил внимание.
26 июн 18, 16:26    [21522670]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Eleanor
Member

Откуда:
Сообщений: 2875
Kontox
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 120
это безопасная процедура, мир не взорвется, если я попробую сам это сделать?

В результате все имеющиеся хранимки и запросы будут перекомпилированы.

См. документацию, секцию Различия между уровнем 120 и более низкими уровнями совместимости:
"Приложения, перенесенные из предыдущих версий SQL Server, необходимо тщательно тестировать, чтобы убедиться в сохранении или повышении их производительности. Если производительность снизилась, можно задать уровень совместимости базы данных равным 110 или предыдущему значению для использования методологии оптимизатора запросов из прежних версий."
26 июн 18, 16:30    [21522687]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
Проблема в том, что взрослых нет рядом:( И Фиг знает где они. Не дозвониться.
Хотя написал им. Но Kopelly дал суперское решение ,которое не требует функции PERCENTILE_CONT
единственное я виноват, сразу не сказал, что нужно для страт(групп), где >=5-ти по ispromo=1
для таких наблюдений, чистятся выбросы по ispromo=0, для переменной salecount.
Мой косяк.:(
26 июн 18, 18:18    [21523013]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Kontox,
Количество IsPromo больше или равно 5 должно быть подряд или просто в периоде?
27 июн 18, 04:36    [21523927]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Если не нужно подряд, то:

with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over () as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay,
Sum(IsPromo) over () as IsPromoCount

From @t)
Update a 
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0
and CTE.IsPromoCount >= 5
27 июн 18, 04:51    [21523930]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
Kopelly , подскажи, пожалуйста, а как мне это изменить в твоем коде, где ты без функции percentile count сделал?
так как пока апдейта нет со стороны админов.
27 июн 18, 10:49    [21524490]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Kontox,

with Numbered as  
(
Select 1.0*row_number() over(Order by SaleCount)/count(*) over() as Num,* From @t
),
Procentile as (
Select top 1 SaleCount From Numbered Where Num <=0.75 Order by SaleCount desc 
 union all
Select top 1 SaleCount From Numbered Where Num >=0.75 Order by SaleCount ),
cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
(Select avg(SaleCount) From Procentile) as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay,
Sum(IsPromo) over () as IsPromoCount
From @t)
Update a 
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0	
and CTE.IsPromoCount >= 5;
27 июн 18, 10:59    [21524534]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
Kopelly ,все супер и быстро сработало. Последний вопрос остался и всё.

смотри

Dt ItemRelation SaleCount DocumentNum DocumentYear ispromo
23.10.2017 11511 143 5 2017 0
30.10.2017 11511 1096 5 2017 0
06.11.2017 11511 617 5 2017 0
13.11.2017 11511 122 5 2017 0
20.11.2017 11511 222 5 2017 0
27.11.2017 11511 667 5 2017 1
04.12.2017 11511 698 5 2017 1


он сосчитал среднее по понедельникам в том числа и для ispromo=1 тогда верно , что 509
но нужно считать среднее по понедельникам только для ispromo=0, тогда будет 276



Можешь плиз поправить с учетом этого. и всё тема закрыта
27 июн 18, 15:48    [21525540]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
ещё момент и критичный
скрипт не считает выбросы внутри страт, он заменяет их по всем понедельникам.
К примеру
у нас 4 наблюдений
и всего 4 страты
рога-и-копыта+кока-кола+11333+2017
максидом+диван+11334+2018
рога-и-копыта+лимонад+11335+2018
икея+стул+24234+2018

операция скрипта должна быть проделана внутри каждой страты отдельно
сейчас берется среднее по всем понедельникам всех страт сразу и выбросы так заменяются.
А надо внутри каждой страты отдельно.
27 июн 18, 16:26    [21525671]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
1000 наблюдений и по 4 стратам
27 июн 18, 16:42    [21525738]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
я все таки апдейтил до 120 sql
этот код заработал

with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over () as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Select * From CTE
Where PERCENTILE < SaleCount
and datePart(WEEKDAY,Dt) < 5
and ispromo = 0;


with cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SaleCount) Over () as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Update a
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0 ;

но все равно он не считает и не заменяет выбросы внутри страты отдельно.
27 июн 18, 17:22    [21525889]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
для проверки тут уже 2 страты
11511 245 5 2017
и
11706 107 50 2017

Dt ItemRelation SaleCount DocumentNum DocumentYear IsPromo
2017-10-20 00:00:00.000 11511 245 5 2017 0
2017-10-21 00:00:00.000 11511 232 5 2017 0
2017-10-22 00:00:00.000 11511 138 5 2017 0
2017-10-23 00:00:00.000 11511 143 5 2017 0
2017-10-24 00:00:00.000 11511 245 5 2017 0
2017-10-25 00:00:00.000 11511 120 5 2017 0
2017-10-26 00:00:00.000 11511 196 5 2017 0
2017-10-27 00:00:00.000 11511 364 5 2017 0
2017-10-28 00:00:00.000 11511 416 5 2017 0
2017-10-29 00:00:00.000 11511 252 5 2017 0
2017-10-30 00:00:00.000 11511 1096 5 2017 0
2017-10-31 00:00:00.000 11511 1333 5 2017 0
2017-11-01 00:00:00.000 11511 999 5 2017 0
2017-11-02 00:00:00.000 11511 683 5 2017 0
2017-11-03 00:00:00.000 11511 429 5 2017 0
2017-11-04 00:00:00.000 11511 1935 5 2017 0
2017-11-05 00:00:00.000 11511 50 5 2017 0
2017-11-06 00:00:00.000 11511 617 5 2017 0
2017-11-07 00:00:00.000 11511 415 5 2017 0
2017-11-08 00:00:00.000 11511 91 5 2017 0
2017-11-09 00:00:00.000 11511 129 5 2017 0
2017-11-10 00:00:00.000 11511 238 5 2017 0
2017-11-11 00:00:00.000 11511 156 5 2017 0
2017-11-12 00:00:00.000 11511 20 5 2017 0
2017-11-13 00:00:00.000 11511 122 5 2017 0
2017-11-14 00:00:00.000 11511 -7 5 2017 0
2017-11-15 00:00:00.000 11511 85 5 2017 0
2017-11-16 00:00:00.000 11511 162 5 2017 0
2017-11-17 00:00:00.000 11511 184 5 2017 0
2017-11-18 00:00:00.000 11511 239 5 2017 0
2017-11-19 00:00:00.000 11511 0 5 2017 0
2017-11-20 00:00:00.000 11511 222 5 2017 0
2017-11-21 00:00:00.000 11511 116 5 2017 0
2017-11-22 00:00:00.000 11511 107 5 2017 0
2017-11-23 00:00:00.000 11511 178 5 2017 0
2017-11-24 00:00:00.000 11511 258 5 2017 1
2017-11-25 00:00:00.000 11511 343 5 2017 1
2017-11-26 00:00:00.000 11511 63 5 2017 1
2017-11-27 00:00:00.000 11511 667 5 2017 1
2017-11-28 00:00:00.000 11511 1708 5 2017 1
2017-11-29 00:00:00.000 11511 1108 5 2017 1
2017-11-30 00:00:00.000 11511 841 5 2017 1
2017-12-01 00:00:00.000 11511 1256 5 2017 1
2017-12-02 00:00:00.000 11511 1037 5 2017 1
2017-12-03 00:00:00.000 11511 169 5 2017 1
2017-12-04 00:00:00.000 11511 698 5 2017 1
2017-12-05 00:00:00.000 11511 532 5 2017 1
2017-10-19 00:00:00.000 11706 39 50 2017 0
2017-10-20 00:00:00.000 11706 47 50 2017 0
2017-10-21 00:00:00.000 11706 56 50 2017 0
2017-10-22 00:00:00.000 11706 23 50 2017 0
2017-10-23 00:00:00.000 11706 37 50 2017 0
2017-10-24 00:00:00.000 11706 33 50 2017 0
2017-10-25 00:00:00.000 11706 22 50 2017 0
2017-10-26 00:00:00.000 11706 35 50 2017 0
2017-10-27 00:00:00.000 11706 82 50 2017 0
2017-10-28 00:00:00.000 11706 68 50 2017 0
2017-10-29 00:00:00.000 11706 28 50 2017 0
2017-10-30 00:00:00.000 11706 67 50 2017 0
2017-10-31 00:00:00.000 11706 93 50 2017 0
2017-11-01 00:00:00.000 11706 73 50 2017 0
2017-11-02 00:00:00.000 11706 388 50 2017 0
2017-11-03 00:00:00.000 11706 358 50 2017 0
2017-11-04 00:00:00.000 11706 325 50 2017 0
2017-11-05 00:00:00.000 11706 159 50 2017 0
2017-11-06 00:00:00.000 11706 269 50 2017 0
2017-11-07 00:00:00.000 11706 169 50 2017 0
2017-11-08 00:00:00.000 11706 148 50 2017 0
2017-11-09 00:00:00.000 11706 61 50 2017 0
2017-11-10 00:00:00.000 11706 72 50 2017 0
2017-11-11 00:00:00.000 11706 68 50 2017 0
2017-11-12 00:00:00.000 11706 41 50 2017 0
2017-11-13 00:00:00.000 11706 28 50 2017 0
2017-11-14 00:00:00.000 11706 69 50 2017 0
2017-11-15 00:00:00.000 11706 35 50 2017 0
2017-11-16 00:00:00.000 11706 38 50 2017 0
2017-11-17 00:00:00.000 11706 59 50 2017 0
2017-11-18 00:00:00.000 11706 60 50 2017 0
2017-11-19 00:00:00.000 11706 35 50 2017 0
2017-11-20 00:00:00.000 11706 36 50 2017 0
2017-11-21 00:00:00.000 11706 53 50 2017 0
2017-11-22 00:00:00.000 11706 33 50 2017 0
2017-11-23 00:00:00.000 11706 32 50 2017 1
2017-11-24 00:00:00.000 11706 91 50 2017 1
2017-11-25 00:00:00.000 11706 99 50 2017 1
2017-11-26 00:00:00.000 11706 45 50 2017 1
2017-11-27 00:00:00.000 11706 57 50 2017 1
2017-11-28 00:00:00.000 11706 128 50 2017 1
2017-11-29 00:00:00.000 11706 66 50 2017 1
2017-11-30 00:00:00.000 11706 336 50 2017 1
2017-12-01 00:00:00.000 11706 294 50 2017 1
2017-12-02 00:00:00.000 11706 276 50 2017 1
2017-12-03 00:00:00.000 11706 117 50 2017 1
2017-12-04 00:00:00.000 11706 141 50 2017 1
2017-12-05 00:00:00.000 11706 107 50 2017 1
2017-12-06 00:00:00.000 11706 64 50 2017 1
27 июн 18, 17:29    [21525905]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
для проверки тут уже 2 страты
11511 5 2017
и
11706 50 2017
27 июн 18, 19:15    [21526285]     Ответить | Цитировать Сообщить модератору
 Re: Замена выбросов средними значениями дней недели в sql  [new]
Kontox
Member

Откуда:
Сообщений: 146
для удобства ввел сам данные в проверяющую панель
http://www.sqlfiddle.com/#!18/6b2ef
27 июн 18, 20:31    [21526524]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить