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

Откуда:
Сообщений: 1
Даны 2 таблицы :
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


Говорится, что выкидышами (Outliers) считаются значения которые лежат ниже, либо выше 3 стандартных отклонений от среднего значения ( < AVG() - 3 * STDDEV(), либо > AVG() + 3 * STDDEV()

Для каждого кастомера нужно найти 5 наиболее недавних дат, для которых были характерны ненормальные значения (выкидыши) в объемах заказа (amount), и для каждой из этих дат ввывести также минимальное и максимальное значения amount. Так чтобы возможный результат получался примерно в таком виде (см. аттачмент).
http://triontp.pop3.ru/333.gif

К сообщению приложен файл. Размер - 15Kb
5 ноя 14, 22:56    [16803249]     Ответить | Цитировать Сообщить модератору
 Re: Найти 5 наиболее недавних записей с "ненормальными" заказами  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
jbionic,

Исходные данные нам самим предлагается придумать и в таблички вбить?
6 ноя 14, 02:23    [16803869]     Ответить | Цитировать Сообщить модератору
 Re: Найти 5 наиболее недавних записей с "ненормальными" заказами  [new]
Crimzic
Member

Откуда: Sydney
Сообщений: 59
jbionic,

Вот одно из возможных решений:
SELECT c.customer_name, P.order_date, P.min_amt, P.max_amt
FROM 
	(SELECT po.customer_id, po.order_date, 
		min_amt = MIN(po.amount), 
		max_amt = MAX(po.amount)
	FROM ( SELECT customer_id, 
				lowerAmountBoundary = AVG(amount) - 3*STDEV(amount), 
				upperAmountBoundary = AVG(amount) - 3*STDEV(amount)
			FROM dbo.purchase_order po
			GROUP BY customer_id) B
		INNER JOIN dbo.purchase_order po ON po.customer_id = B.customer_id
	WHERE EXISTS(SELECT * FROM dbo.purchase_order po1 WHERE po1.customer_id = po.customer_id AND po1.order_date = po.order_date AND NOT(po1.amount BETWEEN B.lowerAmountBoundary AND B.upperAmountBoundary))
	GROUP BY po.customer_id, po.order_date) P
INNER JOIN dbo.customer c ON c.customer_id = P.customer_id

Можно попытаться уменьшить обращение к таблице purchase_order при помощи OVER (PARTITION BY ...), если ещё немного подумать.
Также у вас в таблице нет индексов кроме Primary Key. Не мешало бы добавить на purchase_order (customer_id, order_date) include (amount) и, возможно, purchase_order (customer_id) include (amount) (нужно тестировать на ваших данных).
6 ноя 14, 02:26    [16803872]     Ответить | Цитировать Сообщить модератору
 Re: Найти 5 наиболее недавних записей с "ненормальными" заказами  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
jbionic,

наиболее недавних - самых свежих - самых ближайших
6 ноя 14, 10:17    [16804655]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить