Amazon Timestreamにいろいろなクエリーを投げてみた。

GAリリースされたAmazon Timestreamにいろいろなクエリーを投げてみました。

# サンプルデータ

Timestreamのデータベースを作成する際に、サンプルデータが入ったものを作成することができます。
今回はこちらを使ってクエリーを投げてみました。

|fleet|truck_id|fuel_capacity|model|load_capacity|make|measure_value::double|measure_value::varchar|measure_name|time|
|---|---|---|---|---|---|--:|---|---|---|
|Alpha|7128555298|100|359|1000|Peterbilt|7.842702716127531|-|fuel-reading|2020-10-09 14:11:54.267000000
|Alpha|1641688615|100|359|1000|Peterbilt|-|36.1627° N, 86.7816° W|location|2020-10-09 14:11:52.086000000
|Alpha|9355060257|100|Wrecker|500|Peterbilt|12.0|-|speed|2020-10-09 14:11:48.210000000
|Alpha|7219245711|100|Wrecker|500|Peterbilt|19.78241022554396|-|fuel-reading|2020-10-09 14:11:44.294000000
|Alpha|21135517|100|359|1000|Peterbilt|74.1987909949444|-|fuel-reading|2020-10-09 14:11:27.965000000
|Alpha|433496933|100|Wrecker|500|Peterbilt|112.0|-|load|2020-10-09 14:11:27.788000000
|Alpha|3496454495|150|W925|1000|Kenworth|-|36.1627° N, 86.7816° W|location|2020-10-09 14:11:27.742000000
|Alpha|4354044937|100|359|1000|Peterbilt|69.50232420155284|-|fuel-reading|2020-10-09 14:11:25.140000000
|Alpha|5644432615|150|W925|1000|Kenworth|101.12753874492175|-|fuel-reading|2020-10-09 14:11:24.987000000
|Alpha|7602194211|100|359|1000|Peterbilt|138.0|-|load|2020-10-09 14:11:18.528000000

`time`に時刻、`measure_name`測定値の名前が入り、`measure_value::double`または`measure_value::varchar`に計測値が入ってます。
fleetやtruck_idなどは属性値で、`Dimension`と呼ぶようです。

# SQL
いわゆる通常のSQLが使えます。サブクエリも使えるとのことです。

```sql
SELECT * 
FROM "IoT-Sample"."IoT" 
WHERE truck_id = '4132246625' AND measure_name = 'speed'
ORDER BY time DESC
LIMIT 10
```

|fleet|truck_id|fuel_capacity|model|load_capacity|make|measure_value::double|measure_value::varchar|measure_name|time|
|---|---|---|---|---|---|--:|---|---|---|
|Alpha|4132246625|100|359|1000|Peterbilt|67.0|-|speed|2020-10-09 14:09:21.208000000
|Alpha|4132246625|100|359|1000|Peterbilt|73.0|-|speed|2020-10-09 12:24:41.422000000
|Alpha|4132246625|100|359|1000|Peterbilt|16.0|-|speed|2020-10-09 12:16:49.933000000
|Alpha|4132246625|100|359|1000|Peterbilt|3.0|-|speed|2020-10-09 12:00:57.192000000
|Alpha|4132246625|100|359|1000|Peterbilt|35.0|-|speed|2020-10-09 11:51:33.847000000
|Alpha|4132246625|100|359|1000|Peterbilt|49.0|-|speed|2020-10-09 11:48:21.102000000
|Alpha|4132246625|100|359|1000|Peterbilt|50.0|-|speed|2020-10-09 11:47:10.982000000
|Alpha|4132246625|100|359|1000|Peterbilt|52.0|-|speed|2020-10-09 11:01:30.840000000
|Alpha|4132246625|100|359|1000|Peterbilt|54.0|-|speed|2020-10-09 09:22:08.533000000
|Alpha|4132246625|100|359|1000|Peterbilt|19.0|-|speed|2020-10-09 09:03:11.096000000

## Date / Time Functions
時系列データベースというので、時刻関係の関数が用意されており、`bin(timestamp, X unit)`関数は`X unit`に切り捨てしてくれます。
例えば30分ごとの平均を取得。

```sql
SELECT truck_id, avg(measure_value::double) as avg_speed, BIN(time, 30m) as binned_time
FROM "IoT-Sample"."IoT" 
WHERE truck_id = '4132246625' AND measure_name = 'speed'
GROUP BY truck_id, BIN(time, 30m)
ORDER BY binned_time DESC
LIMIT 10
```

|truck_id|avg_speed|binned_time|
|---|--:|---|
|4132246625|67.0|2020-10-09 14:00:00.000000000|
|4132246625|30.666666666666668|2020-10-09 12:00:00.000000000|
|4132246625|44.666666666666664|2020-10-09 11:30:00.000000000|
|4132246625|52.0|2020-10-09 11:00:00.000000000|
|4132246625|36.5|2020-10-09 09:00:00.000000000|

