来自同一表的Oracle SQL MIN和MAX组合 [英] Oracle SQL MIN and MAX combination from same table

问题描述

我有以下提到的数据.我希望获得最大的启动消息以及相应的最小或成功消息.

I have below mentioned data. I am looking to get max of Start message and corresponding min or success message.

ID1     Timestamp_start_msg_recieved    date        jobid      message time in seconds
1234    5/14/2014 10:02:29              5/14/2014   abc        start 262
1234    5/14/2014 10:02:31              5/14/2014   abc        start 264
1234    5/14/2014 10:02:45              5/14/2014   abc        start 278
1234    5/14/2014 10:02:50              5/14/2014   abc        start 285
1234    5/14/2014 10:09:04              5/14/2014   abc        start 165
1234    5/14/2014 10:09:06              5/14/2014   abc        start 2167
1234    5/14/2014 10:09:16              5/14/2014   abc        start 2180
1234    5/14/2014 10:09:26              5/14/2014   abc        start 2190
1234    5/14/2014 11:45:11              5/14/2014   abc        start 8767
1234    5/14/2014 16:48:20              5/14/2014   abc        start 878
1234    5/14/2014 19:02:52              5/14/2014   abc        start 687
5678    5/14/2014 22:02:52              5/14/2014   pqr        start 501
5678    5/14/2014 23:10:40              5/14/2014   pqr        start 200

成功消息表

ID1     Timestamp_success_msg_recieved  date        jobid  message time in seconds
1234    5/14/2014 10:02:52              5/14/2014   abc    successful 290
1234    5/14/2014 10:09:32              5/14/2014   abc    successful 4280 
1234    5/14/2014 11:45:15              5/14/2014   abc    successful 8774
1234    5/14/2014 11:45:18              5/14/2014   abc    successful 8777
1234    5/14/2014 11:45:19              5/14/2014   abc    successful 8778
1234    5/14/2014 11:45:25              5/14/2014   abc    successful 8784
1234    5/14/2014 16:48:22              5/14/2014   abc    successful 880 
1234    5/14/2014 19:03:00              5/14/2014   abc    successful 699
5678    5/14/2014 22:03:00              5/14/2014   pqr    successful 250
5678    5/19/2014 14:00:16              5/19/2014   pqr    successful 400

预期结果

ID1  IMESTAMP_for_start_message TIMESTAMP_for_success_message    Date       Jobid    msg  msg start_secs success_secs
1234 5/14/2014 10:02:50         5/14/2014 10:02:52           5/14/2014  abc start success 262 290 
1234 5/14/2014 10:09:26         5/14/2014 10:09:32           5/14/2014  abc start success 2190 4280
1234 5/14/2014 11:45:11         5/14/2014 11:45:25           5/14/2014  abc start success 8767 8784
1234 5/14/2014 16:48:20         5/14/2014 16:48:22           5/14/2014  abc start success 878 880
1234 5/14/2014 19:02:52         5/14/2014 19:03:00           5/14/2014  abc start success 687 699
5678 5/14/2014 22:02:52         5/14/2014 22:03:00           5/14/2014  pqr start success 501 699
5678 5/14/2014 23:10:40         null                         5/14/2014  pqr start success 250 null
5678    null                   5/19/2014 14:00:16            5/19/2014  pqr null  success null 400

我正在寻找启动消息和start_secs的最大值，以与成功消息和success_secs的最小值配对.使用带有WITH子句的临时表进行了尝试，还使用了自连接方法.下面是我的查询，但是WITH子句查询返回表中全部数据的MIN.

I am looking for Max of start message and start_secs to pair up with min of success message and success_secs. Tried using Temporary table using WITH clause and also used self join method. Below is my query, But WITH clause query returns MIN of overall data in the table.

使用的查询:

WITH DATA AS
  (SELECT MIN(smt.column13) timestamp_for_success_message
  FROM success_table1 smt, start_table2 b
     WHERE
    (SMT.id1 = b.id1)
    AND (SMT.jobid = b.jobid)
    AND (SMT.timestamp_for_success_message_recieved >= b.timestamp_for_start_message_recieved)
  )
SELECT distinct a.timestamp_for_success_message_recieved,
  b.timestamp_for_start_message_recieved,
  b.id1,
  b.jobid
FROM data a,
  start_table2 b
order by b.timestamp_start_message_recieved, a.timestamp_for_success_message_recieved, b.jobid, b.id1;

推荐答案

对于示例数据，以下方法同样适用

For the sample data, the following works as well

WITH
RawData AS (
  SELECT
    id1
    , jobID
    , message
    , TIMESTAMP_for_start_message timeStamp
    , time_in_seconds
  FROM StartMessageTable
  UNION ALL
  SELECT
    id1
    , jobID
    , message
    , TIMESTAMP_success_msg_received
    , time_in_seconds
  FROM SuccessMessageTable
  ORDER BY id1, jobID, 4
),
Detail AS (
  SELECT
    id1
    , jobID
    , message message1
    , LEAD (message, 1) OVER (ORDER BY id1, jobID, timeStamp) message2
    , timeStamp timeStamp1
    , LEAD (timeStamp, 1) OVER (ORDER BY id1, jobID, timeStamp) timeStamp2
    , time_in_seconds secs1
    , LEAD (time_in_seconds, 1) OVER (ORDER BY id1, jobID, timeStamp) secs2
  FROM RawData
)
SELECT
  id1
  , timeStamp1 TIMESTAMP_for_start_message
  , timeStamp2 TIMESTAMP_for_success_message
  , jobID
  , 'start' msgStart
  , 'success' msgSuccess
  , secs1 start_secs
  , secs2 success_secs
FROM Detail
WHERE (message1 = 'start' AND message2 = 'successful') OR message2 IS NULL
ORDER BY timeStamp1
;

对"start_secs"列中的第一个和最后一个值有相同的警告.
SQL小提琴

with the same caveat regarding the first and last value in the "start_secs" column.
SQL Fiddle

这篇关于来自同一表的Oracle SQL MIN和MAX组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！