SQL查询在过去的N天中获取点击/购买的物品，并获得过去的活动

发布于 2025-02-01 08:12:15 字数 1545 浏览 3 评论 0原文

我试图弄清楚如何编写SQL查询以构建所述的数据集如下：

提取在过去49天内单击的项目以构建目标项目以及在单击目标项目之前发生的相应点击行为。以一个目标项目 a 为例，我们设置了将 a 单击为最后一天的一天以及该用户在过去14天中采取的行为。

这是我要查询的表示例：

user_id
item_id
action（单击，购买）
时间戳

用户ID	项目ID	Action	Time_stamp
1	2	单击	2022-03-02 002 00:00:00
1	3	单击	2022-03-03-02 002 002 002 002 ：10：00
1	4	单击	2022-03-02 00:12:00
2	9	单击	2022-03-02-02 00:01:00
2	11	单击	2022-03-02-02-02 00:08:00
2	3	单击	2022-03- 02 00:12:00
2	2	单击	2022-03-02 00:14:00
2	4	单击	2022-03-02-02 00:18:00

结果应该看起来像这样的结果：

用户ID	项目ID
1	2
1	3
1 4	4
2	9
2	11
2	3
2	2
2	4

每个用户的最后一项ID是目标项目，其余的user_id/item_id对订购了WRT Time_stamp。

谢谢

原文

I'm trying to figure out how to write an SQL query to build the dataset described as follows:

Extract items that were clicked in the past 49 days to construct the target items and the corresponding click behaviors that happened before the target items were clicked. Using one target item a as an example, we set the day that a is clicked as the last day and the behaviors that this user took in the past 14 days.

Here's a sample of the table that I'm querying from:

user_id
item_id
action (click, purchased)
timestamp

user id	item id	action	time_stamp
1	2	click	2022-03-02 00:00:00
1	3	click	2022-03-02 00:10:00
1	4	click	2022-03-02 00:12:00
2	9	click	2022-03-02 00:01:00
2	11	click	2022-03-02 00:08:00
2	3	click	2022-03-02 00:12:00
2	2	click	2022-03-02 00:14:00
2	4	click	2022-03-02 00:18:00