Conditionals and Filters

Additional flexibility when composing a query

Conditionals and Filters Overview

Conditions are used to define what logic is to be executed based on the status of some condition being satisfied. Filters are expressions used to specify a subset of the data items.

CASE

Case statement. Expressions are evaluated in order, and the first to evaluate to true is returned.

ELSE returns a default value if no statement evaluates to true. If there is no ELSE condition and no case evaluates to true, then NULL is returned.

Operator

CASE
WHEN exprs THEN result1
[ WHEN exprs THEN result2 ]
…
[ ELSE default ]
END

Example

CASE
WHEN state = 'CA' THEN 1
WHEN score > 500 THEN 1
ELSE 0
END

CASE expression

“Simple” case statement. This is similar to the above, except on checks equality between a common expression and values.

Operator

CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END

Example

CASE state
WHEN 'CA' THEN 1
WHEN 'TX' THEN 1
ELSE 0
END

WHERE clause

Where clause. Takes a list of filter expressions.

NOTE: we do not currently support subqueries in WHERE clauses.

Operator

WHERE filter_exprs [ AND | OR filter_exprs ]

Example

WHERE 
state = 'CA'

FILTER

Filter with a where clause. The where clause only applies to the aggregation preceding the FILTER keyword. Otherwise, the WHERE clause is identical to any other WHERE clause.

Operator

aggregate_function( * ) [ FILTER ( WHERE filter_exprs ) ]

Example

COUNT(*) 
FILTER (
  WHERE 
  state = 'CA' OR state = 'TX'
)