Life is Really Short, Have Your Life!!

ござ先輩の主に技術的なメモ

売上を顧客の締め日で集計するSQLを簡単に書く方法

detail.chiebukuro.yahoo.co.jp

天才的なソリューションだった。驚いた。日付から締め日を引き算すれば、何月締めであるかを判定できるとは。シカクいアタマをマルくする必要性を痛感した。

締め日集計のめんどいところは、締め日がN件あること。5日、10日、15日、20日、25日、月末の6パターンある場合、2017年12月の締め日別の合計は以下の期間を元に算出する必要がある。

2017年12月の締日別集計期間

締め日 集計期間
5日 2017/11/06〜2017/12/05
10日 2017/11/11〜2017/12/10
15日 2017/11/16〜2017/12/15
20日 2017/11/21〜2017/12/20
25日 2017/11/26〜2017/12/25
月末 2017/12/01〜2017/12/31

当たり前だが、月末の末日は変化する。28、29、30、31の4通りある。締日別で売上を見たいというのは、上記の集計期間別に売上総額を見て、入金予定の算段を立てる目安として使われる事が多い。

この締日別の集計期間をどう作るべきか悩んでいた。最低最悪のソリューションだと締日別にSQLを投げてUNIONするのはありえへんし、CASE-WHENで締日を見てクロス集計するのも無駄に複雑でだるい。そこに彗星の如く現れたのが「日付から締め日を引き算」するというSQLだ。実際にやってみた。

伝票日付から締日を引くSQL(サンプル)

select
	customers.duedate,
	earnings.billdate,
	(earnings.billdate - interval customers.duedate DAY) as monthly
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
where
	year(earnings.billdate) = 2017
group by
	earnings.billdate,customers.duedate

MySQLの場合、日付型のデータにおいてはINTERVALを与えると引き算(足し算)してくれる。実際に引いてみたら、たしかにこれで集計できることがわかった。あとは簡単。DATE_FORMATで年月を割り出し、そいつでGroupByするだけだ。

締日別売上集計SQL

select
	duedate,
	DATE_FORMAT((billdate - interval duedate DAY),'%Y年%m月') as monthly,
	sum(subtotal)
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
group by
	monthly,duedate
order by
	monthly,duedate

手元のMacで8万行の売上データが507msで集計できた。

追記(2017.12.26 17:30)

duedate(締日)に31が登録されている場合(うちのシステムは、末締めの場合31という数値を入力して表現することになっている)月末が31日じゃないものについてはバグになってしまう。11/30から31を引いたら、10/30になってしまい11月伝票が10月伝票になってしまうことに気がついたので、このSQLはクソ。駄目です。

というか、末締めの場合は締日の引き算が不要。その日付の年月が締め対象になる。というわけで、そこをCASE-WHENで場合分けすればOKだった。以下のSQLなら大丈夫なはず。

select
	duedate,
	case 
	  when duedate = 31 then DATE_FORMAT(billdate,'%Y年%m月')
	  else DATE_FORMAT((billdate - interval duedate DAY),'%Y年%m月') 
	end as monthly,
	sum(subtotal)
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
group by
	monthly,duedate
order by
	monthly,duedate