在使用带过滤器的计数和改变条件之间的选择

Question

有两个表，video和category。

create table category (
    id integer primary key,
    name text
);

create table video (
    id integer primary key,
    category_id integer references category (id),
    quality text
);

insert into category (id, name) values (1, 'Entertainment');
insert into category (id, name) values (2, 'Drawing');

insert into video (id, category_id, quality) values (1, 1, 'sd');
insert into video (id, category_id, quality) values (2, 1, 'hd');
insert into video (id, category_id, quality) values (3, 1, 'hd');

我可以得到所有类别的名单与所有视频的数量。

select category.id, category.name, count(video)
from category left outer join video
on (category.id = video.category_id)
group by category.id;

结果

 id |     name      | count 
----+---------------+-------
  2 | Drawing       |     0
  1 | Entertainment |     3
(2 rows)

要获得高清视频数量的所有类别，可以使用这两个查询。

带滤波器的计数

select
category.id,
category.name,
count(video) filter (where video.quality='hd')
from category left outer join video
on (category.id = video.category_id)
group by category.id;

结果

 id |     name      | count 
----+---------------+-------
  2 | Drawing       |     0
  1 | Entertainment |     2
(2 rows)

on

select
category.id,
category.name,
count(video)
from category left outer join video
on (category.id = video.category_id and video.quality='hd')
group by category.id;

结果

 id |     name      | count 
----+---------------+-------
  2 | Drawing       |     0
  1 | Entertainment |     2
(2 rows)

结果是相等的。使用第一和第二种方式的利弊是什么？哪一种更好？

Answer 1

第二个查询在某种程度上更有效，因为join的join谓词减少了先前的行数，而第一个查询保留了所有行，然后依赖聚合函数的筛选器。我建议进行第二个查询。

例如，如果要执行几个条件计数，则第一个查询将非常有用，例如：

select
    category.id,
    category.name,
    count(*) filter (where video.quality='hd') no_hd_videos,
    count(*) filter (where video.quality='sd') no_sd_videos
from category 
left outer join video on category.id = video.category_id
group by category.id;