Google BigQuery基本の「き」について。（はじめに～）

BigQuery基本設定

BigQuery基本文法

BigQuery集計関数

BigQuery四則演算、条件分岐

BigQuery分析関数

BigQueryテーブル結合

BigQueryサブクエリ

BigQuery集合演算、ビュー

BigQuery練習問題

BigQueryおまけ（可視化）

Cf. The Relational Model Content based on Chapter 3 - SlideToDocSQLは、関係データベース管理システム において、データの操作や定義を行うためのデータベース言語、ドメイン固有言語である。プログラミングにおいてデータベースへのアクセスのために、他のプログラミング言語と併用される。

exists : 意味・対訳存在する、現存する、(特殊な条件または場所に)ある、現われる、生存する、生きている、(…で)(やっと)生きていく

No exerpt.

分析関数の結果をフィルタリング

分析関数の拡張

差集合を取得できる。(select * from TABLE1)

積集合を取得できる。(select * from TABLE1)

fromTABLEpivot(

配列型|名前|説明||:-:|:-:|

decleare TERM_START string default '2020-01-01';decleare TERM_END string default '2020-12-31';decleare PREP_TS string;

Google BigQuery基本の「き」について。（はじめに～）

select * from dataset.__TABLE__;TABLESを利用すると、bq show にも匹敵する情報を全テーブル一括で取得することが出来ます。取れる情報は「テーブル作成日」「テーブル最終更新日」「テーブル行数」「テーブルバイト数」です。

BigQuery INFORMATION_SCHEMA ビューは、BigQuery オブジェクトに関するメタデータ情報を提供するシステム定義の読み取り専用ビューです。『 BigQuery INFORMATION_SCHEMA の概要』より

ユーザー定義関数

標準パラメータを使用できる

$ bq query --parameter='param::A' 'select @param'Waiting on bqjob_xxxx ... (0s) Current status: DONE

EDA時や、ざっくり特徴量のカウント等に厳密な結果を求めていない時count(distinct HOGE)　を用いないで、近侍集計関数を用いると幸せ。（メモリの節約にもなるし）

selectID

Excelや、Spreadsheetではお馴染み、countif()。BigQueryでも使えるよ。select

selectextract(year from rsv_date from) as YEAR, extract(month from rsv_date) as MONTH

, SHOP_END_TIME as (select*

null値を代替して置き換える。null回避。NOTE

No exerpt.

LEAD ( scalar_expression [ ,offset(1) ] , [ default(null) ] )OVER ( [ partition_by_clause ] order_by_clause )現在行の後ろの行を持ってくる。

No exerpt.

大文字を小文字に変換

nullifは、故意にnull値を用いることができる。0除算時のエラー回避して、nullを返す。nullif()の中身で行われていることは、以下のcase式と同様。

文字型（STRING）の日付データを日付型（DATE）にしたい時に使える。selectparse_date('%Y %m %d', '20180101') -- 2018-01-01に変換される

レコードの連番を振りなおしたい時にも使える。

型変換なのだが、通常の'cast()'変換とは異なる。データ内に、string や Null を許容して一括変換してくれる。

selectsplit("hello world everyone", ' ')[offset(0)] -- 0番目の要素　hello, split("hello world everyone", ' ')[offset(1)] -- 1番目の要素　world

緯度・経度を用いて分析したい時に使える関数

percentile_cont|discwithin group(order by expression [(asc)|desc])over(partition by partition_list)

No exerpt.

No exerpt.

Query exceeded resource limits. This query used 96791 CPU seconds but would charge only 170M Analysis bytes.This exceeds the ratio supported by the on-demand pricing model.Please consider moving this workload to the flat-rate reservation pricing model, which does not have this limit.

Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complexよく出るこいつ。WITH句を多用していたり、ビューの多段が10個近くになってくるとこのエラーがよく発生することが多いです*2。

No exerpt.

date : 2022-01-01title :BigQueryの裏技法メモexcerpt :

export data options (uri='gs://エクスポート先のバケット名/csv/sql_*.csv.gz', format='CSV'

(チートシートのDL - Google)

create or replace model {my_project.my_dataset}.{モデル名}options(model_type = 'AUTOML_CLASSIFIER' --( ※ )使用アルゴリズム

create or repalce model taxi.TOTAL_AMOUNT_MODELoptions (model_type='linear_reg' -- 線形回帰

GoogleCLoud内部に、BigQueryMLのチュートリアルが用意されている。（なんて親切！）クイックスタート＞BigQuery ML で機械学習モデルを作成するcreate model bqml_tutorial.sample_model

BigQueryのユーザー定義関数について

No exerpt.

一般社団法人データサイエンティスト協会（所在地：東京都港区、代表理事：草野 隆史、以下データサイエンティスト協会）は、構造化データの加工について実践的に学ぶことができる無料の学習環境「データサイエンス100本ノック（構造化データ加工編）」をGitHubに公開しました。「データサイエンス100本ノック（構造化データ加工編）」は、データサイエンス初学者を対象に、データの加工・集計、統計学や機械学習を駆使したモデリングの前処理等を学べるよう、データと実行環境構築スクリプト、演習問題をワンセットにしています。

select*from(

レシート明細テーブル(receipt)から全項目を10件抽出し、どのようなデータを保有して いるか目視で確認せよ。select * from 100knocks.receipt limit 10;レシート明細のテーブル(receipt)から売上日(sales_ymd)、顧客ID (customer_id)、商品コード(product_cd)、売上金額(amount)の順に列を指定し、 10件表示させよ。

顧客テーブル(customer)から顧客ID(customer_id)の末尾が1のものだけ全項目抽出 し、10件だけ表示せよ。select*

レシート明細テーブル(receipt)に対し、件数をカウントせよ。select　count(*)　from　100knocks.receipt;レシート明細テーブル(receipt)の顧客ID(customer_id)に対し、ユニーク件数をカウントせよ。

レシート明細テーブル(receipt)に対し、店舗コード(store_cd)ごとに売上金額 (amount)の標本標準偏差を計算し、降順でTOP5を表示せよ。selectstore_cd

レシート明細テーブル(receipt)の売上金額(amount)を日付(sales_ymd)ごとに集計し、前日からの売上金額増減を計算せよ。なお、計算結果は10件表示すればよい。with ttl_amount as (select

レシート明細テーブル(receipt)の売上エポック秒を日付型に変換し、「日」だけ取り出 してレシート番号(receipt_no)、レシートサブ番号(receipt_sub_no)とともに抽出せ よ。なお、「日」は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。selectformat_date('%d', cast(format_timestamp('%Y-%m-%d', timestamp_seconds(sales_epoch), 'Asia/Tokyo') as date)) as sales_epoch

レシート明細テーブル(receipt)の売上金額(amount)を顧客ID(customer_id)ごとに 合計し、売上金額合計を最小値0、最大値1に正規化して顧客ID、売上金額合計とともに表 示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算する こと。結果は10件表示させれば良い。withuser_amount as (

レシート明細テーブル(receipt)の売上日(sales_ymd)に対し、顧客テーブル (customer)の会員申込日(application_date)からの経過月数を計算し、顧客ID (customer_id)、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い (なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意)。1ヶ月未満は切り捨てること。with tb as (select

単価(unit_price)と原価(unit_cost)の欠損値について、それぞれの平均値で補完した新たなproduct_2を作成せよ。なお、平均値については1円未満を丸めること(四捨五入または偶数への丸めで良い)。補完実施後、各項目について欠損が生じていないことも確認すること。withproduct_2 as (

顧客テーブル(customer)の各顧客に対し、売上実績がある顧客数と売上実績がない顧客 数が1:1となるようにアンダーサンプリングで抽出せよ。顧客テーブル(customer)では、性別に関する情報が非正規化の状態で保持されている。 これを第三正規化せよ。

No exerpt.

||ユニバーサルアナリティクス|GA4||:-|:-|:-||データの計測方法 |計測単位：ページ計測方法：セッション|計測単位：イベント計測方法：ユーザー|

とんでもなく勉強になるクエリを記述する最強の人を真似て写経兼してみた。（参考記事は「REFERENCE」へ）GA4（Firebase）のデータ構造は、初見〇し過ぎる。。都度、データをこねくり回して抽出もいいが、、、手間過ぎる。そんな時に出会った最強の人。感謝ですm(_ _)m