MySQL查询-找到“新”每天的用户 [英] MySQL query - find "new" users per day

问题描述

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

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

查询需要告诉我整个集合中每天有多少事件与新UserID一起发生。因此，每天有多少个事件具有UserID在任何一天以前都不存在。我做了很多尝试，每天都能吸引到不重复的用户，但无法解决每天如何吸引 NEW用户的问题。

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.

推荐答案

谢谢大家的帮助-我已对帮助进行了投票。这是我所做的：

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

我创建了这2个视图（我需要以一个视图结束，并且不得不创建2个视图，因为您似乎无法嵌套选择语句

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).

景点：

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)

这篇关于MySQL查询-找到“新”每天的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！