条件に応じて値を列に挿入
条件に当てはまる行だけ別の値にします。上から順に条件を見て、最初に当てはまったところの値を返します。
分析プロジェクトとの違い
| 見るところ | SQLビルダー | 分析プロジェクト |
|---|---|---|
| 返り値に既存列を使う | できる（条件ごと・不一致時のどちらも） | ない |
| 返り値の型 | 文字列 / 数値 / 日付 / 日付時刻 | 同じ 4 つ |
| 型変更に失敗したときの扱い | ない | 「型変更不可エラーを無視する」がある |
| 比較タイプ | 型ごとに 10〜11 種類 | 日付の単位・範囲指定まで細かく分かれる |
入出力
| ポート | 内容 |
|---|---|
| 入力データ | データ |
| 出力データ | データ |
設定項目
フォームの見出しの並び — 「結果の挿入先を設定」 → 「条件と返り値の設定」 → 「条件一致の場合の設定」 → 「全条件に不一致の場合の設定」。条件によって出ない見出しもあります。
- 結果の挿入先を設定
- 新規列に挿入（既定）か既存列に上書き。
- 新規列名を入力
- 「新規列に挿入」のときに出ます。
- 列を選択必須
- 「既存列に上書き」のときに出ます。
- 型を選択
- 新しく作る列の型。文字列型（既定）/ 数値型 / 日付型 / 日付時刻型。「既存列に上書き」のときは出ません。
- 条件
- 条件と返り値の組。上から順に見て、最初に当てはまった組の返り値になります。
- 対象列を選択必須
- 条件を当てる列。選んだ列の型で、次の比較タイプの顔ぶれが変わります。
- 比較タイプを選択必須
- 下の選べる比較タイプを参照。
- 値を入力必須
- 比べる相手。「いずれかに等しい」「いずれとも等しくない」のときは値を入力してエンターに変わり、複数入れられます。欠損値を見る 2 つでは出ません。
- 既存列の値を返り値にする
- on にすると、固定の値ではなく別の列の値を返します。条件ごとと、全条件に不一致のときの両方で選べます。
- 返り値を入力
- 空欄にすると欠損になります。
選べる比較タイプ
比較タイプごとの一覧
| 比較タイプ | 文字列の列 | ほかの列 |
|---|---|---|
| 等しい(=) / 等しくない(!=) | ○ | ○ |
| いずれかに等しい(in) / いずれとも等しくない(not in) | ○ | ○ |
| 欠損値を含まない / 欠損値を含む | ○ | ○ |
| より小さい(<) / 以下(<=) / より大きい(>) / 以上(>=) | × | ○ |
| 文字を含む / 文字を含まない | ○ | × |
| [条件値]から始まる(starts_with) / [条件値]で終わる(ends_with) | ○ | × |
| 正規表現にマッチ | ○ | × |
生成される SQL
条件の数だけ WHEN が並ぶ CASE 式になります。PostgreSQL につないだ場合です。
SELECT "売上", "店舗", CASE WHEN "売上" >= 100000 THEN 'A' WHEN "売上" >= 50000 THEN 'B' ELSE 'C' END AS "ランク"FROM (SELECT "売上", "店舗" FROM "orders") AS "port_xxxxxxxx"このノードだけは、前のノードの SELECT をいったん内側に入れた形になります。 外側で条件を書くために、入力を FROM (...) でくるみます。
新しい列はいちばん右に付きます。 「既存列に上書き」を選んだときも、その列はもとの位置から外れていちばん右に移ります。並びを決めたいときは、後ろに列の並べ替えを続けてください。
返り値を空欄にすると ELSE null になります。「既存列の値を返り値にする」を選ぶと、THEN '固定値' の代わりに、その列を返り値の型に変換した式が入ります。
エラーと対処
どのエラーも、設定を直してノードを実行し直せば消えます。
Treasure Data で日付型を選んだ
Treasure Data につないだビルダーで、型に日付型・日付時刻型を選ぶと出ます。
日付型はサポートされていません
日付時間型はサポートされていません
直し方 — 文字列型で返してください。
新規列名が空
「新規列に挿入」のまま列名を入れずに実行すると出ます。
列名を入力してください
直し方 — 新規列名を入れてください。
列が選ばれていない
条件の行で列を選んでいない、または「既存列に上書き」で上書き先を選んでいないと出ます。
列を選択してください
直し方 — 対象列・上書き先の列を選ぶか、その行を削除してください。
上流にない列が選ばれている
前のノードで列が消えたあとに実行すると出ます。
存在しない列が選択されています
直し方 — 列を選び直してください。
入力がつながっていない
前のノードがつながっていないと出ます。
カラムが選択されていません。条件を確認してください
直し方 — 入力にノードをつないでください。
よく使う例
1. 売上をランク分けする
お題 — 金額で A / B / C に分けたい
入力 — 売上 = 120000、60000、20000
設定 — 新規列 ランク、型は文字列型。条件 1「売上 以上 100000 → A」、条件 2「売上 以上 50000 → B」、不一致は C
出力 — A B C
2. 不要な値を空にする
お題 — テスト と入っている行の値を消したい
入力 — 店舗 = 渋谷、テスト
設定 — 既存列に上書き、条件「店舗 等しい テスト」、返り値は空欄、不一致は「既存列の値を返り値にする」で 店舗
出力 — テスト の行だけ空欄、ほかはそのまま
3. 欠損を既定値で埋め分ける
お題 — 担当 が空のとき 未割当 にしたい
入力 — 担当 = 山田、空欄
設定 — 条件「担当 欠損値を含む → 未割当」、不一致は既存列 担当
出力 — 空の行だけ 未割当
4. 複数の値をひとまとめにする
お題 — 東京 神奈川 千葉 を 首都圏 にしたい
入力 — 県 = 東京、大阪
設定 — 条件「県 いずれかに等しい 東京 神奈川 千葉 → 首都圏」、不一致は その他
出力 — 首都圏 / その他
5. 0 と 1 のフラグを作る
お題 — 会員かどうかを数値で持ちたい
入力 — 会員区分 = ゴールド、空欄
設定 — 新規列 会員フラグ、型は数値型。条件「会員区分 欠損値を含まない → 1」、不一致は 0
出力 — ゴールド の行が 1、空欄の行が 0
「値を入力」は空のままにできません。 「等しくない」などの比較タイプを選ぶと値の入力が必須になるので、空かどうかで分けたいときは値を要求しない「欠損値を含む」「欠損値を含まない」を使ってください