SQL AVG(COUNT(*))?

Question

我将尝试找出列中一个值平均出现的次数，基于另一列进行分组，然后执行计算。

我有三个表格，类似于这样：

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。 我要列出所有在Loan表中出现次数少于该数字的DVD（和其他每个）的副本。

希望这有点更清晰了...

谢谢

- Dan

---

在你的例子中，应该返回什么结果数据集？对于DVDID=1，它应该输出2，而对于其他两个，则应该输出1吗？ - Mark Pim

因为说“一个值在一列中出现的平均次数”没有任何意义。它出现的次数就是它出现的次数；你不能对一个值求平均值。 - Mark Pim

抱歉，我有点迷糊了！我的意思是我想找出每张DVD副本在借阅表中出现的平均次数。 - Dan

@Dan：这还是不够清晰。请编辑您的问题并展示您想要的结果的示例。 - Bill Karwin

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;

- Dave Costa

---

最近我看到了这种“with”语法。这是标准的SQL还是只有在Oracle中才有？ - Joe Phillips

这是 ANSI SQL 标准的一部分。 - Dave Costa

Answer 2

这应该在Oracle中可行：

create view dvd_count_view
select dvdid, count(1) as howmanytimes
  from loans
 group by dvdid;

select avg(howmanytimes) from dvd_count_view;

- Pablo Santa Cruz

Answer 3

未经测试...

with 
loan_copy_total as 
(
    select dvdid, copyid, count(*) as cnt
    from loan
    group by dvdid, copyid
),
loan_copy_avg as
(
    select dvdid, avg(cnt) as copy_avg
    from loan_copy_total
    group by dvdid
)

select lct.*, lca.copy_avg
from loan_copy_avg lca
inner join loan_copy_total lct on lca.dvdid = lct.dvdid
    and lct.cnt <= lca.copy_avg; 

- dotjoe