时间戳

首页 > 解决方案 > SQL groupby 具有不同的计数

sql - SQL groupby 具有不同的计数

问题描述

我有一个 postgres 数据库，其中包含一个包含 IP、用户和时间字段的表。我需要一个查询来为我提供在定义的时间段内只有一个用户活动的所有 IP 的完整集（即我需要过滤掉有多个或没有用户的 IP，并且每个 IP 应该只有一行） . 用户字段包含一些我可以过滤掉的空值。我正在使用 Pandas 的 read_sql() 方法直接获取数据帧。

我可以通过以下方式轻松获取定义时间段的完整数据帧：

SELECT ip, user FROM table WHERE user IS NOT NULL AND time >= start AND time <= end

然后，我可以使用带有 groupby 和 filter 操作的 pandas 轻松获取这些数据并从中获取我需要的信息。但是，我希望能够使用单个 SQL 查询获得所需的内容。不幸的是，我的 SQL 代码不太热。我在下面的第一次尝试不是很好；我最终得到的数据框与我使用上面的原始查询和一些熊猫争论手动创建数据框时不同。

SELECT DISTINCT ip, user FROM table WHERE user IS NOT NULL AND ip IN (SELECT ip FROM table WHERE user IS NOT NULL AND time >= start AND time <= end GROUP BY ip HAVING COUNT(DISTINCT user) = 1)

谁能在这里指出我正确的方向？谢谢。

编辑：我忽略了每个用户/IP组合有多个条目。来源是网络身份验证流量，用户在 IP 上进行身份验证非常频繁。

样品台头：

---------------------------------
ip         | user    | time
---------------------------------
172.18.0.0 | jbloggs | 1531987000
172.18.0.0 | jbloggs | 1531987100
172.18.0.1 | jsmith  | 1531987200
172.18.0.1 | jbloggs | 1531987300
172.18.0.2 | odin    | 1531987400

如果我要查询这个示例表的时间范围 1531987000 到 1531987400，我想要以下输出：

---------------------
ip         | user 
--------------------
172.18.0.0 | jbloggs
172.18.0.2 | odin

标签： sqlpostgresql

解决方案

这应该工作 

SELECT ip
FROM table
WHERE user IS NOT NULL AND time >= start AND time <= end
GROUP BY ip
HAVING COUNT(ip) = 1

说明：
SELECT ip FROM table WHERE user IS NOT NULL AND time >= start AND time <= end- 过滤掉空值和时间段

...GROUP BY ip HAVING COUNT(ip) = 1- 如果一个 ip 有多个用户，则计数（具有该 ip 的行数）将大于 > 1。

推荐阅读