SQL：计算组内高于平均值的值的数量

Question

如何使用SQL统计高于一组平均值的值？

比如：

我有一个表A，其中包含：

q   t
1   5
1   6
1   2
1   8
2   6
2   4
2   3   
2   1

第一组的平均值是5.25。该组中有两个比平均值高的数，分别为8和6；因此，该组高于平均值的值的数量为2。

第二组的平均值是3.5。该组中有两个比平均值高的数，分别为5和6；因此，该组高于平均值的值的数量为2。

- Topa Denis

---

你正在使用哪种关系型数据库管理系统？ - ughai

我正在使用Microsoft SQL和SQL Management Studio。 - Topa Denis

值是否严格高于平均值？那么一组 1,2,3（平均值为 2）只报告一个值——3？ - Damien_The_Unbeliever

是的，它严格地更高，应该返回高于平均值的值的数量。 - Topa Denis

Answer 1

试试这个：

 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

在子查询中，您将计算两个组的平均值，将其与您的表连接，然后选择从您的表中所有值大于平均值的值的计数。

- Nightmaresux

Answer 2

我的回答与其他回答非常相似，只是平均值是通过将乘法与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代码示例。

这个查询应该可以在最常见的RDBMS系统（SQL Server、Oracle、Postgres）上工作。

- Radu Gheorghiu

Answer 3

这也应该可以工作：

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

- Nico Weisenauer

Answer 4

你可以像这样使用 AVG(t) OVER(PARTITION BY)。 SQL Fiddle

查询

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 |

- ughai