SQL 平均（计数（*））？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/800347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL AVG(COUNT(*))?
提问by
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.
我试图找出一个值在列中出现的平均次数，根据另一列对其进行分组，然后对其进行计算。
I have 3 tables a little like this
我有 3 张桌子，有点像这样
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
etc
等等
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.
基本上，我试图找到出现在借出表中的所有副本 ID，其次数少于该 DVD 的所有副本的平均次数。
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.
因此，在上面的示例中，dvd 1 的副本 5 出现了 3 次，副本 2 两次，副本 1 一次，因此该 DVD 的平均值为 2。贷款表中的那个数字。
I hope that makes a bit more sense...
我希望这更有意义...
Thanks
谢谢
回答by Pablo Santa Cruz
This should work in Oracle:
这应该适用于 Oracle：
create view dvd_count_view
select dvdid, count(1) as howmanytimes
from loans
group by dvdid;
select avg(howmanytimes) from dvd_count_view;
回答by Dave Costa
Similar to dotjoe's solution, but using an analytic function to avoid the extra join. May be more or less efficient.
类似于 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;
回答by dotjoe
Untested...
未经测试...
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;
