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

Откуда:
Сообщений: 11
Здравствуйте. Помогите, пожалуйста, найти решение следующей проблемы:
Нужно скриптом исправить цены следующим образом:
Если у одного товара есть цены/цена на 1000 процентов больше или меньше остальных, то исправить эту цену в таблице на среднее от оставшихся цен этого товара.

Пример:
архив цен товара:
1000
2000
3000
10000000
нам нужно исправить 10000000 на среднее от 1000, 2000, 3000

drop table #product
drop table #price

CREATE TABLE #product
(
    ProductID int NOT NULL,
    [Name] varchar(max) NOT NULL
);

CREATE TABLE #price
(
    PriceID int NOT NULL,
	Product int NOT NULL,
	[Value] decimal(12,2) NOT NULL
);

insert into #product (ProductID, [Name])
select 1, 'Товар 1'
union all
select 2, 'Товар 2'

insert into #price (PriceID, Product, [Value])
select 1, 1, 100.20
union all
select 2, 1, 110.20
union all
select 3, 1, 20000.20
union all
select 4, 2, 10.00
union all
select 5, 2, 20.00
union all
select 6, 2, 10000.20

--мои попытки найти решение
select *
	, max([Value]) over(partition by product) as MAX_
	, min([Value]) over(partition by product) as MIN_
	, round(100 / [value] * max([Value]) over(partition by product),0) as perc_max
	, round(100 / [value] * min([Value]) over(partition by product),0) as perc_min
	, sum([Value]) over(partition by product) - [Value] as SUM_
from #price
28 авг 18, 11:50    [21655927]     Ответить | Цитировать Сообщить модератору
 Re: Исправление некорректно введенных цен товара  [new]
court
Member

Откуда:
Сообщений: 2016
lisischko
Если у одного товара есть цены/цена на 1000 процентов больше или меньше остальных, то исправить эту цену в таблице на среднее от оставшихся цен этого товара.
а если будет, для одного и того же Product 2-а товара и "заниженный" и "завышенный", - они же "поломают" тебе всё "среднее от оставшихся цен этого товара" ...

вот такая ситуация имеется в виду
insert into #price (PriceID, Product, [Value])
select 1, 1, 100.20
union all
select 2, 1, 110.20
union all
select 3, 1, 20000.20
union all
select 4, 2, 10.00
union all
select 5, 2, 20.00
union all
select 6, 2, 10000.20
union all
select 7, 1, 0.20


если всё-таки, речь строго про один товар для каждого Product, то
завышенные
;with maxPrice as (
	select top 1 with ties * from #price order by row_number()over(partition by product order by [Value] desc)),
avgPrice as (
	select Product, avg([Value]) avgPrice, max([Value]) as maxPrice from #price where PriceID not in (select PriceID from maxPrice) group by Product)
	
update a
set a.Value=b.avgPrice  
from maxPrice a inner join avgPrice b on a.Product=b.Product 
where a.Value >= 11.0 * b.maxPrice 	

заниженные
;with minPrice as (
	select top 1 with ties * from #price order by row_number()over(partition by product order by [Value])),
avgPrice as (
	select Product, avg([Value]) avgPrice, min([Value]) as minPrice from #price where PriceID not in (select PriceID from minPrice) group by Product)
	
update a
set a.Value=b.avgPrice  
from minPrice a inner join avgPrice b on a.Product=b.Product 
where a.Value <=  b.minPrice / 11.0
28 авг 18, 12:26    [21655989]     Ответить | Цитировать Сообщить модератору
 Re: Исправление некорректно введенных цен товара  [new]
court
Member

Откуда:
Сообщений: 2016
и, имха, разумнее отталкиваться от медианы, а не от какого-то х.п. "на 1000 процентов больше или меньше остальных"
;with cte as (
	select 
		*
		,mPrice	=PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [Value]) over(partition by Product) 
	from #price),
cte1 as	(
	select 
		*
	from cte
	where Value >= mPrice * 11.0 or Value <= mPrice / 11.0),
cte2 as (
	select 
		Product
		,aPrice	=avg(Value)
	from cte
	where not(Value >= mPrice * 11.0 or Value <= mPrice / 11.0)
	group by Product)

update a
set a.Value = b.aPrice
from cte1 a inner join cte2 b on a.Product=b.Product 
28 авг 18, 12:52    [21656032]     Ответить | Цитировать Сообщить модератору
 Re: Исправление некорректно введенных цен товара  [new]
lisischko
Member

Откуда:
Сообщений: 11
court, спасибо!
Я прочитал про процентиль, ранее с ним не сталкивался, только не пойму зачем Вы далее делите и умножаете на 11.0?
select * from cte where Value >= mPrice * 11.0 or Value <= mPrice / 11.0
28 авг 18, 14:07    [21656215]     Ответить | Цитировать Сообщить модератору
 Re: Исправление некорректно введенных цен товара  [new]
court
Member

Откуда:
Сообщений: 2016
lisischko
только не пойму зачем Вы далее делите и умножаете на 11.0?
это твои "на 1000 процентов больше или меньше"
"на 1000 процентов больше" - это в 11 раз больше, нуу и наоборот
28 авг 18, 14:15    [21656226]     Ответить | Цитировать Сообщить модератору
 Re: Исправление некорректно введенных цен товара  [new]
lisischko
Member

Откуда:
Сообщений: 11
court, :-) Спасибо!
29 авг 18, 08:40    [21657107]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить