お知らせ
過去にQiitaに投稿した内容のアーカイブです。
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が使えます。サブクエリも使えるとのことです。
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分ごとの平均を取得。
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形式で格納されます。
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 | [ { time: 2020-10-09 09:45:32.192000000, value: 65.0 }, { time: 2020-10-09 11:16:40.034000000, value: 45.0 }... View 8 more row(s) ] |
| 1575739296 | [ { time: 2020-10-09 09:15:07.856000000, value: 56.0 }, { time: 2020-10-09 10:29:40.381000000, value: 30.0 }... View 8 more row(s) ] |
| 1588092325 | [ { time: 2020-10-09 09:01:49.081000000, value: 19.0 }, { time: 2020-10-09 09:22:57.346000000, value: 67.0 }... View 8 more row(s) ] |
| 1641688615 | [ { time: 2020-10-09 09:49:20.010000000, value: 37.0 }, { time: 2020-10-09 10:14:42.971000000, value: 42.0 }... View 8 more row(s) ] |
| 1682738967 | [ { time: 2020-10-09 09:07:58.814000000, value: 75.0 }, { time: 2020-10-09 09:28:31.453000000, value: 8.0 }... View 8 more row(s) ] |
| 1712492054 | [ { time: 2020-10-09 09:36:27.020000000, value: 44.0 }, { time: 2020-10-09 09:49:04.039000000, value: 65.0 }... View 8 more row(s) ] |
| 1836816173 | [ { time: 2020-10-09 08:59:28.330000000, value: 29.0 }, { time: 2020-10-09 09:21:43.510000000, value: 15.0 }... View 8 more row(s) ] |
| 199744055 | [ { time: 2020-10-09 09:16:35.398000000, value: 42.0 }, { time: 2020-10-09 09:23:48.835000000, value: 46.0 }... View 8 more row(s) ] |
| 2062792987 | [ { time: 2020-10-09 09:30:31.074000000, value: 45.0 }, { time: 2020-10-09 09:54:24.573000000, value: 50.0 }... View 8 more row(s) ] |
| 21135517 | [ { time: 2020-10-09 09:23:58.552000000, value: 64.0 }, { time: 2020-10-09 10:43:06.367000000, value: 48.0 }... View 8 more row(s) ] |
View X more row(s)の部分はマネジメントコンソールでは省略して表示されており、クリックすると、詳細が表示されます。
| 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は実際の値の範囲内じゃないとだめっぽいです。
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 | [ { time: 2020-10-09 09:45:32.192000000, value: 65.0 }, { time: 2020-10-09 10:15:32.192000000, value: 58.416049695656895 }... View 7 more row(s) ] |
| 1575739296 | [ { time: 2020-10-09 09:15:07.856000000, value: 56.0 }, { time: 2020-10-09 09:45:07.856000000, value: 45.53611215141335 }... View 8 more row(s) ] |
| 1588092325 | [ { time: 2020-10-09 09:01:49.081000000, value: 19.0 }, { time: 2020-10-09 09:31:49.081000000, value: 68.91556160771218 }... View 7 more row(s) ] |
| 1641688615 | [ { time: 2020-10-09 09:49:20.010000000, value: 37.0 }, { time: 2020-10-09 10:19:20.010000000, value: 47.34174652449723 }... View 7 more row(s) ] |
| 1682738967 | [ { time: 2020-10-09 09:07:58.814000000, value: 75.0 }, { time: 2020-10-09 09:37:58.814000000, value: 9.162118557623112 }... View 7 more row(s) ] |
| 1712492054 | [ { time: 2020-10-09 09:36:27.020000000, value: 44.0 }, { time: 2020-10-09 10:06:27.020000000, value: 34.61657724615213 }... View 6 more row(s) ] |
| 1836816173 | [ { time: 2020-10-09 08:59:28.330000000, value: 29.0 }, { time: 2020-10-09 09:29:28.330000000, value: 31.383254052802055 }... View 8 more row(s) ] |
| 199744055 | [ { time: 2020-10-09 09:16:35.398000000, value: 42.0 }, { time: 2020-10-09 09:46:35.398000000, value: 13.60871678326465 }... View 6 more row(s) ] |
| 2062792987 | [ { time: 2020-10-09 09:30:31.074000000, value: 45.0 }, { time: 2020-10-09 10:00:31.074000000, value: 52.07211022782296 }... View 7 more row(s) ] |
| 21135517 | [ { time: 2020-10-09 09:23:58.552000000, value: 64.0 }, { time: 2020-10-09 09:53:58.552000000, value: 57.93405176907693 }... View 8 more row(s) ] |
データが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分ごとにするにはこんな感じでしょうか
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 | [ { time: 2020-10-09 10:00:00.000000000, value: 61.82577514127146 }, { time: 2020-10-09 10:30:00.000000000, value: 55.24182483692835 }... View 7 more row(s) ] |
| 1575739296 | [ { time: 2020-10-09 10:00:00.000000000, value: 40.34983728430808 }, { time: 2020-10-09 10:30:00.000000000, value: 29.77482942057512 }... View 6 more row(s) ] |
| 1588092325 | [ { time: 2020-10-09 10:00:00.000000000, value: 46.790964648508535 }, { time: 2020-10-09 10:30:00.000000000, value: 63.37907403508092 }... View 5 more row(s) ] |
| 1641688615 | [ { time: 2020-10-09 10:00:00.000000000, value: 39.1011371926136 }, { time: 2020-10-09 10:30:00.000000000, value: 59.68175770780711 }... View 6 more row(s) ] |
| 1682738967 | [ { time: 2020-10-09 10:00:00.000000000, value: 16.966425157908184 }, { time: 2020-10-09 10:30:00.000000000, value: 70.67966775160798 }... View 6 more row(s) ] |
| 1712492054 | [ { time: 2020-10-09 10:00:00.000000000, value: 45.89098423361806 }, { time: 2020-10-09 10:30:00.000000000, value: 12.212910765588772 }... View 5 more row(s) ] |
| 1836816173 | [ { time: 2020-10-09 09:00:00.000000000, value: 28.667924923980287 }, { time: 2020-10-09 09:30:00.000000000, value: 32.49950906732863 }... View 8 more row(s) ] |
| 199744055 | [ { time: 2020-10-09 10:00:00.000000000, value: 45.46459879696737 }, { time: 2020-10-09 10:30:00.000000000, value: 3.39022689038606 }... View 4 more row(s) ] |
| 2062792987 | [ { time: 2020-10-09 10:00:00.000000000, value: 50.54843613050327 }, { time: 2020-10-09 10:30:00.000000000, value: 21.4323793899003 }... View 6 more row(s) ] |
| 21135517 | [ { time: 2020-10-09 10:00:00.000000000, value: 56.71598240453767 }, { time: 2020-10-09 10:30:00.000000000, value: 50.6500341736146 }... View 6 more row(s) ] |
