What does filter mean?

Question

select driverid, count(*) 
from f1db.results
    where position is  null
group by driverid order by driverid

My thought: first find out all the records that position is null then do the aggregate function.

select driverid, count(*) filter (where position is null) as outs
   from f1db.results 
group by driverid order by driverid

First time, I meet with filter clause, not sure what does it mean? Two code block results are different. Already googled, seems don't have many tutorials about the FILTER. Kindly share some links.

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES — , Nov 24 '20 at 16:57
See: https://stackoverflow.com/a/27141193/939860, https://dba.stackexchange.com/a/27572/3684 — Erwin Brandstetter, Nov 24 '20 at 18:35

score 2 · Answer 1 · answered Nov 24 '20 at 16:40

A more helpful term to search for is aggregate filters, since FILTER (WHERE) is only used with aggregates.

Filter clauses are an additional filter that is applied only to that aggregate and nowhere else. That means it doesn't affect the rest of the columns!

You can use it to get a subset of the data, like for example, to get the percentage of cats in a pet shop:

SELECT shop_id,
       CAST(COUNT(*) FILTER (WHERE species = 'cat')
            AS DOUBLE PRECISION) / COUNT(*) as "percentage"
FROM animals
GROUP BY shop_id

For more information, see the docs

score 1 · Answer 2 · answered Nov 24 '20 at 16:40

1

FILTER ehm... filters the records which should be aggregated - in your case, your aggregation counts all positions that are NULL.

E.g. you have 10 records:

SELECT COUNT(*)

would return 10.

If 2 of the records have position = NULL

than

SELECT COUNT(*) FILTER (WHERE position IS NULL)

would return 2.

What you want to achieve is to count all non-NULL records:

SELECT COUNT(*) FILTER (WHERE position IS NOT NULL)

In the example, it returns 8.

answered Nov 24 '20 at 16:40

S-Man

22,521
7
40
63

So In this scenario, It looks like the same as HAVING. Since HAVING also is first do aggregate function then do the filter function. – Mark Nov 25 '20 at 04:47
HAVING counts all values, no matter how they look. It's not possible to count only the NULL values with HAVING without applying a WHERE clause. But the WHERE clause would remove the records from the result, whereas FILTER just removes them from the aggregate. FILTER allows a fine adjustment for the input of each aggregate. More: When yku have more aggregates, say COUNT and SUM, you can apply different FILTERs on them: COUNT without NULL and SUM only values over ten (`FILTER (WHERE position > 10)`) – S-Man Nov 25 '20 at 07:21

What does filter mean?

2 Answers2