sh16ma/aggligation_outline_at_bq.md

## aggligation_outline_at_bq.md

      
    Raw
  

              aggligation_outline_at_bq.md
            
          
    BigQuery 集計・分析の為の記述構成

SQLを用いての集計・分析を行うと、クエリだけで軽く数百行を超えることがしばしばある。

おおまかに、どのエリアにどのテーブル（整形したサブクエリ）が存在しているのかを予め決めておくことで、

容易にクエリを記述・読み返すことができる。


※ 大前提： `WITH句`を用いて、複数のテーブル（整形したデータ）を記述して、最終的にまとめていく書き方を想定している。

|| BigQuery - Outline

（※あくまでアウトラインを理解する上で便宜上のクエリ文。)
#standardSQL
/* (memo)
 * e.g. 分析依頼内容や、依頼者の要望クエリを要約しておく。
 */
 
 
/* UDF */
--チームで共有するUDFはココに書いておく。
 
 
/* PERIOD */ 
--チャンク選定（e.g.期間...etc.)
declear FROM TO_ datetime;
set STARAT_ = ;
set END_ = ;


with
/* IMPORT */

      HOGE_RAW as (select from 'prj.ds.hoge_*' where _table_suffix between STARAT_ and END_)
    , FUGA_RAW as (select from 'prj.ds.fuga_*' where _table_suffix <= END_)
    , …


/* PREPROCESS */

    , HOGE_PREP as (select * from HOGE_RAW)
    , FUGA_PREP as (select * from FUGA_RAW where fuga_key in (1, 2, 3, 4))
    , …


/* AGGREGATION */

    , HOGE_CALC as (
        select 
            *
            , count(distinct hoge) as CNT_HOGE 
        from 
            HOGE_PREP 
        group by 1
    )
    , HOGE_FUGA_MARGE as (
        select * 
        from 
            HOGE_CALC 
            left join FUGA_PREP using(ID)
    )
    , …


/* REPORT */

  , REPORT as (select * from HOGE_FUGA_MARGE)


/* EDA */

  , EDA as (select '-- eda --'
       # 探索的データ分析
  )


select * REPORT
;
| Outline - Explain

前段のアウトラインの「/**/」の説明


エリア名
エリア用途
説明


PERIOD
集計期間指定エリア
declareや、set等のBigQuery scrptingを用いて、定義すると汎用的


IMPORT
使用テーブル
使用するテーブルをまとめておく


PREPROCESS
前処理（準備）
分析に使用する特徴量（カラム）を指定したりテーブルを準備する


AGGREGATION
前処理（分析）
準備したテーブル通しを結合したり、集計分析関数（ウィンドウ関数）等を用いて実際に分析する


REPORT
提出用整形
分析を終えたデータを、クライアントの要望や意図に合うように整形する


EDA
探索的データ分析
上述のサブクエリ等を組み上げるうえでのロジック確認したり、アドホックの補填用に記述


| CommentOut - Explain

コメントアウトの振る舞い（使い訳完全主観）


コメント記述
用途
用途（行数別）
備考


 /* コメント */
アウトライン記述時
複数行
-


 --コメント
行の説明時
単一行
-


 # コメント
行の説明時
単一行（or複数行）
Python記述で慣れている方がチームに居るならコッチの方が優しいかも


※ クエリ文を読んで、自明に対してはコメントしない。(何でもかんでもコメントは入れなくて◎)
※ コメントする場合は、 『意図』 を記述する。（どうしてこうしたのか？）

| Column - Explain

特徴量（カラム）の記述振る舞い。（分析者の為の）

DQLの SELECT句 でデータ抽出する際は、極力「 ＊ （=wild card）」を避ける。
#standardSQL
/* NG */
    --会員データ
    MEMBER as (
        select * from `pj.ds.table`
    )
#standardSQL
/* OK */
    --会員データ
    MEMBER as (
        select
            member_id
            , gender
            , age
            , pref
            , created_at
        from 
            `pj.ds.table`
    )

※ 手間ではあるが、使用カラムを一つ一つ記述すること。
※ 効果：　この書き方をしていると、後々に皆幸せになれるため。（騙されたと思って記述せよ）
エリア名	エリア用途	説明
PERIOD	集計期間指定エリア	`declare`や、`set`等のBigQuery scrptingを用いて、定義すると汎用的
IMPORT	使用テーブル	使用するテーブルをまとめておく
PREPROCESS	前処理（準備）	分析に使用する特徴量（カラム）を指定したりテーブルを準備する
AGGREGATION	前処理（分析）	準備したテーブル通しを結合したり、集計分析関数（ウィンドウ関数）等を用いて実際に分析する
REPORT	提出用整形	分析を終えたデータを、クライアントの要望や意図に合うように整形する
EDA	探索的データ分析	上述のサブクエリ等を組み上げるうえでのロジック確認したり、アドホックの補填用に記述
コメント記述	用途	用途（行数別）	備考
`/* コメント */`	アウトライン記述時	複数行	-
`--コメント`	行の説明時	単一行	-
`# コメント`	行の説明時	単一行（or複数行）	Python記述で慣れている方がチームに居るならコッチの方が優しいかも