将Excel的FILTER公式转换为PostgreSQL查询并实现学生姓名匹配

数据结构 #

学生表（表名：student） #

STUDENT NAME    START DATE  TEACHER NAME
X   01/01/2023  A
Y   17/02/2023  A
Z   20/03/2023  A
R   01/01/2023  B
S   17/02/2023  B
T   20/03/2023  B

调度表（表名：dispatch） #

DISPATCH DATE   TEACHER NAME
09/03/2023  A
26/01/2023  A
24/02/2023  A
09/03/2023  A
25/03/2023  A
24/03/2023  A
09/03/2023  B
24/03/2023  B

需求解析 #

原Excel公式=FILTER(A:A,C:C=G3,B:B<>"", B:B=MAX(FILTER(B:B,C:C=G3,B:B<>"",B:B<=F3)))的核心逻辑：
针对每条调度记录（G3为教师姓名，F3为调度日期），筛选出同一教师名下、入学日期非空，且入学日期是小于等于当前调度日期的最大入学日期的学生姓名。

PostgreSQL查询实现 #

SELECT s."STUDENT NAME"
FROM dispatch d
-- 子查询：为每条调度记录匹配对应教师、对应日期范围内的最大入学日期
JOIN (
    SELECT 
        d."TEACHER NAME",
        TO_DATE(d."DISPATCH DATE", 'DD/MM/YYYY') AS dispatch_date,
        MAX(TO_DATE(s."START DATE", 'DD/MM/YYYY')) AS max_start_date
    FROM dispatch d
    JOIN student s 
        ON s."TEACHER NAME" = d."TEACHER NAME"
        AND TO_DATE(s."START DATE", 'DD/MM/YYYY') <= TO_DATE(d."DISPATCH DATE", 'DD/MM/YYYY')
        AND s."START DATE" IS NOT NULL
    GROUP BY d."TEACHER NAME", TO_DATE(d."DISPATCH DATE", 'DD/MM/YYYY')
) sub 
    ON d."TEACHER NAME" = sub."TEACHER NAME" 
    AND TO_DATE(d."DISPATCH DATE", 'DD/MM/YYYY') = sub.dispatch_date
-- 关联学生表，找到对应最大入学日期的学生
JOIN student s 
    ON s."TEACHER NAME" = d."TEACHER NAME"
    AND TO_DATE(s."START DATE", 'DD/MM/YYYY') = sub.max_start_date
-- 匹配预期结果的输出顺序
ORDER BY 
    d."TEACHER NAME", 
    TO_DATE(d."DISPATCH DATE", 'DD/MM/YYYY');

关键说明 #

• 若表中日期已存储为PostgreSQL原生date类型，可直接去掉所有TO_DATE()转换函数，用日期字段直接比较
• 子查询通过GROUP BY为每条调度记录锁定符合条件的最大入学日期，解决普通关联导致的重复数据问题
• 外层两次关联确保仅返回对应最大入学日期的学生

预期执行结果 #

STUDENT NAME
Y
X
Y
Y
Z
Z
S
T

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