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

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

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

100
100
90
85
85
70

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

91.67
90
86.67
87.5
другое значение


ответ (90+85+85)/3 = 86.6666667
24 янв 19, 11:23    [21793076]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

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

100
100
90
85
85
70

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

91.67
90
86.67
87.5
другое значение


ответ (90+85+85)/3 = 86.6666667


ну если иметь ввиду, что я самое большое и самое мальенькое значение не учитываю
24 янв 19, 11:24    [21793077]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

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

100
100
90
85
85
70

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

91.67
90
86.67
87.5
другое значение


ответ (90+85+85)/3 = 86.6666667


а если бы учитывалось еще и одно из двух значений 100, то 90
24 янв 19, 11:27    [21793080]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
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


а по какой причине было принято решение использовать рекурсивный оператор WITH, в каких случаях его нужно использовать?
24 янв 19, 11:37    [21793089]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
dermama
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


а по какой причине было принято решение использовать рекурсивный оператор WITH, в каких случаях его нужно использовать?

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

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


а по какой причине было принято решение использовать рекурсивный оператор WITH, в каких случаях его нужно использовать?

рекурсивный оператор.... запишу


обобщенное табличное выражение.
24 янв 19, 11:42    [21793101]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
dermama,

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, count(*) over () as max_rn
 FROM emp) x
WHERE rn NOT IN (max_rn,1)
24 янв 19, 11:47    [21793111]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, count(*) over () as max_rn
 FROM emp) x
WHERE rn NOT IN (max_rn,1)


блин, это гениально, спасибо.
24 янв 19, 11:49    [21793112]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Kopelly
dermama,
SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, count(*) over () as max_rn
 FROM emp) x
WHERE rn NOT IN (max_rn,1)

тоже мимо, следующий :)

используйте когда хотите, не нравиться перенесите в CROSS
24 янв 19, 11:49    [21793113]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
TaPaK
Kopelly
dermama,
SELECT AVG(SAL) avg_sal
FROM(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn, count(*) over () as max_rn
 FROM emp) x
WHERE rn NOT IN (max_rn,1)

тоже мимо, следующий :)

используйте когда хотите, не нравиться перенесите в CROSS


почему мимо? просто ROW_NUMBER на RANK заменить,

Тапак, подскажи пожалуйста, для чего необходим Cross join?
24 янв 19, 11:53    [21793118]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

тоже мимо, следующий :)

используйте когда хотите, не нравиться перенесите в CROSS


почему мимо? просто ROW_NUMBER на RANK заменить,

Тапак, подскажи пожалуйста, для чего необходим Cross join?


в рамках данной задачи конечно
24 янв 19, 11:54    [21793119]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

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

тоже мимо, следующий :)

используйте когда хотите, не нравиться перенесите в CROSS


почему мимо? просто ROW_NUMBER на RANK заменить,

Тапак, подскажи пожалуйста, для чего необходим Cross join?

это точно также исключает только одно максимальное и минимальное значение
24 янв 19, 11:55    [21793121]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
msLex
Member

Откуда:
Сообщений: 8091
dermama
почему мимо? просто ROW_NUMBER на RANK заменить,

нельзя
т.к.

count(*) = max(row_numver), но != max(rank)
24 янв 19, 11:56    [21793122]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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


почему мимо? просто ROW_NUMBER на RANK заменить,

Тапак, подскажи пожалуйста, для чего необходим Cross join?

это точно также исключает только одно максимальное и минимальное значение


SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM asd

sal rn
60 1
70 2
80 3
90 4
100 5
100 6

SELECT sal, RANK() OVER(ORDER BY sal desc) rn
FROM asd

sal rn
100 1
100 1
90 3
80 4
70 5
60 6

если выбрать .....попробовал сделать такого плана запрос
SELECT sal, MAX(rn) dd
FROM (SELECT sal, RANK() OVER(ORDER BY sal desc) rn from asd) x
GROUP BY sal

я в итоге всеравно получаю 100 в итоговом множестве....я совсем запутался, правда.
24 янв 19, 12:02    [21793131]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

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

это точно также исключает только одно максимальное и минимальное значение


SELECT sal, ROW_NUMBER() OVER(ORDER BY sal) rn
FROM asd

sal rn
60 1
70 2
80 3
90 4
100 5
100 6

SELECT sal, RANK() OVER(ORDER BY sal desc) rn
FROM asd

sal rn
100 1
100 1
90 3
80 4
70 5
60 6

