Conditionals and Filters
Additional flexibility when composing a query
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 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
“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. 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 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'
)
Last modified 26d ago