WITH emp_info AS (SELECT emp_no, emp_name_ch, emp_dept, class_id, class_noFROM AUSZDLMS.i_facs_employeeWHERE emp_dept LIKE 'MS01%'),ptms_info AS (SELECT emp_no,card_date,card_time,card_time AS F1_SJ,import_date,location_id,in_out,CAST(card_date || ' ' || card_time AS timestamp) AS card_timestamp -- 构建完整的timestampFROM AUSZDLMS.h_ata_recordWHERE card_date >= current_date - 1 -- 查询当天数据),ptms_combined AS (SELECT e.emp_no,e.emp_name_ch,e.emp_dept,e.class_id,e.class_no,p.card_date,p.card_time,p.F1_SJ,p.location_id,p.in_out,p.card_timestamp,LAG(p.card_timestamp) OVER (PARTITION BY e.emp_no ORDER BY p.card_timestamp) AS prev_card_timestampFROM emp_info eLEFT JOIN ptms_info p ON e.emp_no = p.emp_noWHERE p.card_time IS NOT NULLAND p.location_id NOT LIKE '%F1-1F%'AND p.location_id NOT LIKE '%F1-2F%'AND p.location_id NOT LIKE '%F1-3F%'AND p.location_id NOT LIKE '%F1-4F%'AND p.location_id NOT LIKE '%F2-3F%'AND p.location_id NOT LIKE '%F2-4F%')SELECT emp_no,emp_name_ch,emp_dept,class_id,class_no,card_date,card_time,F1_SJ,prev_card_timestamp,EXTRACT(EPOCH FROM (card_timestamp - prev_card_timestamp)) / 60 AS time_diff_minutes,location_id,in_outFROM ptms_combinedWHERE prev_card_timestamp IS NOT NULLAND EXTRACT(EPOCH FROM (card_timestamp - prev_card_timestamp)) / 60 > 30ORDER BY emp_no, card_date, F1_SJ;
