サブクエリを使ってサンプルテーブル的なものを簡単に用意する【BigQuery Tips】
想定する場面
何かクエリを試したいけど手頃なテーブルがBigQuery上にない時がたまによくあるかと思います。
必要なデータが少ない場合は、以下の方法でサブクエリにデータを書ききり、テーブル代わりとして扱えるものを用意した方が早いことがあります。
今回のゴール
以下のテーブル代わりのサブクエリを用意します。
| x | y | z |
|---|---|---|
| 1 | 2 | "a" |
| 3 | 4 | "i" |
| 5 | 6 | "u" |
| 7 | 8 | "e" |
| 9 | 10 | "o" |
補足
なお、今回紹介するクエリはテーブルの読み込みが発生しないので、実行してもお金がかかりません。
やり方
ARRAY-STRUCT型 を宣言し、データを書き込み、UNNESTしたものをサブクエリとしています。
以下のクエリでは、temp_tableというサブクエリですが、こちらは今回のゴールであげたテーブルとして扱うことができます。
参考：https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#declaring-an-array-type
WITH temp_table AS (
SELECT
*
FROM
#UNNESTします。
UNNEST(
(
SELECT
#以下のようにARRAY型の中にSTRUCT型を宣言します。
#STRUCTの宣言の中で、列名とその型も宣言します。
ARRAY<STRUCT<x INT64,y INT64,z STRING>>[
##############################################
###ここにサンプルデータを、以下の形式で書き込みます。###
##############################################
(1,2,"a"),
(3,4,"i"),
(5,6,"u"),
(7,8,"e"),
(9,10,"o")
] AS sample_data_arr
)
)
)
SELECT
*,
x * y AS xy,
CONCAT("k",z) AS kz
FROM
temp_table #サブクエリとして定義したtemp_tableですが、テーブル代わりとして扱えています。
BigQueryでマルチカーソルを使う
作業イメージ
やり方
今回は、Google Analyticsのサンプルデータセットを使用します。なぜか新UIでデータセットが表示されないので、キャプチャは旧UIのものを使用します。
totals内のいくつかのカラムのSUMをとります。
なお、Google Analytics サンプルデータセットへのアクセスについては、以下を参照してください。
support.google.com
- まとめて集計したいカラムを書き出します。schemeタブでカラム名をクリックすると、エディタ部分にカラム名が書き出されるので、カラム名をひらすらクリックします。この時、対象のカラムが連続するように書き出しておきます。
- ここで一度Format Queryをします。
- 対象のカラムが全て書き出されたら、対象のカラムの先頭にマウスカーソルを移動します。
- alt を押します。すると、マウスカーソルが十字に変化します。この時マウス左ボタンを押したまま、マウスを真下に動かします。対象のカラムの最後まで移動したタイミングでマウス左ボタンを離します。これでマルチカーソル化が出来ました。
- あとは集計関数を書き、カラム名を元の列名にします。
完成したクエリはこちら
SELECT SUM(totals.visits) AS visits, SUM(totals.hits) AS hits, SUM(totals.pageviews) AS pageviews, SUM(totals.timeOnSite) AS timeOnSite, SUM(totals.bounces) AS bounces, SUM(totals.transactions) AS transactions,[f:id:sem-aa-bq:20181009222900g:plain] SUM(totals.transactionRevenue) AS transactionRevenue, SUM(totals.newVisits) AS newVisits, SUM(totals.screenviews) AS screenviews, SUM(totals.uniqueScreenviews) AS uniqueScreenviews, SUM(totals.timeOnScreen) AS timeOnScreen, SUM(totals.totalTransactionRevenue) AS totalTransactionRevenue, SUM(totals.sessionQualityDim) AS sessionQualityDim FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
以上で、操作は完了です。
