运行统计查询 - Amazon IoT Analytics
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

Amazon IoT Analytics 不再向新客户提供。的现有客户 Amazon IoT Analytics 可以继续照常使用该服务。了解更多

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

运行统计查询

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

对 Amazon IoT SiteWise 演示风电场数据运行统计查询
  1. 运行以下SQL命令以查找具有特定资产(Demo Turbine Asset 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
    assetname assetpropertyname assetpropertyunit 最新

    演示涡轮机资产 4

    RotationsPerSecond

    RPS

    0.48575

    演示涡轮机资产 4

    过载状态

    1

    演示涡轮机资产 4

    RotationsPerMinute

    RPM

    29.14528

    演示涡轮机资产 4

    扭矩(牛顿米)

    Nm

    3545.35429

    演示涡轮机资产 4

    过载状态字符串

    过载

    演示涡轮机资产 4

    平均功率

    瓦特

    10770.62132

    演示涡轮机资产 4

    平均风速

    m/s

    33.57922

    演示涡轮机资产 4

    扭矩(KiloNewton 米)

    kNm

    3.54535

    演示涡轮机资产 4

    风向

    46.05476

  2. 将元数据表和原始表链接起来,确定所有资产(父资源除外)的最大风速属性。

    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
    assetname assetpropertyname assetpropertyunit 最新 timeSeriesId max_speed

    beec3903-2f94-437b-aa8f-235337810550

    fe066382-88e7-4fdd-84cf-321ff52d4c54

    cce0ccc8-e8f9-468d-88e4-139adcb0592f

    Wind Speed (风速)

    fe066382-88e7-4fdd-84cf-321ff52d4c54_cce0ccc8-e8f9-468d-88e4-139adcb0592f

    47.9813

    beec3903-2f94-437b-aa8f-235337810550

    c9e967ed-2416-49d4-af16-c231f8a7a72e

    cce0ccc8-e8f9-468d-88e4-139adcb0592f

    Wind Speed (风速)

    c9e967ed-2416-49d4-af16-c231f8a7a72e_cce0ccc8-e8f9-468d-88e4-139adcb0592f

    47.97489

    beec3903-2f94-437b-aa8f-235337810550

    ee058b1a-570e-49b3-899e-0e3c9f750e59

    cce0ccc8-e8f9-468d-88e4-139adcb0592f

    Wind Speed (风速)

    ee058b1a-570e-49b3-899e-0e3c9f750e59_cce0ccc8-e8f9-468d-88e4-139adcb0592f

    47.97294

    beec3903-2f94-437b-aa8f-235337810550

    dc2cc045-1188-47cc-969f-eea93387c935

    cce0ccc8-e8f9-468d-88e4-139adcb0592f

    Wind Speed (风速)

    dc2cc045-1188-47cc-969f-eea93387c935_cce0ccc8-e8f9-468d-88e4-139adcb0592f

    41.46872

  3. 要查找资产(Demo Turbine Asset 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')
    平均风速

    28.20818

后续步骤

清除教程资源