если выбрать .....попробовал сделать такого плана запрос
SELECT sal, MAX(rn) dd
FROM (SELECT sal, RANK() OVER(ORDER BY sal desc) rn from asd) x
GROUP BY sal

я в итоге всеравно получаю 100 в итоговом множестве....я совсем запутался, правда.
''

Пожалуйста, не читайте мое сообщение., я сам понял, что написал бред, запрос делает все верно, я реализовал не то, что хотел...
24 янв 19, 12:04    [21793137]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
dermama
ответ (90+85+85)/3 = 86.6666667
Ага... то есть отбросить не максимальную (минимальную) зарплату, а все зарплаты с максимальным (минимальным) значением.
Иными словами, для макс/мин значений дубликаты "схлопывать", тогда как для оставшихся значений - нет.
dermama
а если бы учитывалось еще и одно из двух значений 100, то 90

Знаете, у Вас как постановщика есть вполне конкретная задача - и от Вас не должно по идее поступать никаких "если бы", "может быть" и т.п. Это мы можем (и, к сожалению, нам приходится) гадать...
24 янв 19, 12:10    [21793146]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
Akina
dermama
ответ (90+85+85)/3 = 86.6666667
Ага... то есть отбросить не максимальную (минимальную) зарплату, а все зарплаты с максимальным (минимальным) значением.
Иными словами, для макс/мин значений дубликаты "схлопывать", тогда как для оставшихся значений - нет.
dermama
а если бы учитывалось еще и одно из двух значений 100, то 90

Знаете, у Вас как постановщика есть вполне конкретная задача - и от Вас не должно по идее поступать никаких "если бы", "может быть" и т.п. Это мы можем (и, к сожалению, нам приходится) гадать...


В таком случае мы зря затеяли котовасию с дубликатами? и если в таблице есть много одинаковых значений, при этом все они максимальные, то следовать необходимо условию задачи и игнорировать дубликаты, так как они не максимальные уже значения, а лишь дублирующие друг друга, хотя понятие максимальное , если пользоваться функция ранжирования, они как раз таки и получают за счет присвоения ранга... поэтому дубликаты стоит игнорировать)))
24 янв 19, 12:14    [21793155]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
dermama
Akina
пропущено...
Ага... то есть отбросить не максимальную (минимальную) зарплату, а все зарплаты с максимальным (минимальным) значением.
Иными словами, для макс/мин значений дубликаты "схлопывать", тогда как для оставшихся значений - нет.
пропущено...

Знаете, у Вас как постановщика есть вполне конкретная задача - и от Вас не должно по идее поступать никаких "если бы", "может быть" и т.п. Это мы можем (и, к сожалению, нам приходится) гадать...


В таком случае мы зря затеяли котовасию с дубликатами? и если в таблице есть много одинаковых значений, при этом все они максимальные, то следовать необходимо условию задачи и игнорировать дубликаты, так как они не максимальные уже значения, а лишь дублирующие друг друга, хотя понятие максимальное , если пользоваться функция ранжирования, они как раз таки и получают за счет присвоения ранга... поэтому дубликаты стоит игнорировать)))


либо избегать использования ранжирующих функций.....
24 янв 19, 12:16    [21793159]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Помогите Даше определится с ТЗ (с)
24 янв 19, 12:20    [21793169]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
dermama
обобщенное табличное выражение.
Просто такая форма записи. Можно то же самое записать подзапросом.
24 янв 19, 12:30    [21793186]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
dermama
Member

Откуда:
Сообщений: 226
alexeyvg
dermama
обобщенное табличное выражение.
Просто такая форма записи. Можно то же самое записать подзапросом.


понятно, спасибо.
24 янв 19, 12:32    [21793195]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
Oleg_SQL
Member

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

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

Сделал так. через 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, 13:22    [21793287]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Oleg_SQL
Так у Вас считается макс и мин от всех сотрудников. А нужно для каждого сотрудника отдельно их отсечь, а уж из остатка считать..

Новый виток... высечь сотрудников
24 янв 19, 13:23    [21793288]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
a_voronin
Member

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

А не проще ли сделать самое простое?

(SUM() - MIN() - MAX())/(COUNT() - 2)

Про проверку на деление на 0 не забыть
24 янв 19, 14:43    [21793400]     Ответить | Цитировать Сообщить модератору
 Re: Среднее значения без учета макс и мин!  [new]
a_voronin
Member

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

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

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



SELECT (SUM(salary)-MIN(salary)-MAX(salary))/(NULLIF(COUNT(salary), 2)-2)
24 янв 19, 14:45    [21793403]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить