Ограничение GROUP BY на основе значений COUNT () в mySQL

Эти типы условий в MySQL болезненны. Один метод использует переменные. Вот метод, который не делает:

SELECT el.eventcount, el.eventname
FROM (SELECT COUNT(el.id) AS eventcount, el.eventname
FROM eventlog el
GROUP BY el.eventname
) el JOIN
(SELECT cnt
FROM (SELECT DISTINCT COUNT(el.id) as cnt
FROM eventlog el
GROUP BY el.eventname
) el
ORDER BY cnt DESC
LIMIT 3
) ell
ON ell.cnt = el.eventcount
ORDER BY el.eventcount DESC;

РЕДАКТИРОВАТЬ:

Решение с использованием переменных выглядит следующим образом и включает ограничение на 2 для счетчика 1:

SELECT *
FROM (SELECT e.*,
(@rn1 := if(@c1 = eventcount, @rn1 + 1,
if(@c1 := eventcount, 1, 1)
)
) as rn
FROM (SELECT e.*,
(@rn := if(@c = eventcount, @rn,
if(@c := eventcount, @rn + 1, @rn + 1)
)
) as rank
FROM (SELECT COUNT(el.id) AS eventcount, el.eventname
FROM eventlog el
GROUP BY el.eventname
) e CROSS JOIN
(SELECT @c := 0, @rn := 0) params
ORDER BY eventcount DESC
) e CROSS JOIN
(SELECT @c1 := 0, @rn1 := 0) params
ORDER BY eventcount DESC
) e
WHERE rank <= 3 AND
(eventcount > 1 OR rn <= 2);

Самый внутренний счет перечисляет счет. Второе перечисляет в пределах счета. На самом деле, эти два вопроса, вероятно, могут быть объединены в один подзапрос, но с осторожностью.

Это должно быть в состоянии немного рефакторинг, но он возвращает правильный ответ на данный момент:

SELECT eventcount, eventname
FROM
(SELECT el.eventcount, el.eventname
FROM (SELECT COUNT(el.id) AS eventcount, el.eventname
FROM eventlog el
GROUP BY el.eventname
) el JOIN
(SELECT counts
FROM (SELECT DISTINCT COUNT(el.id) as counts
FROM eventlog el
GROUP BY el.eventname
) el
ORDER BY counts DESC
LIMIT 3
) el2
ON el2.counts = el.eventcount
WHERE el.eventcount != 1
UNION ALL
(SELECT el.eventcount, el.eventname
FROM (SELECT COUNT(el.id) AS eventcount, el.eventname
FROM eventlog el
GROUP BY el.eventname
) el JOIN
(SELECT counts
FROM (SELECT DISTINCT COUNT(el.id) as counts
FROM eventlog el
GROUP BY el.eventname
) el
ORDER BY counts DESC
LIMIT 3
) el2
ON el2.counts = el.eventcount AND el2.counts = 1
LIMIT 2)) tmp
ORDER BY tmp.eventcount DESC;

SQL Fiddle: http://sqlfiddle.com/#!9/10f0d/92

Если вы можете использовать временные таблицы ..

Предварительно рассчитайте количество событий и сохраните результат во временной таблице:

create temporary table tmp_eventcounts
select eventname, count(1) as eventcount
from eventlog
group by eventname
order by eventcount desc
;

Содержание tmp_eventcounts:

|        eventname | eventcount |
|------------------|------------|
| machine1.started |          3 |
| machine1.stopped |          2 |
|    cluster.alive |          2 |
| machine3.started |          1 |
|  machine2.reboot |          1 |
| machine4.started |          1 |
| machine0.started |          1 |

Выберите первые 3 учетных записи событий и сохраните их в другой временной таблице:

create temporary table tmp_top3counts
select distinct eventcount
from tmp_eventcounts
order by eventcount desc
limit 3
;

Содержание tmp_top3counts:

| eventcount |
|------------|
|          3 |
|          2 |
|          1 |

Теперь выберите все имена событий с верхними 3 счетами событий, но eventcount > 1.
Также можно выбрать до двух имен событий с 3 верхними учетными записями, но eventcount = 1
Используйте UNION, чтобы объединить два результата:

select eventcount, eventname
from tmp_top3counts
join tmp_eventcounts using(eventcount)
where eventcount > 1
union all (
select eventcount, eventname
from tmp_top3counts
join tmp_eventcounts using(eventcount)
where eventcount = 1
limit 2
)
order by eventcount desc;

Результат:

| eventcount |        eventname |
|------------|------------------|
|          3 | machine1.started |
|          2 | machine1.stopped |
|          2 |    cluster.alive |
|          1 |  machine2.reboot |
|          1 | machine3.started |

http://sqlfiddle.com/#!9/b332df/1

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

select eventcount, eventname
from (
select distinct eventcount
from (
select eventname, count(1) as eventcount
from eventlog
group by eventname
) tmp_eventcounts
order by eventcount desc
limit 3
) tmp_top3counts
join (
select eventname, count(1) as eventcount
from eventlog
group by eventname
) tmp_eventcounts using(eventcount)
where eventcount > 1
union all (
select eventcount, eventname
from (
select distinct eventcount
from (
select eventname, count(1) as eventcount
from eventlog
group by eventname
) tmp_eventcounts
order by eventcount desc
limit 3
) tmp_top3counts
join (
select eventname, count(1) as eventcount
from eventlog
group by eventname
) tmp_eventcounts using(eventcount)
where eventcount = 1
limit 2
)
order by eventcount desc;

http://sqlfiddle.com/#!9/2eea6/4 😉

Хотя это может показаться сумасшедшим, его легко создать в PHP:

$tmp_eventcounts = "select eventname, count(1) as eventcount
from eventlog
group by eventname
";

$tmp_top3counts = "select distinct eventcount
from ( {$tmp_eventcounts} ) tmp_eventcounts
order by eventcount desc
limit 3
";

$sql = "select eventcount, eventname
from ( {$tmp_top3counts} )  tmp_top3counts
join ( {$tmp_eventcounts} ) tmp_eventcounts using(eventcount)
where eventcount > 1
union all (
select eventcount, eventname
from ( {$tmp_top3counts} )  tmp_top3counts
join ( {$tmp_eventcounts} ) tmp_eventcounts using(eventcount)
where eventcount = 1
limit 2
)
order by eventcount desc
";

Примечание. Похоже, что MySQL необходимо будет снова и снова выполнять одни и те же подзапросы. Но он должен иметь возможность кэшировать результаты и использовать их повторно.

Вы можете попробовать это:

SELECT count(eventname), eventname FROM table
group by eventname
HAVING(count(eventname)) > 1
order by count(eventname) DESC
limit 3