BigQuery 大量の時間データから利用時間を求めてみた

# とある興味からシステムの利用時間を調べたくなったので考えた

## テストデータ（大量の記録された時間データがあると想定）

```sql
SELECT
 *
FROM
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -650 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -600 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -200 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -150 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -50  AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp())      AS time_at)
;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/147882/0f8caff6-25c2-711f-962e-9fbb36af18f9.png)


## LAG関数を使って1つ前の時間を取得する
```sql
SELECT
 time_at,
 LAG(time_at, 1) OVER (ORDER BY time_at asc) as before_time_at
FROM
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -650 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -600 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -200 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -150 AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -50  AS time_at),
 (SELECT TIMESTAMP_TO_SEC(current_timestamp())      AS time_at)
;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/147882/72d1f04a-0a8c-636e-2caa-4f19a685da2d.png)


## 1つ前の時間との差分を計算
```sql
SELECT
 time_at,
 before_time_at,
 (time_at - before_time_at) AS diff_sec
FROM
 (
　SELECT
　 time_at,
　 LAG(time_at, 1) OVER (ORDER BY time_at asc) as before_time_at
　FROM
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -650 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -600 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -200 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -150 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -50  AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp())      AS time_at)
 )
;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/147882/5c70f8f1-90ec-3316-d4d7-fb376b87275e.png)


## 5分以上の未操作は除外してみる
```sql
SELECT
 time_at,
 before_time_at,
 IF((time_at - before_time_at) > (5 * 60), null, (time_at - before_time_at)) AS diff_sec
FROM
 (
　SELECT
　 time_at,
　 LAG(time_at, 1) OVER (ORDER BY time_at asc) as before_time_at
　FROM
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -650 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -600 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -200 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -150 AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -50  AS time_at),
　 (SELECT TIMESTAMP_TO_SEC(current_timestamp())      AS time_at)
 )
;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/147882/f8cc4b76-70e9-9031-177d-5d9a8dd3c25d.png)


## 後は集計

```sql
SELECT
 SUM(diff_sec)
FROM
 (
 SELECT
  time_at,
  before_time_at,
  IF((time_at - before_time_at) > (5 * 60), null, (time_at - before_time_at)) AS diff_sec
 FROM
  (
 　SELECT
 　 time_at,
 　 LAG(time_at, 1) OVER (ORDER BY time_at asc) as before_time_at
 　FROM
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -650 AS time_at),
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -600 AS time_at),
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -200 AS time_at),
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -150 AS time_at),
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp()) -50  AS time_at),
 　 (SELECT TIMESTAMP_TO_SEC(current_timestamp())      AS time_at)
  )
 )
;
```

![image](https://qiita-image-store.s3.amazonaws.com/0/147882/d082e101-1d7e-aaf7-ad88-6514bdf70bd6.png)

---
# おまけ
BigQueryで日付の計算する時は、unixtimeで計算した方が良いぞ。
理由は忘れたが、変な動作したはず。