运行统计查询

在上运行统计查询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

   AsetName	资产属性名称	资产/财产股	最新
   演示涡轮资产 4	RotationsPerSecond	RPS	0.48575
   演示涡轮资产 4	超速行驶状态		1
   演示涡轮资产 4	RotationsPerMinute	RPM	29.14528
   演示涡轮资产 4	扭矩（牛顿米）	Nm	3545.35429
   演示涡轮资产 4	Overds Strings		超速驾驶
   演示涡轮资产 4	平均功耗	瓦特	10770.62132
   演示涡轮资产 4	平均风速	M/s	33.57922
   演示涡轮资产 4	扭矩 (KiloNewton 米）	kNM	3.54535
   演示涡轮资产 4	风向	DES	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

   AsetName	资产属性名称	资产/财产股	最新	timeSeriesId	max_spee
   beec3903-2f94-437b-aa8f-235337810550	fe066382-88e7-4fdd-84cf-321ff52d4c54	cce0ccc8-e8f9-468d-88e4-139adcb0592f	Wind Speed (风速)	fe066382-88e7-4fdd-84cf-321ff52d4c54_cce0cc8-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_cce0cc8-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