PostgreSQL:根据日期确定回访者-联接还是窗口函数? [英] PostgreSQL: Identifying return visitors based on date - joins or window functions?
问题描述
我希望在7天之内确定回访网站的访客.数据样本和解决问题的尝试如下:
I am looking to identify return visitors to a website within a 7 day window. A data sample and attempt at solving are included below:
visitor_id(integer)
session_id(integer)
event_sequence(integer)
d_date(date)
原始数据样本:
+-----------+-------------+----------------+-------------+
| visitor_id| session_id | event_sequence | d_date |
+-----------+-------------+----------------+-------------+
| 1 | 1 | 1 | 2017-01-01 |
| 1 | 1 | 2 | 2017-01-01 |
| 1 | 1 | 3 | 2017-01-01 |
| 1 | 2 | 1 | 2017-01-05 |
| 1 | 2 | 2 | 2017-01-05 |
| 1 | 3 | 1 | 2017-01-20 |
| 1 | 4 | 1 | 2017-01-25 |
| 2 | 1 | 1 | 2017-01-02 |
| 2 | 1 | 2 | 2017-01-02 |
| 2 | 2 | 1 | 2017-01-02 |
| 2 | 2 | 2 | 2017-01-02 |
| 2 | 2 | 3 | 2017-01-02 |
| 2 | 3 | 1 | 2017-01-18 |
+-----------+-------------+----------------+-------------+
对于每个访问者会话，我想知道访问者是否在访问日期的后7天内返回(进行了另一个会话).最终，该表在 visitor_id ， session_id 级别上将是唯一的，并包含一个标志，用于指示访问者是否在接下来的7天之内返回.
I would like to know, for each visitor-session, whether the visitor returns (has another session) within the subsequent 7 days of the visit date. Ultimately the table would be unique at the visitor_id, session_id level and include a flag for whether the visitor returned in the subsequent 7 days.
以下是我期望输出显示的样子:
The following is how I would expect my output to look:
+-----------+-------------+-----------------+-------------+
| visitor_id| session_id | returned_7_days | d_date |
+-----------+-------------+-----------------+-------------+
| 1 | 1 | 1 | 2017-01-01 |
| 1 | 2 | 0 | 2017-01-05 |
| 1 | 3 | 1 | 2017-01-20 |
| 1 | 4 | 0 | 2017-01-25 |
| 2 | 1 | 1 | 2017-01-02 |
| 2 | 2 | 0 | 2017-01-02 |
| 2 | 3 | 0 | 2017-01-18 |
+-----------+-------------+-----------------+-------------+
解决此问题的一种方法是将每个 visitor_id - session_id 组合加入相应的 visitor_id ，如下所示:
One way to solve this involves joining every visitor_id-session_id combination to the corresponding visitor_id, as so:
SELECT t2.visitor_id, t2.session_id, t2.d_date, t1.start_date
FROM table t2
INNER JOIN (
SELECT visitor_id, session_id, min(d_date) as start_date
FROM table t1
GROUP BY visitor_id, session_id
) t1
ON t1.visitor_id = t2.visitor_id
对于每个 visitor_id - session_id 组合，哪个返回与该 visitor_id 对应的所有其他会话的日期.从那里，我可以比较 d_date 是否在 start_date 的7天内.但是，这似乎不是解决问题的有效方法，尤其是当有数百万个独特的 visitor_id 组合，每个组合都带有数十个 session_id - event_sequence 组合.
Which returns, for each visitor_id-session_id combination, the dates of all other sessions corresponding to that visitor_id. From there, I can compare whether d_date is within 7 days of start_date. However, this does not appear an efficient way to solve the problem, especially when there are millions of unique visitor_id combinations, each crossed with dozens of session_id-event_sequence combinations.
有没有更好的方法可以解决我没有想到的问题?
Is there a better way to solve this problem I am not thinking of?
推荐答案
首先，我用 DISTINCT 删除 event_sequence (假设所有事件都在同一天)，然后使用窗口函数 lead 并与下次访问的日期进行比较:
First, I remove event_sequence with a DISTINCT (assuming that all events are on the same day), then I use the window function lead and compare with the date of the next visit:
SELECT visitor_id,
session_id,
COALESCE(
lead(d_date) OVER w - d_date,
10
) < 7 AS revisited,
d_date
FROM (SELECT DISTINCT visitor_id,
session_id,
d_date
FROM "table"
) t
WINDOW w AS (PARTITION BY visitor_id
ORDER BY d_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
ORDER BY visitor_id, session_id;
┌────────────┬────────────┬───────────┬────────────┐
│ visitor_id │ session_id │ revisited │ d_date │
├────────────┼────────────┼───────────┼────────────┤
│ 1 │ 1 │ t │ 2017-01-01 │
│ 1 │ 2 │ f │ 2017-01-05 │
│ 1 │ 3 │ t │ 2017-01-20 │
│ 1 │ 4 │ f │ 2017-01-25 │
│ 2 │ 1 │ t │ 2017-01-02 │
│ 2 │ 2 │ f │ 2017-01-02 │
│ 2 │ 3 │ f │ 2017-01-18 │
└────────────┴────────────┴───────────┴────────────┘
(7 rows)
这篇关于PostgreSQL:根据日期确定回访者-联接还是窗口函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
