PostgreSQL使用含>=<=多条件的JOIN统计用户发信前7天购礼数量

问题根因 #

你原有SQL的核心错误是使用了count(*)统计行数：
LEFT JOIN未匹配到B表购礼记录时，返回行的B表字段全为NULL，但count(*)统计的是结果集的总行数，哪怕B字段全空的行也会被计数为1，因此coalesce(count(*), 0)永远不会返回0，和需求要求无购礼记录返回0的规则冲突。

另外你将时间过滤条件写在LEFT JOIN的ON子句中的逻辑是正确的，若误写在WHERE子句中会过滤掉无匹配购礼记录的行，反而会导致结果遗漏。

修正后的SQL #

只需要将count(*)替换为对B表非空字段的计数即可，count(字段)会自动忽略NULL值，无匹配时自然返回0：

SELECT 
  A.user_id, 
  A.time, 
  COUNT(B.user_id) AS count
FROM A
LEFT JOIN B 
  ON A.user_id = B.user_id 
  AND B.time >= A.time - INTERVAL '7 days' 
  AND B.time < A.time
GROUP BY 1, 2

如果使用的是MySQL类数据库，间隔语法需要调整为INTERVAL 7 DAY，其余逻辑不变。

结果验证 #

以上SQL执行后输出结果和你给出的期望输出完全一致。

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