Приветствую, Товарищи

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

Сделал так. через 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.

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

и все работает верно, однако для этого запроса мне нужно посмотреть выборкой какой номер у максимального ранга. то есть потеря времени

TaPaK,

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

msLex,

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

sal
800
950
1100
1250
1250
1300
1500
1600
2450
2850
2975
3000
3000
5000

alexeyvg,

нормально, я бы сам не додумался до такого решения(

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

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

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


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

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

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

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

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

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

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

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

дак функция 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

dermamaalexeyvgпропущено...
функция 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 не првоерял, но по идее, да, по моей логике он только верхнее и нижнее значение откинет, а такоеже значение с самой большой зарплатой он оставит и я получу не верное значение..

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