BrainPad SQLForBeginners2019をbigqueryでやってみた

/*
2-1-2. SELECT① 特定フィールドの抽出
receipt_item からid, user_id, receipt_id, priceのデータを2件取得する
*/

select
  id
  ,user_id
  ,receipt_id
  ,price
from `test.dataset.receipt_item`
limit 2
;

/*
2-1-3. SELECT② COUNT, DISTINCT, AS
receipt_itemテーブルのreceipt_idフィールドに対して
「重複を許した場合の件数」
「重複を除いた場合の件数」をそれぞれ取得し、列名を別名に変更する 
*/

select
  count(receipt_id) as receipt
  ,count(distinct receipt_id) as dis_receipt
from `test.dataset.receipt_item`
limit 2
;

/*
2-1-4. SELECT③ ORDER BY, SELECT *
receiptテーブルの全てのフィールドに5件分、データ取得する
ただし、user_idの昇順、read_atの降順に並び替える
*/

select
  *
from `test.dataset.receipt`
order by
  user_id asc, read_at desc
;

/*
2-2-1. WHERE① 比較条件
userテーブルで、誕生日が2006年1月1日以降の人のbirthday, genderの
2つのフィールドについて5件分データ取得し、誕生日の昇順に並び替えて表示する。
*/

select
  birthday
  ,gender
from `test.dataset.user`
where
  birthday >= '2006-01-01'
order by
  birthday asc
limit 5
;

/*
2-2-2. WHERE② 論理条件
userテーブルで、「性別が2(女性)」かつ「state_codeが'13'(東京)または'27'(大阪)」
である人の総数を取得する
*/

select
  count(*)
from `test.dataset.user`
where
  gender = 2
and
  (
    state_code = 13
    or
    state_code = 27
  )
;

/*
2-2-3. WHERE③ パターンマッチングLIKE
receiptテーブルで、shop_nameが文字列「B」で終わるデータについて、
全てのフィールドを5件分取得する
*/

select
  *
from `test.dataset.receipt`
where
  shop_name like '%B'
limit 5
;

/*
2-2-4. WHERE④ 範囲条件BETWEEN
receiptテーブルで、paid_atが2010年1月1日から2010年12月31日までであるデータについて、
id, user_id, paid_atを5件分取得する。ただし、paid_atの昇順に並び替える。
*/

select
  id
  ,user_id
  ,paid_at
from `test.dataset.receipt`
where
  paid_at between '2010-01-01' and '2010-12-31'
order by
  paid_at asc
limit 5
;

/*
2-2-5. WHERE⑤ IN条件
receipt_itemテーブルで、nameが文字列「まぐろ」「りんご」であるデータについて、
id, user_id, receipt_id, nameを10件取得する。
*/

select
  id
  ,user_id
  ,receipt_id
  ,name
from `test.dataset.receipt_item`
where
  name in ('まぐろ', 'りんご')
order by
  id asc
limit 5
;

/*
2-3-1. GROUP BY① 集計関数
receipt_itemテーブルで、receipt_id毎にグループ化したレコードの金額priceの
最小値を求め、5件分表示する。
*/

select
  receipt_id
  ,min(price) as min_price
from `test.dataset.receipt_item`
group by
  receipt_id
order by
  receipt_id asc
limit 5
;

/*
2-3-2. GROUP BY② HAVING 集計結果に条件指定
receipt_itemテーブルで、receipt_id毎にグループ化し、レコードの金額priceに対して最大値を求め、最大値が1000以上のものを5件取得する。
ただし、求めた最大値の昇順に並び替えて表示する。
*/

select
  receipt_id
  ,max(price) as max_price
from `test.dataset.receipt_item`
group by
  receipt_id
having
  max_price >= 1000
order by
  max_price asc
limit 5
;

/*
3-1-1. 条件式① CASE
userテーブルでstate_codeが’27’（大阪）であるユーザーの総数を求める。
（state_codeが’27’であるとき1、他は0のフラグを作成して合計する） 
*/

select
  sum(
    case
      when state_code = 27 then 1
      else 0
    end
  ) as sum_osaka_user
from `test.dataset.user`
;

select
  NULL as `null` -- NULLの入ったカラムnullができる
  ,coalesce(NULL, 0) as co_null  -- NULLだったら0が入る
;

/*
3-1-2. 条件式② COALESCE
NULLをもつフィールド`null`と、NULLを整数0に置換したフィールド`co_null`を1件取得する。（テーブルは不要なので、FROM句も不要）
*/

select
  user_id
  ,create_date
  ,cast(create_date as date) as cast_create_user
from `test.dataset.user`
order by user_id asc
limit 5
;

