多对多关系下SQL Left Join实现：按调研日期匹配员工地区

核心SQL实现 #

CREATE OR REPLACE VIEW Survey_Scores_With_Location AS
SELECT
    ss.*,
    ei.Location
FROM
    Survey_Scores ss
INNER JOIN
    Employee_Info ei
ON
    ss.Employee_Number = ei.Employee_Number
    -- 匹配调研日期处于员工信息的有效区间内
    AND ss.Survey_Date >= ei.Record_Start_Date
    -- 处理当前有效记录（Record_End_Date为NULL的情况）
    AND ss.Survey_Date <= COALESCE(ei.Record_End_Date, '9999-12-31');

关键逻辑说明 #

• 日期区间匹配：通过Survey_Date落在Record_Start_Date和Record_End_Date之间的条件，替代单纯按Employee_Number关联，彻底解决多对多关联导致的报错和数据重复问题，确保每条调研记录只匹配员工当日的有效地区信息。
• 处理当前有效记录：用COALESCE将Record_End_Date为NULL的记录替换为'9999-12-31'，保证当前在职员工的信息能匹配所有未过期的调研日期。

额外优化：处理极端情况 #

如果Employee_Info存在同一员工在同一日期有多个重叠有效记录的异常情况（理论上业务数据应避免这种情况），可以用QUALIFY + ROW_NUMBER()确保只返回唯一有效的地区：

CREATE OR REPLACE VIEW Survey_Scores_With_Location AS
SELECT
    ss.*,
    ei.Location
FROM
    Survey_Scores ss
INNER JOIN
    Employee_Info ei
ON
    ss.Employee_Number = ei.Employee_Number
    AND ss.Survey_Date >= ei.Record_Start_Date
    AND ss.Survey_Date <= COALESCE(ei.Record_End_Date, '9999-12-31')
-- 按调研记录分组，取最新生效的地区记录
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY ss.Survey_ID ORDER BY ei.Record_Start_Date DESC) = 1;

（注：需替换Survey_ID为Survey_Scores表的主键或唯一标识字段）

性能建议 #

由于Survey_Scores有300万条数据，为提升关联效率，建议给Employee_Info表创建复合索引：

CREATE INDEX idx_employee_info_date_range ON Employee_Info (Employee_Number, Record_Start_Date, Record_End_Date);

或启用Snowflake的搜索优化服务（Search Optimization Service），针对日期区间查询加速。

内容的提问来源于stack exchange，提问作者Logan Nielsen