PostgreSQL中按日期范围关联Sale与Royalty表并计算分成金额

核心思路 #

Royalty表的每条记录对应一个生效时间段：

• 生效起始时间：Royalty.createdAt
• 生效结束时间：若deletedAt为null，则表示当前及未来一直生效，用'infinity'::timestamptz替代；否则用deletedAt

我们需要为每条Sale记录匹配其createdAt时刻正处于生效状态的Royalty记录，再计算rate * price。

实现SQL #

SELECT
    s.ItemId,
    r.rate * s.price AS royalty_amount,
    s.createdAt AS sale_created_at
FROM
    Sale s
JOIN
    Royalty r 
    ON s.createdAt >= r.createdAt
    AND s.createdAt < COALESCE(r.deletedAt, 'infinity'::timestamptz);

关键细节说明 #

• 用COALESCE(r.deletedAt, 'infinity'::timestamptz)处理永久生效的Royalty记录，无需后续新增数据时修改查询语句
• 采用>=和<的左闭右开区间逻辑，避免BETWEEN闭区间可能导致的时间点重叠冲突（比如一条Royalty的deletedAt刚好等于另一条的createdAt）
• 新增Royalty数据后，该查询会自动适配，无需额外维护

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