Выберите строки из базы данных, где значение в одном столбце отличается, и ограничьте до 5 последних

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

Скрипка:: http://sqlfiddle.com/#!2/d5b05 /16

Желаемый результат. Желаемый результат при выборе из этого набора данных:

SELECT * FROM `image` ORDER BY `date_updated` DESC;

|   IMAGE | WIDTH | HEIGHT | DATE_ADDED | DATE_UPDATED | UPDATED_BY_IP |
|---------|-------|--------|------------|--------------|---------------|
| 1x1XGY4 |  1920 |   1080 | 1417546414 |   1421712314 |   192.168.0.7 |
| 1x1XGY3 |  1920 |   1080 | 1417546413 |   1421712313 |   192.168.0.7 |
| 1x1XGY2 |  1920 |   1080 | 1417546412 |   1421712312 |  192.168.0.10 |
| 1x1XGY1 |  1920 |   1080 | 1417546411 |   1421712311 |  192.168.0.10 |
| 1oApS54 |  1920 |   1080 | 1417138874 |   1421685474 |   192.168.0.2 |
| 1oApS53 |  1920 |   1080 | 1417138873 |   1421685473 |   192.168.0.2 |
| 1oApS52 |  1920 |   1080 | 1417138872 |   1421685472 |  192.168.0.10 |
| 1oApS51 |  1920 |   1080 | 1417138871 |   1421685471 |  192.168.0.10 |
| 1ydhtQ4 |  1920 |   1080 | 1421460434 |   1421685154 |   192.168.0.6 |
| 1ydhtQ3 |  1920 |   1080 | 1421460433 |   1421685153 |   192.168.0.7 |
| 1ydhtQ2 |  1920 |   1080 | 1421460432 |   1421685152 |  192.168.0.10 |
| 1ydhtQ1 |  1920 |   1080 | 1421460431 |   1421685151 |   192.168.0.5 |
| 1WyQib4 |  1920 |   1080 | 1420869354 |   1421634384 |   192.168.0.8 |
| 1WyQib3 |  1920 |   1080 | 1420869353 |   1421634383 |   192.168.0.2 |
| 1WyQib2 |  1920 |   1080 | 1420869352 |   1421634382 |   192.168.0.3 |
| 1WyQib1 |  1920 |   1080 | 1420869351 |   1421634381 |  192.168.0.10 |
| 1izDqg4 |  1920 |   1080 | 1416948144 |   1421608564 |   192.168.0.2 |
| 1izDqg3 |  1920 |   1080 | 1416948143 |   1421608563 |   192.168.0.2 |
| 1izDqg2 |  1920 |   1080 | 1416948142 |   1421608562 |   192.168.0.5 |
| 1izDqg1 |  1920 |   1080 | 1416948141 |   1421608561 |  192.168.0.10 |

С оператором псевдовыбора:

ВЫБЕРИТЕ * ОТ изображения ОТ ГДЕ update_by_ip ОТЛИЧАЕТСЯ ORDER BY date_updated DESC LIMIT 5

|   IMAGE | WIDTH | HEIGHT | DATE_ADDED | DATE_UPDATED | UPDATED_BY_IP |
|---------|-------|--------|------------|--------------|---------------|
| 1x1XGY4 |  1920 |   1080 | 1417546414 |   1421712314 |   192.168.0.7 |
| 1x1XGY2 |  1920 |   1080 | 1417546412 |   1421712312 |  192.168.0.10 |
| 1oApS54 |  1920 |   1080 | 1417138874 |   1421685474 |   192.168.0.2 |
| 1ydhtQ4 |  1920 |   1080 | 1421460434 |   1421685154 |   192.168.0.6 |
| 1ydhtQ1 |  1920 |   1080 | 1421460431 |   1421685151 |   192.168.0.5 |

Закрытый результат:

Лучшее, что я мог придумать, это:

SELECT DISTINCT updated_by_ip, MAX(date_updated) AS date_updated 
FROM `image` GROUP BY updated_by_ip ORDER BY date_updated DESC LIMIT 5;

Это дает мне:

| UPDATED_BY_IP | DATE_UPDATED |
|---------------|--------------|
|   192.168.0.7 |   1421712314 |
|  192.168.0.10 |   1421712312 |
|   192.168.0.2 |   1421685474 |
|   192.168.0.6 |   1421685154 |
|   192.168.0.5 |   1421685151 |

Из которых я мог бы сделать

while (SELECT DISTINCT updated_by_ip ...)
{
    $result_rows[] = SELECT * FROM image 
                    WHERE updated_by_ip = query[updated_by_ip] 
                    AND date_updated = query[date_updated] LIMIT 1
}

Тем не менее, я надеялся найти способ сделать это без необходимости выполнять кучу постобработки и дополнительных запросов, а выбор по updated_by_ip и date_updated не кажется очень стабильным.

Спасибо.


mysql sqlfiddle
person Snook    schedule 20.01.2015    source источник

Ответы (3)


arrow_upward
0
arrow_downward

Чтобы сделать это без расширения MySQL GROUP BY, вы можете попробовать следующее:

Во-первых, с помощью этого подзапроса получите самое последнее время обновления с пяти различных IP-адресов.

     SELECT updated_by_ip, MAX(date_updated) as date_updated
       FROM image  
      GROUP BY updated_by_ip
      ORDER BY 2 DESC
      LIMIT 5

Если ваша таблица большая, индекс (updated_by_ip, date_updated) поможет повысить производительность.

Затем присоедините его к основному запросу к этому подзапросу, чтобы получить результат.

SELECT i.*
  FROM image i
  JOIN (
         SELECT updated_by_ip, MAX(date_updated) as date_updated
           FROM image  
          GROUP BY updated_by_ip
          ORDER BY 2 DESC
          LIMIT 5
        ) m USING(updated_by_ip, date_updated)
ORDER BY i.date_updated DESC

См. это: http://sqlfiddle.com/#!2/d5b05/21/0< /а>

person O. Jones    schedule 20.01.2015

arrow_upward
0
arrow_downward

Это не самый красивый запрос (неверный по стандарту SQL), но он работает в MySQL:

SELECT * FROM `image`
GROUP BY updated_by_ip
ORDER BY `date_updated` DESC

В Postgres вы бы использовали DISTINCT ON(...), но MySQL не поддерживает это, поэтому простое группирование по столбцам, которые вы хотите выделить, является самым простым обходным решением. Альтернативой является использование подзапросов, но это менее оптимально.

person Wolph    schedule 20.01.2015
comment
Спасибо за предложение, однако это не возвращает последнее изображение, просмотренное для определенного IP, группировка возвращает произвольную строку для каждого результата IP. - person Snook; 20.01.2015

arrow_upward
0
arrow_downward

Один из подходов заключается в использовании переменных для перечисления строк:

SELECT i.*
FROM (SELECT i.*,
             (@rn := if(@uip = updated_by_ip, @rn + 1,
                        if(@uip := updated_by_ip, 1, 1)
                       )
             )
      FROM image i CROSS JOIN
           (SELECT @uip := '', @rn := 0) vars
      WHERE updated_by_ip 
      ORDER BY updated_by_ip, date_updated DESC
     ) i
WHERE seqnum <= 5;
person Gordon Linoff    schedule 20.01.2015