出发点:与数据相关的查询、统计、分析操作交给数据库自己完成。前台访问的时候,直接调用具体的函数,确保传的参数准确即可。
/*
查询每小时能耗数据
P_BuildID:楼宇代码
P_EnergyCode:能源类型,支持模糊查询
P_StartDate:统计起始时间
P_EndDate:统计结束时间
用例:SELECT F_HourValue,F_CollectTime FROM EMS.QueryEneryItemHourResult('110108A001','01%','2015-03-23','2015-03-24');
*/
CREATE FUNCTION QueryEneryItemHourResult(@P_BuildID char(10),@P_EnergyCode varchar(5),@P_StartDate datetime,@P_EndDate datetime)
RETURNS @EneryItemHourResult TABLE (F_HourValue numeric(18,4),F_CollectTime datetime) AS
BEGIN
WITH C AS(
SELECT (F_OrigValue-MAX(F_OrigValue) OVER(PARTITION BY OrigValue.F_MeterID ORDER BY F_CollectTime ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS HourValue,
F_CollectTime,
F_EnergyItemCode
FROM EMS.T_OV_MeterOrigValue AS OrigValue
LEFT JOIN EMS.T_ST_MeterEnergyItemInfo AS MeterEnergy
ON O