かなり久しぶりの投稿。
今日は日付関係です。
日付関係のSQLって人によって癖が出たりしますよね。
今回はデータ上もっている日付から今時点（今日）までどのくらい経過したのかを
算出するSQLをご紹介します。
データの準備
以下のようなデータを準備しました。
create table sales (
customerid varchar(3) --顧客ID
,salesdate date); --販売日
insert into sales values
(001,'2020/1/1'),
(002,'2020/2/1'),
(003,'2020/3/1'),
(004,'2020/4/1'),
(005,'2020/5/1'),
(006,'2020/6/1'),
(007,'2020/7/1'),
(008,'2020/8/1'),
(009,'2020/9/1'),
(010,'2020/10/1'),
(011,'2020/11/1'),
(012,'2020/12/1'),
(013,'2021/2/28'),
(014,'2021/3/1'),
(015,'2021/3/2'),
(016,'2021/3/10'),
(017,'2021/3/20'),
(018,'2021/3/31'),
(019,'2021/4/1'),
(020,'2021/4/2'),
(021,'2021/4/3'),
(022,'2021/4/4'),
(023,'2021/4/5'),
(024,'2021/4/10'),
(025,'2021/4/20'),
(026,'2021/4/30'),
(027,'2021/5/1');
今日の日付
起点になる日付は以下の通り。
select current_timestamp
2021-05-01 12:24:58.504566+09つまり、2021年5月1日です。
単純に経過日を算出する
これは特に難しくもなく、今日から販売日を引けば良いだけです。
select customerid
,salesdate
,cast(current_timestamp as date) - salesdate as "elapsed days"
from sales
経過月を算出する
これ意外と難しいと思いませんか？
というのも月は30日の月もあれば、31日の月もある。
また、2月に至っては28日の年もあれば、29日の年もある。
ということで、単純に経過日を算出後に〇〇日で割れば良い
という話でもなさそうです。
※会社によっては単純に90日経過で3ヵ月経過と見做す
といったルールに則っているところもありますけど、
厳密には。ね・・・。
結論から言うと、以下のような方法で経過月を算出できます。
select customerid
,salesdate
,cast(extract(year from age(cast(current_timestamp as date), salesdate)) * 12
+extract(month from age(cast(current_timestamp as date), salesdate)) AS numeric) as "elapsed months"
from sales
current_timestampは2021/5/1です。
結果は以下の通りです。
おそらく他にももっと良い方法があるかもしれませんが、パッと数分で考えた結果はこれでした。
ちょっとややこしい。
extractで部分的にフィールドを取得しています。
yearは年で、monthは月ですね。
ageは以前、リナレス選手のこれまでの一つ一つの試合について何歳の時に行ったかを求めるときに使いました。
つまり、
① ageで何年何ヶ月経過しているのかを算出した後、
② year部分には12ヶ月かけて取得
③ month部分はそのまま取得
④ ②と③を足す
だけです。
上記を分解して見てみると
select customerid
,salesdate
,age(cast(current_timestamp as date), salesdate) as "①"
,extract(year from age(cast(current_timestamp as date), salesdate)) * 12 as "②"
,extract(month from age(cast(current_timestamp as date), salesdate)) as "③"
,cast(extract(year from age(cast(current_timestamp as date), salesdate)) * 12
+extract(month from age(cast(current_timestamp as date), salesdate)) AS numeric) as "④ = elapsed months"
from sales
ageを使えば、月による経過日数もきちんと算出してくれます。
つまり、何ヶ月と何日であったり、何年、何ヶ月、何日経過ということも
簡単に表現可能です。
こちら、とある〇〇を購入されたお客様で最終購入日から何ヶ月経過した方のみ
メルマガ配信などといった条件付けで利用できます。
是非、ご利用ください。
では。