/*
3-2-2. 型変換 CAST
userテーブルで、create_dateをtimestamp型→date型に変換する。
・抽出フィールド：id, create_date, 型変換後のcreate_date
・idの昇順で5件分表示
*/

select
  user_id
  ,cast(last_login as date) as last_login
  ,substr(cast(last_login as string), 6, 2) as last_login_month
from `test.dataset.user`
order by user_id asc
limit 5
;

/*
3-3-2. 文字列関数② LENGTH, REPLACE
receipt_itemテーブルでnameの文字数を取得する(length_nameとする)。また、nameの ’スナック’ を ’ポテトチップス’ に置換する(replace_nameとする)。
・抽出フィールド：id, name, length_name, replace_name
・idの昇順で5件分表示
*/

select
  id
  ,name
  ,length(name) as length_name
  ,replace(name, 'スナック', 'ポテチ') as replace_name
from `test.dataset.receipt_item`
order by id asc
limit 5
;

/*
3-4-1. 日付関数 CURRENT_DATE, DATEDIFF
userテーブルで、birthdayの日付から現在の日付までの日数を計算する(date_diffとする)。
・抽出フィールド：id, birthday, current_date, date_diff
・idの昇順で5件分表示
*/

select
  user_id
  ,birthday
  ,current_date() as current_date
  ,date_diff(
    current_date(), birthday, day
  ) as date_diff
from `test.dataset.user`
order by user_id asc
limit 5
;

/*
3-4-2.年齢
userテーブルのbirthdayから、最終ログインの日付におけるユーザーの年齢を求める。
・抽出フィールド：id, last_login, birthday, age_at_last_login
・idの昇順で5件分表示
*/

select
  user_id
  ,cast(last_login as date) as last_login
  ,birthday
  ,date_diff(
    cast(last_login as date), birthday, year
  ) as age_at_last_login
from `test.dataset.user`
order by user_id asc
limit 5
;

/*
4-1-2.サブクエリ パターン①
receipt_itemテーブルで、user_id, priceとpriceの中央値を並べて表示する。
ただし、user_id（integer型に変換）の昇順で5件表示とし、中央値はMEDIAN()を使用せよ。
*/

select
  user_id
  ,price
  ,(select stddev_pop(price) from  `test.dataset.receipt_item`) as stddev_price
from `test.dataset.receipt_item`
order by user_id asc, price asc
limit 5
;

/*
4-1-3. サブクエリ パターン②
receipt_itemテーブルとuserテーブルを用いて、name(商品名)が’カップラーメンA‘を
買ったことのある人を性別にカウントする。
*/

select
  gender
  ,count(user_id) as count_user_id
from `test.dataset.user`
where
  user_id in (select distinct
                user_id
              from `test.dataset.receipt_item`
              where
                name = 'カップラーメンA'
             )
group by gender
;

/*
4-1-4. サブクエリ パターン③
receipt_itemテーブルとuserテーブルを用いて、1ユーザーあたりの累計合計金額の平均値を求める。
ヒント
① サブクエリでuser_idでグループ化し、合計金額を求める　
② サブクエリの結果をもとに平均値を求める
*/

select
  avg(sum_price) as avg_sum_price_user
from
  (select
     sum(price) as sum_price
   from `test.dataset.receipt_item`
   group by user_id
  )
;

/*
4-1-5. WITH句　サブクエリの代用
receiptテーブルを用いて、「1ユーザーあたりのレシート登録数」の平均値を求める。
ヒント
① 先ずはWITH句を使って、ユーザーIDごとのレシート登録数を集計する。
（ユーザーID,レシート登録数の２列からなる表を作成する）
② ①で作成した表から、レシート登録数の平均を計算する。
*/

with rc as (
  select
    user_id
    ,count(id) as receipt_count
   from `test.dataset.receipt`
   group by user_id
)
select
  avg(receipt_count) as avg_receipt_count
from
  rc
;

/*
4-2-2. INNER JOIN
receipt_itemテーブル（左表）とuserテーブル（右表）を、userテーブルのidとreceipt_itemテーブルのuser_idを用いて内部結合する。
・receipt_itemテーブルのidをinteger型に変換し、昇順で5件分表示
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
*/

select
  ri.id as receipt_id
  ,u.user_id as user_id
  ,ri.name
  ,u.gender
from `test.dataset.receipt_item` as ri
inner join
  `test.dataset.user` as u
on ri.user_id = u.user_id
order by ri.receipt_id asc
limit 5
;

