来自同一表的Oracle SQL MIN和MAX组合

debugcn 发表于 Dev

数据分析师

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

开始消息表

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。

使用的查询：

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;

Abecee

对于示例数据，以下内容也适用

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小提琴

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。