SQL 平均(计数(*))?
我试图找出一个值在列中出现的平均次数，根据另一列对其进行分组，然后对其进行计算.
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;
相关文章