Skip to main content

​
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'
)