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

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

运行统计查询

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

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

  1. 运行以下 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
    AssetName 资产属性名称 资产财产单位 最新

    演示涡轮机资产 4

    每秒旋转

    RPS

    0.48575

    演示涡轮机资产 4

    过载状态

    1

    演示涡轮机资产 4

    每分钟轮调

    RPM

    29.14528

    演示涡轮机资产 4

    扭矩(牛顿计)

    Nm

    3545.35429

    演示涡轮机资产 4

    过载状态字符串

    超速

    演示涡轮机资产 4

    平均功率

    Watts

    10770.62132

    演示涡轮机资产 4

    平均风速

    m/s

    33.57922

    演示涡轮机资产 4

    扭矩(KilonNewton 计)

    kNM

    3.54535

    演示涡轮机资产 4

    风向

    Deges

    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 资产属性名称 资产财产单位 最新 TimeSeries ID 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. 要查找资产(演示涡轮机资产 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

下一步

清理教程资源