group byの注意点

SQL

今日、こんな感じのテーブルを作って、毎晩日次売上を月次売上に足しこむバッチを走らせる、という仕事をした。

●店舗別日次売上
create table daily (
	store_id integer not null primary key,	-- 店舗ID
	store_group_id integer not null,	-- 店舗が所属する店舗グループID
	sales bigint not null			-- 今日の売上
);

●店舗別月次売上
create table monthly (
	store_id integer not null primary key,
	store_group_id integer not null,
	sales bigint not null
);

やり方を２つ思いついた。

  • full outer join でヨコに結合して足し算
  • union でタテ積みして集計

まず結合を使ってみる。

(1)ヨコに結合
select 
	coalesce(d.store_id, m.store_id),
	coalesce(d.store_group_id, m.store_group_id),
	coalesce(d.sales, 0) + coalesce(m.sales, 0) as sales
from daily d 
full outer join monthly m
on d.store_id=m.store_id

前提として、月半ばで開店または閉店する店舗がある（つまり「日次にあって月次にない店舗」「月次にあって日次にない店舗」がある）ので、full outer join以外の結合方法では欠落する店舗が発生する。なのでfull outer joinが必須。
outer joinしたので、うっかりnullを返さないよう全ての列にcoalesceを付加する必要がある。


coalesceの処置が面倒くさいので、group byを使ってみる。
こっちの方が見通しがいい。

(2)タテ積みして集計
select 
	store_id, 
	store_group_id,
	sum(sales) as sales
from (
	select * from daily
	union all
	select * from monthly
) dm
group by 
	store_id, 
	store_group_id

ところが。

タテ積みの方は仕様を満たしていない

ここで、店舗が所属するグループが月半ばで変更されることもある、と判明した。
また、月次サマリは最新のグループIDをその店舗の所属グループIDとして集計せよ、という仕様になった。
この時、(2)のクエリは仕様を満たしていない。
例えばstore_id=2の店舗の所属グループが、1から999に変わったとする。
正解はこうなのに

store_id store_group_id sales
1 1 500
2 999 600
3 2 600
4 2 100

結果はstore_id=2のレコードが2行になる。

store_id store_group_id sales
1 1 500
2 1 500
2 999 100
3 2 600
4 2 100

group byするなら、スカラサブクエリを使うしかないのかな。これはまた見通しが悪い...

select 
	store_id, 
	coalesce(
		(select store_group_id
		 from daily where store_id=dm.store_id),
		(select store_group_id
		 from monthly where store_id=dm.store_id)	
	),
	sum(sales) as sales
from (
	select * from daily
	union all
	select * from monthly
) dm
group by 
	store_id

わかったこと

結局何がわかったのだろう。

  • group byに並べる条件には、集計単位になる項目と、ただの見出しの項目がある。
  • ただの見出しの項目は、集計単位になる項目に従属して値が一意に決まる（「店舗IDが決まれば店舗グループIDが決まる」等）。
  • ただの見出しのつもりで並べたのに、実は集計単位に従属していない項目があったら、結果が狂ってしまう。

group byに指定してもしなくても結果表の行数が変わらない項目は、「見出しのつもりで並べた項目」だ。
それが常に結果表の行数を変えないのか＝テストデータが都合のいい感じになってるだけじゃないのか、必ず確認しなくてはならない。