Help us understand the problem. What is going on with this article?

[BigQuery] 一部のカラムだけクエリしたくない場合はexcept関数があるよ

私が知らなかっただけで、BigQuery使っている人なら皆知ってるのかなぁと不安な思いもあるが、知らないままでいるには非常に勿体無い機能なので紹介したい。
あと書き方忘れた時にパッと検索できるように記事化しておきたい

3行で

  • SQLでモデルを作るための特徴量を詰め込んだテーブルを作成する時、カラムが多すぎると明示的に記載するの面倒じゃないですか
  • でも、テーブルに目的変数や特徴量として使いたくないカラム(インデックス用のカラムとか作成時刻カラムとか)が含まれていると単純に*(アスタリスク)で書けないですよね
  • でも、BigQueryではクエリしたくない列をexcept()として指定すると*(アスタリスク)でいい感じに書けるんですわ

どういうこと?

SQLでクエリする対象がテーブルのほとんど全てなのだけど、一部だけクエリしたくない場合は普通は排除したいカラム以外を明示的に記載してクエリすることになる

何をクエリするべきかSQL文に記載されるので、クエリの仕方としては正しいもののデータ分析を行う場合には時折クエリしたいカラム数が数百を越えることがある

それを全て明示的にクエリするのはクエリ文があまりに長くなりすぎて可読性が非常に低くなる(そして1つ1つのカラムにはさほど興味もない)

BigQueryならこう書ける

BigQueryであれば、テーブルの中で排除したいカラムを明示的に記載して残りは*(アスタリスク)で全て取り出すというクエリ方法が可能!!!

やることは* except(排除したいカラム名)をSELECT文に記載するだけ

こんな感じ(公開データなので皆クエリできるはず)

irisデータのうち種類(species)以外をクエリしたい
SELECT 
    * except(species)
FROM 
    `bigquery-public-data.ml_datasets.iris` 
;

結果画面(単一カラム指定)

スクリーンショット 2019-12-08 20.15.14.png

ちゃんと除外したいspeciesが除かれている

こうやって書くだけで、自分がクエリしたくないカラム以外をクエリできるとは。。。

複数除外したいカラムを指定することもできる

irisデータのうち種類(species)以外をクエリしたい
SELECT 
    * except(sepal_length,species)
FROM 
    `bigquery-public-data.ml_datasets.iris` 
;

結果画面(複数カラム指定)

スクリーンショット 2019-12-08 20.18.54.png

特殊な使い方だけど、かなり便利!!!凄い!!!

クエリの意味合い

*(アスタリスク)の部分を左集合と考えてexcept()がある部分を右集合と考えると全てのカラムが含まれる左集合から除外するカラムが含まれている右集合とを比較して左にしか含まれないカラムを出力するというexcept関数の本来の機能をレコードに対してではなくカラムに対して実現していると解釈できる

ちなみにレコードを比較するExcept関数もあります

どちらかというとこっちがメジャーな機能だがレコードを比較してexceptに含まれないレコードを返す機能もある

-- 最初の10行と5行を取り出して後半の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)
;

結果画面

スクリーンショット 2019-12-08 20.41.07.png

最後に

公式リファレンスのサンプルコードの中で当然のように使われていたので、

「まさかそんな事出来るはずねえだろ!!」と思ってクエリしたら出来たのでビビりました

もっと早く知りたかった

アスタリスクを極力使わないというBigQueryのベストプラクティスにはやや反している感はあるけど、使いどころを間違わなければ非常に便利

おしまい

-参考-

BigQueryで2つのテーブルの差分を求める方法

【BigQuery】集合演算子(UNION…)まとめ

標準 SQL クエリ構文:except

BigQueryのStandard SQLの便利な機能とLegacy SQLからの変更点をいくつか紹介

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away