SQL: как получить последние элементы?

#mysql #sql #database

#mysql #sql #База данных

Вопрос:

У меня есть журнал для документов, которые проходят через мое приложение. Журнал выглядит следующим образом:

 TABLE: log
==================================================
| log_id | document_id | status_code | timestamp |
==================================================
| 1      | 10          | 100         | 12345     |
--------------------------------------------------
| 2      | 10          | 200         | 23456     |
--------------------------------------------------

Мне нужен список элементов, document_id которые были «застряли» в определенном status_code в течение заданного времени (скажем, 10 минут; timestamp это временная метка Unix, кстати). Если конкретный document_id «застрял» в определенном status_code , это status_code будет последним status_code для этого document_id .

Как мне запросить это? 2 вещи, в которых я не уверен:

Как мне проверить, находился ли документ в определенном статусе в течение определенного периода времени? Я предполагаю, что могу использовать какую-то функцию / формулу в моем SQL (например, now — 10 минут), но я не знаю, как это сделать.
Как мне проверить длительность только последнего кода состояния?

1. Для какой СУБД это нужно? (Хотя я предполагаю, MySQL что это вполне вероятно из ваших предыдущих вопросов)

2. log_id для таблицы log одинаковый для двух разных строк — действительно??

Ответ №1:

 SELECT log.document_id
     , (UNIX_TIMESTAMP() - log.timestamp) / 60
       AS MinutesSinceLastChange 
FROM log
  JOIN
    ( SELECT document_id
           , MAX(timestamp) AS last_change
      FROM log
      GROUP BY document_id
      HAVING (last_change < (UNIX_TIMESTAMP() - 60 * 10))  <-- that is 10 minutes
    ) AS grp
    ON  grp.document_id = log.document_id
    AND grp.last_change = log.timestamp
WHERE log.status_code = "200"             <-- code you want to check

Ответ №2:

Для вашего нет. один и нет. второй вопрос, это мое предложение для (я предполагаю, что вы используете MySQL):

 SELECT 
    `document_id`, 
    SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 1, 5) AS `last_status`,
    SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 7, 5) AS `prev_status`,

    UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 1, 19)) AS `last_timestamp`,
    UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 21, 19)) AS `prev_timestamp`
FROM `log`
GROUP BY `document_id`
HAVING `last_timestamp` - `prev_timestamp` > 60*10 AND `last_status` IN (100,200);

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

Если ваш код состояния может содержать более 5 цифр, замените его RPAD( status_code ,X), где X — максимальное количество длин status_code

60 * 10 — это 10 минут

last_status В (100,200) — коды состояния, которые вы хотите получить только.

1. OP пишет: » Если определенный document_id «застрял» в определенном status_code, этот status_code будет последним status_code для этого document_id «. Вам не нужно проверять предварительную запись, только последнюю.

2. хотя мой код состояния действительно является целыми числами, я не думаю, что решение должно предполагать это. Я думаю, ему просто нужно найти последний код состояния этого документа.

Ответ №3:

 SELECT log.document_id, log.status_code, max(log.timestamp) - min(log.timestamp)
FROM ( 
  SELECT MAX(log_id) as log_id
  FROM log
  GROUP BY document_id) latestLog
    INNER JOIN log latestStatus ON latestStatus.log_id = latestLog.log_id
    INNER JOIN log on latestStatus.status_code = log.status_code
GROUP BY log.document_id, log.status_code
HAVING (max(log.timestamp) - min(log.timestamp)) > 600