shutao917 commented on issue #11126:
URL: https://github.com/apache/doris/issues/11126#issuecomment-1192384610
> What action was performed?
exec sql like this:
set enable_vectorized_engine=true;
INSERT INTO yz_datawarehouse_tmp.ads_female_prod_multiple_serv_temp
SELECT * FROM (
WITH ads_female_prod_multiple_female_temp
AS(
SELECT
f.tenant_id
,f.org_id
,f.id AS animal_id
,f.herd_code
,f.gbcode
,f.yz_id
,f.earno
,f.breed_code
,f.breed_name
,f.breed_type_code
,f.breed_type_name
,f.strain_type_code
,f.strain_type_name
,f.birthDate
,f.fatherId
,fa.herd_code AS fa_herd_code
,fa.gbcode AS fa_gbcode
,f.motherId
,ma.herd_code AS ma_herd_code
,ma.gbcode AS ma_gbcode
,f.birthWeight
FROM yz_datawarehouse_dwd.dwd_anc_female_file f
LEFT JOIN yz_datawarehouse_dwd.dwd_anc_female_file fa ON fa.id = f.fatherId
LEFT JOIN yz_datawarehouse_dwd.dwd_anc_female_file ma ON ma.id = f.motherId
),mating AS (
SELECT
serv.tenant_id,
serv.org_id,
--************************ 猪只信息***********************
serv.yz_id,
serv.animal_id,
serv.herd_code,
serv.earno,
serv.gbcode,
serv.breed_code,
serv.breed_name,
serv.breed_type_name,
serv.strain_type_name,
serv.group_code, -- 档案批次号
serv.matingOrgDataCalDate,
--************************配种前信息***********************
serv.pre_serv_date, --前一次配种日期
priorservresult,
priorservresult_name,
serv.lweandate,
CASE WHEN priorservresult IN (1,2,3,4) THEN pre_serv_result_date --
上一情期结果为返情、空怀、未怀孕、流产，配种前的状态开始日期为上一情期终止妊娠日期
WHEN priorservresult = 5 THEN CAST(to_date(endlactime) AS DATE) --
上一情期配种结果为分娩，配种前的状态开始日期为结束哺乳日期
ELSE CAST(to_date(entry_time) AS DATE) END
AS pre_status_date,
--************************ 配种信息***********************
serv.services_id, --配种表id
is_mat_this_farm, -- 是否本场配种
serv.heat_time, -- 发情时间
serv.servtime, -- 配种时间
serv.servdate, -- 配种日期
CASE WHEN is_serv_backup=1 THEN '是' ELSE '否' END AS is_serv_backup,
serv.mat_age,
serv.parity,
serv.parity+1 AS join_parity,
serv.mating_org_id AS serv_org_id, --配种猪场id
serv.lockLevel,
serv.staticLevel,
serv.backflowLevel,
serv.serv_org_name, -- 配种猪场
serv.rect_penname , -- 配种位置
serv.rect_name AS serv_room_name, --配种栋舍
serv.pen_name AS serv_pen_name, --配种栏位
serv.admin_name, -- 配种空间管理员
serv.modify_user_name , -- 配种操作员
serv.offsbreedname , --后裔品种品系
serv.boar_code, --与配公猪编码
serv.wean_serv_days , -- 断配间距
--*********************** 配种结果信息***********************
serv.is_mat_transfer, --是否配种后跨场转移
is_result_this_farm,
serv.servresult,
serv.servresultdays,
serv.servresult_date,
serv.servresult_name ,
servresult_org_id,
f.name AS servresult_org_name,
CONCAT(loc.rect_name,'_',loc.pen_name) AS mating_end_first_loc, -- 配种后转入位置
loc.rect_name AS mating_end_first_room,
loc.pen_name AS mating_end_first_pen,
row_number() OVER(PARTITION BY
serv.tenant_id,serv.org_id,serv.animal_id,serv.servtime ORDER BY
loc.bind_time_str ASC) AS rank
FROM
yz_datawarehouse_dws.dws_female_mating_detail serv
LEFT JOIN yz_datawarehouse_dim.dim_pig_location loc
ON loc.bind_id = serv.yz_id AND loc.bind_time BETWEEN serv.servtime
AND IFNULL(serv.servresult_time,to_date(NOW()))
LEFT JOIN yz_datawarehouse_dim.dim_hmc_farm f ON serv.servresult_org_id =
f.id
)
SELECT
m.tenant_id,
m.org_id,
m.yz_id,
m.animal_id,
m.herd_code,
m.earno,
m.gbcode,
m.breed_code,
m.breed_name,
m.breed_type_name,
m.strain_type_name,
m.group_code, -- 档案批次号
m.matingOrgDataCalDate,
--************************配种前信息***********************
m.pre_serv_date, --前一次配种日期
m.priorservresult,
m.priorservresult_name,
m.lweandate,
m.pre_status_date,
--************************ 配种信息***********************
m.services_id, --配种表id
m.is_mat_this_farm, -- 是否本场配种
m.heat_time, -- 发情时间
m.servtime, -- 配种时间
m.servdate, -- 配种日期
m.is_serv_backup,
m.mat_age,
m.parity,
m.join_parity,
m.serv_org_id, --配种猪场id
m.lockLevel,
m.staticLevel,
m.backflowLevel,
m.serv_org_name, -- 配种猪场
m.rect_penname , -- 配种位置
m.serv_room_name, --配种栋舍
m.serv_pen_name, --配种栏位
m.admin_name, -- 配种空间管理员
m.modify_user_name , -- 配种操作员
m.offsbreedname , --后裔品种品系
m.boar_code, --与配公猪编码
m.wean_serv_days , -- 断配间距
--*********************** 配种结果信息***********************
m.is_mat_transfer, --是否配种后跨场转移
m.is_result_this_farm,
m.servresult,
m.servresultdays,
m.servresult_date,
m.servresult_name ,
m.servresult_org_id,
m.servresult_org_name,
m.mating_end_first_loc, -- 配种后转入位置
m.mating_end_first_room,
m.mating_end_first_pen,
female.fa_herd_code,
female.fa_gbcode,
female.ma_herd_code,
female.ma_gbcode,
female.birthWeight
FROM ads_female_prod_multiple_female_temp female
JOIN mating m ON m.animal_id = female.animal_id
WHERE m.rank = 1) a;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
