Как в один (my)SQL-запрос посчитать искомое (максимум по группировке)?

Question

[Arris]

Дано: таблица `messages` вида

id_member INT 
poster_time INT
... и много других, ненужных нам полей

id_member - айдишка пользователя, написавшего комментарий.
poster_time - timestamp, когда написали комментарий.

Надо выяснить, кто из пользователей (с каким айди) в каждом месяце написал максимум сообщений.
Ожидается ответ вида:

ГГГГ-ММ     юзерайди   число-сообщений
ГГГГ-ММ     юзерайди   число-сообщений

Мы с коллегой моск сломали. Составили совершенно неоптимальный запрос с трехкратной вложенностью селектов. Ходим вокруг да около, чуем, что ответ рядом, но - никак.

Заранее спасибо!

Comments

[Arris]

Наш запрос вот таков:

SELECT aa_member,amsg_count,aa_date FROM (
SELECT
id_member AS aa_member,
COUNT( smf_messages.id_msg ) AS amsg_count,
(DATE_FORMAT ( FROM_UNIXTIME(smf_messages.poster_time) , '%Y-%m')) AS aa_date
FROM smf_messages
GROUP BY aa_date , aa_member
ORDER BY aa_date) xxx WHERE (amsg_count, aa_date) IN (
SELECT MAX(amsg_count) AS amsg_count, aa_date FROM (
SELECT
id_member AS a_member,
COUNT( smf_messages.id_msg ) AS amsg_count,
(DATE_FORMAT ( FROM_UNIXTIME(smf_messages.poster_time) , '%Y-%m')) AS aa_date
FROM smf_messages
GROUP BY aa_date , id_member
ORDER BY aa_date) yyy GROUP BY aa_date )

На таблице 170к мессаг он работает дольше, чем мы можем себе позволить. На локалхосте 2:34, на ремоутхосте лимит 30 секунд.

Answer 1

[Rsa97]

SELECT `t3`.`id_member`, `t3`.`month`, `t3`.`count`
    FROM (
        SELECT `month`, MAX(`count`) AS `maxcount`
            FROM (
                SELECT DATE_FORMAT(FROM_UNIXTIME(`poster_time`),'%Y-%m') AS `month`,
                       COUNT(*) AS `count`
                    FROM `messages`
                    GROUP BY `id_member`, `month`
            ) AS `t1`
            GROUP BY `month`
    ) AS `t2`
    JOIN (
        SELECT DATE_FORMAT(FROM_UNIXTIME(`poster_time`),'%Y-%m') AS `month`,
               COUNT(*) AS `count`, `id_member`
        FROM `messages`
        GROUP BY `id_member`, `month`
    ) AS `t3` ON `t3`.`month` = `t2`.`month`
        AND `t3`.`count` = `t2`.`maxcount`
    ORDER BY `t3`.`month`

Но запрос тяжёлый, поскольку не использует индекс по дате.
Лучше всего наверное создать дополнительную таблицу со счётчиком, и на основную таблицу повесить триггеры, которые будут менять счётчики при добавлении/удалении сообщений.

Comments

[Arris]

Это единичная задача, в общем то. Ну не единичная, но нечастая.

[Arris]

Все работает, отлично, спасибо. Время выполнения в пределах 3 секунд на 170к строк.

Answer 2

[Александр Аксентьев]

select 
date_format(from-unixtime(poster_time),'%y-%m'), 
(select id_member from messages as msg2 where date_format(from-unixtime(msg1.poster_time),'%y-%m')=date_format(from-unixtime(msg2.poster_time),'%y-%m')  group by concat(date_format(from-unixtime(poster_time),'%y-%m'), id_member) order by max(count(msg2.id_member)) desc limit 1) 
from messages as msg1
group by date_format(from-unixtime(msg1.poster_time),'%y-%m')

Возможно как-то так

Или с количество сообщений

select 
date_format(from-unixtime(poster_time),'%y-%m'), 
(select concat(id_member,'-',count(msg2.id_member)) from messages as msg2 where date_format(from-unixtime(msg1.poster_time),'%y-%m')=date_format(from-unixtime(msg2.poster_time),'%y-%m')  group by concat(date_format(from-unixtime(poster_time),'%y-%m'), id_member) order by max(count(msg2.id_member)) desc limit 1)  as member_and_messages
from messages as msg1
group by date_format(from-unixtime(msg1.poster_time),'%y-%m')

но надо будет разрезать member_and_messages. Иначе без второго вложенного запроса не достать и то и другое

Еще проще

<?php
	
	for($i=12;$i>0;$i--)
	{
		$date = date('y-m',strtotime("-{$i} month"));
		
		$query = "select id_member, max(count(id_member)) as posts from messages where date_format(from-unixtime(poster_time),'%y-%m') = '{$date}' group by id_member limit 1";
	}

Comments

[Arris]

На первую:

Query : select date_format(from_unixtime(poster_time),'%y-%m'), (select id_member from smf_messages as msg2 where date_format(fr...
Error Code : 1111
Invalid use of group function
На вторую такой же результат :(

[Александр Аксентьев]

Arris: проще сделать несколько php строк, чтобы не парить мозг

[Arris]

Александр Аксентьев: Задача перешла уже на уровень эм... "из принципа"

Answer 3

[furashcka]

Добавить поле `month` порядковый номер месяца 1-12

в php получать unixtime число которое например не меньше 2014 и не больше 2015

$unixtime1 = 1388534400; //01/01/2014 @ 12:00am (UTC)
$unixtime2 = 1420070400; //01/01/2015 @ 12:00am (UTC)

SELECT id_member, MAX(count) AS `count`, poster_time
FROM 
(
    SELECT id_member, month, poster_time, COUNT( * ) AS `count`
    FROM messages 
    WHERE poster_time >= $unixtime1 AND poster_time <= $unixtime2
    GROUP BY id_member, month
) T 
GROUP BY month

Ну а дату получать ГГГГ-ММ:
date("Y-m", $resultSQL['poster_time'])

Comments

[Arris]

Вьюхи создавать могу, добавлять поля в базу - нини. :(

Answer 4

[nozzy]

select
id_member,
MONTH(FROM_UNIXTIME(poster_time)) ,
count(*)
from messages
group by id_member, MONTH(FROM_UNIXTIME(poster_time))

Comments

[Arris]

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

[nozzy]

Вот так?
select
CONCAT(t1."y", '-',t1."m") as yd
,t1."id"
,t1."c"
from
(
select
id_member "id",
YEAR(FROM_UNIXTIME(poster_time)) "y" ,
MONTH(FROM_UNIXTIME(poster_time)) "m" ,
count(*) "c"
from messages
group by id_member, YEAR(FROM_UNIXTIME(poster_time)) ,MONTH(FROM_UNIXTIME(poster_time))
) t1
where t1."y" = '2015'

[nozzy]

Еще нужно max() добавить.

[Arris]

_куда_ добавить?