## Timeseries views

Timestreamでは、`timeseries`というデータタイプが用意されいて、`CREATE_TIME_SERIES`関数を使うことで、`timeseries`型に変換できます。
`timeseries`型のデータは、時刻と計測値のペアがJSON形式で格納されます。

```sql
SELECT truck_id, CREATE_TIME_SERIES(time, measure_value::double) as speed
FROM "IoT-Sample"."IoT" 
WHERE measure_name = 'speed'
GROUP BY truck_id
ORDER BY truck_id
LIMIT 10
```

|truck_id|speed|
|---|---|
|1234546252|[<br>{ time: 2020-10-09 09:45:32.192000000, value: 65.0 },<br>{ time: 2020-10-09 11:16:40.034000000, value: 45.0 }...<br>View 8 more row(s)<br>]
|1575739296|[<br>{ time: 2020-10-09 09:15:07.856000000, value: 56.0 },<br>{ time: 2020-10-09 10:29:40.381000000, value: 30.0 }...<br>View 8 more row(s)<br>]
|1588092325|[<br>{ time: 2020-10-09 09:01:49.081000000, value: 19.0 },<br>{ time: 2020-10-09 09:22:57.346000000, value: 67.0 }...<br>View 8 more row(s)<br>]
|1641688615|[<br>{ time: 2020-10-09 09:49:20.010000000, value: 37.0 },<br>{ time: 2020-10-09 10:14:42.971000000, value: 42.0 }...<br>View 8 more row(s)<br>]
|1682738967|[<br>{ time: 2020-10-09 09:07:58.814000000, value: 75.0 },<br>{ time: 2020-10-09 09:28:31.453000000, value: 8.0 }...<br>View 8 more row(s)<br>]
|1712492054|[<br>{ time: 2020-10-09 09:36:27.020000000, value: 44.0 },<br>{ time: 2020-10-09 09:49:04.039000000, value: 65.0 }...<br>View 8 more row(s)<br>]
|1836816173|[<br>{ time: 2020-10-09 08:59:28.330000000, value: 29.0 },<br>{ time: 2020-10-09 09:21:43.510000000, value: 15.0 }...<br>View 8 more row(s)<br>]
|199744055|[<br>{ time: 2020-10-09 09:16:35.398000000, value: 42.0 },<br>{ time: 2020-10-09 09:23:48.835000000, value: 46.0 }...<br>View 8 more row(s)<br>]
|2062792987|[<br>{ time: 2020-10-09 09:30:31.074000000, value: 45.0 },<br>{ time: 2020-10-09 09:54:24.573000000, value: 50.0 }...<br>View 8 more row(s)<br>]
|21135517|[<br>{ time: 2020-10-09 09:23:58.552000000, value: 64.0 },<br>{ time: 2020-10-09 10:43:06.367000000, value: 48.0 }...<br>View 8 more row(s)<br>]

`View X more row(s)`の部分はマネジメントコンソールでは省略して表示されており、クリックすると、詳細が表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41574/1c3e8b97-4f41-da5d-e2c0-2629031fae4a.png)


|time|value|
|---|--:|
|2020-10-09 09:45:32.192000000|65.0|
|2020-10-09 11:16:40.034000000|45.0|
|2020-10-09 11:28:41.938000000|47.0|
|2020-10-09 11:33:45.047000000|55.0|
|2020-10-09 12:19:13.367000000|75.0|
|2020-10-09 12:36:46.970000000|10.0|
|2020-10-09 13:30:40.722000000|44.0|
|2020-10-09 13:56:28.837000000|60.0|
|2020-10-09 14:01:50.177000000|15.0|
|2020-10-09 14:03:38.020000000|0.0|

## Time series functions

時系列データに対する関数も用意されています。例えば、`INTERPOLATE_LINEAR`関数は値を埋めてくれます。

> Fills in missing data using linear interpolation.

下の例は、30分ごとのデータを線形補間で生成しています。`sequence(start, stop, step)`関数で生成する値の間隔を指定していますが、startとstopは実際の値の範囲内じゃないとだめっぽいです。

```SQL
SELECT truck_id, 
  INTERPOLATE_LINEAR(
    CREATE_TIME_SERIES(time, measure_value::double),
    SEQUENCE(min(time), max(time), 30m)
  ) as speed
FROM "IoT-Sample"."IoT" 
WHERE measure_name = 'speed'
GROUP BY truck_id
ORDER BY truck_id
LIMIT 10
```

