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

Откуда:
Сообщений: 226
Приветствую, Товарищи

Задание. Найти среднее значение всех зарплат, без учета их максимального и минимального значения.

Сделал так. через MIN() MAX() OVER

SELECT AVG(sal)
FROM (SELECT sal, MIN(sal) OVER() min_sal, MAX(sal) OVER() max_sal
FROM emp) x
WHERE sal NOT IN (min_sal, max_sal)
GO


все работает. среднее значение зп выдает верное.
Решил сделать другим способом, посредством функции ROW_NUMBER.

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM emp) x
WHERE rn NOT IN ((SELECT MAX(rn) FROM emp),
(SELECT MIN(rn) FROM emp))

GO

ПО идее я выбираю все начения кроме макс и мин зарплаты. однако запрсо не работает.
подскажите на что обратить внимание, как переделать.
Ошибка в том. что в предикате WHERE нельзя использовать агрегатные функции, поидее можно сделать с HAVING.
24 янв 19, 09:31    [21792888]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

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

Может вам медиана нужна?
24 янв 19, 09:35    [21792892]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

В итоге я отчаялся и сделал

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM emp) x
WHERE rn BETWEEN 2 AND 13
GO

и все работает верно, однако для этого запроса мне нужно посмотреть выборкой какой номер у максимального ранга. то есть потеря времени
24 янв 19, 09:37    [21792896]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

нет, именно среднюю зп без учета максимальной зп во всей таблице и минимальной
24 янв 19, 09:40    [21792898]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

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

нет, именно среднюю зп без учета максимальной зп во всей таблице и минимальной

бредовая сущность, 50 000 уйдёт, а 49999 учитываем
24 янв 19, 09:42    [21792900]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Alibek B.
Member

Откуда:
Сообщений: 3601
Задача глупая, но у нее есть такое же глупое решение.
Среднее — это сумма значений, деленная на количество значений.
Нужный результат — это (сумма значений - min - max) деленная на (количество значений - 2).
24 янв 19, 09:56    [21792926]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
msLex
Member

Откуда:
Сообщений: 8091
Alibek B.
Задача глупая, но у нее есть такое же глупое решение.
Среднее — это сумма значений, деленная на количество значений.
Нужный результат — это (сумма значений - min - max) деленная на (количество значений - 2).


Нет, т.к. записей с максимальным и минимальным значением может быть больше чем по одной.
24 янв 19, 10:01    [21792936]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
dermama
dermama,

В итоге я отчаялся и сделал

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM emp) x
WHERE rn BETWEEN 2 AND 13
GO

и все работает верно, однако для этого запроса мне нужно посмотреть выборкой какой номер у максимального ранга. то есть потеря времени
Так сделайте 2 ROW_NUMBER:
SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, ROW_NUMBER() OVER(ORDER BY sal desc) rm
     FROM emp) x
WHERE rn > 1 AND rm > 1 


TaPaK
бредовая сущность, 50 000 уйдёт, а 49999 учитываем
В принципе это широко используемое в статистической науке удаление верхних и нижних выбросов, хотя делают это обычно в более общем виде, а не "удаляя одно значение".
И используется такое редко, всё таки медианное значение для подобных целей подходит лучше.
24 янв 19, 10:02    [21792939]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

В моем случае не так, однако логика вернаю

sal
800
950
1100
1250
1250
1300
1500
1600
2450
2850
2975
3000
3000
5000
24 янв 19, 10:03    [21792940]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

нормально, я бы сам не додумался до такого решения(
24 янв 19, 10:06    [21792945]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
dermama
Найти среднее значение всех зарплат, без учета их максимального и минимального значения.
SELECT (SUM(salary)-MIN(salary)-MAX(salary))/(COUNT(salary)-2)

dermama
все работает. среднее значение зп выдает верное.

Насколько верно получаемое значение, если 2 или более записей имеют равное макс. или мин. значение?
24 янв 19, 10:09    [21792947]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Топ топ в подзапросах
Правда совершенно непонятно, вам надо исключить все максимальные-минимальные значения или только по одноой записи?

Селект среднее
Из
Если оно не равно (максимальное из/ топ 1 ордер desc) и не равно (минимальное из/ топ 1 ордер asc)
24 янв 19, 10:11    [21792949]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

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

и как с дублями будет жить?

; WITH t as (SELECT MIN(a) as Vmin,MAX(a) as  VMax FROM @a)
SELECT 	
	AVG(a)
FROM @a
CROSS JOIN t
WHERE a > vMin AND a < VMax
24 янв 19, 10:12    [21792952]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
Akina
dermama
Найти среднее значение всех зарплат, без учета их максимального и минимального значения.
SELECT (SUM(salary)-MIN(salary)-MAX(salary))/(COUNT(salary)-2)

dermama
все работает. среднее значение зп выдает верное.

Насколько верно получаемое значение, если 2 или более записей имеют равное макс. или мин. значение?

дак функция ROW_number дает уникальные значения для каждой зарплаты, не важно, сколько одинаковых значений зараплат у меня в итоговом множестве
24 янв 19, 10:15    [21792958]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
TaPaK
alexeyvg,

и как с дублями будет жить?

; WITH t as (SELECT MIN(a) as Vmin,MAX(a) as  VMax FROM @a)
SELECT 	
	AVG(a)
FROM @a
CROSS JOIN t
WHERE a > vMin AND a < VMax
Я показал, как модифицировать запрос ТС, логика которого ТС подходит.
А то, что его запрос противоречит его же постановке задачи, это уже другое дело :-)

