sql - 在 Postgresql 中，如何使用具有多个条件的连接，包括 >= 和 <=

Question

我有表 A 和表 B。表 A 中的每一行代表用户每次发送消息。表 B 中的每一行代表用户每次购买礼物的时间。

目标：对于用户每次发送消息，计算他们在发送消息的时间戳之前的 7 天内购买了多少礼物。有些用户从不发送消息，有些用户从不购买礼物。如果表 A 中的用户在 7 天内没有购买礼物，则计数应为 0。

表 A：

用户身份	时间
12345	2021-09-04 09:43:55
12345	2021-09-03 00:39:30
12345	2021-09-02 03:26:07
12345	2021-09-05 15:48:34
23456	2021-09-09 09:06:22
23456	2021-09-08 08:06:21
00001	2021-09-03 15:38:15
00002	2021-09-03 15:38:15

表 B：

用户身份	时间
12345	2021-09-01 09:43:55
12345	2021-08-03 00:42:30
12345	2021-09-03 02:16:07
00003	2021-09-05 15:48:34
23456	2021-09-03 09:06:22
23456	2021-09-10 08:06:21

预期输出：

用户身份	时间	数数
12345	2021-09-04 09:43:55	2
12345	2021-09-03 00:39:30	1
12345	2021-09-02 03:26:07	1
12345	2021-09-05 15:48:34	2
23456	2021-09-09 09:06:22	1
23456	2021-09-08 08:06:21	1
00001	2021-09-03 15:38:15	0
00002	2021-09-03 15:38:15	0

我试过的查询：

SELECT A.user_id, A.time, coalesce(count(*), 0) 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

返回的计数与预期结果不匹配，但不确定我是否正确执行连接和条件。

标签： sqlpostgresqljoinleft-join

Answer 1

您需要对可能为 NULL 的列（即表 B）中的值进行计数，以便获得不存在匹配项的正确计数。即更具体COUNT(*)到COUNT(b.column_from_b_table)。请参阅下面的工作演示小提琴修改：

SELECT 
    A.user_id, 
    A.time, 
    coalesce(count(B.user_id), 0) 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;

用户身份	时间	数数
1	2021-09-03T15:38:15.000Z	0
12345	2021-09-05T15:48:34.000Z	2
23456	2021-09-08T08:06:21.000Z	1
12345	2021-09-04T09:43:55.000Z	2
12345	2021-09-03T00:39:30.000Z	1
23456	2021-09-09T09:06:22.000Z	1
2	2021-09-03T15:38:15.000Z	0
12345	2021-09-02T03:26:07.000Z	1

---

在 DB Fiddle 上查看

让我知道这是否适合您。