ALTER PROCEDURE [dbo].[proce_Find_ATT]
@Testingdate varchar(50),
@M00_Family varchar(20),
@M01_Area varchar(50),
@M02_Line varchar(50),
@M06_Station varchar(50)
AS
BEGINselect
值= Fixing_DT,
Testingdate,
Class,
P01_HumanCount,
GROUPCode,
设备=case when @M01_Area='Cartridge' then M02_Line+'_'+M06_Station
else M06_Station
end,
平均值= (select avg(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
最大值= (select max(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
最小值= (select min(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
Min_Cyc=(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,Fixing_DT having(count(Fixing_DT)>1)) t),
CT最小循环值= case when (select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station )is not null then
(select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station )+
(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
cast((select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) as int)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode, Fixing_DT having(count(Fixing_DT)>1)) t)
else
(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate, GROUPCode,Fixing_DT having(count(Fixing_DT)>1)) t)
end
into #T_A from T_ATT as b group by P01_HumanCount,Testingdate,Class,GROUPCode,M00_Family,M01_Area,M02_Line ,M06_Station,GROUPCode,Fixing_DT
select Testingdate+'_'+设备 as 设备s,值 ,Testingdate,Class,GROUPCode into #tables from #T_A
我在这里求中位值:
SELECT
data_with_rownumber.设备s,
AVG(data_with_rownumber.值) AS median
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY 设备s ORDER BY 值) AS seq,
设备s,值 FROM #tables ) data_with_rownumber JOIN
(
SELECT 设备s, COUNT(1) AS NumOfVal FROM #tables
GROUP BY 设备s ) data_count
ON (
data_count.设备s = data_with_rownumber.设备s
AND (
(data_count.NumOfVal % 2 = 0 AND data_with_rownumber.seq IN (data_count.NumOfVal / 2, (data_count.NumOfVal / 2) + 1))
OR
(data_count.NumOfVal % 2 = 1 AND data_with_rownumber.seq = 1 + data_count.NumOfVal / 2)
)
)
GROUP BY
data_with_rownumber.设备s
END
这是结果, 结果有问题,13.395000是第一条的结果,也就是说第二条和第三条都是错的,都是填充的第一条的结果!
2011-05-08_OP20 13.395000
2011-05-08_OP30 13.395000
2011-06-08_OP30 13.395000
我不在这里拼接
select Testingdate+'_'+设备 as 设备s,值 ,Testingdate,Class,GROUPCode into #tables from #T_A在PARTITION 输入两个值
SELECT ROW_NUMBER() OVER(PARTITION BY 设备s ORDER BY 值) AS seq,
出来的结果是正确的,但少一条记录!
因为要按时间和设备名分组!
AND (
(data_count.NumOfVal % 2 = 0 AND data_with_rownumber.seq IN (data_count.NumOfVal / 2, (data_count.NumOfVal / 2) + 1))
OR
(data_count.NumOfVal % 2 = 1 AND data_with_rownumber.seq = 1 + data_count.NumOfVal / 2)
)
--这里你要用case when 来判断,如果记录数是偶数就用第一个,奇数就用第二个。
-- and (case when count(*)%2 = 0 then ... else ... end)
select
值= Fixing_DT,
Testingdate,
Class,
P01_HumanCount,
GROUPCode,
设备=case when @M01_Area='Cartridge' then M02_Line+'_'+M06_Station
else M06_Station
end,
平均值= (select avg(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
最大值= (select max(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
最小值= (select min(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) ,
Min_Cyc=(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,Fixing_DT having(count(Fixing_DT)>1)) t),
CT最小循环值= case when (select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station )is not null then
(select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station )+
(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
cast((select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode) as int)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode, Fixing_DT having(count(Fixing_DT)>1)) t)
else
(select min(t.aa) from (select Fixing_DT as aa from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and Fixing_DT<
(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode)
group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate, GROUPCode,Fixing_DT having(count(Fixing_DT)>1)) t)
end
into #T_A from T_ATT as b group by P01_HumanCount,Testingdate,Class,GROUPCode,M00_Family,M01_Area,M02_Line ,M06_Station,GROUPCode,Fixing_DT
select Testingdate+'_'+设备 as 设备s,值 ,Testingdate,Class,GROUPCode into #tables from #T_A我这一段貌似少取一段数据!帮我看看问题在哪?我今天为那个问题纠结一下午了,头很痛!
少取了一个日期的数据
数据是aaa 2009-09-09
bbb 2009-09-08
ccc 2009-09-07
select Testingdate+'_'+设备 as 设备s,值 ,Testingdate,Class,GROUPCode into #tables from #T_A--你把这些数据放出来,然后根据这些数据说明你要的结果是怎样的。
--select * from #tables