Amazon IoT Analytics 不再向新客户提供。的现有客户 Amazon IoT Analytics 可以继续照常使用该服务。了解更多
本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。
运行统计查询
既然您已经浏览了 Amazon IoT SiteWise 数据,就可以运行统计查询,为您的工业设备提供宝贵的见解。以下查询演示了您可以检索的一些信息。
对 Amazon IoT SiteWise 演示风电场数据运行统计查询
-
运行以下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
-
将元数据表和原始表链接起来,确定所有资产(父资源除外)的最大风速属性。
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
-
要查找资产(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