|truck_id|speed|
|---|---|
|1234546252|[<br>{ time: 2020-10-09 09:45:32.192000000, value: 65.0 },<br>{ time: 2020-10-09 10:15:32.192000000, value: 58.416049695656895 }...<br>View 7 more row(s)<br>]|
|1575739296|[<br>{ time: 2020-10-09 09:15:07.856000000, value: 56.0 },<br>{ time: 2020-10-09 09:45:07.856000000, value: 45.53611215141335 }...<br>View 8 more row(s)<br>]|
|1588092325|[<br>{ time: 2020-10-09 09:01:49.081000000, value: 19.0 },<br>{ time: 2020-10-09 09:31:49.081000000, value: 68.91556160771218 }...<br>View 7 more row(s)<br>]|
|1641688615|[<br>{ time: 2020-10-09 09:49:20.010000000, value: 37.0 },<br>{ time: 2020-10-09 10:19:20.010000000, value: 47.34174652449723 }...<br>View 7 more row(s)<br>]|
|1682738967|[<br>{ time: 2020-10-09 09:07:58.814000000, value: 75.0 },<br>{ time: 2020-10-09 09:37:58.814000000, value: 9.162118557623112 }...<br>View 7 more row(s)<br>]|
|1712492054|[<br>{ time: 2020-10-09 09:36:27.020000000, value: 44.0 },<br>{ time: 2020-10-09 10:06:27.020000000, value: 34.61657724615213 }...<br>View 6 more row(s)<br>]|
|1836816173|[<br>{ time: 2020-10-09 08:59:28.330000000, value: 29.0 },<br>{ time: 2020-10-09 09:29:28.330000000, value: 31.383254052802055 }...<br>View 8 more row(s)<br>]|
|199744055|[<br>{ time: 2020-10-09 09:16:35.398000000, value: 42.0 },<br>{ time: 2020-10-09 09:46:35.398000000, value: 13.60871678326465 }...<br>View 6 more row(s)<br>]|
|2062792987|[<br>{ time: 2020-10-09 09:30:31.074000000, value: 45.0 },<br>{ time: 2020-10-09 10:00:31.074000000, value: 52.07211022782296 }...<br>View 7 more row(s)<br>]|
|21135517|[<br>{ time: 2020-10-09 09:23:58.552000000, value: 64.0 },<br>{ time: 2020-10-09 09:53:58.552000000, value: 57.93405176907693 }...<br>View 8 more row(s)<br>]|

データが30分おきになっています。

|time|value|
|---|--:|
|2020-10-09 09:45:32.192000000|65.0|
|2020-10-09 10:15:32.192000000|58.416049695656895|
|2020-10-09 10:45:32.192000000|51.83209939131379|
|2020-10-09 11:15:32.192000000|45.248149086970685|
|2020-10-09 11:45:32.192000000|60.18373944405349|
|2020-10-09 12:15:32.192000000|73.37867258972554|
|2020-10-09 12:45:32.192000000|15.522237945272241|
|2020-10-09 13:15:32.192000000|34.44762245218557|
|2020-10-09 13:45:32.192000000|53.213475743081105|

`min(time)`からの30分間隔は少し気持ちが悪いので、00分、30分ごとにするにはこんな感じでしょうか

```SQL
SELECT truck_id, 
  INTERPOLATE_LINEAR(
    CREATE_TIME_SERIES(time, measure_value::double),
    SEQUENCE(bin(min(time)+ 1h ,1h), max(time), 30m)
  ) as speed
FROM "IoT-Sample"."IoT" 
WHERE measure_name = 'speed'
GROUP BY truck_id
ORDER BY truck_id
LIMIT 10
```

|truck_id|speed|
|---|---|
|1234546252|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 61.82577514127146 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 55.24182483692835 }...<br>View 7 more row(s)<br>]
|1575739296|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 40.34983728430808 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 29.77482942057512 }...<br>View 6 more row(s)<br>]
|1588092325|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 46.790964648508535 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 63.37907403508092 }...<br>View 5 more row(s)<br>]
|1641688615|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 39.1011371926136 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 59.68175770780711 }...<br>View 6 more row(s)<br>]
|1682738967|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 16.966425157908184 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 70.67966775160798 }...<br>View 6 more row(s)<br>]
|1712492054|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 45.89098423361806 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 12.212910765588772 }...<br>View 5 more row(s)<br>]
|1836816173|[<br>{ time: 2020-10-09 09:00:00.000000000, value: 28.667924923980287 },<br>{ time: 2020-10-09 09:30:00.000000000, value: 32.49950906732863 }...<br>View 8 more row(s)<br>]
|199744055|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 45.46459879696737 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 3.39022689038606 }...<br>View 4 more row(s)<br>]
|2062792987|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 50.54843613050327 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 21.4323793899003 }...<br>View 6 more row(s)<br>]
|21135517|[<br>{ time: 2020-10-09 10:00:00.000000000, value: 56.71598240453767 },<br>{ time: 2020-10-09 10:30:00.000000000, value: 50.6500341736146 }...<br>View 6 more row(s)<br>]