group by したデータの中で時間が近いもの同士の時差を求めたいです。

Question

下記の様なテーブルがあります。startは時間分秒の形式で例えば101503（10時15分03秒)の様になります。

例hogeテーブル

id | start |code
1 | 101503| 001
1 | 111801| 002
1 | 111604| 002
2 | 111504| 002
2 | 092604| 001
2 | 122604| 001
2 | 122603| 002

これに対して同じidでgroup by してstartが最大のものと最小のものの時差を求めるとしたら以下のようにすれば取得できました。（こちらの質問にて解決）

select id
     , extract(hour from diff_time) * 3600 
     + extract(minute from diff_time) * 60 
     + extract(second from diff_time) as diff_second
from (
    select id
         , max(to_timestamp(start, 'HH24MISS') 
         - min(to_timestamp(start, 'HH24MISS') as diff_time
    from hoge
    group by id
) t

ただ、今回は最大のものと最小のものではなく、
違うcode（001または002）同士で最も近い時間同士の時差を求めたいです。
同じIDに001,002の組み合わせが二つある場合はどちらの組み合わせの時差でも良いです。

例えば上記例のid 1 であれば 101503と111604の時差を求めたいです。id 2 であれば122603 と 122604の時差になります。

このような場合SQLでどうにか取得することは可能でしょうか？

お分かりになる方がいればご教示いただきたいです。

Answer 1

以下のようなSQLになるかと思います。

SELECT "id", MIN("diff")
FROM (
  SELECT 
    H1."id",
      ABS(EXTRACT(HOUR   FROM TO_TIMESTAMP(H1."start", 'HH24MISS') - TO_TIMESTAMP(H2."start", 'HH24MISS')))*3600
    + ABS(EXTRACT(MINUTE FROM TO_TIMESTAMP(H1."start", 'HH24MISS') - TO_TIMESTAMP(H2."start", 'HH24MISS')))*60
    + ABS(EXTRACT(SECOND FROM TO_TIMESTAMP(H1."start", 'HH24MISS') - TO_TIMESTAMP(H2."start", 'HH24MISS'))) AS "diff"
  FROM hoge H1 
  JOIN hoge H2 ON H1."id" = H2."id" AND H1."code" <> H2."code" 
)
GROUP BY "id"