Как в my sql использовать функцию MIN() для COUNT()?

Question

Как в my sql использовать функцию MIN() для COUNT()?

Рейтинг: 1Ответов: 2Опубликовано: 13.05.2023

У меня есть две таблицы - visitors (столбцы id, first_name и last_name) и attendance (столбцы visitor_id и date_attendance).

Т.е. в одной таблице содержатся данные посетителей (фамилия имя), а во второй таблице дата посещения посетителей.

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

Но как отразить только тех посетителей, у которых минимальные количества посещений, я не могу понять. Я только учусь, подскажите, как это осуществить?

SELECT CONCAT(last_name, ' ', first_name) AS 'Посетители', COUNT(*) AS 'Количество посещений'
FROM visitors AS V, attendance AS A
WHERE V.id = A.visitor_id
GROUP BY A.visitor_id;

sql

Источник: Stack Overflow на русском

Answer 1

▲ 0

Прежде всего хочется отметить пару неточностей в приведённом вами запросе. В предложении SELECT вы оперируете столбцами fist_name и last_name, а ведь по ним агрегирование не проводилось.

При определённых настройках MySQL разрешает такое делать, но это совершенно неправильно. Чтобы запрос был корректен, нужно делать GROUP BY не только по столбцу id, но и, собственно, по столбцам fist_name и last_name.

Во-вторых, вы соединяете таблицы посредством обычного сравнения ключей в предложении WHERE, но это не совсем корректно. Стандартным подходом в SQL считается использование INNER JOIN-ов.

Итак, для решения задачи нужно использовать несколько подзапросов. Самый глубокий подзапрос вы уже привели: поиск количества посещений для каждого посетителя.
Вышестоящим подзапросом будет поиск минимума среди найденных количеств.
Наконец, основным запросом будет выбор тех людей, количество посещений которых минимально.

Для удобства распишем все подзапросы последовательно.

Нахождение количества посещений для всех посетителей:

SELECT COUNT(*) c
FROM attendance
GROUP BY visitor_id;

Нахождение минимума среди этих количеств:

SELECT MIN(t.c) FROM (
    SELECT COUNT(*) c
    FROM attendance
    GROUP BY visitor_id;
) t;

Нахождение посетителей, количество посещений которых минимально:

SELECT v.id, v.first_name, v.last_name, COUNT(*) c
FROM visitors v INNER JOIN attendance a
ON v.id = a.visitor_id
GROUP BY a.visitor_id
HAVING COUNT(*) = (
    SELECT MIN(t.c) FROM (
        SELECT COUNT(*) c
        FROM attendance
        GROUP BY visitor_id;
    ) t
);

Можно не искать минимум, а использовать сортировку и взятие первого значения. Тогда итоговый запрос может выглядеть так:

SELECT v.id, v.first_name, v.last_name, COUNT(*) c
FROM visitors v INNER JOIN attendance a
ON v.id = a.visitor_id
GROUP BY a.visitor_id
HAVING COUNT(*) = (
    SELECT COUNT(*) c
    FROM attendance
    GROUP BY visitor_id
    ORDER BY c
    LIMIT 1;
);

Ответ был улучшен и дополнен благодаря замечаниям @teran.

Answer 2

▲ 0

В дополнение к ответу @Byb можно создать временную таблицу с данными о количестве посещений для каждого посетителя:

CREATE TEMPORARY TABLE tmpTbl AS (
  SELECT CONCAT(last_name, ' ', first_name) AS `Посетитель`, COUNT(*) AS `Посещения`
  FROM visitors AS V
  JOIN attendance AS A ON V.id = A.visitor_id 
  GROUP BY CONCAT(last_name, ' ', first_name)
);

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

SELECT *
FROM tmpTbl tbl
WHERE tbl.`Посещения` = (SELECT MIN(`Посещения`) FROM tmpTbl);

Для версии MySQL 8.0 и выше можно использовать CTE (Common Table Expressions), в соответствии с предложением @teran:

WITH tmpTbl(full_name, visits) AS (
  SELECT CONCAT(last_name, ' ', first_name) AS full_name, COUNT(*) AS visits
  FROM visitors AS V
  JOIN attendance AS A ON V.id = A.visitor_id 
  GROUP BY CONCAT(last_name, ' ', first_name)
)
SELECT *
FROM tmpTbl
WHERE visits = (SELECT MIN(visits) FROM tmpTbl);

Также можно выполнить группировку только в таблице attendance, чтобы эффективнее использовать JOIN и конкатенацию имён:

WITH tmpVisits(visitor_id, visits) AS (
  SELECT visitor_id, COUNT(*) AS visits
  FROM attendance 
  GROUP BY visitor_id
)
SELECT CONCAT(v.first_name, ' ', v.last_name) full_name, a.visits
FROM tmpVisits a
JOIN visitors v ON a.visitor_id = v.id 
WHERE a.visits = (SELECT MIN(visits) FROM tmpVisits);

Как в my sql использовать функцию MIN() для COUNT()?

Ответы