供料车间用电分析

1.能源管理仪表电量

declare @times date = '2020-06-10'
DECLARE @month DATE = CONVERT(DATE,CONVERT(VARCHAR(8),@times,120)+'01',120)
DECLARE @last_day DATE = DATEADD(d,-1,@month)
DECLARE @last_month DATE = CONVERT(DATE,CONVERT(VARCHAR(8),@last_day,120)+'01',120)
DECLARE @event VARCHAR(max)= '',@sql VARCHAR(max) = '',@queryexp VARCHAR(max) = ''
--用电量
DECLARE @tab5 TABLE(
	id INT,
	MeterCode VARCHAR(10),
	MeterName VARCHAR(50)
)
INSERT INTO @tab5 VALUES(1,'1196','1＃生料磨系统')
INSERT INTO @tab5 VALUES(2,'1120','1＃生料磨1＃变压器')
INSERT INTO @tab5 VALUES(3,'1124','1#空压机')
INSERT INTO @tab5 VALUES(4,'1125','2#空压机')
INSERT INTO @tab5 VALUES(5,'1126','3#空压机')
INSERT INTO @tab5 VALUES(6,'1121','1＃生料磨2＃变压器')
INSERT INTO @tab5 VALUES(7,'1127','食堂公寓')
INSERT INTO @tab5 VALUES(8,'1131','取暖泵4')
INSERT INTO @tab5 VALUES(9,'1132','取暖泵5')
INSERT INTO @tab5 VALUES(10,'1130','取暖泵123')
INSERT INTO @tab5 VALUES(11,'1148','2＃生料磨1＃变压器')
INSERT INTO @tab5 VALUES(12,'1156','6#水井')
INSERT INTO @tab5 VALUES(13,'1153','2＃2201皮带')
INSERT INTO @tab5 VALUES(14,'1154','2＃2203皮带')
INSERT INTO @tab5 VALUES(15,'1155','2＃2204皮带')
INSERT INTO @tab5 VALUES(16,'1157','2＃3504皮带')
INSERT INTO @tab5 VALUES(17,'1149','2＃生料磨2＃变压器')
if object_id('tempdb..#tab8') is not NULL
DROP TABLE #tab8
--查询出每个电表的每一天的电量  查询时间为 两个月
SELECT * INTO #tab8 FROM 
(
	SELECT a.id,a.InsertDay,SUM(b.ItemValue) MediumYield
	FROM (
	select * from @tab5
	LEFT JOIN (SELECT InsertTime,InsertDay FROM dbo.S_SystemDate WHERE InsertDay>=@last_month AND InsertDay<=@times)b 
	ON 1=1
	)a
	LEFT JOIN dbo.EM_MeterItemValue b ON a.MeterCode=b.MeterCode AND b.ItemCode in ('10169','10161','10127') 
	AND a.InsertTime=b.InsertTime
	GROUP  BY a.id,a.InsertDay
)a

--select * from #tab8

--查询出 每一天的电量 上一天的电量  还有差值 放到#tab9表
if object_id('tempdb..#tab9') is not NULL
DROP TABLE #tab9
SELECT * INTO #tab9 FROM (
SELECT a.*,b.MediumYield AS YesterdayMediumYield,convert(decimal(18,2),b.MediumYield)-convert(decimal(18,2),a.MediumYield) AS Gap FROM #tab8 a
LEFT JOIN #tab8 b ON DATEADD(d,-1,a.InsertDay)=B.InsertDay AND a.id=b.id
WHERE a.InsertDay>@last_day)a


--replace的作用是 把日期中的 '-' 变成'_'
--在sqlserver中以数字开头的列名需要 加[]号才可以识别
--这样的话就可以使用 pivot 函数进行 行列转换了
if object_id('tempdb..#tab7') is not NULL
DROP TABLE #tab7
SELECT * INTO #tab7 FROM (
SELECT id,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_1今日' AS day,MediumYield FROM #tab9
UNION all
SELECT id,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_2昨日' AS day,YesterdayMediumYield FROM #tab9
UNION all
SELECT id,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_3差值' AS day,Gap FROM #tab9)a

