运行统计查询

对 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	assetpropertyname	assetpropertyunit	最新
   演示涡轮机资产 4	每秒转数	RPS	0.48575
   演示涡轮机资产 4	过载状态		1
   演示涡轮机资产 4	每分钟转数	RPM	29.14528
   演示涡轮机资产 4	扭矩（牛顿米）	Nm	3545.35429
   演示涡轮机资产 4	过载状态字符串		过载
   演示涡轮机资产 4	平均功率	瓦特	10770.62132
   演示涡轮机资产 4	平均风速	m/s	33.57922
   演示涡轮机资产 4	扭矩（千牛米）	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. 要查找资产（演示涡轮机资产 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