Блог

Главная - Вопросы по программированию - Несколько агрегатных функций в SQL-запросе

Несколько агрегатных функций в SQL-запросе

#mysql #sql

#mysql #sql

Вопрос:

Для этого примера я получил 3 простые таблицы (страница, подразделы и подписчики):

Мои три таблицы

Для каждой страницы мне нужно знать, сколько у нее дочерних элементов и подписчиков. Мой результат должен выглядеть следующим образом:

Результат

Я попытался использовать функцию COUNT в сочетании с GROUP BY следующим образом:

 SELECT p.ID, COUNT(s.UID) AS SubCount, COUNT(f.UID) AS FollowCount 
FROM page p, subs s, followers f 
WHERE p.ID = s.ID AND p.ID = f.ID AND s.ID = f.ID 
GROUP BY p.ID

Очевидно, что это утверждение возвращает неверный результат.

Моей другой попыткой было использование двух разных операторов SELECT, а затем объединение двух вложенных результатов в одну таблицу. 

 SELECT p.ID, COUNT(s.UID) AS SubCount FROM page p, subs s WHERE p.ID = s.ID GROUP BY p.ID

и 

 SELECT p.ID, COUNT(f.UID) AS FollowCount FROM page p, follow f WHERE p.ID = f.ID GROUP BY p.ID

Я чувствую, что должен быть более простой / короткий способ сделать это, но я слишком неопытен, чтобы найти его.

Ответ №1:

Никогда не используйте запятые в FROM предложении. Всегда используйте правильный, явный, стандартный JOIN синтаксис.

Далее узнайте, что COUNT() делает. Подсчитывает количество ненулевых значений. Итак, ваши выражения будут возвращать одно и то же значение — потому что f.UID и s.UID никогда не NULL (из-за JOIN условий).

Проблема в том, что разные измерения умножают суммы. Простое решение заключается в использовании COUNT(DISTINCT) :

 SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p JOIN
     subs s
     ON p.ID = s.ID JOIN
     followers f 
     ON s.ID = f.ID 
GROUP BY p.ID;

Внутренние соединения эквивалентны исходному запросу. Вероятно, вам нужны left join s, чтобы вы могли получать нулевые значения:

 SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p LEFT JOIN
     subs s
     ON p.ID = s.ID LEFT JOIN
     followers f 
     ON p.ID = f.ID 
GROUP BY p.ID;

Комментарии:

1. Ваше решение работает до тех пор, пока на странице нет 0 подписчиков или вспомогательных страниц. Я удалил последнюю запись моей вспомогательной таблицы, чтобы на странице с идентификатором 2 было 0 вспомогательных файлов и 4 подписчика. Теперь идентификатор 2 вообще не отображается. Я заменил все соединения левыми соединениями. Теперь идентификатор 2 отображается со значениями 0 Subs и 0 Followers (хотя у него 4 Followers). Вы знаете, что я там сделал не так?

2. @RolfWolf . . . Второе соединение должно быть с p.id , а не s.id .

3. Накладные расходы на декартово произведение, произведенное между subs и followers таким способом, могут быть значительными, если числа высоки. Я думаю, что в этом случае лучше использовать решение с коррелированными подзапросами…

4. @LukasEder . . . Или путем агрегирования перед присоединением. Мой ответ был направлен на то, как OP первоначально сформулировал запрос.

Ответ №2:

В этом случае должен сработать скалярный подзапрос.

 SELECT p.id,
       (SELECT Count(s_uid)
        FROM   subs s1
        WHERE  s1.s_id = p.id) AS cnt_subs,
       (SELECT Count(f_uid)
        FROM   followers f1
        WHERE  f1.f_id = p.id) AS cnt_fol
FROM   page p
GROUP  BY p.id;