Life is Really Short, Have Your Life!!

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

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

Sql

SQLそれ自体は一番最後にあります。

detail.chiebukuro.yahoo.co.jp

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

締日集計のめんどいところは、締日がN件あること。5日、10日、15日、20日、25日、月末の６パターンある場合、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

追記(2018.03.23 14:40)

バグが有ることが判明しました。

20日締で5/25付の顧客は6月付にならなければなりません。5/21〜6/20 が期間なので。でも、5/25 で 締日が20日の場合、単純に日付から締日を引き算してしまうと、5/5になり、5月付になってしまいます。5/25は6月付やん。アカンわ。

「日付の日にち」>「締日」だったら日付の年月を1ヶ月加算、「日付の日にち」<=「締日」はその日付の年月を請求月とする。これが正しいようです。末締の場合は何も加算する必要が無いので注意が必要です。

締日5日で検証
締日 日付 請求月
5日(2017/10/06〜2017/11/5) 2017/10/25 11月締
5日(2017/10/06〜2017/11/5) 2017/10/4 10月締
締日10日で検証
締日 日付 請求月
10日(2017/10/11〜2017/11/10) 2017/10/9 10月締
10日(2017/10/11〜2017/11/10) 2017/10/31 11月締
締日15日で検証
締日 日付 請求月
15日(2017/10/16〜2017/11/15) 2017/10/9 10月締
15日(2017/10/16〜2017/11/15) 2017/10/31 11月締
締日20日で検証
締日 日付 請求月
20日(2017/10/21〜2017/11/20) 2017/10/9 10月締
20日(2017/10/21〜2017/11/20) 2017/10/24 11月締
締日25日で検証
締日 日付 請求月
25日(2017/10/26〜2017/11/25) 2017/11/10 11月締
25日(2017/10/26〜2017/11/20) 2017/11/28 12月締

良さそうですね。締日よりも先の日付(締日は含まない)だったら、1ヶ月先が請求月。正しい気がします。

というわけで、締日集計のSQLの改訂版です。

SELECT
	CASE 
	  WHEN duedate = 31 THEN DATE_FORMAT(billdate,'%Y年%m月')
	  WHEN duedate < 31 AND DAY(billdate) <= duedate THEN DATE_FORMAT(billdate,'%Y年%m月')
	  WHEN duedate < 31 AND DAY(billdate) > duedate THEN DATE_FORMAT(DATE_ADD(billdate, INTERVAL 1 MONTH),'%Y年%m月')
	END AS monthly,
	duedate,
	sum(subtotal)
FROM
	earnings
	JOIN customers ON customers.id = earnings.customer_id
WHERE
	duedate > 0
GROUP BY
	monthly,duedate
ORDER BY
	monthly,duedate

念のため、締日での請求月の算出動作確認用SQLも貼り付けておきます。

SELECT
	billdate, -- 伝票日付
	duedate, -- 顧客の締日(1〜31)
	CASE 
	  WHEN duedate = 31 THEN DATE_FORMAT(billdate,'%Y年%m月') -- 末締は伝票月＝請求月
	  WHEN duedate < 31 AND DAY(billdate) <= duedate THEN DATE_FORMAT(billdate,'%Y年%m月') -- 末締以外で、伝票日 <= 締日なら、伝票月＝請求月
	  WHEN duedate < 31 AND DAY(billdate) > duedate THEN DATE_FORMAT(DATE_ADD(billdate, INTERVAL 1 MONTH),'%Y年%m月')  -- 末締以外で、伝票日 > 締日なら、伝票月＝請求月の翌月
	END AS monthly
FROM
	earnings
	JOIN customers ON customers.id = earnings.customer_id
WHERE
	duedate > 0
GROUP BY
	billdate,duedate

SQLを学習できるWebサービスを作りました。

www.start-sql.net