Блог

Главная - Вопросы по программированию - Альтернатива функции ранга в mysql 5.7, чтобы узнать последнюю переписку, сгруппированную по cr-id

Альтернатива функции ранга в mysql 5.7, чтобы узнать последнюю переписку, сгруппированную по cr-id

#mysql #sql #database #subquery #window-functions

#mysql #sql #База данных #подзапрос #окно-функции

Вопрос:

У меня есть таблица, похожая на это:

 cr-id  prod-name  timestamp 

p1     prod-1     2020-10-06 01:00:00.0
p1     prod-2     2020-10-06 02:00:00.0
p1.    prod-1     2020-07-03 08:32:00.0
p2.    prod-2     2020-06-01 07:39:00.0
p2.    prod-1     2020-04-05 03:32:00.0
p3.    prod-2     2020-03-02 02:23:00.0
p3.    prod-2     2020-07-04 02:23:00.0

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

  cr-id  prod-name  timestamp              rank

p1     prod-1     2020-10-06 01:00:00.0    2
p1     prod-2     2020-10-06 02:00:00.0    1
p1.    prod-1     2020-07-03 08:32:00.0    3
p2.    prod-2     2020-06-01 07:39:00.0    1
p2.    prod-1     2020-04-05 03:32:00.0    2
p3.    prod-2     2020-03-02 02:23:00.0    2
p3.    prod-2     2020-07-04 02:23:00.0    1

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

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

1. вам нужен новый столбец или вы хотите, чтобы rank возвращался из запроса?

2. Я хочу новый столбец

3. почему p1. строка имеет ранг 3, а не ранг 1?

Ответ №1:

После добавления столбца ранга выполните:

 set @last='';
set @rank=NULL;
update yourtable
    set rank=if(
        @last=(@last:=cr_id),
        (@rank:=@rank 1),
        (@rank:=1)
    )
order by cr_id,`timestamp`;

Ответ №2:

Один из вариантов использует коррелированный подзапрос:

 select t.*,
    (
        select 1   count(*) 
        from mytable t1 
        where t1.cr_id = t.cr_id and t1.timestamp > t.timestamp
    ) rn
from mytable t

Ответ №3:

Другой вариант:

 SELECT t1.`cr-id`, t1.`prod-name`, t1.`timestamp`, COUNT(*) `rank`
FROM src_table t1
JOIN src_table t2 ON t1.`cr-id` = t2.`cr-id`
                 AND t1.`timestamp` >= t2.`timestamp`
GROUP BY t1.`cr-id`, t1.`prod-name`, t1.`timestamp`