Как определить среднюю сумму столбца в Postgresql?

Рассмотрим следующую таблицу базы данных 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 не увенчались успехом. Есть предположения?


postgresql sql count average
person Huuuze    schedule 04.12.2013    source источник

Ответы (4)


arrow_upward
2
arrow_downward 

select avg(totalbooks) from 
(select count(1) totalbooks from books group by author_id) bookcount

Я думаю, что ваши данные примера на самом деле имеют только 3 книги для идентификатора автора 2, поэтому это не вернет 2.3

http://sqlfiddle.com/#!15/3e36e/1

С 4-й книгой:

http://sqlfiddle.com/#!15/67eac/1

person Martin Booth    schedule 04.12.2013
comment
Вам не нужен подзапрос. Посмотрите в мой ответ. - person Jerzy Pawlikowski; 05.12.2013
comment
Какой дурацкий способ создания данных в SQLFiddle! Вы можете использовать многозначное предложение VALUES, чтобы сделать это гораздо менее болезненным; см. sqlfiddle.com/#!15/9635d/1 - person Craig Ringer; 05.12.2013

arrow_upward
1
arrow_downward

Вам понадобится подзапрос. Внутренний запрос будет count книги с GROUP BY author; внешний запрос просканирует результаты внутреннего запроса и avg их.

Для этого вы можете использовать подзапрос в предложении FROM или использовать CTE (выражение WITH).

person Craig Ringer    schedule 04.12.2013

arrow_upward
1
arrow_downward

Для среднего количества книг на автора вы можете сделать просто:

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 в зависимости от желаемого результата (это важно, только если у одной книги много авторов).

Как правильно заметил Крейг Рингер, 2 разных варианта могут быть дорогими. Для тестовой производительности я сгенерировал 50 000 строк и получил следующие результаты:

  • Мой запрос с 2 DISTINCTS: ~ 70 мс
  • Мой запрос с 1 DISTINCT: ~ 40 мс
  • Подход Мартина Бута: ~30 мс

Затем добавил 1 миллион строк и снова протестировал:

  • Мой запрос с 2 DISTINCTS: ~ 1520 мс
  • Мой запрос с 1 DISTINCT: ~ 820 мс
  • Подход Мартина Бута: ~1060 мс

Затем добавил еще 9 миллионов строк и снова протестировал:

  • Мой запрос с 2 DISTINCTS: ~ 17 с
  • Мой запрос с 1 DISTINCT: ~ 11 с
  • Подход Мартина Бута: ~19 с.

Так что универсального решения нет.

person Jerzy Pawlikowski    schedule 04.12.2013
comment
Интересный подход; он должен избегать узла HashAggregate подхода подзапроса, но будет использовать несколько дорогой count(distinct) для двух независимых ключей. Мне было бы интересно посмотреть, как они сравниваются по производительности на нетривиальном наборе данных. - person Craig Ringer; 05.12.2013
comment
@CraigRinger: Вы правы. Я добавил результаты теста производительности в свой ответ. - person Jerzy Pawlikowski; 05.12.2013
comment
На самом деле я не был уверен, каким путем это пойдет. Кроме того, возможно, медленнее не значит неправильно. Спасибо, что попробовали. - person Craig Ringer; 05.12.2013
comment
Я добавил несколько строк и снова проверил. Кажется, решение должно зависеть от размера таблицы. - person Jerzy Pawlikowski; 05.12.2013

arrow_upward
0
arrow_downward

Это должно работать:

SELECT AVG(cnt) FROM (
  SELECT COUNT(*) cnt FROM t
  GROUP BY author_id
) s
person Mosty Mostacho    schedule 04.12.2013