SQL: количество значений выше среднего для группы

Question

SQL: количество значений выше среднего для группы

Как можно использовать SQL для подсчета значений выше среднего по группе?

Например:

У меня есть таблица A с:

Среднее значение для группы 1 составляет 5,25. Есть два значения выше 5,25 в группе, 8 и 6; таким образом, количество значений, которые выше среднего для группы, равно 2.

Среднее значение для группы 2 равно 3,5. Есть два значения выше 3,5 в группе, 5 и 6; таким образом, количество значений, которые выше среднего для группы, равно 2.

1230 4

sql average

4 ответов:

Comments

Ничего не найдено.

Nightmaresux · Accepted Answer · 2015-06-25 17:31:46

Попробуйте это :
 select count (*) as countHigher,a.q from yourtable a join 

 (select AVG(t) as AvgT,q from yourtable a group by q) b on a.q=b.q

 where a.t > b.AvgT

 group by a.q
В подзапросе вы подсчитаете среднее значение для обеих групп, соедините его в таблице, а затем выберете count of all values from your table, где значение больше среднего

Radu Gheorghiu · Accepted Answer · 2015-06-25 17:43:18

Мой ответ очень похож на другие ответы, за исключением того, что среднее вычисляется с десятичными значениями путем сложения умножения с 1.0.
Это не оказывает отрицательного влияния на значения, но делает неявное преобразование из целого числа в плавающее значение, так что сравнение выполняется с 5.25 для первой группы, 3.5 для второй группы, вместо 5 и 4 соответственно.
SELECT count(test.q) GroupCount
    ,test.q Q
FROM test
INNER JOIN (
    SELECT q
        ,avg(t * 1.0) averageValue
    FROM test
    GROUP BY q
    ) result ON test.q = result.q
WHERE test.t > result.averageValue
GROUP BY test.q
Здесь есть рабочий SQLFiddle этого кода.

Это запрос должен работать на наиболее распространенных системах СУБД (SQL Server, Oracle, Postgres).

Nico Weisenauer · Accepted Answer · 2015-06-25 18:06:42

Это также должно работать:

SELECT t1.name, COUNT(t1.value)
FROM table t1
WHERE t1.value >
  (SELECT AVG(value) FROM table t2
   WHERE t1.name=t2.name)
GROUP BY t1.name

ughai · Accepted Answer · 2015-06-25 17:31:27

Вы можете использовать AVG(t) OVER(PARTITION BY) Вот так. скрипка SQL

Запрос
SELECT COUNT(*)OVER(PARTITION BY q) overcount,q,t 
FROM
(
SELECT q,t,AVG(t)OVER(PARTITION BY q) grp_avg FROM o
)C
WHERE t > grp_avg;
Вывод
| overcount | q | t |
|-----------|---|---|
|         2 | 1 | 6 |
|         2 | 1 | 8 |
|         2 | 2 | 6 |
|         2 | 2 | 4 |