终止支持通知： Amazon 将于 2025 年 12 月 15 日终止对的支持 Amazon IoT Analytics。2025 年 12 月 15 日之后，您将无法再访问 Amazon IoT Analytics 控制台或 Amazon IoT Analytics 资源。有关更多信息，请参阅[Amazon IoT Analytics 终止支持](https://docs.amazonaws.cn/iotanalytics/latest/userguide/iotanalytics-end-of-support.html)。

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

# 运行统计查询
<a name="tutorial-step3"></a>

既然您已经浏览了 Amazon IoT SiteWise 数据，就可以运行统计查询，为您的工业设备提供宝贵的见解。以下查询演示了您可以检索的一些信息。

**对 Amazon IoT SiteWise 演示风电场数据运行统计查询**

1. <a name="join-latest-values"></a>运行以下 SQL 命令，查找特定资产（演示涡轮机资产 4）所有数值属性的最新值。

   ```
   SELECT assetName,
       assetPropertyName,
       assetPropertyUnit,
       max_by(value, timeInSeconds) AS Latest
   FROM (
       SELECT *,
           CASE assetPropertyDataType
           WHEN 'DOUBLE' THEN
           cast(doubleValue AS varchar)
           WHEN 'INTEGER' THEN
           cast(integerValue AS varchar)
           WHEN 'STRING' THEN
           stringValue
           WHEN 'BOOLEAN' THEN
           cast(booleanValue AS varchar)
           ELSE NULL
           END AS value
       FROM my_iotsitewise_datastore.asset_metadata AS asset_metadata
       JOIN my_iotsitewise_datastore.raw AS raw
           ON raw.seriesId = asset_metadata.timeSeriesId
       WHERE startYear=2021
           AND startMonth=7
           AND startDay=8
           AND assetName='Demo Turbine Asset 4'
   )
   GROUP BY assetName, assetPropertyName, assetPropertyUnit
   ```

## 结果
<a name="w13aac15c27c15c15b5b3b5b1b1"></a>    
[\[See the AWS documentation website for more details\]](http://docs.amazonaws.cn/iotanalytics/latest/userguide/tutorial-step3.html)

1. <a name="child-max-speeds"></a>将元数据表和原始表链接起来，确定所有资产（父资源除外）的最大风速属性。

   ```
   SELECT child_assets_data_set.parentAssetId,
           child_assets_data_set.childAssetId,
           asset_metadata.assetPropertyId,
           asset_metadata.assetPropertyName,
           asset_metadata.timeSeriesId,
           raw_data_set.max_speed
   FROM (
      SELECT sourceAssetId AS parentAssetId,
           targetAssetId AS childAssetId
       FROM my_iotsitewise_datastore.asset_hierarchy_metadata
       WHERE associationType = 'CHILD'
   ) 
   AS child_assets_data_set
   JOIN mls_demo.asset_metadata AS asset_metadata
       ON asset_metadata.assetId = child_assets_data_set.childAssetId
   JOIN (
       SELECT seriesId, MAX(doubleValue) AS max_speed
       FROM my_iotsitewise_datastore.raw
       GROUP BY seriesId
   ) 
   AS raw_data_set
   ON raw_data_set.seriesId = asset_metadata.timeseriesid
   WHERE assetPropertyName = 'Wind Speed'
   ORDER BY max_speed DESC
   ```

## 结果
<a name="w13aac15c27c15c15b5b5b5b1b1"></a>    
[\[See the AWS documentation website for more details\]](http://docs.amazonaws.cn/iotanalytics/latest/userguide/tutorial-step3.html)

1. <a name="average-single-property"></a>要查找资产（演示涡轮机资产 2）的特定属性（风速）的平均值，请运行以下 SQL 命令。必须将 `my_bucket_id` 替换为存储桶 ID。

   ```
   SELECT AVG(doubleValue) as "Average wind speed"
   FROM my_iotsitewise_datastore.raw
   WHERE seriesId = 
       (SELECT timeseriesId
       FROM my_iotsitewise_datastore.asset_metadata as asset_metadata
       WHERE asset_metadata.assetname = 'Demo Turbine Asset 2'
               AND asset_metadata.assetpropertyname = 'Wind Speed')
   ```

## 结果
<a name="w13aac15c27c15c15b5b7b5b1b1"></a>    
[\[See the AWS documentation website for more details\]](http://docs.amazonaws.cn/iotanalytics/latest/userguide/tutorial-step3.html)

## 后续步骤
<a name="tutorial-step3.1"></a>

 [清除教程资源](tutorial-step4.md)