PostgreSQL基于日期条件关联trial_lessons与purchases表方案咨询

错误原因 #

你原有逻辑的问题出在CASE语句的返回值判断规则：2021-09-01之后满足student_id匹配的场景返回值为2，但你最后限定了返回值等于1才关联，直接把这部分符合规则的关联结果全部过滤，因此输出不符合预期。

可行实现方案 #

这种条件关联逻辑完全可以直接写在JOIN条件中，不需要强制拆分查询，有两种常用写法：

1. 修正CASE写法 #

调整CASE的返回值规则，所有符合关联条件的场景统一返回1即可：

ON (
  CASE 
    WHEN p.purchase_date < '2021-09-01' AND p.student_id = t.student_id AND p.subject_id = t.subject_id THEN 1
    WHEN p.purchase_date >= '2021-09-01' AND p.student_id = t.student_id THEN 1
    ELSE 0 
  END = 1
)

注：示例中p代指purchases表，t代指trial_lessons表，可替换为你实际使用的表别名。

2. 布尔逻辑写法（更推荐） #

直接用OR拆分不同时间范围的关联规则，可读性更高，也更容易被数据库优化器识别命中索引：

ON p.student_id = t.student_id
AND (
  p.purchase_date < '2021-09-01' AND p.subject_id = t.subject_id
  OR p.purchase_date >= '2021-09-01'
)

如果需要规避2021-09-01之后的脏数据（比如存在非null的subject_id异常数据），可以额外加校验规则：

ON p.student_id = t.student_id
AND (
  (p.purchase_date < '2021-09-01' AND p.subject_id = t.subject_id)
  OR (p.purchase_date >= '2021-09-01' AND p.subject_id IS NULL)
)

拆分查询的适用场景 #

如果两张表的数据量达到千万级以上，拆分两次查询再UNION ALL可能获得更好的性能：

• 第一次关联2021-09-01之前的购买记录，用student_id+subject_id双字段匹配
• 第二次关联2021-09-01及之后的购买记录，只用student_id匹配
• 两次结果合并输出即可
  小数据量场景下直接用上述JOIN条件写法即可，代码更简洁易维护。

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