SQL 平均(计数(*))？

Question

我试图找出一个值在列中出现的平均次数，根据另一列对其进行分组，然后对其进行计算。

我有 3 张 table ，有点像这样

DVD

ID | NAME
1  | 1       
2  | 1     
3  | 2      
4  | 3

COPY 

ID | DVDID   
1  | 1  
2  | 1  
3  | 2  
4  | 3  
5  | 1

LOAN

ID | DVDID | COPYID  
1  | 1     |  1  
2  | 1     |  2  
3  | 2     |  3    
4  | 3     |  4  
5  | 1     |  5
6  | 1     |  5
7  | 1     |  5
8  | 1     |  2

等等

基本上，我试图找到出现在借出表中的所有副本 ID，其次数少于该 DVD 的所有副本的平均次数。

所以在上面的例子中，dvd 1 的副本 5 出现了 3 次，副本 2 两次，副本 1 一次，因此该 DVD 的平均值是 2。贷款表中的那个数字。

我希望这更有意义...

谢谢

Answer 1

类似于 dotjoe 的解决方案，但使用解析函数来避免额外的连接。可能或多或少有效率。

with 
loan_copy_total as 
(
    select dvdid, copyid, count(*) as cnt
    from loan
    group by dvdid, copyid
),
loan_copy_avg as
(
    select dvdid, copyid, cnt, avg(cnt) over (partition by dvdid) as copy_avg
    from loan_copy_total
)
select *
from loan_copy_avg lca
where cnt <= copy_avg;