Para obtener capacidades similares a las de Amazon Timestream, considere Amazon Timestream LiveAnalytics para InfluxDB. Ofrece una ingesta de datos simplificada y tiempos de respuesta a las consultas en milisegundos de un solo dígito para realizar análisis en tiempo real. Obtenga más información [aquí](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html).
Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.
# Consultas con funciones de agregación
A continuación, se muestra un ejemplo de conjunto de datos de un escenario de IoT para ilustrar las consultas con funciones agregadas.
**Topics**
+ [Datos de ejemplo](#sample-queries.iot-scenarios.example-data)
+ [Consultas de ejemplo](#sample-queries.iot-scenarios.example-queries)
## Datos de ejemplo
Timestream le permite almacenar y analizar los datos de los sensores de IoT, como la ubicación, el consumo de combustible, la velocidad y la capacidad de carga de una o más flotas de camiones, para permitir una gestión eficaz de la flota. A continuación, se muestra el esquema y algunos de los datos de una tabla iot\_trucks que almacena la telemetría, como la ubicación, el consumo de combustible, la velocidad y la capacidad de carga de los camiones.
| Time | truck\_id | Make | Modelo | Fleet | 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 | carga | 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 | ubicación | null | 47,6062 N, 122,3321 W |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1 000 | fuel\_reading | 10.1 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1 000 | carga | 950,3 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1 000 | speed | 50,8 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1 000 | ubicación | null | 40,7128 grados N, 74,0060 grados W |
## Consultas de ejemplo
Obtener una lista de todos los atributos y valores de los sensores que se monitorean para cada camión de la flota.
```
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
```
Obtener la lectura de combustible más reciente de cada camión de la flota en las últimas 24 horas.
```
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
```
Identificar los camiones que han estado funcionando con poco combustible (menos del 10 %) en las últimas 48 horas:
```
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
)
```
Calcular la carga media y la velocidad máxima de cada camión durante la última semana:
```
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
```
Obtener la eficiencia de carga de cada camión durante la semana pasada:
```
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
```