DB

BigQueryで全角半角変換など文字列の正規化する方法

BigQueryで全角半角変換など文字列の正規化する方法


半角文字や全角文字が混在しているテキストを正規化して揃えたい

テキストデータを扱っていると、全角の EGG と半角の EGG のような全角と半角だけど同じ文字で書かれたテキストを同一として扱いたくなる。

しかし全角文字と半角文字はデータ上は別物なので、比較演算子で比較しても同じものとしては扱われない。

また大文字や小文字のように upper(), lower() で変換するわけにも行かない。

ではどうやって全角半角を統一すればいいのか。

そこで今回はBigQueryで全角半角変換など文字列の正規化する方法について解説する。

この記事を書いている人


からさん
システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。最近GCPを色々と習得中。

記事を読むメリット

BigQueryで全角半角変換など文字列の正規化する方法がわかる


データの準備


まずはデータを準備する。

CREATE OR REPLACE TABLE
pj_name.test.tmp_text
AS
(
  (select "EGG" as text)
  union all
  (select "EGG" as text)
  union all
  (select "エッグ" as text)
  union all
  (select "エッグ" as text)
) 


作成されたテーブルを確認する。

select * from pj_name.test.tmp_text

結果

Row text
1 エッグ
2 EGG
3 エッグ
4 EGG


冒頭で述べたように全角テキストと半角テキストは別のデータなので、 distinct で重複を省いても全角と半角は重複扱いにはならないので、データ数は減らない。

select
distinct text
from pj_name.test.tmp_text

結果

Row text
1 エッグ
2 EGG
3 エッグ
4 EGG



BigQueryで全角半角変換など文字列の正規化する方法


ではどのように全角半角変換するのかというと、normalize() 関数を用いる。

BigQueryには文字列の正規化用に normalize() 関数が存在する。

NORMALIZE(value[, normalization_mode])
説明

文字列値を取り、正規化された文字列として返します。

正規化は、2 つの文字列を同等にするために使用されます。画面上で同じように表示される 2 つの文字列が、実際には異なる Unicode コードポイントを持つような状況で、正規化がよく使用されます。

参考:標準 SQL の式、関数、演算子  |  BigQuery  |  Google Cloud


正規化のモードは4種類あるが、半角全角の正規化をするなら NFKC が良いだろう。

実際に使ってみると以下のようになる。

NFKC正規化によって半角カタカナは全角になり、全角英数は半角英数に変換される。

select
normalize(text, NFKC) as n_text
from pj_name.test.tmp_text

結果

Row n_text
1 エッグ
2 EGG
3 エッグ
4 EGG


そして全角半角の片方に正規化されたテキストはデータとしても一致するので、distinct による重複の省略対象にできる。

select
distinct
normalize(text, NFKC) as n_text
from pj_name.test.tmp_text

結果

Row n_text
1 エッグ
2 EGG



まとめ


今回はBigQueryで全角半角変換など文字列の正規化する方法について紹介した。

正規化するには normalize() 関数を用いる。


少年
簡単だね!
2017年の時点ではこの関数は無かったようで、自作の関数で対応するしかなかったみたいですね
からさん
少年
便利になったんやね


他にもBigQuery関連の記事もあるので、もし気になったら見ていって欲しい。

コチラもオススメ

KRSW

駆け出し機械学習エンジニア。機械学習、DB、WEBと浅く広い感じ。 Junior machine learning engineer. Not a specialist but a generalist who knows DB, WEB too.

役に立ったらシェアしてくれると励みになります。

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

Copyright© ITips , 2021 All Rights Reserved.