sample_join.sql

1-- sample_join.sql2-- 基于 Hive SQL 的非实时样本拼接3-- 对应书中 code_hive_sample_join4--5-- [MOCK] 以下 SQL 在 Hive/Spark SQL 环境下可运行。6-- 实际任务还需明确日期分区、Label 去重规则，7-- 以及同一 Request ID 出现多个特征版本时的选版策略。89WITH latest_label AS (10    SELECT request_id, item_id, click, watch_time_ms, event_time_ms11    FROM (12        SELECT request_id, item_id, click, watch_time_ms, event_time_ms,13            ROW_NUMBER() OVER (14                PARTITION BY request_id, item_id15                ORDER BY event_time_ms DESC) AS rn16        FROM dwd_item_feedback17        WHERE dt BETWEEN '${start_dt}' AND '${end_dt}'18    ) t19    WHERE rn = 120)21INSERT OVERWRITE TABLE dws_training_sample PARTITION (dt='${biz_dt}')22SELECT f.request_id, f.item_id, f.user_features, f.context_features,23    f.item_features, f.request_time_ms,24    l.click, l.watch_time_ms, l.event_time_ms AS label_time_ms25FROM dwd_request_features f26JOIN latest_label l27    ON f.request_id = l.request_id AND f.item_id = l.item_id28WHERE f.dt = '${biz_dt}';