PostgreSQL聚合函数的filter子句

一张表存储了学生id，科目，分数三个字段，求每个学生60分以下与参加的总科目占比。（今天电脑不好用，图片总是这样）

 

其实一个count(*) filter  就可以查出来，但是没用过PG的一个人竟然说我这么简单的SQL查询都不会，只是因为我没有用嵌套查询。回来总结了以下，自己想到了以下几种方法（只查询出了对应的数目没有做除法）：

 

--filter函数

select stu_id, count(*), count(*) filter (where score<60) as "不及格"

from sherry.agg_filter

group by stu_id

 

--使用case when

select stu_id, count(*), sum(case when score<60 then 1 else 0) as "不及格"

from sherry.agg_filter group by stu_id

 

--关联查询

select stu_id, count(*), (select count(*) as c from sherry.agg_filter b where a.stu_id = b.stu_id and b.score < 60) as t2

from sherry.agg_filter a

group by a.stu_id

 

--临时表关联

with raw as(

select stu_id, count(*) as c1 from sherry.agg_filter

where score < 60

group by stu_id

),

raw2 as(

select stu_id, count(*) as c2

from sherry.agg_filter 

group by stu_id)

select b.stu_id, c1, c2

from raw a

right join raw2 b

on a.stu_id = b.stu_id