|id|name|age|floor|status|

SQLでデータを分析するにあたり基礎的なウォーミングアップ。

様々な結合操作の一覧と参考集

BigQueryでSQLを書いていて繰り返し処理できたらいいのにと思っているのです。

CUI操作による、PostgreSQLのインストールから、データベースへアクセスまで。

SQLでデータを分析するにあたり基礎的なウォーミングアップ。

select*from(

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

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

顧客テーブル(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 (

BigQuery集合演算、ビュー

BigQuery練習問題

BigQueryおまけ（可視化）

BigQuery基本設定

BigQuery基本文法

BigQuery集計関数

BigQuery四則演算、条件分岐

BigQuery分析関数

BigQueryテーブル結合

BigQueryサブクエリ

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

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

selectID

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

差集合を取得できる。cf. 分析入門 - SECTION10 - GitPress

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

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

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

selecttable_name, column_name

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に変換される

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

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

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

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

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

No exerpt.

ユーザー定義関数

UNNEST を使うと、ARRAYなどの配列や、REPEATEDなカラムを、開くことができる。selectfullVisitorId as UU

分析関数の拡張