SQL：如何获取最后一项？

I have a log for documents that go through my application. The log looks like this:

我有一个日志用于通过我的应用程序的文档。日志看起来像这样:

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

I need a list of document_id that have been "stuck" in a certain status_code for a given duration (say 10 minutes; timestamp is Unix timestamp, btw). If a particular document_id is "stuck" in a certain status_code, that status_code will be the last status_code for that document_id.

我需要一个document_id列表,它已经被某个status_code“卡住”了一段时间(例如10分钟;时间戳是Unix时间戳,顺便说一句)。如果特定的document_id在某个status_code中“卡住”,则该status_code将是该document_id的最后一个status_code。

How do I query this? The 2 things I'm not sure of:

我该如何查询?我不确定的两件事:

How do I check if the document has been in a certain status for a certain period of time? I'm guessing I can use some sort of function/formula in my SQL (e.g. now - 10 minutes), but I don't know how to do that.

如何检查文档是否在某段时间内处于某种状态?我猜我可以在我的SQL中使用某种函数/公式(例如现在 - 10分钟),但我不知道该怎么做。

How do I check the duration of only the last status code?

如何检查最后一个状态代码的持续时间?

3 个解决方案

#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

For Your no. one and no. two question, this is my proposition for (I guess You are using MySQL):

为了你的号码。一,不。两个问题,这是我的主张(我猜你使用的是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);

All right, what is happing there. We're grouping rows by document_id, and ordering status codes and timestamps inside GROUP_CONCAT to get the last and pre-last entry.

好吧,那里有什么东西。我们按document_id对行进行分组,并在GROUP_CONCAT内部排序状态代码和时间戳,以获取最后一个和前一个条目。

If Your status code can have more than 5 digits, then replace it RPAD(status_code,X), where X is maximal number of status_code length

如果您的状态代码可以有超过5位数,那么将其替换为RPAD(status_code,X),其中X是status_code长度的最大数量

60*10 - is 10 minutes

60 * 10 - 是10分钟

last_status IN (100,200) - status codes You want to get only.

last_status IN(100,200) - 状态代码您只想获取。

#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

#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