![如何计算唯一值?](https://linux22.com/image/539333/%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%E5%94%AF%E4%B8%80%E5%80%BC%EF%BC%9F.png)
我正在尝试获取唯一 ip_addresses 的数量(在本例中为“3”)。表格如下所示:
结构:
CREATE TABLE bandits (
key text NOT NULL,
ip_address inet,
offence text,
count bigint DEFAULT 1);
数据:
从 stdin 复制 bandits(key,ip_address,offence,count); 127.0.0.1_测试 127.0.0.1 测试 1 127.0.0.2_测试 127.0.0.2 测试 3 127.0.0.2_测试2 127.0.0.2 测试2 1 127.0.0.3_测试 127.0.0.3 测试 1
答案1
SELECT COUNT(DISTINCT ip_address) FROM bandits
答案2
正如这里提到的:https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow,使用更长的版本可能会更快:
SELECT count(*) FROM (SELECT DISTINCT ip_address FROM bandits) AS bandits_distinct