テーブルの改修を行った時の検証用クエリメモです

旧テーブルと新テーブルについて月別で件数差分を出力する時のサンプルコード

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
with oldBase as (
select
substr(createdAt, 1, 7) as ym, -- レコード作成日時情報(項目名は適宜変更)
count(1) as old_count
from
dataset.oldtable  -- 旧テーブルを指定
group by 1
)
, newBase as (
select
substr(createdAt, 1, 7) as ym, -- レコード作成日時情報(項目名は適宜変更)
count(1) as new_count
from
dataset.newtable  -- 新テーブルを指定
group by 1
)
select
coalesce(o.ym, n.ym) as ym, -- oldとnewどちらかの月にしかレコードない場合の考慮
o.old_count,
n.new_count,
o.old_count - n.new_count as diff
from
oldBase as o
full join newBase as n using(ym)
order by 1
with oldBase as ( select substr(createdAt, 1, 7) as ym, -- レコード作成日時情報(項目名は適宜変更) count(1) as old_count from dataset.oldtable  -- 旧テーブルを指定 group by 1 ) , newBase as ( select substr(createdAt, 1, 7) as ym, -- レコード作成日時情報(項目名は適宜変更) count(1) as new_count from dataset.newtable  -- 新テーブルを指定 group by 1 ) select coalesce(o.ym, n.ym) as ym, -- oldとnewどちらかの月にしかレコードない場合の考慮 o.old_count, n.new_count, o.old_count - n.new_count as diff from oldBase as o full join newBase as n using(ym) order by 1
with oldBase as (

  select
    substr(createdAt, 1, 7) as ym,  -- レコード作成日時情報(項目名は適宜変更)
    count(1) as old_count
  from
    dataset.oldtable  -- 旧テーブルを指定
  group by 1

)

, newBase as (

  select
    substr(createdAt, 1, 7) as ym,  -- レコード作成日時情報(項目名は適宜変更)
    count(1) as new_count
  from
    dataset.newtable  -- 新テーブルを指定
  group by 1

)

select
  coalesce(o.ym, n.ym) as ym, -- oldとnewどちらかの月にしかレコードない場合の考慮
  o.old_count,
  n.new_count,
  o.old_count - n.new_count as diff
from
  oldBase as o
  full join newBase as n using(ym)
order by 1

旧テーブルと新テーブルの差分レコードを出力する時のサンプルコード

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
with oldBase as (
select
* except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する
from
dataset.oldtable -- 旧テーブルを指定
)
, newBase as (
select
* except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する
from
dataset.newtable -- 新テーブルを指定
)
, diff as (
select 'old_diff' as status, * from ( select * from oldBase except distinct select * from newBase)
union all
select 'new_diff' as status, * from ( select * from newBase except distinct select * from oldBase)
)
select * from diff order by 1
with oldBase as ( select * except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する from dataset.oldtable -- 旧テーブルを指定 ) , newBase as ( select * except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する from dataset.newtable -- 新テーブルを指定 ) , diff as ( select 'old_diff' as status, * from ( select * from oldBase except distinct select * from newBase) union all select 'new_diff' as status, * from ( select * from newBase except distinct select * from oldBase) ) select * from diff order by 1
with oldBase as (

  select
    * except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する
  from
    dataset.oldtable -- 旧テーブルを指定
)

, newBase as (
  select
    * except(addColumn1) -- except指定することで、diffの対象外項目を指定する。不要なら削除する
  from
    dataset.newtable -- 新テーブルを指定
)

, diff as (
  select 'old_diff' as status, * from ( select * from oldBase except distinct select * from newBase)
  union all
  select 'new_diff' as status, * from ( select * from newBase except distinct select * from oldBase)
)

select * from diff order by 1

この記事が気に入ったら『目黒で働く分析担当の作業メモ』ご支援をお願いします!

※OFUSEに飛びます


おすすめの記事