I was looking at the BigQuery documentation on conditional expressions: https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions
It outlines CASE and IF as below:
1. CASE, as a 'switch' statement
CASE expr
WHEN expr_to_match THEN result
[ ... ]
[ ELSE else_result ]
END
2. CASE, as an if-then-else tree
CASE
WHEN condition THEN result
[ ... ]
[ ELSE else_result ]
END
3. IF, as expected
IF(expr, true_result, else_result)
Is there any meaningful difference between 2. and 3. above?
- are there any real implications, in performance or otherwise?
- are there are widely preferred conventions in their usage, or is it mostly up to personal/team's preference?
I suppose if there is a relatively deep if-else tree then 2. might be a bit cleaner to write, as it doesn't come with trailing close-parentheses:
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
WHEN condition_3 THEN result_3
WHEN condition_4 THEN result_4
WHEN condition_5 THEN result_5
WHEN condition_6 THEN result_6
ELSE default_result END
↑ above is a perhaps a little cleaner than below ↓
IF(condition_1, result_1
IF(condition_2, result_2
IF(condition_3, result_3
IF(condition_4, result_4
IF(condition_5, result_5
IF(condition_6, result_6,
default_result))))))
Aucun commentaire:
Enregistrer un commentaire