У меня есть таблица вроде этого:
Table: p
+----------------+
| id | w_id |
+---------+------+
| 5 | 8 |
| 5 | 10 |
| 5 | 8 |
| 5 | 10 |
| 5 | 8 |
| 6 | 5 |
| 6 | 8 |
| 6 | 10 |
| 6 | 10 |
| 7 | 8 |
| 7 | 10 |
+----------------+
Каков лучший SQL, чтобы получить следующий результат?
+-----------------------------+
| id | most_used_w_id |
+---------+-------------------+
| 5 | 8 |
| 6 | 10 |
| 7 | 8 |
+-----------------------------+
Другими словами, чтобы получить, за id, наиболее часто встречающийся w_id.
Обратите внимание, что в приведенном выше примере id 7 относится к 8 раз и 10 раз.
Таким образом, либо (7, 8), либо (7, 10) будут делать результат. Если невозможно
забрать один, то оба (7, 8) и (7, 10) в наборе результатов будут в порядке.
Я придумал что-то вроде:
select counters2.p_id as id, counters2.w_id as most_used_w_id
from (
select p.id as p_id,
w_id,
count(w_id) as count_of_w_ids
from p
group by id, w_id
) as counters2
join (
select p_id, max(count_of_w_ids) as max_counter_for_w_ids
from (
select p.id as p_id,
w_id,
count(w_id) as count_of_w_ids
from p
group by id, w_id
) as counters
group by p_id
) as p_max
on p_max.p_id = counters2.p_id
and p_max.max_counter_for_w_ids = counters2.count_of_w_ids
;
но я не уверен, что это лучший способ сделать это. И я должен был повторить один и тот же подзапрос два раза.
Лучшее решение?
Попробуйте этот запрос
select p_id, ccc , w_id from
(
select p.id as p_id,
w_id, count(w_id) ccc
from p
group by id,w_id order by id,ccc desc) xxx
group by p_id having max(ccc)
вот ссылка sqlfidddle
Вы также можете использовать этот код, если не хотите полагаться на первую запись негрупповых столбцов
select p_id, ccc , w_id from
(
select p.id as p_id,
w_id, count(w_id) ccc
from p
group by id,w_id order by id,ccc desc) xxx
group by p_id having ccc=max(ccc);
Попробуйте использовать Пользовательские переменные
select id,w_id
FROM
( select T.*,
if(@id<>id,1,0) as row,
@id:=id FROM
(
select id,W_id, Count(*) as cnt FROM p Group by ID,W_id
) as T,(SELECT @id:=0) as T1
ORDER BY id,cnt DESC
) as T2
WHERE Row=1
Формальный SQL
Фактически - ваше решение является правильным с точки зрения нормального SQL. Зачем? Потому что вы должны придерживаться значений привязки от исходных данных к сгруппированным данным. Таким образом, ваш запрос не может быть упрощен. MySQL позволяет смешивать негрупповые столбцы и групповую функцию, но это абсолютно ненадежно, поэтому я не буду рекомендовать вам полагаться на этот эффект.
MySQL
Поскольку вы используете MySQL, вы можете использовать переменные. Я не большой поклонник их, но для вашего дела они могут быть использованы для упрощения вещей:
SELECT
c.*,
IF(@id!=id, @i:=1, @i:[email protected]+1) AS num,
@id:=id AS gid
FROM
(SELECT id, w_id, COUNT(w_id) AS w_count
FROM t
GROUP BY id, w_id
ORDER BY id DESC, w_count DESC) AS c
CROSS JOIN (SELECT @i:=-1, @id:=-1) AS init
HAVING
num=1;
Итак, для ваших данных результат будет выглядеть так:
+------+------+---------+------+------+ | id | w_id | w_count | num | gid | +------+------+---------+------+------+ | 7 | 8 | 1 | 1 | 7 | | 6 | 10 | 2 | 1 | 6 | | 5 | 8 | 3 | 1 | 5 | +------+------+---------+------+------+
Таким образом, вы нашли свой id и соответствующий w_id. Идея заключается в том, чтобы подсчитать строки и перечислить их, обратив внимание на то, что мы заказываем их в подзапросе. Поэтому нам нужна только первая строка (потому что она будет представлять данные с наивысшим количеством).
Это может быть заменено на одиночный GROUP BY id - но, опять же, сервер может выбрать любую строку в этом случае (он будет работать, потому что он займет первую строку, но документация ничего не говорит об этом для обычного случая).
Одна маленькая приятная вещь об этом - вы можете выбрать, например, 2-й по частоте или 3-й, очень гибкий.
Производительность
Чтобы повысить производительность, вы можете создать индекс на (id, w_id) - очевидно, он будет использоваться для упорядочения и группировки записей. Но переменные и HAVING, тем не менее, будут производить по очереди сканирование для набора, выводимое внутренним GROUP BY. Это не так плохо, как при полном сканировании исходных данных, но все же это нехорошо делать это с переменными. С другой стороны, делать это с помощью JOIN и подзапроса, как в вашем запросе, не будет сильно отличаться, из-за того, что также создается таблица временных значений для набора результатов подзапроса.
Но, конечно, вам придется протестировать. И имейте в виду - у вас уже есть правильное решение, которое, кстати, не связано с конкретными СУБД и хорошо относится к общему SQL.