私が知らなかっただけで、BigQuery使っている人なら皆知ってるのかなぁと不安な思いもあるが、知らないままでいるには非常に勿体無い機能なので紹介したい。
あと書き方忘れた時にパッと検索できるように記事化しておきたい
3行で
- SQLでモデルを作るための特徴量を詰め込んだテーブルを作成する時、カラムが多すぎると明示的に記載するの面倒じゃないですか
- でも、テーブルに目的変数や特徴量として使いたくないカラム(インデックス用のカラムとか作成時刻カラムとか)が含まれていると単純に*(アスタリスク）で書けないですよね
- でも、BigQueryではクエリしたくない列をexcept()として指定すると*(アスタリスク)でいい感じに書けるんですわ
どういうこと？
SQLでクエリする対象がテーブルのほとんど全てなのだけど、一部だけクエリしたくない場合は普通は排除したいカラム以外を明示的に記載してクエリすることになる
何をクエリするべきかSQL文に記載されるので、クエリの仕方としては正しいもののデータ分析を行う場合には時折クエリしたいカラム数が数百を越えることがある
それを全て明示的にクエリするのはクエリ文があまりに長くなりすぎて可読性が非常に低くなる（そして１つ１つのカラムにはさほど興味もない）
BigQueryならこう書ける
BigQueryであれば、テーブルの中で排除したいカラムを明示的に記載して残りは*(アスタリスク)で全て取り出すというクエリ方法が可能！！！
やることは* except(排除したいカラム名)をSELECT文に記載するだけ
こんな感じ(公開データなので皆クエリできるはず)
SELECT
* except(species)
FROM
`bigquery-public-data.ml_datasets.iris`
;
結果画面(単一カラム指定)
ちゃんと除外したいspeciesが除かれている
こうやって書くだけで、自分がクエリしたくないカラム以外をクエリできるとは。。。
複数除外したいカラムを指定することもできる
SELECT
* except(sepal_length,species)
FROM
`bigquery-public-data.ml_datasets.iris`
;
結果画面(複数カラム指定)
特殊な使い方だけど、**かなり便利！！！**凄い！！！
クエリの意味合い
*(アスタリスク)の部分を左集合と考えてexcept()がある部分を右集合と考えると全てのカラムが含まれる左集合から除外するカラムが含まれている右集合とを比較して左にしか含まれないカラムを出力するというexcept関数の本来の機能をレコードに対してではなくカラムに対して実現していると解釈できる
ちなみにレコードを比較するExcept関数もあります
どちらかというとこっちがメジャーな機能だがレコードを比較してexceptに含まれないレコードを返す機能もある
-- 最初の10行と５行を取り出して後半の5行だけ出力する
WITH a as (
SELECT
*
FROM
`bigquery-public-data.ml_datasets.iris`
LIMIT 10
),
b as (
SELECT
*
FROM
`bigquery-public-data.ml_datasets.iris`
LIMIT 5
)
SELECT
*
FROM
(SELECT * FROM a EXCEPT DISTINCT SELECT * FROM b)
;
結果画面
最後に
公式リファレンスのサンプルコードの中で当然のように使われていたので、
「まさかそんな事出来るはずねえだろ！！」と思ってクエリしたら出来たのでビビりました
もっと早く知りたかった
アスタリスクを極力使わないというBigQueryのベストプラクティスにはやや反している感はあるけど、使いどころを間違わなければ非常に便利
おしまい