编辑代码

with all_data as (
select 
	bsga."Average" ,
	-- 根据不同的 procedure 动态选择 FurnaceID 和 luguanhao
    CASE
        WHEN '${procedure}' = 'battery_spc_gather_auto' THEN bsga."FurnaceID"
        -- WHEN '${procedure}' IN ('spc_anneal_offline', 'spc_oxidation_offline') THEN bsga."equipmentNo"
        WHEN '${procedure}' IN ('spc_anneal_offline', 'spc_oxidation_offline', 'battert_spc_scales_texturing', 'battert_spc_scales_alkalicase', 'spc_texturing_offline', 'spc_alkalicase_offline', 'spc_pepoly_offline', 'spc_alumina1_offline', 'spc_alumina2_offline', 'spc_positive_membrane_offline', 'spc_dorsal_membrane_offline', 'spc_pullingforce1_offline', 'spc_pullingforce2_offline', 'spc_threepointbendingresistance_offline', 'battert_spc_scales_silk') THEN bsga."equipmentNo"
        ELSE NULL
    END AS "FurnaceID",
    -- 拼接 luguanhao
    CASE
        WHEN '${procedure}' = 'battery_spc_gather_auto' THEN right(bsga."FurnaceID", 2) || '-' || bsga."pipeNo"
        WHEN '${procedure}' IN ('spc_anneal_offline', 'spc_oxidation_offline', 'battert_spc_scales_texturing', 'battert_spc_scales_alkalicase', 'spc_texturing_offline', 'spc_alkalicase_offline', 'spc_pepoly_offline', 'spc_alumina1_offline', 'spc_alumina2_offline', 'spc_positive_membrane_offline', 'spc_dorsal_membrane_offline', 'spc_pullingforce1_offline', 'spc_pullingforce2_offline', 'spc_threepointbendingresistance_offline', 'battert_spc_scales_silk') THEN bsga."equipmentNo" || '-' || bsga."pipeNo"
        ELSE NULL
    END AS luguanhao,
	bsga."Homogen" ,
	bsga."creationDate",
	to_char(bsga."creationDate", 'HH24MI')::INTEGER as timeInter,
	case when to_char(bsga."creationDate", 'HH24MI')::INTEGER >= 800 and to_char(bsga."creationDate", 'HH24MI')::INTEGER  < 2000 then 'D'
		when to_char(bsga."creationDate", 'HH24MI')::INTEGER >= 2000 then  'N'
		else 'N' end AS banci
	--right(bsga."FurnaceID",2) 
from ${procedure} bsga 
where 1=1
	and bsga.workshop = 'C1'
	and bsga."Average" != '0.0'
	and bsga."creationDate" >= to_timestamp('${startDate}', 'YYYY-MM-DD HH24:MI:SS')
	and bsga."creationDate" < (to_timestamp('${endDate}', 'YYYY-MM-DD HH24:MI:SS'))
	-- ${if(len(deviceId)>0 && procedure=="battery_spc_gather_auto"," and bsga.\"FurnaceID\" in ('" +deviceId+ "')","")}
	-- ${if(len(deviceId)>0 && procedure=="spc_anneal_offline"," and bsga.\"equipmentNo\" in ('" +deviceId+ "')","")}
	-- ${if(len(deviceId)>0 && procedure=="spc_oxidation_offline"," and bsga.\"equipmentNo\" in ('" +deviceId+ "')","")}
    -- 根据 procedure 和 deviceId 筛选设备
    AND (
        CASE
            WHEN '${procedure}' = 'battery_spc_gather_auto' AND length('${deviceId}') > 0 THEN bsga."FurnaceID" IN (SELECT unnest(string_to_array('${deviceId}', ',')))
            WHEN '${procedure}' IN ('spc_anneal_offline', 'spc_oxidation_offline', 'battert_spc_scales_texturing', 'battert_spc_scales_alkalicase', 'spc_texturing_offline', 'spc_alkalicase_offline', 'spc_pepoly_offline', 'spc_alumina1_offline', 'spc_alumina2_offline', 'spc_positive_membrane_offline', 'spc_dorsal_membrane_offline', 'spc_pullingforce1_offline', 'spc_pullingforce2_offline', 'spc_threepointbendingresistance_offline', 'battert_spc_scales_silk') AND length('${deviceId}') > 0 THEN bsga."equipmentNo" IN (SELECT unnest(string_to_array('${deviceId}', ',')))
            ELSE TRUE
        END
    )
)

select 
	luguanhao,
	avg(cast(ad."Average" as DECIMAL)) as Average,
	avg(cast(ad."Homogen" as DECIMAL)) as Homogen
from 
	all_data ad
where 
	${reportCombox} = 3
	${if(len(banci)==0,"","and ad.\"banci\" = '"+banci+"'")}
group by luguanhao
order by luguanhao