英文:
SQL Server 18 - merging dates around midnight
问题
我想问一下是否有人可以帮助我解决这个问题。我有一个包含用户访问网站的表格。重要的列是时间戳，格式如下：
| 时间戳 |
| 2017-06-26 23:52:09.000 |
| 2017-06-26 23:52:22.000 |
| 2017-06-26 23:58:13.000 |
| 2017-06-26 23:52:59.000 |
| 2017-06-27 00:02:35.000 |
时间戳用于计算会话。时间戳中的一个小时或更长时间表示一个会话。问题是，当网站上的活动发生在午夜后时，计算的会话会不同。因此，下面的示例会生成两个会话，而不是一个。
| 2017-06-26 23:52:59.000 |
| 2017-06-27 00:02:35.000 |
我需要找到一种方法，将一个时间戳（午夜后）与其他时间戳（午夜后）连接或合并起来。感谢任何建议。我已经尝试了一段时间，但没有取得任何进展。
英文:
I would like to ask if anyone could help me with this. I have a table with user traffic on websites. The important column is Timestamp which looks like this.
| Timestamp |
| 2017-06-26 23:52:09.000 |
| 2017-06-26 23:52:22.000 |
| 2017-06-26 23:58:13.000 |
| 2017-06-26 23:52:59.000 |
| 2017-06-27 00:02:35.000 |
Timestamps are used to calculate sessions. An hour or so in timestamps means one session. The problem is that calculated session is different when the activity on the websites was made after midnight. Thus, the example below makes two sessions, not one.
| 2017-06-26 23:52:59.000 |
| 2017-06-27 00:02:35.000 |
I need to find and merge or somehow connect one Timestamp (after midnight) to others (after midnight). Thank you for any advice. I have been trying for a while but I can´t make any progress.
答案1
得分: 0
你可以使用 (LEAD(Timestamp) OVER (PARTITION BY user ORDER BY Timestamp) - Timestamp) 吗？
英文:
Could you use (LEAD(Timestamp) OVER (PARTITION BY user ORDER BY Timestamp) - Timestamp)?
答案2
得分: 0
如果我理解正确，您想根据时间戳之间的一小时间隔分配会话。
这可以通过窗口函数轻松完成：
select t.*,
sum(case when timestamp < dateadd(hour, 1, prev_timestamp) then 0
else 1
end) over (partition by user_id order by timestamp) as session_number
from (select t.*, lag(timestamp) over (partition by user_id order by timestamp) as prev_timestamp
from t
) t;
我猜测存在用户ID，并且您想要按每个用户执行此操作。
英文:
If I understand correctly, you want to assign sessions based on a gap of an hour in the timestamps.
This is easily done with window functions:
select t.*,
sum(case when timestamp < dateadd(hour, 1, prev_timestamp) then 0
else 1
end) over (partition by user_id order by timestamp) as session_number
from (select t.*, lag(timestamp) over (partition by user_id order by timestamp) as prev_timestamp
from t
) t;
I am speculating on the existence of a user id and that you want to do this per user.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论