/*
4-2-3. LEFT JOIN
receipt_itemテーブル(左表)とuserテーブル(右表)を、userテーブルのidとreceipt_itemテーブルのuser_idを用いて外部結合する。※receipt_itemテーブルidの昇順で5件分表示
・抽出フィールド：receipt_item テーブルid, name ／ userテーブル id, gender
・receipt_itemテーブルのid（integer型に変換） >= 135を対象とし、昇順で5件分表示する。
*/

select
  ri.id as receipt_id
  ,u.user_id as user_id
  ,ri.name
  ,u.gender
from `test.dataset.receipt_item` as ri
left join
  `test.dataset.user` as u
on ri.user_id = u.user_id
where
  ri.receipt_id >= 135
order by ri.receipt_id asc
limit 5
;

/*
4-3-1. 一時テーブルの作成
userテーブルからcreate_dateが2013年2月のものを抽出し、一時テーブルuser_201302として保存せよ。
また、確認のため作成した一時テーブルuser_201302からデータを5件取得せよ。
*/

create temp table user_201302
(
  id int64
  ,gender int64
  ,birthday date
  ,state_code int64
  ,last_login timestamp
  ,create_date timestamp
);
-- insert
insert user_201302
with a as (
  select
    *
  from `test.dataset.user`
  where
    create_date between '2013-02-01' and '2013-02-28'
)

select
  *
from a
;

-- check data
select
  *
from user_201302
order by id asc
limit 5
;

/*
5-1-2. ウィンドウ関数 集計関数群
receiptテーブルで、user_idで区切ったパーティション毎にCOUNTで集計を行う。
・user_idが3以下のデータを対象とする。
*/

select
  user_id
  ,count(user_id) over(partition by user_id)  as count_user_id
from `test.dataset.receipt`
where
  user_id <= 3
;

/*
5-1-3. ウィンドウ関数② ランク付け関数群
receipt_itemテーブルで、receipt_id毎にpriceの最大値を求める。
・receipt_idが10以下のデータを対象
*/

select
  receipt_id
  ,price as max_price
  ,name
from 
  (
    select
      *
      -- price高い順のranking取得
      ,row_number() over(partition by receipt_id order by price desc) as rank
    from `test.dataset.receipt_item`
  )
where
  receipt_id <= 10
  and
  -- 1番高いもののみに絞る
  rank = 1
order by receipt_id asc
;

/*
5-1-4. ウィンドウ関数③ ラグ・リード関数群
receipt_itemテーブルで、user_idで区切ったパーティション毎にidの昇順に並び替え、priceについて1行後の値をlead1_price、2行後の値をlead2_priceとして取得する。
・抽出フィールド：user_id, id, price, lead1_price, lead2_price
・user_idが3以下のデータを対象
*/

select
  user_id
  ,id
  ,price
  ,lead(price, 1) over(partition by user_id order by id asc) as lead1_price
  ,lead(price, 2) over(partition by user_id order by id asc) as lead2_price
from `test.dataset.receipt_item`
where
  user_id <= 3
order by user_id asc
;

/*
5-2-1. EXISTS
userテーブルのレコードで、idがreceiptテーブルのuser_idとして存在していないものをカウントする。
*/

select
  count(*) as count
from `test.dataset.user` as u
where not exists (
  select
    1
  from `test.dataset.receipt` as r
  where
    r.user_id = u.user_id 
)
;

/*
5-3-2. 横持ち→縦持ち
receiptテーブルのpaid_at, read_at, update_atを、
それぞれのフィールド名をxxx_at、値をdateとした縦持ちに変換する。
*/

-- create tempが面倒なのでwithにした
with tidy as (
  select
    id
    ,'paid_at' as xxx_at
    ,paid_at as date
  from `test.dataset.receipt`
  
  union all
  
  select
    id
    ,'read_at' as xxx_at
    ,read_at as date
  from `test.dataset.receipt`
  
  union all
  
  select
    id
     ,'update_at' as xxx_at
     ,update_at as date
  from `test.dataset.receipt`
)
select
  *
from tidy
order by id, date
limit 10
;

/*
5-3-3.縦持ち→横持ち
user, receiptテーブルを用いて、
都道府県(state_code)ごとの3〜5月の購入件数を表示する。
*/

with ms as (
  select
    substr(cast(r.paid_at as string), 6, 2) as paid_at_month
    ,u.state_code
  from `test.dataset.receipt` as r
  inner join
    `test.dataset.user` as u
  on r.user_id = u.user_id
)
select
   state_code
   ,count(
      case
        when paid_at_month = '03' then paid_at_month
      end
    ) as march
    ,count(
      case
        when paid_at_month = '04' then paid_at_month
      end
    ) as april
    ,count(
      case
        when paid_at_month = '05' then paid_at_month
      end
    ) as may
from ms
group by state_code
order by state_code
limit 10
;