编辑代码

with all_data as (
    select
        creationDate,

        Average,
        Homogen,

        difference,

        net_weight,

        jsonb_array_elements(testData::jsonb) ->> 'MaxPower' AS maxpower,

        jsonb_array_elements(testData::jsonb) ->> 'Thickness' AS thickness,
        jsonb_array_elements(testData::jsonb) ->> 'Refraction' AS refraction,

        FurnaceID,
        equipmentNo as FurnaceID,
        lineNo as FurnaceID,

        CONCAT(RIGHT(FurnaceID, 2), '-', pipeNo) as luguanhao,
        CONCAT(equipmentNo, '-', pipeNo) as luguanhao,
        lineNo as luguanhao,

        LEFT(pipeNo, LENGTH(pipeNo) -1) as num,

        case
            when to_char(creationDate, 'HH24')::INTEGER >= 8 and to_char(creationDate, 'HH24')::INTEGER < 20 then 'D'
            else 'N'
        end as banci

    FROM
        -- 硼扩方阻
        battery_spc_gather_auto
        -- 在线退火方阻
        spc_anneal_offline
        -- 离线退火方阻
        spc_anneal_offline
        -- 氧化方阻
        spc_oxidation_offline
        -- 制绒减重
        battert_spc_scales_texturing
        -- 碱抛减重
        battert_spc_scales_alkalicase
        -- 制绒反射率
        spc_texturing_offline
        -- 碱抛反射率
        spc_alkalicase_offline
        -- Pepoly
        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
    where 2=2
        and ${reportCombox} = 2
        and workshop = 'C1'
        and creationDate >= to_timestamp('${startDate}', 'YYYY-MM-DD HH24:MI:SS')
        and creationDate < to_timestamp('${endDate}', 'YYYY-MM-DD HH24:MI:SS')

        ${if(len(deviceId) > 0," and FurnaceID in ('" +deviceId+ "') ","")}
        ${if(len(deviceId) > 0," and equipmentNo in ('" +deviceId+ "') ","")}
        ${if(len(deviceId) > 0," and lineNo in ('" +deviceId+ "') ","")}
)
select 
    luguanhao,
    AVG(cast(Average as DECIMAL)) as Average,
    AVG(cast(Homogen as DECIMAL)) as Homogen

    AVG(cast(difference as DECIMAL)) as difference

    AVG(cast(net_weight as DECIMAL)) as netWeight

    AVG(cast(maxpower as DECIMAL)) as maxpower

    AVG(cast(thickness as DECIMAL)) as thickness
    AVG(cast(refraction as DECIMAL)) as refraction
from all_data ad
where 1=1
    ${if(len(banci)==0,"","and ad.banci = '"+banci+"'")}

group by luguanhao
order by RIGHT(ad.FurnaceID, 2)::DECIMAL, ad.num

group by luguanhao
order by ad.FurnaceID::DECIMAL, ad.num

group by luguanhao
order by ad.FurnaceID::DECIMAL
;