こうしてSQLに苦しめられて成長する

f:id:hacomono-tech:20210303222358j:plain

初めまして、hacomono の塩濱です。まちいろには開発未経験からジョインしており、ようやくフラフラの首が座ってき始めました。

まちいろでSQL周りで関わりのあるツールとしては、 MySQLWorkbenchというツールを活用してスキーマの定義・管理を行っています。

f:id:hacomono-tech:20210303221648p:plain

ActiveRecordのwhere等では難しい複雑なクエリを使用してDBのデータを取ってくる場合には、直接SQLを書く必要が出てくることもあるかと思いますが、そんな時にMySQLWorkbench上で、想定のデータが取れるかSQLの試し書きのようなことにも使用してます。

また、Re:dashというダッシュボードツールも利用しており、お客様環境などでXXXXのデータ一覧が欲しいと依頼があった場合に、クエリを作成してデータの抽出ということを行っています。

f:id:hacomono-tech:20210303221710p:plain

今回は苦労したRe:dash上で作成したクエリについて書いていきます。

あ、YYYY-MM形式をdateに変換し、月を加算し、フォーマットを統一すれば良いのか

なんのこっちゃという話なので順を追って書いていきます。

＜今回のゴール＞

プランの適用終了年月で検索をかけてプラン退会者一覧を出したい　

以下のようなテーブルがあるとします。

・メンバーテーブル
CREATE TABLE member (id int, name varchar(255));

・プラン契約メンバーテーブル
CREATE TABLE member_plan
(id int, member_id int, plan_id int,start_date DATE, end_date DATE);

まず、プラン契約者のみを出してみる。

SELECT 
　m.id AS メンバーID, m.name AS 名前, mp.plan_id AS プランID, 
　mp.start_date AS プラン適用開始日, mp.end_date AS プラン適用終了日 
FROM member m 
INNER JOIN member_plan mp ON m.id = mp.member_id

f:id:hacomono-tech:20210303221840p:plain

退会者の条件とは

上の表の場合だと田中太郎が退会者の位置付けになります。
佐藤花子はプラン変更者であり、退会者には含まれません。

つまり、適用終了日に1ヶ月加算したプラン適用開始日がないメンバーを出せば良いとなります。

Re:dash内で退会年月で検索して一覧を出したい

WHEREで検索かけられるようにしてあげる

WHERE DATE_FORMAT(mp.end_date, '%Y-%m') = '{{ プラン適用終了年月(YYYY-MM) }}'

こんな感じのフォームがRe:dash内にできます。

f:id:hacomono-tech:20210303221948p:plain

これで退会年月で該当メンバーを検索することが可能となります。

順に条件を作成していく

つまり適用終了日に1ヶ月加算したプラン適用開始日がないメンバーを出せば良いとなります。

これは、冒頭で記載していたあ、YYYY-MM形式をdateに変換し、月を加算し、フォーマットを統一すれば良いのかにやっと繋がります。

外からはYYYY-MM形式で文字列がくるのでdateに変換する

STR_TO_DATE　＜STR_TO_DATE( 文字列 , フォーマット)＞

文字列を日付形式に変換してくれます。

YYYY-MM形式で文字がくるので-01で保管してあげます。
STR_TO_DATE('{{ プラン適用完了年月 (YYYY-MM) }}-01', '%Y-%m-%d')

1ヶ月加算する

DATE_ADD　＜DATE_ADD(対象の日付, INTERVAL 1 MONTH)＞

加算に使用します。

先ほどの日付に変換したものに1ヶ月加算する処理を加えます。
DATE_ADD(STR_TO_DATE('{{ プラン適用完了年月 (YYYY-MM) }}-01', '%Y-%m-%d'), INTERVAL 1 MONTH)

フォーマットを統一してあげる

DATE_FORMAT　＜DATE_FORMAT(対象に日付, '指定フォーマット')＞

日付を指定したフォーマットへ変換してくれます。

全部合わせてしまいます。
DATE_FORMAT(DATE_ADD(STR_TO_DATE('{{ プラン適用完了年月 (YYYY-MM) }}-01', '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y%m%d')

最終的に

サブクエリとかゴニョゴニョしましてこんな感じになります。

SELECT 
　m.id AS メンバーID, m.name AS 名前, mp.plan_id AS プランID, 
　mp.start_date AS プラン適用開始日, mp.end_date AS プラン適用終了日 
FROM member m 
INNER JOIN member_plan mp ON m.id = mp.member_id
WHERE DATE_FORMAT(mp.end_date, '%Y-%m') = '{{ プラン適用終了年月(YYYY-MM) }}'
AND NOT exists (SELECT * FROM member_plan mp2 WHERE mp2.ember_id = m.id)
AND mp2.start_date = 
DATE_FORMAT(DATE_ADD(STR_TO_DATE('{{ プラン適用完了年月 (YYYY-MM) }}-01', '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y%m%d')