SQL Server：每30分钟或更长时间计数一次

发布于 2025-01-28 04:54:36 字数 1133 浏览 6 评论 0原文

我们有一个活动数据库，将用户互动记录到网站，存储一个包括time1，session_id和cosition_id的

2022-05-12 08:00:00|11|1
2022-05-12 08:20:00|11|1
2022-05-12 08:30:01|11|1
2022-05-12 08:14:00|22|2
2022-05-12 08:18:00|22|2
2022-05-12 08:16:00|33|1
2022-05-12 08:50:00|33|1

日志要有两个单独的查询：

查询＃1：，如果每天有30分钟或更多的日志，我需要多次计数会话。

例如：最初count = 0

session_id = 11，它从08:00开始，最后一次使用相同的session_id为08:30 -count = 1
session_id = 22它在08:14开始，最后一次使用同一会话为08:14-仍然= 1，因为它少于30分钟

，我尝试了此查询，但是它没有可行的

select 
    count(session_id)
from
    table1 
where 
    @datetime between Time1 and dateadd(minute, 30, Time1);

预期结果：

查询＃2：这是上述查询的扩展，我每天都需要唯一的客户，其会话是30分钟或更高的。

例如：从上表中，我将在5月8日

预期结果

” 。

原文

We have an activity database that records user interaction to a website, storing a log that includes values such as Time1, session_id and customer_id e.g.

2022-05-12 08:00:00|11|1
2022-05-12 08:20:00|11|1
2022-05-12 08:30:01|11|1
2022-05-12 08:14:00|22|2
2022-05-12 08:18:00|22|2
2022-05-12 08:16:00|33|1
2022-05-12 08:50:00|33|1

I need to have two separate queries:

Query #1: I need to count sessions multiple times if they have a log of 30 minutes or more grouping them on sessions on daily basis.

For example: Initially count=0

For session_id = 11, it starts at 08:00 and the last time with the same session_id is 08:30 -- count=1
For session_id = 22 it starts at 08:14 and the last time with the same session is 08:14 -- still the count=1 since it was less than 30 min

I tried this query, but it didn't work

select 
    count(session_id)
from
    table1 
where 
    @datetime between Time1 and dateadd(minute, 30, Time1);

Expected result:

Query #2: it's an extension of the above query where I need the unique customers on daily basis whose sessions were 30 min or more.

For example: from the above table I will have two unique customers on May 8th

Expected result

For the Time1 column, the input is in timestamp format when I show it in output I will group it on a basis.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怀里藏娇 2025-02-04 04:54:36

这是一个两级聚集（组成）问题。您需要从一个子查询开始，以获取每个会话的第一个也是最后一个时间戳。

             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id

接下来，您需要使用这样的子查询：

SELECT COUNT(session_id),
       COUNT(DISTINCT customer_id),
       CAST(start_time AS DATE)
  FROM (
             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id
       ) a
 WHERE DATEDIFF(MINUTE, start_time, end_time) >= 30
 GROUP BY CAST(start_time AS DATE);

This is a two-level aggregation (GROUP BY) problem. You need to start with a subquery to get the first and last timestamp of each session.

             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id

Next you need to use the subquery like this:

SELECT COUNT(session_id),
       COUNT(DISTINCT customer_id),
       CAST(start_time AS DATE)
  FROM (
             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id
       ) a
 WHERE DATEDIFF(MINUTE, start_time, end_time) >= 30
 GROUP BY CAST(start_time AS DATE);

回复收藏 0 原文

~没有更多了~