MySQL 查询 - 查找“新”每日用户数

发布于 2024-10-07 09:31:38 字数 416 浏览 2 评论 0原文

我有一个包含以下字段的数据表

EventID        : Int, AutoIncrement, Primary Key
EventType      : Int                             ' Defines what happened
EventTimeStamp : DateTime                        ' When the Event Happened
UserID         : Int                             ' Unique

查询需要告诉我整个集合中每天使用新 UserID 发生的事件数。因此，每天存在多少个具有前一天不存在的 UserID 的事件。我已经尝试了很多，并且每天可以获得唯一用户，但无法弄清楚如何每天获得“新”用户。

原文

I have a table of data with the following fields

EventID        : Int, AutoIncrement, Primary Key
EventType      : Int                             ' Defines what happened
EventTimeStamp : DateTime                        ' When the Event Happened
UserID         : Int                             ' Unique

The query needs to tell me how many events occurred with new UserIDs for each day in the whole set. So, for each day, how many events exist which have a UserID which doesn't exist in any prior day. I've tried lots, and I can get unique users per day, but can't work out how to get 'NEW' users per day.

分享到QQ

分享到微博

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

发布评论

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

后来的我们 2024-10-14 09:31:38

Select count(EventId) from table
where 
UserId 
  not in (select UserId from table where EventTimeStamp < now() - interval 1 day)

Select count(EventId) from table
where 
UserId 
  not in (select UserId from table where EventTimeStamp < now() - interval 1 day)

回复收藏 0 原文

相对绾红妆 2024-10-14 09:31:38

感谢大家的帮助 - 我已投票赞成该帮助。这就是我所做的：

我创建了这 2 个视图（我需要最终得到一个视图，并且必须创建 2 个视图，因为看起来您无法在视图中嵌套 select 语句）。

目击事件：

select min(to_days(`Events`.TimeStamp)) AS Day0,
    `Events`.TimeStamp AS TimeStamp,
    `Events`.UserID AS UserID
from `Events` group by `Events`.UserID order by `Events`.UserID

新用户：

select count(distinct Sightings.UserID) AS Count,
    date(Sightings.TimeStamp) AS Date from Sightings
    group by date(Sightings.TimeStamp)

Thank you all for your help - I've voted up the assistance. Here's what I did:

I created these 2 views (I needed to end up with a view, and had to create 2 as it seems you can't nest select statements within views).

Sightings:

select min(to_days(`Events`.TimeStamp)) AS Day0,
    `Events`.TimeStamp AS TimeStamp,
    `Events`.UserID AS UserID
from `Events` group by `Events`.UserID order by `Events`.UserID

NewUsers:

select count(distinct Sightings.UserID) AS Count,
    date(Sightings.TimeStamp) AS Date from Sightings
    group by date(Sightings.TimeStamp)

回复收藏 0 原文

走过海棠暮 2024-10-14 09:31:38

好问题。我没有确切的解决方案，但这是我之前见过的方法：

执行 SELECT，将给定 userID 的 EventTimeStamp 与 MIN(EventTimeStamp) 进行比较，由同一表上的嵌套 SELECT 语句确定，以计算每个 ID 的 MIN 时间戳（例如 GROUP BY UserID）。

回复收藏 0 原文

宛菡 2024-10-14 09:31:38

首先为每个用户第一次到达时获取一个表b，
然后加入该表以获取该用户当天的所有事件。

SELECT DATE(a.EventTimeStamp), COUNT(*) FROM table a
JOIN
(SELECT MIN(EventTimeStamp) AS EventTimeStamp, UserID from table group by userID) b
ON a.UserID = b.UserID
AND DATE(a.EventTimeStamp) = DATE(b.EventTimeStamp) 
GROUP BY DATE(a.EventTimeStamp)

First get a table b with for each user when he first arrived,
then join that table to get all events for that user for that day.

SELECT DATE(a.EventTimeStamp), COUNT(*) FROM table a
JOIN
(SELECT MIN(EventTimeStamp) AS EventTimeStamp, UserID from table group by userID) b
ON a.UserID = b.UserID
AND DATE(a.EventTimeStamp) = DATE(b.EventTimeStamp) 
GROUP BY DATE(a.EventTimeStamp)

回复收藏 0 原文

十年九夏 2024-10-14 09:31:38

select EventTimeStamp , sum(cnt) from 
(
    with usr_min as 
    (  
        select UserID, min(EventTimeStamp) as min_EventTimeStamp from table group by UserID 
    )
    select a.UserID, a.EventTimeStamp,
           CASE when a.EventTimeStamp = b.min_EventTimeStamp then 1
           ELSE 0 END AS cnt
    from table a join usr_min b ON a.UserID = b.UserID
)
group by EventTimeStamp

select EventTimeStamp , sum(cnt) from 
(
    with usr_min as 
    (  
        select UserID, min(EventTimeStamp) as min_EventTimeStamp from table group by UserID 
    )
    select a.UserID, a.EventTimeStamp,
           CASE when a.EventTimeStamp = b.min_EventTimeStamp then 1
           ELSE 0 END AS cnt
    from table a join usr_min b ON a.UserID = b.UserID
)
group by EventTimeStamp

回复收藏 0 原文