BigQuery ～qualify句編～

はじめに

こんには！データアナリティクス事業部の池田です。最近は勉強の秋ということで学ぶ意欲が高まっています笑
今回は今年の初めにBQでGAされたqualify句の説明をしていきたいと思います！

Qualify 句とは？

公式ドキュメントはこちら。
ドキュメントより下記の説明が記載されています。

QUALIFY 句は、分析関数の結果をフィルタリングします。ウィンドウ関数は、QUALIFY 句または SELECT リストに含める必要があります。

bool_expression が TRUE と評価された行のみが含まれます。bool_expression が NULL または FALSE と評価された行は破棄されます。

BigQuery公式ドキュメント

以上から分かる通り、Qualify 句は分析関数の結果を使い条件式を書くことができる句になることが分かります！where 句や having 句と同じ感覚ですね！分析関数とは sum(), count(), rank() などのことです！それでは早速具体例を入れて使っていきましょう！！

Qualify 句を使ってみる

Qualify 句はある程度、分析したい層が決まっているときにすごく便利だと思います。例えば、月別で顧客を購入金額の高い順に並べ、上位10位に入る顧客を抽出したテーブルを作成したいとします。

まず qualify なしのクエリを書いてみます。

-- 月の売り上げが上位10番目の顧客を抽出する

with sum_sales as (
  select
    cast(Order_Date as string format "YYYYMM") as YM
    , Customer_ID
    , Customer_Name
    , Segment
    , sum(Sales) as total_sales
  from
    `fortwitter.twitter_neta.sample_super`
  where
    Segment = "Consumer"
  group by
    YM
    , Customer_ID
    , Customer_Name
    , Segment
)

, rank_kokyaku as (
  select
    YM
    , Customer_ID
    , Customer_Name
    , total_sales
    , rank() over(
      partition by YM
      order by total_sales desc  -- 降順
    ) as kokyaku_rank
  from
    sum_sales
)

select
  *
from
  rank_kokyaku
where
  kokyaku_rank <= 10 -- 10以上を抽出する
order by
  YM, kokyaku_rank

上位10以上を取得

qualify句を使うことでランク付けだけのサブクエリが不要になりランクを付けすぐに抽出することができます。
以下がqualify句を使ったクエリです。

-- 月の売り上げが上位10番目の顧客を抽出する

with sum_sales as (
  select
    cast(Order_Date as string format "YYYYMM") as YM
    , Customer_ID
    , Customer_Name
    , Segment
    , sum(Sales) as total_sales
  from
    `fortwitter.twitter_neta.sample_super`
  where
    Segment = "Consumer"
  group by
    YM
    , Customer_ID
    , Customer_Name
    , Segment
)

select
  YM
  , Customer_ID
  , Customer_Name
  , total_sales
  , rank() over(
    partition by YM
    order by total_sales desc  -- 降順
  ) as kokyaku_rank
from
  sum_sales
qualify
  kokyaku_rank <= 10 -- 10以上を抽出する

もしくはランク付け↓カラムが不要な場合はqualify句に直接書くことができます。

-- 月の売り上げが上位10番目の顧客を抽出する

with sum_sales as (
  select
    cast(Order_Date as string format "YYYYMM") as YM
    , Customer_ID
    , Customer_Name
    , Segment
    , sum(Sales) as total_sales
  from
    `fortwitter.twitter_neta.sample_super`
  where
    Segment = "Consumer"
  group by
    YM
    , Customer_ID
    , Customer_Name
    , Segment
)

select
  YM
  , Customer_ID
  , Customer_Name
  , total_sales
from
  sum_sales
qualify
  rank() over(partition by YM order by total_sales desc)  <= 10 -- 10以上を抽出する

不要なカラムを増やしたくない時はこっちのがスマートかもしれませんね！

さいごに

以上が、qualify句の使い方です。簡単ですが、サブクエリを1つ消す力を持っている句です！使いこなしましょう！
ウィンドウ関数を使ったカラムで条件抽出したい。。。となったときはqualify句を思い出して是非役に立ててください！