使用聚合函数的查询

以下是物联网场景示例数据集的示例，用于说明使用聚合函数的查询。

示例数据

Timestream 使您能够存储和分析物联网传感器数据，例如一个或多个卡车车队的位置、油耗、速度和负载能力，从而实现有效的车队管理。以下是 iot_trucks 表的架构和一些数据，该表存储了卡车的位置、油耗、速度和装载能力等遥测数据。

Time	卡车_ID	Make	模型	实例集	燃料容量	负载容量	measure_name	measure_value::double	measure_value::varchar
2019-12-04 19:00:00.000 000 000	123456781	GMC	Astro	Alpha	100	500	fuel_reading	65.2	null
2019-12-04 19:00:00.000 000 000	123456781	GMC	Astro	Alpha	100	500	负载	400.0	null
2019-12-04 19:00:00.000 00000 000	123456781	GMC	Astro	Alpha	100	500	speed	90.2	null
2019-12-04 19:00:00.000 00000 000	123456781	GMC	Astro	Alpha	100	500	location	null	47.6062 N，122.3321 W
2019-12-04 19:00:00.000 00000 000	123456782	肯沃思	W900	Alpha	150	1000	fuel_reading	10.1	null
2019-12-04 19:00:00.000 00000 000	123456782	肯沃思	W900	Alpha	150	1000	负载	950.3	null
2019-12-04 19:00:00.000 00000 000	123456782	肯沃思	W900	Alpha	150	1000	speed	50.8	null
2019-12-04 19:00:00.000 00000 000	123456782	肯沃思	W900	Alpha	150	1000	location	null	40.7128 北 40.7128 度，西 74.0060 度

示例查询

获取车队中每辆卡车正在监控的所有传感器属性和值的列表。

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