使用FILTER子句时的奇怪行为

Question

我有两个问题

SELECT COUNT(DISTINCT keyword_id),
  date_trunc('month', rank_date) AS date
  FROM keyword_ranks, keywords
  WHERE keywords.deleted_at IS NULL
  AND keywords.id=keyword_ranks.keyword_id 
  AND keywords.business_id=27 GROUP BY date_trunc('month', rank_date);

结果集是

​

​

现在我添加了一些FILTER子句，比如

SELECT COUNT(DISTINCT keyword_id) FILTER (WHERE rank>50 OR rank is null) AS "50+",
  COUNT(DISTINCT keyword_id) FILTER (WHERE rank BETWEEN 21 AND 50) AS "21-50",
  COUNT(DISTINCT keyword_id) FILTER (WHERE rank BETWEEN 11 AND 20) AS "11-20",
  COUNT(DISTINCT keyword_id) FILTER (WHERE rank BETWEEN 4 AND 10) AS "4-10",
  COUNT(DISTINCT keyword_id) FILTER (WHERE rank BETWEEN 1 AND 3) AS "1-3",
  date_trunc('month', rank_date) AS date
  FROM keyword_ranks, keywords
  WHERE keywords.deleted_at IS NULL
  AND keywords.id=keyword_ranks.keyword_id 
  AND keywords.business_id=27 GROUP BY date_trunc('month', rank_date);

它的结果集是

​

​

从第一个查询结果可以看出，每个月的distinct keyword_id计数为147。但是，当使用FILTER子句时，为什么总数超过147？如果这不是获得不同计数的方法，那该怎么做呢？请容忍我的新手问题。任何帮助都非常感谢

Answer 1

ids在一个等级中是不同的，但在总数中不是。检查此示例：

with v (id, rank) as (values (1,1),(1,10))
select
    count(distinct id) as total,
    count(distinct id) filter (where rank < 10) as "< 10",
    count(distinct id) filter (where rank >= 10) as ">= 10"
from v
;
 total | < 10 | >= 10 
-------+------+-------
     1 |    1 |     1

使排名总和与总和匹配的唯一方法是不使用distinct。

with v (id, rank) as (values (1,1),(1,10))
select
    count(id) as total,
    count(id) filter (where rank < 10) as "< 10",
    count(id) filter (where rank >= 10) as ">= 10"
from v
;
 total | < 10 | >= 10 
-------+------+-------
     2 |    1 |     1