Untuk kemampuan serupa dengan Amazon Timestream LiveAnalytics, pertimbangkan Amazon Timestream untuk InfluxDB. Ini menawarkan konsumsi data yang disederhanakan dan waktu respons kueri milidetik satu digit untuk analitik waktu nyata. Pelajari lebih lanjut [di sini](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html).

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

# Kueri dengan fungsi agregat
<a name="sample-queries.iot-scenarios"></a>

Di bawah ini adalah contoh contoh skenario IoT kumpulan data untuk menggambarkan query dengan fungsi agregat.

**Topics**
+ [Contoh data](#sample-queries.iot-scenarios.example-data)
+ [Kueri contoh](#sample-queries.iot-scenarios.example-queries)

## Contoh data
<a name="sample-queries.iot-scenarios.example-data"></a>

Timestream memungkinkan Anda menyimpan dan menganalisis data sensor IoT seperti lokasi, konsumsi bahan bakar, kecepatan, dan kapasitas muat satu atau lebih armada truk untuk memungkinkan manajemen armada yang efektif. Di bawah ini adalah skema dan beberapa data tabel iot\_trucks yang menyimpan telemetri seperti lokasi, konsumsi bahan bakar, kecepatan, dan kapasitas muat truk.


| Waktu | truck\_id | Membuat | Model | Armada | kapasitas bahan bakar | load\_capacity | ukuran\_nama | ukuran\_nilai: :ganda | ukuran\_nilai: :varchar | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 2019-12-04 19:00:00.000 000000 | 123456781 | GMC | Astro | Alfa | 100 | 500 | bahan bakar\_membaca | 65.2 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456781 | GMC | Astro | Alfa | 100 | 500 | muat | 400,0 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456781 | GMC | Astro | Alfa | 100 | 500 | kecepatan | 90.2 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456781 | GMC | Astro | Alfa | 100 | 500 | lokasi | null | 47.6062 N, 122.3321 W | 
| 2019-12-04 19:00:00.000 000000 | 123456782 | Kenworth | W900 | Alfa | 150 | 1000 | bahan bakar\_membaca | 10.1 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456782 | Kenworth | W900 | Alfa | 150 | 1000 | muat | 950,3 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456782 | Kenworth | W900 | Alfa | 150 | 1000 | kecepatan | 50,8 | null | 
| 2019-12-04 19:00:00.000 000000 | 123456782 | Kenworth | W900 | Alfa | 150 | 1000 | lokasi | null | 40,7128 derajat N, 74,0060 derajat W | 

## Kueri contoh
<a name="sample-queries.iot-scenarios.example-queries"></a>

Dapatkan daftar semua atribut sensor dan nilai yang dipantau untuk setiap truk di armada.

```
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
```

Dapatkan pembacaan bahan bakar terbaru dari setiap truk di armada dalam 24 jam terakhir.

```
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
```

Identifikasi truk yang menggunakan bahan bakar rendah (kurang dari 10%) dalam 48 jam terakhir:

```
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
)
```

Temukan beban rata-rata dan kecepatan maksimal untuk setiap truk selama seminggu terakhir:

```
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
```

Dapatkan efisiensi beban untuk setiap truk selama seminggu terakhir:

```
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
```