問題描述

我試圖找出一個值在列中出現的平均次數，根據另一列對其進行分組，然后對其進行計算.

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 平均(計數(*))?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！