--因为 @event 和 @queryexp涉及到复用的问题 所以要先设置为 ''
--@event 动态字符串就是获取 #tab7中的所有字段作为转置后的列名
SET @event = ''
SET @queryexp = ''
SELECT @event = @event + ',[' + day + ']' FROM (SELECT DISTINCT day FROM #tab7)a ORDER BY day
select @queryexp=@queryexp+',max(['+day+']) as '+'['+day+']'  from (select distinct [day] from #tab7) a order by [day]
--right函数 第一个参数是字符串 第二个参数是一个数字，代表从右往左截取的长度
SET @event = RIGHT(@event,LEN(@event)-1)
SET @queryexp = RIGHT(@queryexp,LEN(@queryexp)-1)


--将行 转置 为 列后的数据存放到 全局临时表 ##tab6中
--在写动态sql中 不能使用局部临时表 根据FactoryCode 进行分组
if object_id('tempdb..##tab6') is not NULL
DROP TABLE ##tab6
set @sql='select * into ##tab6 from (select id,'+@queryexp+' from( select id,'+@event +'from #tab7 a  
pivot (max(MediumYield) for day in('+@event+') 
) as pv ) b group by id )a '



--获取本月和上月的数据和差值  获取差值的时候注意 应该先把他们转换成保留两位小数然后进行减法
SELECT a.MediumYield ,b.MediumYield AS LastMonthMediumYield,convert(decimal(18,2),a.MediumYield)-convert(decimal(18,2),b.MediumYield) AS MonthMediumYieldGap FROM 
(SELECT id,SUM(MediumYield) MediumYield FROM #tab8 
WHERE InsertDay>=@month AND InsertDay<=@times
GROUP BY id
)a
LEFT JOIN 
(
	SELECT id,SUM(MediumYield) MediumYield FROM #tab8 
        WHERE InsertDay>=@last_month AND InsertDay<=@last_day
	GROUP BY id
)b ON a.id = b.id
where a.id >1
order by a.id

2.石破系统

--石灰石系统
DECLARE @tab1 TABLE(
	FactoryCode VARCHAR(8)
)
INSERT INTO @tab1 values('10010104')
INSERT INTO @tab1 values('10010204')

--时间表和FactoryCode
if object_id('tempdb..#date_table') is not NULL
DROP TABLE #date_table
SELECT * INTO #date_table
FROM
(SELECT InsertTime,InsertDay   FROM dbo.S_SystemDate WHERE InsertDay>=@last_month AND insertDay<=@times)a 
LEFT JOIN @tab1 b ON 1=1



DECLARE @tab2 TABLE(
	FactoryCode VARCHAR(8),
	insertDay date,
	Type VARCHAR(20),
	MaterialYield FLOAT 
)

DECLARE @tab3 TABLE
(
	FactoryCode VARCHAR(8),
	insertDay date,
	Type VARCHAR(20),
	MaterialYield FLOAT,
	LastDayMaterialYield float,
	Gap FLOAT
)


if object_id('tempdb..#tab4') is not NULL
DROP TABLE #tab4
CREATE TABLE #tab4
(
	FactoryCode VARCHAR(8),	
	Type VARCHAR(20),
	day VARCHAR(30),
	MaterialYield FLOAT
)


