SQL SERVER 串表计算数据，日期预处理，group聚类

weixin_43182240

已于 2023-11-06 16:41:42 修改

阅读量53

点赞数

分类专栏：数据库文章标签：数据库

于 2023-11-06 16:08:19 首次发布

本文链接：https://blog.csdn.net/weixin_43182240/article/details/134248259

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库表一设备基础信息：[T_EQUIPMENT_INFO]

表二实时温湿度记录[T_EQUIPMENT_COLLECTION_MSG]

表三实时数据报警记录 [T_EQUIPMENT_COLLECTION_DATA]

计算1，每日各设备数据量，用数据长度把时间进行日期格式化convert(varchar(10),[T_EQUIPMENT_COLLECTION_MSG].[DATE],120) 日期：

  SELECT [T_EQUIPMENT_INFO].[EQUIPMENT_NUM], convert(varchar(10),[T_EQUIPMENT_COLLECTION_MSG].[DATE],120) 日期 ,
  count( [T_EQUIPMENT_COLLECTION_MSG].EQUIPMENT_ID) as 数据量
FROM [ATC_N2Box].[dbo].[T_EQUIPMENT_COLLECTION_MSG] left join [T_EQUIPMENT_INFO] on EQUIPMENT_ID=[T_EQUIPMENT_INFO].ID 
group by [T_EQUIPMENT_INFO].[EQUIPMENT_NUM],convert(varchar(10),[T_EQUIPMENT_COLLECTION_MSG].[DATE],120) 
order by 日期 desc

计算2，每日各设备数据报警量：

  SELECT [T_EQUIPMENT_INFO].[EQUIPMENT_NUM], convert(varchar(10),[T_EQUIPMENT_COLLECTION_DATA].[DATE],120) 日期 ,
  count( [T_EQUIPMENT_COLLECTION_DATA].EQUIPMENT_CJ_ID) as 报警数量
FROM [ATC_N2Box].[dbo].[T_EQUIPMENT_COLLECTION_DATA] left join [T_EQUIPMENT_INFO] on EQUIPMENT_ID=[T_EQUIPMENT_INFO].ID 
group by [T_EQUIPMENT_INFO].[EQUIPMENT_NUM],convert(varchar(10),[T_EQUIPMENT_COLLECTION_DATA].[DATE],120) 
order by 日期 desc

计算3，每日各设备报警量与数据量之比，需要上述两个query，再进行二次计算，整数直接除得出的是取整的，先乘以一个浮点数变成浮点数，再用decimal格式化小数点位数，convert(decimal(18,2),(报警数量*100.0/数据量)) as 报警比例

  select a.*,b.报警数量 , convert(decimal(18,2),(报警数量*100.0/数据量)) as 报警比例 from (SELECT [T_EQUIPMENT_INFO].[EQUIPMENT_NUM], 
  convert(varchar(10),[T_EQUIPMENT_COLLECTION_MSG].[DATE],120) 日期 ,  count( [T_EQUIPMENT_COLLECTION_MSG].EQUIPMENT_ID) as 数据量
FROM [ATC_N2Box].[dbo].[T_EQUIPMENT_COLLECTION_MSG] left join [T_EQUIPMENT_INFO] on EQUIPMENT_ID=[T_EQUIPMENT_INFO].ID 
group by [T_EQUIPMENT_INFO].[EQUIPMENT_NUM],convert(varchar(10),[T_EQUIPMENT_COLLECTION_MSG].[DATE],120) 
) a left join (SELECT [T_EQUIPMENT_INFO].[EQUIPMENT_NUM], convert(varchar(10),[T_EQUIPMENT_COLLECTION_DATA].[DATE],120) 日期 ,
  count( [T_EQUIPMENT_COLLECTION_DATA].EQUIPMENT_CJ_ID) as 报警数量
FROM [ATC_N2Box].[dbo].[T_EQUIPMENT_COLLECTION_DATA] left join [T_EQUIPMENT_INFO] on EQUIPMENT_ID=[T_EQUIPMENT_INFO].ID 
group by [T_EQUIPMENT_INFO].[EQUIPMENT_NUM],convert(varchar(10),[T_EQUIPMENT_COLLECTION_DATA].[DATE],120) 
) b on a.EQUIPMENT_NUM=b.EQUIPMENT_NUM and a.日期=b.日期 order by a.日期 desc,报警比例 desc