我有表 A 和表 B。表 A 中的每一行代表用戶每次發送訊息的時間。表 B 中的每一行代表用戶每次購買禮物的時間。
目標：對于用戶每次發送訊息，計算他們在發送訊息的時間戳之前 7 天內購買了多少禮物。有些用戶從不發送訊息，有些用戶從不購買禮物。如果表 A 中的用戶在 7 天內沒有購買禮物，則計數應為 0。
表一：
| 用戶身份 | 時間 |
|---|---|
| 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
回傳的計數與預期的結果不匹配，但不確定我是否正確執行了連接和條件。
uj5u.com熱心網友回復：
您需要對可能為 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 上查看
讓我知道這是否適合您。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/315239.html
標籤：sql PostgreSQL 加入 左连接
上一篇：Flink臨時連接只作業幾秒鐘
下一篇：基于嵌套成員資格有條件地連接R表