CREATE MATERIALIZED VIEW mv_material_participant_hour_report
PARTITION BY (Report_Date)
REFRESH ASYNC START(“2025-01-21 18:05:00”) EVERY(INTERVAL 10 MINUTE) AS
SELECT
Report_Date,temp.Upload_User_Id AS User_id,role_id,temp.Material_Type,temp.agent_material_id,temp.Platform_Type,temp.Account_Id,HOUR,temp.spec_id,Spec_Name,temp.Customer_Id,temp.Customer_Name,Product_Id,Product_Name,temp.Account_Name,temp.Project_Id,temp.Project_Name,
temp.Spec_Material_Id,temp.media_material_id,IFNULL(temp.Material_Id,bind.material_id) Material_Id,IFNULL(temp.material_name,bind.media_material_name) material_name,Material_Original_Name,IFNULL(b.signature,bind.media_material_md5) AS signature,
yfci.id AS Industry_Id,yfci.industry_name AS Industry_Name,
eu.display_name AS User_Name,editor,editor_name,director,director_name,shoter,shoter_name,
IFNULL(b.img_path,IFNULL(bind.binding_material_url,bind.media_material_url)) AS img_path,b.video_pre_path AS video_pre_path,
Role_Name,IFNULL(pdei.main_dept,ul.dept_name) AS Dept_Name,IFNULL(pdei.main_dept_id,ul.dept_id) AS Dept_Id,Order_Type,script_id,spec_schedule_id,
temp.material_cost,temp.cost,conversion,impression,click,
video_play_count,video_outer_play_count,video_outer_play100_count,deep_conversions_count,page_phone_call_direct_count,apply_pv,page_reservation_count,order_pv,page_consult_count,credit_pv,
video_avg_play_time,video_play_time,Material_Create_Time,creative_count,b.media_material_tag
FROM mv_material_participant_hour_report_01 temp
LEFT JOIN tidb.mbg_core.emarbox_user eu ON temp.Upload_User_Id = eu.user_id
LEFT JOIN tidb.mbg_core.pig_dd_employee_info pdei ON eu.login_name = pdei.dsp_account_no
LEFT JOIN (SELECT stat_date,user_id,MAX(dept_id) dept_id,MAX(dept_name) dept_name FROM tidb.mbg_core.pig_dd_user_dept_info_daily GROUP BY stat_date,user_id ) ul ON ul.user_id=pdei.user_id AND temp.report_date=ul.stat_date
LEFT JOIN tidb.mbg_core.yxt_finance_customer yfc ON temp.Customer_Id = yfc.id
LEFT JOIN tidb.mbg_core.yxt_finance_customer_industry yfci ON yfc.customer_industry_id = yfci.id
LEFT JOIN tidb.mbg_business.agent_material_binding_202101 bind
ON bind.agent_material_id=temp.agent_material_id AND bind.media_id=temp.platform_type AND bind.material_type=temp.material_type AND temp.account_id=bind.account_id
LEFT JOIN (
SELECT 0 AS material_type,image_id AS m_id,img_mark_path AS img_path,NULL AS video_pre_path,img_md5 AS signature FROM tidb.mbg_business.creative_material_image a
UNION ALL
SELECT 1 AS material_type,video_id AS m_id,video_mark_path AS img_path,video_pre_path,video_md5 assignature FROM tidb.mbg_business.creative_material_video a) b
ON IFNULL(temp.material_id,bind.material_id) = b.m_id AND temp.Material_Type=b.material_type
LEFT JOIN (
SELECT 4 AS Platform_Type,material_id,COUNT(1) AS creative_count FROM tidb.yixintui_operate.creative_material_tt_experience GROUP BY material_id
UNION ALL
SELECT 2 AS Platform_Type,material_id,COUNT(1) AS creative_count FROM tidb.yixintui_operate.creative_material_gdt_v3 GROUP BY material_id
UNION ALL
SELECT 5 AS Platform_Type,photo_id AS material_id,COUNT(1) AS creative_count FROM tidb.yixintui_operate.Synads_Ks_Creative GROUP BY photo_id
UNION ALL
SELECT 6 AS Platform_Type,material_id,COUNT(1) AS creative_count FROM tidb.yixintui_operate.creative_material_qc GROUP BY material_id
) AS cma ON temp.Platform_Type = cma.Platform_Type AND temp.agent_material_id = cma.material_id
LEFT JOIN tidb.yixintui_operate.board_design_material_tag_int b
ON temp.spec_material_id = b.spec_material_id ;