--需要变成 生料系统 或者 其他系统的时候 需要改一下这边的产量取法
--石灰石产量取得是OutFactoryTime字段
if object_id('tempdb..#t2') is not NULL
DROP TABLE #t2
SELECT * INTO #t2
FROM 
(

SELECT a.FactoryCode,a.InsertDay,'_1用电量' AS Type,ISNULL(SUM(b.MediumYield),0) AS  MaterialYield
FROM (SELECT insertTime, insertDay,FactoryCode FROM #date_table) a
LEFT JOIN (SELECT InputTime,FactoryCode,MediumYield FROM dbo.EM_MediumConsume WHERE CountType=1
) b on a.InsertTime=b.InputTime AND a.FactoryCode = b.FactoryCode 
GROUP BY a.FactoryCode,a.InsertDay
UNION ALL 
SELECT a.FactoryCode,a.InsertDay,'_2产量' AS Type,ISNULL(SUM(b.NetWeight),0) AS MaterialYield 
FROM (SELECT DISTINCT insertDay,FactoryCode FROM #date_table) a
LEFT JOIN (SELECT CONVERT(VARCHAR(10),OutFactoryTime,120) AS insertTime,NetWeight,PdNum AS FactoryCode
FROM dbo.PM_IO_MaterialIntoFactory 
WHERE PdNum IS NOT NULL
) b on a.InsertDay=b.insertTime AND a.FactoryCode = b.FactoryCode
GROUP BY a.FactoryCode,a.InsertDay

UNION ALL 

select a.FactoryCode,a.InsertDay,'_3运行时间' AS Type,sum(b.RunTime)/4 as MediumYield from #date_table a
left join PM_MaterialTime b on a.insertTime=b.InputTime and a.FactoryCode=b.FactoryCode 
group by a.FactoryCode,a.insertDay

)a

--SELECT * FROM #t2


--添加上功率和电耗
INSERT INTO @tab2
(
    FactoryCode,
    insertDay,
    Type,
    MaterialYield
)
SELECT *  FROM 
(
SELECT FactoryCode,InsertDay,Type,MaterialYield FROM #t2
UNION all
SELECT a.FactoryCode,a.InsertDay,'_4功率'AS Type,MAX(a.MaterialYield/NULLIF(b.MaterialYield,0)) FROM #t2 a
 JOIN #t2 b ON a.FactoryCode=b.FactoryCode AND a.InsertDay=b.InsertDay
AND a.Type='_1用电量' AND b.Type='_3运行时间'
GROUP BY a.FactoryCode,a.InsertDay

UNION ALL
SELECT a.FactoryCode,a.InsertDay,'_5电耗'AS Type,MAX(a.MaterialYield/NULLIF(b.MaterialYield,0)) FROM #t2 a
 JOIN #t2 b ON a.FactoryCode=b.FactoryCode AND a.InsertDay=b.InsertDay
AND a.Type='_1用电量' AND b.Type='_2产量'
GROUP BY a.FactoryCode,a.InsertDay
)a

--SELECT FactoryCode,CONVERT(VARCHAR(10),insertDay,120)+ FROM #t3


INSERT INTO @tab3
SELECT a.FactoryCode,a.insertDay,a.Type,a.MaterialYield,b.MaterialYield,convert(decimal(18,2),b.MaterialYield)-convert(decimal(18,2),a.MaterialYield) FROM @tab2 a
LEFT JOIN @tab2 b ON a.FactoryCode=b.FactoryCode AND DATEADD(d,-1,a.insertDay)=b.insertDay AND a.Type=b.Type
WHERE a.insertDay > @last_day

--SELECT * FROM PM_MainMachineHalt
INSERT INTO #tab4
SELECT FactoryCode,Type,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_1今日',MaterialYield FROM @tab3
UNION ALL 
SELECT FactoryCode,Type,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_2昨日',LastDayMaterialYield FROM @tab3
UNION ALL
SELECT FactoryCode,Type,'a_'+REPLACE(CONVERT(VARCHAR(10),insertDay,120),'-','_')+'_3差值',Gap FROM @tab3
--SELECT * FROM #tab4 ORDER BY FactoryCode,Type,day
SET @event = ''
SET @queryexp = ''
SELECT @event = @event + ',[' + day + ']' FROM (SELECT DISTINCT day FROM #tab4)a ORDER BY day
select @queryexp=@queryexp+',max(['+day+']) as '+'['+day+']'  from (select distinct [day] from #tab4) a order by [day]
SET @event = RIGHT(@event,LEN(@event)-1)
SET @queryexp = RIGHT(@queryexp,LEN(@queryexp)-1)
--PRINT(@event)
--PRINT(@queryexp)

set @sql='select '+@queryexp+' from( select FactoryCode,Type,'+@event +'from #tab4 a  
pivot (max(MaterialYield) for day in('+@event+') 
) as pv ) b group by FactoryCode,Type order by FactoryCode,Type '
--PRINT(@sql)
EXEC(@sql)





--石破系统月综合

select 
case when a.Type='_4功率' then convert(decimal(18,2),MonthMaterialYield/day(@times) ) --今日
	 when a.Type='_5电耗' then convert(decimal(18,2),MonthMaterialYield/day(@times) )
	 else MonthMaterialYield end,
case when a.Type='_4功率' then convert(decimal(18,2),LastMonthMaterialYield/day(@times) )--昨日
	 when a.Type='_5电耗' then convert(decimal(18,2),LastMonthMaterialYield/day(@times) )
	 else LastMonthMaterialYield end,
	 
case when a.Type='_4功率' then (convert(decimal(18,2),a.MonthMaterialYield/day(@times))-convert(decimal(18,2),b.LastMonthMaterialYield/day(@times)))
	 when a.Type='_5电耗' then (convert(decimal(18,2),a.MonthMaterialYield/day(@times))-convert(decimal(18,2),b.LastMonthMaterialYield/day(@times)))
	 else (convert(decimal(18,2),a.MonthMaterialYield)-convert(decimal(18,2),b.LastMonthMaterialYield)) end
  from (
select FactoryCode,Type,sum(MaterialYield) as MonthMaterialYield from @tab2
where insertDay>=@month and insertDay<=@times
group by FactoryCode,Type
)a
left join (
	select FactoryCode,Type,sum(MaterialYield) as LastMonthMaterialYield from @tab2
        where insertDay>=@last_month and insertDay<=@last_day
	group by FactoryCode,Type
)b 
on a.FactoryCode=b.FactoryCode and a.Type=b.Type
order by a.FactoryCode,a.Type