SQL 平均(计数(*))? [英] SQL AVG(COUNT(*))?

问题描述

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

I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it.

我有 3 张桌子，有点像这样

I have 3 tables a little like this

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 的所有副本的平均次数.

Basically, I'm trying to find all the copy ids that appear in the loan table LESS times than the average number of times for all copies of that DVD.

因此在上面的示例中，dvd 1 的副本 5 出现了 3 次，副本 2 两次，副本 1 一次，因此该 DVD 的平均值为 2.我想列出该 DVD 的所有副本(以及每个副本)在 Loan 表中出现的数字小于该数字.

So in the example above, copy 5 of dvd 1 appears 3 times, copy 2 twice and copy 1 once so the average for that DVD is 2. I want to list all the copies of that (and each other) dvd that appear less than that number in the Loan table.

我希望这更有意义...

I hope that makes a bit more sense...

谢谢

推荐答案

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

Similar to dotjoe's solution, but using an analytic function to avoid the extra join. May be more or less efficient.

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;

这篇关于SQL 平均(计数(*))?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！