如何选择一系列日期(使用与日期相关的子查询进行选择)

Question

假设我的PostgreSQL数据库中有一个表

 -----------+-----------------------------
 IP        | inet                        
 timestamp | timestamp                   
 ports     | integer REFERENCES other_table_does_not_matter                    
 hostname  | character varying           

每小时检查一次每个 IP。

我想对计数（IP）进行分组，即按天汇总每日不可用，如下所示：

SELECT timestamp::date AS date, COUNT (DISTINCT IP) AS count 
FROM ip_check WHERE ports=9 AND ip NOT IN (SELECT IP FROM ip_check WHERE hostname <> '')
GROUP BY timestamp::date
ORDER BY timestamp::date ASC;

但还有一个附加条件。我需要排除即使在短时间内也可用/可访问的 IP（在我的情况下主机名<>"表示该 IP 可用）：

... AND IP NOT IN (SELECT ... WHERE hostname <> '' AND DATE(timestamp)='2019-01-25')

换句话说。我想在一系列日期上运行单个 SELECT（如下所示），并按天对结果进行分组。

SELECT COUNT(DISTINCT IP) 
FROM ip_check 
WHERE ports=9 AND DATE(timestamp)='2019-01-25' 
AND IP NOT IN 
    (SELECT IP 
     FROM ip_check 
     WHERE hostname <> '' AND DATE(timestamp)='2019-01-25');

示例数据：

 (ip;timestamp;ports;hostname)
 (1.1.1.1;2019-01-24 10:11;9;'')
 (1.1.1.1;2019-01-24 11:11;9;'hostA')
 (1.1.1.1;2019-01-24 1:11;9;'')
 (1.1.1.1;2019-01-24 2:11;9;'')
 (1.1.1.1;2019-01-24 3:11;9;'')
 (1.1.1.1;2019-01-24 4:11;9;'')
 (2.1.1.1;2019-01-24 10:11;9;'')
 (3.1.1.1;2019-01-24 10:11;9;'hostC')
 (1.1.1.1;2019-01-25 10:11;9;'')
 (1.1.1.1;2019-01-25 11:11;9;'')
 (1.1.1.1;2019-01-25 1:11;9;'hostA')
 (2.1.1.1;2019-01-25 10:11;9;'')
 (3.1.1.1;2019-01-25 10:11;9;'')

期望输出：

     data   | count
------------+-------
 2019-01-24 |  1
 2019-01-25 |  2
(2 rows)

解释：

• 2019-01-24 - 只有 1 个 IP （2.1.1.1） 完全不可用
• 2019-01-25 - 2 IP （2.1.1.1， 3.1.1.1） 完全不可用

Answer 1

要展示每天完全不可用的 ip，您可以使用以下查询：

SELECT ips.ip, dts.dt
FROM
(SELECT DISTINCT ip FROM mytable) ips
CROSS JOIN (SELECT DISTINCT timestamp::date dt FROM mytable) dts
WHERE NOT EXISTS (
  SELECT 1 FROM mytable WHERE ip = ips.ip AND timestamp::date = dts.dt AND hostname <> ''
)

该查询的工作原理是在不同的 IP 和天数之间生成笛卡尔连接，并使用相关的子查询，然后在应被视为完全不可用的元组中进行筛选。

对于示例数据，查询将返回：

ip        dt
2.1.1.1   2019-01-24
2.1.1.1   2019-01-25
3.1.1.1   2019-01-25

---

要获取每天完全不可用的 ip 计数，我们只需将其转换为聚合查询：

SELECT dts.dt, COUNT(*)
FROM
(SELECT DISTINCT ip FROM mytable) ips
CROSS JOIN (SELECT DISTINCT timestamp::date dt FROM mytable) dts
WHERE NOT EXISTS (
  SELECT 1 FROM mytable WHERE ip = ips.ip AND timestamp::date = dts.dt AND hostname <> ''
)
GROUP BY dts.dt

返回：

dt            count
2019-01-24    1
2019-01-25    2

数据库小提琴演示