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

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

# 具有時間序列函數的查詢
<a name="sample-queries.devops-scenarios"></a>

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

## 資料集和查詢範例
<a name="sample-queries.devops-scenarios.example"></a>

您可以使用 Timestream for LiveAnalytics 來了解並改善服務和應用程式的效能和可用性。以下是在該資料表上執行的範例資料表和範例查詢。

資料表`ec2_metrics`存放遙測資料，例如 CPU 使用率和來自 EC2 執行個體的其他指標。您可以檢視下表。


| 時間 | region | az | Hostname (主機名稱) | measure\_name | measure\_value::double | measure\_value::bigint | 
| --- | --- | --- | --- | --- | --- | --- | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1a | 前端01 | cpu\_utilization | 35.1 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1a | 前端01 | memory\_utilization | 55.3 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_in | null | 1,500 | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_out | null | 6,700 | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1b | 前端02 | cpu\_utilization | 38.5 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1b | 前端02 | memory\_utilization | 58.4 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_in | null | 23，000 | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_out | null | 12,000 | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1c | 前端03 | cpu\_utilization | 45.0 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1c | 前端03 | memory\_utilization | 65.8 | null | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_in | null | 15,000 | 
| 2019-12-04 19：00：00.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_out | null | 836，000 | 
| 2019-12-04 19：00：05.000000000 | us-east-1 | us-east-1a | 前端01 | cpu\_utilization | 55.2 | null | 
| 2019-12-04 19：00：05.000000000 | us-east-1 | us-east-1a | 前端01 | memory\_utilization | 75.0 | null | 
| 2019-12-04 19：00：05.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_in | null | 1，245 | 
| 2019-12-04 19：00：05.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_out | null | 68，432 | 
| 2019-12-04 19：00：08.000000000 | us-east-1 | us-east-1b | 前端02 | cpu\_utilization | 65.6 | null | 
| 2019-12-04 19：00：08.000000000 | us-east-1 | us-east-1b | 前端02 | memory\_utilization | 85.3 | null | 
| 2019-12-04 19：00：08.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_in | null | 1，245 | 
| 2019-12-04 19：00：08.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_out | null | 68，432 | 
| 2019-12-04 19：00：20.000000000 | us-east-1 | us-east-1c | 前端03 | cpu\_utilization | 12.1 | null | 
| 2019-12-04 19：00：20.000000000 | us-east-1 | us-east-1c | 前端03 | memory\_utilization | 32.0 | null | 
| 2019-12-04 19：00：20.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_in | null | 1，400 | 
| 2019-12-04 19：00：20.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_out | null | 345 | 
| 2019-12-04 19：00：10.000000000 | us-east-1 | us-east-1a | 前端01 | cpu\_utilization | 15.3 | null | 
| 2019-12-04 19：00：10.000000000 | us-east-1 | us-east-1a | 前端01 | memory\_utilization | 35.4 | null | 
| 2019-12-04 19：00：10.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_in | null | 23 | 
| 2019-12-04 19：00：10.000000000 | us-east-1 | us-east-1a | 前端01 | network\_bytes\_out | null | 0 | 
| 2019-12-04 19：00：16.000000000 | us-east-1 | us-east-1b | 前端02 | cpu\_utilization | 44.0 | null | 
| 2019-12-04 19：00：16.000000000 | us-east-1 | us-east-1b | 前端02 | memory\_utilization | 64.2 | null | 
| 2019-12-04 19：00：16.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_in | null | 1，450 | 
| 2019-12-04 19：00：16.000000000 | us-east-1 | us-east-1b | 前端02 | network\_bytes\_out | null | 200 | 
| 2019-12-04 19：00：40.000000000 | us-east-1 | us-east-1c | 前端03 | cpu\_utilization | 66.4 | null | 
| 2019-12-04 19：00：40.000000000 | us-east-1 | us-east-1c | 前端03 | memory\_utilization | 86.3 | null | 
| 2019-12-04 19：00：40.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_in | null | 300 | 
| 2019-12-04 19：00：40.000000000 | us-east-1 | us-east-1c | 前端03 | network\_bytes\_out | null | 423 | 

尋找過去 2 小時內特定 EC2 主機的平均 p90、p95 和 p99 CPU 使用率：

```
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 主機以 30 秒間隔固定的平均 CPU 使用率：

```
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 主機以 30 秒間隔固定的平均 CPU 使用率，並使用線性插補填入缺少的值：

```
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 主機以 30 秒間隔固定的平均 CPU 使用率，根據最後的觀察值使用插補填入缺少的值：

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