要获得与亚马逊 Timestream 类似的功能 LiveAnalytics，可以考虑适用于 InfluxDB 的亚马逊 Timestream。适用于 InfluxDB 的 Amazon Timestream 提供简化的数据摄取和个位数毫秒级的查询响应时间，以实现实时分析。点击[此处](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html)了解更多信息。

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

# 查询示例
<a name="sample-queries"></a>

本节包括 Timestream 的查询语言 LiveAnalytics的示例用例。

**Topics**
+ [简单查询](sample-queries.basic-scenarios.md)
+ [包含时间序列函数的查询](sample-queries.devops-scenarios.md)
+ [使用聚合函数查询](sample-queries.iot-scenarios.md)

# 简单查询
<a name="sample-queries.basic-scenarios"></a>

以下是最近为表添加的 10 个数据点。

```
SELECT * FROM <database_name>.<table_name>
ORDER BY time DESC
LIMIT 10
```

以下是特定度量的 5 个最早数据点。

```
SELECT * FROM <database_name>.<table_name>
WHERE measure_name = '<measure_name>'
ORDER BY time ASC
LIMIT 5
```

以下内容采用纳秒级粒度的时间戳。

```
SELECT now() AS time_now
, now() - (INTERVAL '12' HOUR) AS twelve_hour_earlier -- Compatibility with ANSI SQL 
, now() - 12h AS also_twelve_hour_earlier -- Convenient time interval literals
, ago(12h) AS twelve_hours_ago -- More convenience with time functionality
, bin(now(), 10m) AS time_binned -- Convenient time binning support
, ago(50ns) AS fifty_ns_ago -- Nanosecond support
, now() + (1h + 50ns) AS hour_fifty_ns_future
```

多度量记录的度量值由列名进行标识。单度量记录的度量值由 `measure_value::<data_type>` 进行表示，其中 `<data_type>` 为 `double`、`bigint`、`boolean` 或 `varchar` 其中之一（如 [支持的数据类型](supported-data-types.md) 中所述）。有关度量值建模方式的更多信息，请参阅[单表与多表](https://docs.aws.amazon.com/timestream/latest/developerguide/data-modeling.html#data-modeling-multiVsinglerecords)。

以下内容从多度量记录中检索名为 `speed` 的度量的值，其中 `measure_name` 为 `IoTMulti-stats`。

```
SELECT speed FROM <database_name>.<table_name> where measure_name = 'IoTMulti-stats'
```

以下内容从单度量记录中检索 `double` 值，其中`measure_name` 为 `load`。

```
SELECT measure_value::double FROM <database_name>.<table_name> WHERE measure_name = 'load'
```

# 包含时间序列函数的查询
<a name="sample-queries.devops-scenarios"></a>

**Topics**
+ [示例数据集和查询](#sample-queries.devops-scenarios.example)

## 示例数据集和查询
<a name="sample-queries.devops-scenarios.example"></a>

您可以使用 Timestream LiveAnalytics 来了解和提高您的服务和应用程序的性能和可用性。以下是示例表及其上运行的示例查询。

表 `ec2_metrics` 存储遥测数据，例如 EC2 实例的 CPU 利用率及其他指标。您可以查看下表。


| 时间 | region | az | 主机名 | measure\$1name | measure\$1value::double | measure\$1value::bigint | 
| --- | --- | --- | --- | --- | --- | --- | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  cpu\$1utilization  |  35.1  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  memory\$1utilization  |  55.3  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  1500  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  6,700  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  cpu\$1utilization  |  38.5  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  memory\$1utilization  |  58.4  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  23,000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  12000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  cpu\$1utilization  |  45.0  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  memory\$1utilization  |  65.8  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  15000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  836,000  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  cpu\$1utilization  |  55.2  |  null  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  memory\$1utilization  |  75.0  |  null  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  1,245  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  68,432  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  cpu\$1utilization  |  65.6  |  null  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  memory\$1utilization  |  85.3  |  null  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  1,245  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  68,432  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  cpu\$1utilization  |  12.1  |  null  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  memory\$1utilization  |  32.0  |  null  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  1,400  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  345  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  cpu\$1utilization  |  15.3  |  null  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  memory\$1utilization  |  35.4  |  null  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  23  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us–east–1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  0  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  cpu\$1utilization  |  44.0  |  null  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  memory\$1utilization  |  64.2  |  null  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  1,450  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us–east–1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  200  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  cpu\$1utilization  |  66.4  |  null  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  memory\$1utilization  |  86.3  |  null  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  300  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us–east–1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  423  | 

计算过去 2 小时内特定 EC2 主机的平均 CPU 利用率、p90、p95 和 p99：

```
SELECT region, az, hostname, BIN(time, 15s) AS binned_timestamp,
    ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.9), 2) AS p90_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.95), 2) AS p95_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.99), 2) AS p99_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY region, hostname, az, BIN(time, 15s)
ORDER BY binned_timestamp ASC
```

确定 CPU 利用率比过去 2 小时整个实例集平均 CPU 利用率高出 10% 或以上的 EC2 主机：

```
WITH avg_fleet_utilization AS (
    SELECT COUNT(DISTINCT hostname) AS total_host_count, AVG(measure_value::double) AS fleet_avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
), avg_per_host_cpu AS (
    SELECT region, az, hostname, AVG(measure_value::double) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
    GROUP BY region, az, hostname
)
SELECT region, az, hostname, avg_cpu_utilization, fleet_avg_cpu_utilization
FROM avg_fleet_utilization, avg_per_host_cpu
WHERE avg_cpu_utilization > 1.1 * fleet_avg_cpu_utilization
ORDER BY avg_cpu_utilization DESC
```

计算过去 2 小时内特定 EC2 主机的 CPU 平均利用率，按 30 秒间隔进行分箱：

```
SELECT BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
ORDER BY binned_timestamp ASC
```

计算过去 2 小时内特定 EC2 主机的 CPU 平均利用率，按 30 秒间隔进行分箱，并使用线性插值填补缺失值：

```
WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LINEAR(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```

计算过去 2 小时内特定 EC2 主机的 CPU 平均利用率，按 30 秒间隔进行分箱，并使用基于末次观测值结转的插值填补缺失值：

```
WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LOCF(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```

# 使用聚合函数查询
<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\$1trucks 表的架构和部分数据，该表存储卡车的位置、油耗、速度和载重等遥测数据。


| 时间 | truck\$1id | Make | 模型 | Fleet | fuel\$1capacity | load\$1capacity | measure\$1name | measure\$1value::double | measure\$1value::varchar | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
|  2019-12-04 19:00:00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  fuel\$1reading  |  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  |  地点  |  null  |  47.6062 N, 122.3321 W  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  fuel\$1reading  |  10.1  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  负载  |  950.3  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  speed  |  50.8  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  地点  |  null  |  北纬 40.7128 度，西经 74.0060 度  | 

## 示例查询
<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
```