Для сформулированной задачи, конечено, правильным будет ваш вариант.
24 янв 19, 10:16    [21792960]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
dermama
Akina
пропущено...
SELECT (SUM(salary)-MIN(salary)-MAX(salary))/(COUNT(salary)-2)

пропущено...

Насколько верно получаемое значение, если 2 или более записей имеют равное макс. или мин. значение?

дак функция ROW_number дает уникальные значения для каждой зарплаты, не важно, сколько одинаковых значений зараплат у меня в итоговом множестве


Тьфу, не туда смотрел, всеравно MAX, MIN () OVER() просто находит мне макс и мин результаты по всему множеству, и уже тоже без разницы, сколько там одинаковых значений, ну как я понимаю
24 янв 19, 10:17    [21792961]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
dermama
dermama
пропущено...

дак функция ROW_number дает уникальные значения для каждой зарплаты, не важно, сколько одинаковых значений зараплат у меня в итоговом множестве


Тьфу, не туда смотрел, всеравно MAX, MIN () OVER() просто находит мне макс и мин результаты по всему множеству, и уже тоже без разницы, сколько там одинаковых значений, ну как я понимаю


хотяяя, необходимо проверить, так сразу не смогу сказать, я изучаю
24 янв 19, 10:17    [21792963]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
dermama
Akina
пропущено...
SELECT (SUM(salary)-MIN(salary)-MAX(salary))/(COUNT(salary)-2)


пропущено...

Насколько верно получаемое значение, если 2 или более записей имеют равное макс. или мин. значение?

дак функция ROW_number дает уникальные значения для каждой зарплаты, не важно, сколько одинаковых значений зараплат у меня в итоговом множестве
функция ROW_number просто нумерует записи

Вот если есть зарплаты

100
100
90
80
70
60

То запрос с ROW_number уберёт только верхнюю и нажнюю строчки, и среднее будет считаться по
100
90
80
70

А нужно, наверное, так?
90
80
70
24 янв 19, 10:19    [21792965]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
alexeyvg
dermama
пропущено...

дак функция ROW_number дает уникальные значения для каждой зарплаты, не важно, сколько одинаковых значений зараплат у меня в итоговом множестве
функция ROW_number просто нумерует записи

Вот если есть зарплаты

100
100
90
80
70
60

То запрос с ROW_number уберёт только верхнюю и нажнюю строчки, и среднее будет считаться по
100
90
80
70

А нужно, наверное, так?
90
80
70


Проверил свой запрос с MIN MAX () OVER счиатем верно, находит среднеее значение 240 на 3 = 80
24 янв 19, 10:27    [21792978]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
dermama
alexeyvg
пропущено...
функция ROW_number просто нумерует записи

Вот если есть зарплаты

100
100
90
80
70
60

То запрос с ROW_number уберёт только верхнюю и нажнюю строчки, и среднее будет считаться по
100
90
80
70

А нужно, наверное, так?
90
80
70


Проверил свой запрос с MIN MAX () OVER счиатем верно, находит среднеее значение 240 на 3 = 80


с ROW_NUMBER не првоерял, но по идее, да, по моей логике он только верхнее и нижнее значение откинет, а такоеже значение с самой большой зарплатой он оставит и я получу не верное значение..
24 янв 19, 10:29    [21792984]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
dermama
по идее, да, по моей логике он только верхнее и нижнее значение откинет, а такоеже значение с самой большой зарплатой он оставит и я получу не верное значение..
Тогда используйте вариант:
TaPaK
; WITH t as (SELECT MIN(a) as Vmin,MAX(a) as  VMax FROM @a)
SELECT 	
	AVG(a)
FROM @a
CROSS JOIN t
WHERE a > vMin AND a < VMax
24 янв 19, 10:33    [21792990]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
alexeyvg
Вот если есть зарплаты

100
100
90
80
70
60

То запрос с ROW_number уберёт только верхнюю и нажнюю строчки, и среднее будет считаться по
100
90
80
70

А нужно, наверное, так?
90
80
70


если нужно последнее, то меняем Row_Number() на rank() (или dense_rank())
24 янв 19, 10:37    [21793002]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
iap
Member

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

посмотрите это (прямо по теме - часть 1):





24 янв 19, 10:45    [21793017]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

всем большое спасибо, проблема с одинаковыми макс или мин значениемя поможет решить RANK так как он одинаковым значениям присвоит одинаковый ранг, чего не делает ROW_number, видео посмотрю после работы.

Однако, у меня остался вопрос, как все таки решить именно МОЙ пример через ROW_NUMBER и с обязательным условием WHERE

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM emp) x
WHERE rn NOT IN ((SELECT MAX(rn) FROM emp),
(SELECT MIN(rn) FROM emp))

GO
ТО есть как сделать с NOT IN
тоесть как изменить WHERE при этом во вложенном запросе не использовать еще один ROW_NUMBER , я имею ввиду следующий запрос

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, ROW_NUMBER() OVER(ORDER BY sal desc) rm
FROM emp) x
WHERE rn > 1 AND rm > 1
GO
24 янв 19, 11:18    [21793069]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
dermama
с ROW_NUMBER не првоерял, но по идее, да, по моей логике он только верхнее и нижнее значение откинет, а такоеже значение с самой большой зарплатой он оставит и я получу не верное значение..

Скажите, какой ответ должен быть для таких данных:

100
100
90
85
85
70

Варианты ответов:

91.67
90
86.67
87.5
другое значение
24 янв 19, 11:20    [21793071]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить