如何计算会话长度 [英] How to calculate session length

问题描述

每当我的网站访问者执行操作时，我都会将该操作记录到数据库中，同时记录操作发生的时间。我正在构建一个仪表板，以便更好地概述用户在网站上的行为。



我想跟踪的一个指标是会话长度。



如何判断用户活动了多长时间？



理想情况下我会喜欢创建一个SQL查询来拉出所有用户并将它们分组到会话中，其中任何两个操作之间的差距永远不会超过配置的超时，例如30分钟。如果差距大于超时，则应将其计为两个单独的会话。



有关如何执行此操作的任何建议 - 在SQL或C＃/ Linq中？



我尝试了什么：



我还没试过还有什么 - 我正在努力思考如何开始解决这个问题。

Every time a visitor to my website performs an action, I am logging that action to a database, along with the time the action occurred. I am in the process of building a dashboard to provide a better overview of what users are doing on the site.

One of the metrics I would like to track is session length.

How can I work out how long the user has been active?

Ideally I would like to create an SQL query that pulls out all the users and groups them into sessions, where the gap between any two actions is never greater than a configured timeout, say, 30 minutes. If the gap is greater than the timeout, then it should be counted as two separate sessions.

Any suggestions on how to do this - either in SQL or C#/Linq?

What I have tried:

I haven't tried anything yet - i'm struggling to think how to begin tackling this.

推荐答案

我很想把信息添加到数据库中 - 一个标识一个数据的值会话，因此它们被自动分组。

如果您向数据库添加GUID字段，那么当您记录操作时，您可以对上一个会话条目进行简单的搜索：它是同一个用户，并且时间戳不到30分钟。如果存在，请使用新条目的GUID值。如果没有，请创建一个新的GUID并插入它。

然后当你想运行你的指标时，它是一个相对简单的GROUP BY子句，无论是在SQL还是Linq。



我这样做的原因是由于人为事件导致的一小部分额外处理，使得数据处理变得微不足道。如果你不这样做，那么首先你必须解决这些组，然后处理它们以获取信息 - 这将是一个更复杂的查询（尽管SQL Server LAG [ ^ ]和 LEAD [< a href =https://msdn.microsoft.com/en-gb/library/hh213125.aspxtarget =_ blanktitle =新窗口> ^ ]语句引入SQL 2012会有所帮助） 。

错字：woudl代表will - OriginalGriff [/ edit]

I'd be very tempted to add info to the DB - a value which identifies a session, so they are "automatically" grouped.
If you add a GUID field to the DB, then when you record an action you can do a trivial search for the previous session entry: it's the same user, and with a time stamp less than 30 minutes ago. If it exists, use it's GUID value for your new entry. If it doesn't, create a new GUID and insert that.
Then when you want to run your metrics, it's a relatively simple GROUP BY clause, either in SQL or Linq.

The reason I'd do it like that is that it's a tiny bit of extra processing as a result of a human-scale event, making the data processing trivial. If you don't, then first you have to work out the groups, and then process them to get the info - which is going to be a lot more complex a query (though the SQL Server LAG[^] and LEAD[^] statements introduced to SQL 2012 would help).
[edit]Typo: "woudl" for "would" - OriginalGriff[/edit]

这篇关于如何计算会话长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！