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

Откуда: Питер
Сообщений: 63
Очень хитрое задание:
Есть таблица вида

Id
Parent_id
Order_date
Amount

Имя таблицы purchase

Для каждого parent_id надо вывести не более пяти записей выходящих за 3 "сигмы" (ф-ция stdev, среднеквадратичное отклонение) и минимальную с максимальной суммы для данной даты

Базовый запрос вышел таким:
Select t.parent_id, t.order_date, min(pr.amount) mina, max(pr.amount) maxa
From purchase t
Join purchase pr on t.order_date= pr.order
Where abs(t.amount - (select avg(t2.amount) from purchase t2)) > (3 * (select stdev(t3.amount) from purchase t3))


1. Правильно ли составлен запрос? Или надо какую-то другую величину сравнивать с 3*сигма?
2. Как реализовать вывод не более 5 значений для каждого из parent_id? Top или ему подобные (limit, rows) выдадут не то (5 "верхних", а не 5 из каждой группы)
15 ноя 13, 14:59    [15136744]     Ответить | Цитировать Сообщить модератору
 Re: поиск нетипичных данных  [new]
qwerty112
Guest
Kotetsu
1. Правильно ли составлен запрос? Или надо какую-то другую величину сравнивать с 3*сигма?

1006908 п.6
Kotetsu
2. Как реализовать вывод не более 5 значений для каждого из parent_id? Top или ему подобные (limit, rows) выдадут не то (5 "верхних", а не 5 из каждой группы)

пронумеровать
row_number()over(partition by parent_id order by ???) as rn
и вывести во внешнем запросе с условием rn<=5

или

...top 1 with ties ...
...
order by (row_number()over(partition by parent_id order by ???)-1)/5

вроде так...
15 ноя 13, 15:34    [15137190]     Ответить | Цитировать Сообщить модератору
 Re: поиск нетипичных данных  [new]
iap
Member

Откуда: Москва
Сообщений: 47107
Kotetsu
Очень хитрое задание:
Есть таблица вида

Id
Parent_id
Order_date
Amount

Имя таблицы purchase

Для каждого parent_id надо вывести не более пяти записей выходящих за 3 "сигмы" (ф-ция stdev, среднеквадратичное отклонение) и минимальную с максимальной суммы для данной даты

Базовый запрос вышел таким:
Select t.parent_id, t.order_date, min(pr.amount) mina, max(pr.amount) maxa
From purchase t
Join purchase pr on t.order_date= pr.order
Where abs(t.amount - (select avg(t2.amount) from purchase t2)) > (3 * (select stdev(t3.amount) from purchase t3))


1. Правильно ли составлен запрос? Или надо какую-то другую величину сравнивать с 3*сигма?
2. Как реализовать вывод не более 5 значений для каждого из parent_id? Top или ему подобные (limit, rows) выдадут не то (5 "верхних", а не 5 из каждой группы)
А есть ещё STDEVP
STDEV и STDEVP в BOL не нашел формул
15 ноя 13, 15:47    [15137297]     Ответить | Цитировать Сообщить модератору
 Re: поиск нетипичных данных  [new]
Kotetsu
Member

Откуда: Питер
Сообщений: 63
Так. Косякнул в запросе
 Select t.parent_id, t.order_date, min(pr.amount) mina, max(pr.amount) maxa From purchase t Join purchase pr on t.order_date= pr.order_date Where abs(t.amount - (select avg(t2.amount) from purchase t2)) > (3 * (select stdev(t3.amount) from purchase t3)) 

Там второй раз ordrer_date должно быть
Структура таблицы -
id - int, not null. То же с parent_id
order_date - date
Исходных нет, есть примерный результат
parent_id order_date mina maxa
1 1/10/2011 10082 32041
1 2/5/2011 10047 33229
1 3/19/2011 5 30526
1 3/25/2011 10027 36804
1 3/29/2011 10147 33545
2 1/2/2011 10000 34674
2 1/5/2011 10024 33700
15 ноя 13, 16:31    [15137802]     Ответить | Цитировать Сообщить модератору
 Re: поиск нетипичных данных  [new]
Kotetsu
Member

Откуда: Питер
Сообщений: 63
полный запрос, для тех, кому интересно:
with 
dev(tdev,sigma) as
((select 1,stdev(devcalc.amount) from dbo.purchase_order devcalc))
----------------------------------------------------------------
, avg_res(tabs, amount_mid) as
(select 1,avg(calc.amount) from dbo.purchase_order calc)
----------------------------------------------------------------
,res (customer, order_date, Row, mina, maxa)
as(
Select cus.customer_name, t.order_date, 
ROW_NUMBER() OVER(PARTITION BY t.customer_id ORDER BY t.amount) AS Row
,min(pr.amount) mina, max(pr.amount) maxa 
From dbo.purchase_order t
Join dbo.purchase_order pr on t.order_date= pr.order_date
join dbo.customer cus on cus.customer_id = t.customer_id
join dev on tdev = 1
join avg_res on tabs = 1
where 
abs(t.amount - amount_mid) 
> 
(sigma * 3)
group by t.purchase_order_id, t.customer_id, cus.customer_name, t.order_date, t.amount
)
-----------------------------------------------------------------
select customer, order_date, Row, mina, maxa
from res where Row <= 5
;


таблицы :
create table dbo.customer	(
customer_id	int		identity primary key clustered
		,	customer_name	nvarchar(256)	not null
)

create table dbo.purchase_order	(
			purchase_order_id	int	identity primary key clustered
		,	customer_id		int	not null
		,	amount			money	not null
		,	order_date		date	not null
)
16 ноя 13, 15:46    [15141444]     Ответить | Цитировать Сообщить модератору
 Re: поиск нетипичных данных  [new]
Kotetsu
Member

Откуда: Питер
Сообщений: 63
тему можно закрывать
16 ноя 13, 15:47    [15141445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить