DB

BigQueryでrepeated型カラムの行数を数える方法

BigQueryでrepeated型カラムの行数を数える方法

GoogleのBigQuery(通称「BQ」)はデータ分析によく使われる。

そしてBigQueryには特殊な列(カラム)として、ネストされた繰り返し列 REPEATED 列を作ることができる。
言わば1つのテーブルの中に親行と複数の子行がいるようなイメージである。

1つのテーブルの中に親行と子行がいるとひとつの問題が出てくる。
どうやって行数を数えればいいのか

親行は count() で数えることができる。
では子行はどうやって数えれば良いのか

今回はそんな問題を解決するために、「BigQueryでrepeated型カラムの行数を数える方法」を解説する。

この記事を書いている人


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

記事を読むメリット

  • BigQueryでrepeated型カラムの行数を数える方法がわかる

  • BigQueryのrepeated型カラムとは

    BigQueryのrepeated型カラムとは、ネストされた繰り返し列のことである。

    一般的なテーブルでは1行に対して各カラムの持つことができる値はひとつ。
    しかしBigQueryで REPEATED と設定された列は1行の中で複数の値を持つことができる

    イメージは1つのテーブルの中に親行と複数の子行がいるようなイメージである。

    具体的な例だと、以下のように人単位のレコードに対して、現在や過去に住んだことのある住所を複数持つことができる。

    • id
    • first_name
    • last_name
    • dob(生年月日)
    • addresses(ネストと繰り返しのあるフィールド)
      • addresses.status(現在または以前)
      • addresses.address
      • addresses.city
      • addresses.state
      • addresses.zip
      • addresses.numberOfYears(居住年数)

    これを数えるにはどうすればいいのか。



    BigQueryでrepeated型カラムの行数を数える方法

    BigQueryでrepeated型カラムの行数を数える方法。

    それは array_length() を使うことである。

    array_length() を使えば REPEATED と設定された列の数を数えることができる。
    具体的には以下のように用いる。

    SELECT sum(array_length(repeated型カラム名)) FROM データセット名.テーブル名
    



    上の例だと全てのrepeated型カラム行数の合計をとっているが、repeated型カラムが2行以上ある親行の数を取得したい場合は SUM() ではなくWHERE句の方で指定する。

    SELECT count(1) FROM データセット名.テーブル名
    WHERE array_length(repeated型カラム名) >= 2
    



    BigQueryでrepeated型カラムの行数を数える方法 まとめ

    BigQueryでは1行の中で複数個の値を持つことができる REPEATED のカラムが存在する。
    repeated型カラムの行数を数えるには array_length() を使えば繰り返し列であるrepeated型カラムを数えることができる。





    他にもBigQueryでdate型を比較する方法や、select結果を用いてテーブルを作成する方法についての記事もあるので、もし気になったら覗いてみてほしい。

    コチラもオススメ

    KRSW

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

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

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

    -DB
    -,

    Translate »

    Copyright© ITips , 2020 All Rights Reserved.