考虑以下Postgresql数据库表:
id | book_id | author_id
---------------------------
1 | 1 | 1
2 | 2 | 1
3 | 3 | 2
4 | 4 | 2
5 | 5 | 2
6 | 6 | 3
7 | 7 | 2
在本例中,作者1写了2本书,作者2写了4本书,而作者3写了1本书。如何确定作者使用SQL编写的平均书籍数量?换言之,我试图得到,"一个作家平均写了2.3本书"。
到目前为止,AVG和COUNT的尝试都失败了。有什么想法吗?
select avg(totalbooks) from
(select count(1) totalbooks from books group by author_id) bookcount
我认为你的示例数据实际上只有3本作者id为2的书,所以这不会返回2.3
http://sqlfiddle.com/#!15/3e36e/1
与第四本书:
http://sqlfiddle.com/#!15/67eac/1
您需要一个子查询。内部查询将count
包含GROUP BY author
的图书;外部查询将扫描内部查询的结果并CCD_ 3它们。
为此,可以在FROM
子句中使用子查询,也可以使用CTE(WITH
表达式)。
对于每个作者的平均图书数量,您可以简单地执行以下操作:
SELECT 1.0*COUNT(DISTINCT book_id)/count(DISTINCT author_id) FROM tbl;
每个作者的图书数量:
SELECT 1.0*COUNT(DISTINCT book_id)/count(DISTINCT author_id)
FROM tbl GROUP BY author_id;
我们需要1.0因子使结果不是整数。
您可以删除DISTINCT,这取决于您想要的结果(只有当一本书有很多作者时才重要)。
正如Craig Ringer正确指出的那样,两个区别可能代价高昂。为了测试性能,我生成了50000行,得到了以下结果:
- 我的2个DISTINCTS查询:~70ms
- 我的1 DISTINCT查询:约40ms
- Martin Booth的方法:约30ms
然后添加了100万行并再次测试:
- 我的2个DISTINCTS查询:~1520ms
- 我的1 DISTINCT查询:~820ms
- Martin Booth的方法:约1060ms
然后又增加了900万行,并再次测试:
- 我的2个DISTINCTS的查询:~17s
- 我的查询有1个DISTINCT:~11s
- 马丁·布斯的方法:~19s
因此,没有一个通用的解决方案。
这应该有效:
SELECT AVG(cnt) FROM (
SELECT COUNT(*) cnt FROM t
GROUP BY author_id
) s