【Athena】Order By後の隣接レコードとの比較方法

前提

以下のようなテーブルを作成します（locationなどないためそのままでは動きません）。

create.sql

CREATE EXTERNAL TABLE IF NOT EXISTS hamada_test.qiita_20200527 (
  `user_id` int,
  `hoge_id` int,
  `timestamp` timestamp 
)

テストデータはこのようになっています

select.sql

SELECT * FROM "hamada_test"."qiita_20200527" order by user_id, hoge_id, timestamp

user_id	hoge_id	time_stamp
1	21	2019-01-07 07:48:00.000
1	21	2019-01-07 09:05:00.000
1	21	2019-01-07 15:57:00.000
1	21	2019-01-07 17:54:00.000
1	23	2019-01-07 06:03:00.000
1	23	2019-01-07 08:34:00.000
1	23	2019-01-07 15:53:00.000
1	23	2019-01-07 21:50:00.000
2	21	2019-01-07 12:21:00.000
2	21	2019-01-07 16:06:00.000
2	21	2019-01-07 18:47:00.000
2	21	2019-01-07 18:51:00.000
2	23	2019-01-07 07:47:00.000
2	23	2019-01-07 11:10:00.000
2	23	2019-01-07 12:35:00.000
2	23	2019-01-07 17:30:00.000

order by user_id, hoge_id, timestamp した際に隣り合うレコードと比較したい、今回だと時刻を比較したい際に使ったテクニックです。

結論

SELECT
    user_id,
    hoge_id,
    timestamp,
    LAG(timestamp) over(partition by user_id, hoge_id ORDER BY user_id, hoge_id, timestamp) AS previous_timestamp
FROM
    hamada_test."qiita_20200527"

LAG ウィンドウ関数を使用すると簡単に出せます。user_id、hoge_idで区切ってtimestamp順に並べ、一つ前の行を出します。LAG関数の第二引数を2や3にすると２つ前、３つ前の行が取得できます。

参考
分析関数（ウインドウ関数）をわかりやすく説明してみた

複雑にしてしまっていたSQL

最初このようなSQL組んでいましたが、その必要なかったです…
このようなSQLを流すと一つ前のレコードが一つの行に入ります。

select
    base_table.*,
    previous_table.timestamp as previous_timestamp
from
    (
        SELECT
            user_id,
            hoge_id,
            timestamp,
            ROW_NUMBER() over(partition by user_id, hoge_id order by user_id, hoge_id, timestamp) as index
        FROM
            "qiita_20200527"
    ) as base_table
    left join
        (
            SELECT
              user_id,
              hoge_id,
              timestamp,
              ROW_NUMBER() over(partition by user_id, hoge_id order by user_id, hoge_id, timestamp) as index
            FROM
                "qiita_20200527"
        ) as previous_table
    on  base_table.user_id = previous_table.user_id
    and base_table.hoge_id = previous_table.hoge_id
    and base_table.index = previous_table.index + 1
order by
    base_table.user_id,
    base_table.hoge_id,
    base_table.timestamp

これでtimestampとprevious_timestampが一つの行に入るのでこれらを比較すれば時刻差などが取れます。またindex1は前の行が存在しないのでprevious_timestampが空になっています。

解説

無駄っぽいように見えますが

SELECT
    user_id,
    hoge_id,
    timestamp,
    ROW_NUMBER() over(partition by user_id, hoge_id order by user_id, hoge_id, timestamp) as index
FROM
    "qiita_20200527"

このサブクエリで同じテーブルを結合させています。肝なのは ROW_NUMBER() over(partition by user_id, hoge_id order by user_id, hoge_id, timestamp) で、これでuser_id・hoge_idで区切ったあと、timestampで順番を付けています。
そのテーブルを結合させる際に

on  base_table.user_id = previous_table.user_id
and base_table.hoge_id = previous_table.hoge_id
and base_table.index = previous_table.index + 1

このようにindexとindex+1で結合させています。これで一つ前の行と結合させることができます。

連続するデータを前後で比較する際にSQLのみで対応したかったためこのように試しました。
もしよりよいやり方ありましたら教えていただけますと助かります。