如需與 Amazon Timestream for LiveAnalytics 類似的功能，請考慮使用 Amazon Timestream for InfluxDB。它提供簡化的資料擷取和單一位數毫秒查詢回應時間，以進行即時分析。[在這裡](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html)進一步了解。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# 具有彙總函數的查詢
<a name="sample-queries.iot-scenarios"></a>

以下是 IoT 案例範例資料集，以說明具有彙總函數的查詢。

**Topics**
+ [範例資料](#sample-queries.iot-scenarios.example-data)
+ [查詢範例](#sample-queries.iot-scenarios.example-queries)

## 範例資料
<a name="sample-queries.iot-scenarios.example-data"></a>

Timestream 可讓您存放和分析 IoT 感應器資料，例如一或多個卡車機群的位置、油耗、速度和負載容量，以實現有效的機群管理。以下是存放遙測的資料表 iot\_trucks 結構描述和一些資料，例如卡車的位置、油耗、速度和負載容量。


| 時間 | truck\_id | Make | 模型 | 機群 | fuel\_capacity | load\_capacity | measure\_name | measure\_value::double | measure\_value::varchar | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
|  2019-12-04 19：00：00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  fuel\_reading  |  65.2  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  載入  |  400.0  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  speed  |  90.2  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  location  |  null  |  47.6062 N、122.3321 W  | 
|  2019-12-04 19：00：00.000000000  |  123456782  |  根沃思  |  W900  |  Alpha  |  150  |  1000  |  fuel\_reading  |  10.1  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456782  |  根沃思  |  W900  |  Alpha  |  150  |  1000  |  載入  |  950.3  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456782  |  根沃思  |  W900  |  Alpha  |  150  |  1000  |  speed  |  50.8  |  null  | 
|  2019-12-04 19：00：00.000000000  |  123456782  |  根沃思  |  W900  |  Alpha  |  150  |  1000  |  location  |  null  |  40.7128 度 N、74.0060 度 W  | 

## 查詢範例
<a name="sample-queries.iot-scenarios.example-queries"></a>

取得機群中每輛卡車監控的所有感應器屬性和值清單。

```
SELECT
    truck_id,
    fleet,
    fuel_capacity,
    model,
    load_capacity,
    make,
    measure_name
FROM "sampleDB".IoT
GROUP BY truck_id, fleet, fuel_capacity, model, load_capacity, make, measure_name
```

取得過去 24 小時內機群中每輛卡車的最新燃料讀數。

```
WITH latest_recorded_time AS (
    SELECT
        truck_id,
        max(time) as latest_time
    FROM "sampleDB".IoT
    WHERE measure_name = 'fuel-reading'
    AND time >= ago(24h)
    GROUP BY truck_id
)
SELECT
    b.truck_id,
    b.fleet,
    b.make,
    b.model,
    b.time,
    b.measure_value::double as last_reported_fuel_reading
FROM
latest_recorded_time a INNER JOIN "sampleDB".IoT b
ON a.truck_id = b.truck_id AND b.time = a.latest_time
WHERE b.measure_name = 'fuel-reading'
AND b.time > ago(24h)
ORDER BY b.truck_id
```

識別過去 48 小時內以低油量 （低於 10 %) 執行的卡車：

```
WITH low_fuel_trucks AS (
    SELECT time, truck_id, fleet, make, model, (measure_value::double/cast(fuel_capacity as double)*100) AS fuel_pct
    FROM "sampleDB".IoT
    WHERE time >= ago(48h)
    AND (measure_value::double/cast(fuel_capacity as double)*100) < 10
    AND measure_name = 'fuel-reading'
),
other_trucks AS (
SELECT time, truck_id, (measure_value::double/cast(fuel_capacity as double)*100) as remaining_fuel
    FROM "sampleDB".IoT
    WHERE time >= ago(48h)
    AND truck_id IN (SELECT truck_id FROM low_fuel_trucks)
    AND (measure_value::double/cast(fuel_capacity as double)*100) >= 10
    AND measure_name = 'fuel-reading'
),
trucks_that_refuelled AS (
    SELECT a.truck_id
    FROM low_fuel_trucks a JOIN other_trucks b
    ON a.truck_id = b.truck_id AND b.time >= a.time
)
SELECT DISTINCT truck_id, fleet, make, model, fuel_pct
FROM low_fuel_trucks
WHERE truck_id NOT IN (
    SELECT truck_id FROM trucks_that_refuelled
)
```

尋找過去一週每輛卡車的平均負載和最高速度：

```
SELECT
    bin(time, 1d) as binned_time,
    fleet,
    truck_id,
    make,
    model,
    AVG(
        CASE WHEN measure_name = 'load' THEN measure_value::double ELSE NULL END
    ) AS avg_load_tons,
    MAX(
        CASE WHEN measure_name = 'speed' THEN measure_value::double ELSE NULL END
    ) AS max_speed_mph
FROM "sampleDB".IoT
WHERE time >= ago(7d)
AND measure_name IN ('load', 'speed')
GROUP BY fleet, truck_id, make, model, bin(time, 1d)
ORDER BY truck_id
```

取得過去一週每輛卡車的負載效率：

```
WITH average_load_per_truck AS (
    SELECT
        truck_id,
        avg(measure_value::double)  AS avg_load
    FROM "sampleDB".IoT
    WHERE measure_name = 'load'
    AND time >= ago(7d)
    GROUP BY truck_id, fleet, load_capacity, make, model
),
truck_load_efficiency AS (
    SELECT
        a.truck_id,
        fleet,
        load_capacity,
        make,
        model,
        avg_load,
        measure_value::double,
        time,
        (measure_value::double*100)/avg_load as load_efficiency -- , approx_percentile(avg_load_pct, DOUBLE '0.9')
    FROM "sampleDB".IoT a JOIN average_load_per_truck b
    ON a.truck_id = b.truck_id
    WHERE a.measure_name = 'load'
)
SELECT
    truck_id,
    time,
    load_efficiency
FROM truck_load_efficiency
ORDER BY truck_id, time
```