SELECT
student,
CASE
WHEN grade_num = 12 THEN 'Senior'
WHEN grade_num = 11 THEN 'Junior'
...
ELSE 'Not in HS' -- Catch others!
END AS grade
FROM students;
| student | grade |
| ---------- | ---------- |
| Viraj | Junior |
| Stephanie | Senior |
| Lewis | Not in HS |
条件の評価
演算子
条件
例
=
値が等しい
column_a = 'value_a'
IN (...)
値がリスト内にある
column_a IN ('value_a', 'value_b')
>, <
値が大きい/小さい
column_a > 0
>=, <=
> と < の同等版（等号含む）
column_a >= 0
BETWEEN
値が範囲内（両端含む）
column_a BETWEEN 0 AND 10
これらは AND、OR、NOT と組み合わせ可能
Snowflake は列指向 DB のため、CASE 文は効率的です
気温のカテゴリ分け
SELECT
todays_date,
temperature,
CASE
WHEN temperature BETWEEN 70 AND 90 THEN 'Ideal for Swimming'
WHEN temperature >= 50 AND tempreature < 70 THEN 'Perfect for Sports'
WHEN temperature > 32 AND temperature < 50 THEN 'Spring/Fall Temps'
WHEN temperature > 0 AND temperature <= 32 THEN 'Winter Weather'
ELSE 'Extreme Temperatures'
END AS temperature_description
FROM weather;
気温のカテゴリ分け
条件式の組み合わせ
SELECT
todays_date,
temperature,
status,
CASE
WHEN temperature > 70 AND status NOT IN ('Rain', 'Wind') THEN 'Beach'
WHEN temperature BETWEEN 45 AND 70 AND status = 'Sun' THEN 'Sports'
WHEN temperature <= 32 OR status = 'Snow' THEN 'Skiing'
ELSE 'Stay In'
END AS activity
FROM weather;
Preparing Video For Download...
