人をダメにする try_cast
おはようございます、ねすたです、よろしくどうぞお願いします。
今回は初の技術系記事投稿です。
会社におけるねすたのポジションとしては、バックエンドではあるものの、メインタスクの1つとして『データ抽出』があります。
営業や企画Tが調査したいものや分析したいもの、またクライアントに理解してもらいやすいようにDBの内容を整形・集計を行い、依頼者にヒアリングしながらクエリを作成していきます。
BIツールはRe:dashを利用しており、インフラはほぼAWSなのでAthenaデータソースを利用する機会が多くなってきました。
Athenaデータソースのクエリに関してはPrestoで記述するのですが、初めて扱うSQLだったので非常に興味深かった記憶があります。
OracleやMySQLと比較するとメリデメ両方ありますが、Prestoにおけるメリットの1つとしては「関数が豊富」というイメージが強いです。
文字列関連の関数はもちろんのこと、今回ピックアップしたいのは型変換の関数です。
とはいえ種類が豊富ではなく、
/* param の型を type 型に変換 */
cast(param AS type)
/* param の型を type 型に変換し、変換できない場合はnullを返す */
try_cast(param AS type)上記の関数がメインとなります。
中でも try_cast 、これはありがたい。
コメントとして記述した通り、『変換できない場合はnullを返す』が何に適用されるのかが気になるので使ってみました。
ーーーーー
＜integerに変換した時の例＞
SELECT try_cast('' AS integer) AS "空文字",
try_cast(NULL AS integer) AS "NULL",
try_cast('abc' AS integer) AS "数値以外",
try_cast('①' AS integer) AS "環境依存文字",
try_cast('1.00' AS integer) AS "小数点あり数値",
try_cast('1' AS integer) AS "小数点なし数値"
FROM table結果はこちら。
正常に動作し、結果も概ね想定（希望）通りといった感じです。
ちなみにcastのほうだとNULLパターンと小数点なし数値パターン以外は変換エラーになってしまうので、try_cast の用途としてまとめると、
■NULLや空文字：NULLを返す
■数値以外の文字を含む：NULLを返す
■数値のみの文字列：変換された数値を返す
といったところでしょうか。変換エラーの可能性を潰してくれる関数。
便利。いろいろ考慮しなくても書くだけで対応してくれる。すごい便利。
変換対象のカラムをSUMして数値を集計したい、なんて時にはNULLを集計に含めるのは個人的に好きではないので、
ifnull(try_cast(param AS type), 0)こうしてあげることで、明示的に0としてSUM対象に含めてあげることができたりもするんだなと。しっくりくるわ〜。
ーーーーー
・・・あれ？
これはいけない。いや、try_cast自体は有能だと思います。めっちゃ有能。
ただ、使い方を間違えるとまずいのでは？と思いました、それは
『データ不備に気づけない（可能性がある）』
これはありそう。
例えば「商品ID」というvarchar型のカラムがあり、将来的な文字列を含ませられる拡張性を持たせつつも実際のデータは数値しか現状想定されていない場合。
これに対してtry_castしましたと。
SELECT try_cast(商品ID AS integer) AS "変換後"
FROM table
WHERE try_cast(商品ID AS integer) IS NOT NULL;クエリの結果は正常、integerに変換されて欲しい結果も満たされます。
そんな商品IDに対し、"abc"という値を持つレコードが存在した場合にWHERE句で弾かれてしまい、このクエリ上ではデータ不備に気づけないのでは？と。
ーーーーー
(；´Д｀)
ーーーーー
まあ、数値のみの項目に対して文字列型を選択すること自体が間違っているので、ちゃんとした組織であれば杞憂に終わります。
弊社の場合は割とそういう設定が個人の意思に依存するケースが多々ありまして、そういう組織でホイホイ使うのは少し危険な香りはしています。使いますけどね。
大事なのはこの言葉に尽きます。
try_castは用法用量を守って正しくお使いください。
以上です。