◆ 一つ前のデータと JOIN して時間差を見る

アクセスログみたいな時系列データから切れ目を見つけたいです
連続してアクセスが続いてる部分はひとまとまりとして しばらく時間が空いた最初のデータを見つけたいです

プログラム上に値があるなら特に難しいものでもなく for 文などで時系列順に見ていって一つ前との差分を計算して閾値を超えたらそこにマークするみたいな処理ができます

ただ今回は データが RDB に入ってたので 一旦全部 SELECT で取ってこずに SQL でいい感じにできないかなーと考えてみました

やりたいことは一つ前との差分を見て ある数値を超えていたらそのデータを取得するというものです
これまでこういうことを SQL でやることはなかったのですが 考えてみると同じテーブルの一つ前の値と JOIN することでできそうでした
JOIN すると 1 つの行に連続する 2 つの時刻があるのでその差をもとに WHERE で絞り込みます

ダミーデータを作って試してみました
15 分以上時間があいたら切れ目とします

CREATE TABLE ta (
id integer PRIMARY KEY,
ts timestamp without time zone NOT NULL
);

INSERT INTO tbl VALUES
(1, '2020-01-01 00:00:00'),
(2, '2020-01-01 00:01:45'),
(3, '2020-01-01 00:05:02'),
(4, '2020-01-01 00:08:59'),
(5, '2020-01-01 00:10:28'),
(6, '2020-01-01 00:12:59'),
(7, '2020-01-01 00:14:13'),
(8, '2020-01-01 00:14:47'),
(9, '2020-01-01 00:19:27'),
(10, '2020-01-01 00:20:37'),
(11, '2020-01-01 00:22:47'),
(12, '2020-01-01 00:23:22'),
(13, '2020-01-01 00:27:06'),
(14, '2020-01-01 00:30:02'),
(15, '2020-01-01 00:30:33'),
(16, '2020-01-01 00:35:07'),
(17, '2020-01-01 00:37:33'),
(18, '2020-01-01 00:38:48'),
(19, '2020-01-01 00:59:24'),
(20, '2020-01-01 01:02:25'),
(21, '2020-01-01 01:04:39'),
(22, '2020-01-01 01:05:25'),
(23, '2020-01-01 01:08:40'),
(24, '2020-01-01 01:41:06'),
(25, '2020-01-01 01:43:07'),
(26, '2020-01-01 01:45:04'),
(27, '2020-01-01 01:47:35'),
(28, '2020-01-01 01:49:13'),
(29, '2020-01-01 01:52:52'),
(30, '2020-01-01 01:54:51'),
(31, '2020-01-01 01:54:52'),
(32, '2020-01-01 01:58:43'),
(33, '2020-01-01 02:03:20'),
(34, '2020-01-01 02:50:14'),
(35, '2020-01-01 02:51:55'),
(36, '2020-01-01 02:54:17'),
(37, '2020-01-01 02:55:45'),
(38, '2020-01-01 02:59:58'),
(39, '2020-01-01 03:01:03'),
(40, '2020-01-01 03:04:26'),
(41, '2020-01-01 03:08:41'),
(42, '2020-01-01 03:09:21'),
(43, '2020-01-01 03:13:49'),
(44, '2020-01-01 04:00:49'),
(45, '2020-01-01 04:05:07'),
(46, '2020-01-01 04:45:29'),
(47, '2020-01-01 05:29:42'),
(48, '2020-01-01 05:30:32'),
(49, '2020-01-01 06:12:13'),
(50, '2020-01-01 06:13:13');

SELECT *, t1.ts - t2.ts FROM ta t1 LEFT JOIN ta t2 ON t1.id = t2.id + 1
WHERE t2.id IS NOT NULL AND t1.ts > t2.ts + interval '15 minute';

結果は

| id |                   ts | id |                   ts |                                         ?column? |
|----|----------------------|----|----------------------|--------------------------------------------------|
| 19 | 2020-01-01T00:59:24Z | 18 | 2020-01-01T00:38:48Z | 0 years 0 mons 0 days 0 hours 20 mins 36.00 secs |
| 24 | 2020-01-01T01:41:06Z | 23 | 2020-01-01T01:08:40Z | 0 years 0 mons 0 days 0 hours 32 mins 26.00 secs |
| 34 | 2020-01-01T02:50:14Z | 33 | 2020-01-01T02:03:20Z | 0 years 0 mons 0 days 0 hours 46 mins 54.00 secs |
| 44 | 2020-01-01T04:00:49Z | 43 | 2020-01-01T03:13:49Z | 0 years 0 mons 0 days 0 hours 47 mins 0.00 secs |
| 46 | 2020-01-01T04:45:29Z | 45 | 2020-01-01T04:05:07Z | 0 years 0 mons 0 days 0 hours 40 mins 22.00 secs |
| 47 | 2020-01-01T05:29:42Z | 46 | 2020-01-01T04:45:29Z | 0 years 0 mons 0 days 0 hours 44 mins 13.00 secs |
| 49 | 2020-01-01T06:12:13Z | 48 | 2020-01-01T05:30:32Z | 0 years 0 mons 0 days 0 hours 41 mins 41.00 secs |

いい感じに取れました

親レコードを保持している作りでもないと自分自身のテーブルと JOIN なんてやらないのですが こういう処理ではあたりまえだったりするのでしょうか


ちなみにダミーデータはこんなコードでつくりました

const rand = x => ~~(Math.random() * x)
const r = () => rand(100) > 20 ? rand(60 * 5) : rand(60 * 30) + 60 * 20

let k = 0
const a = []

for (let i = 0; i < 50; i++) {
const d = new Date(2020, 0, 1, 9, 0, k)
a.push(d.toJSON().slice(0, -5).replace("T", " "))
k += r()
}

const values = a.map((e, i) => `(${i + 1}, '${e}')`).join(",\n")

copy(`INSERT INTO ta VALUES \n${values};`)