是否有一种方法来计算基于不同行的平均值，而不使用子查询?

Question

如果有这样的数据:

+----+-------+
| id | value |
+----+-------+
|  1 |    10 |
|  1 |    10 |
|  2 |    20 |
|  3 |    30 |
|  2 |    20 |
+----+-------+

如何在不使用子查询(即直接查询表)的情况下计算基于不同id的平均值?

对于上面的例子，它将是(10+20+30)/3 = 20

我试着做以下事情:

SELECT AVG(IF(id = LAG(id) OVER (ORDER BY id), NULL, value)) AS avg
FROM table

基本上我在想，如果我按id排序并检查前一行，看看它是否具有相同的id，值应该是NULL，因此它不会被计算在内，但不幸的是，我不能把分析函数放在聚合函数内。

Answer 1

据我所知，没有子查询就无法做到这一点。我会使用:

SELECT AVG(avg_value)
FROM
(
SELECT AVG(value) AS avg_value
FROM yourTable
GROUP BY id
) t;

Answer 2

WITH RANK AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY ID) AS RANK
FROM 
TABLE
QUALIFY RANK = 1
)
SELECT 
AVG(VALUES)
FROM RANK

Answer 3

外部查询将有其他需要访问表中所有数据的参数

我把这个注释解释为想要对每一行取平均值——而不是做一个聚合。如果是这样，可以使用窗口函数:

select t.*,
avg(case when seqnum = 1 then value end) over () as overall_avg
from (select t.*,
row_number() over (partition by id order by id) as seqnum
from t
) t;

Answer 4

是有办法的，只需在avg函数中使用distinct，如下所示:

select avg(distinct value) from tab;

http://sqlfiddle.com/!4/9d156/2/0