在数据库中分离新用户和返回用户

本文介绍了在数据库中分离新用户和返回用户的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个很大的数据库，但是出于这个问题的目的我已经简化了它：

基本上，每次用户在我的网站上单击某些内容时，它都会在数据库中记录为一行： UserID 字段是用于标识用户的Cookie，而 Stuff 和 MoreStuff 是有关点击的数据。注意，显然，每次 Foo和 Foo都可能不同。和 Bar

我想做的是：使用SQL查询，过滤掉所有的第一次访问或所有重复的访问（我假设是否可以做一个，我可以为另一个求反。因此，如果我要过滤掉样本中所有的重复访问，我将得到：

，绿色表示所选行，红色表示拒绝的行。

操作方法

解决方案

您可以使用聚合函数 COUNT（），然后 HAVING 语句，例如：

SELECT userID，来自tbl 的COUNT（用户ID）来自用户ID的组有COUNT（用户ID）> = 2

如果您想过滤谁是重复访问者，则可以重复使用以上查询：

SELECT *从tbl 存在（选择userID，COUNT（userID）从tbl GROUP BY用户ID 有COUNT个（userID）> == 2 ） GROUP BY用户ID

希望看到您可以更改的首次访问者 HAVING COUNT（userID）> = 2 到 HAVING COUNT（userID）= 1 或当然代替 EXISTS 使用不存在。

请参见 演示

I have quite a large DB, but I've simplified it for the purpose of this question:

Basically, every time a user clicks something on my site, it gets logged as a row in my DB: the UserID field is a cookie that is used to identify the user, and Stuff and MoreStuff are data about the click. Note that, obviously, these are likely to be different every time, "Foo" and "Bar" is just a representation.

What I want to do is this: with an SQL query, filter out either all of the first visits, or all of the repeated visits (I assume if I can do one, I can invert my filter for the other). So, if I were to filter out all of the repeat visits on my sample, I'd get this:

with green representing the selected rows and red representing the rejected ones.

How can I do this with just SQL?

解决方案

You could use aggregate function COUNT() and then HAVING statement like:

SELECT userID, COUNT(userID) FROM tbl GROUP BY userID HAVING COUNT(userID) >= 2

You can then re-use the above query if you want to filter out who are the repeat visitors like:

SELECT * FROM tbl WHERE EXISTS ( SELECT userID, COUNT(userID) FROM tbl GROUP BY userID HAVING COUNT(userID) >= 2 ) GROUP BY userID

If you want to see first-time visitors you could change HAVING COUNT(userID) >= 2 to HAVING COUNT(userID) = 1 or of course instead of EXISTS use NOT EXISTS.

See Demo