退会者を抽出する方法（MySQL）

Question

前提条件

メンバーテーブル member m

id	mail_addres	name
1	[e-mail]	testA
2	[e-mail]	testB
3	[e-mail]	testC

契約商品テーブル member_item mi

id	member_id	start_date	end_date
1	1	2020/01/01	2020/03/30
2	2	2020/01/01	2020/04/30
3	2	2020/05/01	null
4	3	2020/05/01	2020/06/30
5	3	2020/07/01	2020/08/31

以下のクエリを実行すると、

SELECT m.id,
       m.name,
       mi.start_date,
       mi.end_date
FROM member m
INNER JOIN member_item mi ON m.id = mi.member_id

クエリ実行後

id	name	start_date	end_date
1	testA	2020/01/01	2020/03/30
2	testB	2020/01/01	2020/04/30
2	testB	2020/05/01	null
3	testC	2020/05/01	2020/06/30
3	testC	2020/07/01	2020/08/31

上記の表で抽出されます。

今回したいこと→退会者のみ抽出したい

退会者を判断する材料としては、
定額制の商品には適用開始期間（start_date）と適用終了期間（end_date）があり、
end_dateに値が入っていると退会者とします。
表で言うとtestAは退会者に該当します。

しかし、定額制の商品は別の商品で再契約ができてしまいます。
testBのように別商品で5月(start_date)と再度契約することができます。
この場合、testBは再契約しているので、退会者には該当しません。

※中には再契約後に退会する人もいます。（該当者testC）

試したこと

SELECT m.id,
       m.name,
       mi.start_date,
       mi.end_date
FROM member m
INNER JOIN member_item mi ON m.id = mi.member_id
WHERE mi.end_date IS NOT NULL  //追加

ひとまずend_dateが無い場合は表に出さないようにした。
すると、以下のように抽出されます。

id	name	start_date	end_date
1	testA	2020/01/01	2020/03/30
2	testB	2020/01/01	2020/04/30
3	testC	2020/05/01	2020/06/30
3	testC	2020/07/01	2020/08/31

今回知りたいこと

・この場合における、testBを抽出条件に出さない方法
・testCは最新の退会日付が入っているレコードのみを抽出したい

何か方法面などご教示頂けますと幸いです。

出したいクエリ

id	name	start_date	end_date
1	testA	2020/01/01	2020/03/30
3	testC	2020/07/01	2020/08/31

Comments

[Orlofsky]

質問のテーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに https://teratail.com/help/question-tips#questionTips3-7 の [コード] で見易さを考えて修正できた方が適切なコメントが付き易いです。

[saider_san]

Orlofsky様　コメントありがとうございます！

アドバイスありがとうございます。
以後、そのように質問形式を変更して質問しようと思います。

[m.ts10806]

退会者はどのようにデータ管理しているのでしょう。

[m.ts10806]

あぁ、終わってた。
「以後」ではなく「今」してください。質問は編集できますし、「次」とすると大抵忘れます。

Answer 1

こうして、こう！MySQL苦手。
ただ何となくtestBみたいなデータの判定がまずそうに思うなあ…。

select m.id,m.name,mi.start_date,mi.end_date
from member m
  inner join member_item mi on mi.member_id=m.id
    and mi.id = (
      select id
      from member_item x
      where x.member_id=m.id
      order by x.end_date is null desc,x.end_date desc
      limit 1
    )
where mi.end_date is not null

Comments

[saider_san]

sousuke様
コメントありがとうございます！
こちらのパターンで取りたいデータが取得ができました。

こういった書き方もあるのだなと、とても勉強になりました。
ありがとうございます！

Answer 2

最新を取得すれば、end_dateの判断だけですね。

SELECT m.id, m.name, mi.start_date, mi.end_date
from member_item mi inner join member m
     om mi.menber_id = m.id
where not exists(
       select 1 from member_item 
       where menber_id=mi.menber_id and start_date > mi.start_date
     )
  and end_date is not null

どっちが低コストか分かりませんが、別パターン

SELECT m.id, m.name, mi.start_date, mi.end_date
from member_item mi inner join member m
     om mi.menber_id = m.id
where (mi.menber_id, mi.start_date) in (
       select menber_id, max(start_Date)
       from member_item 
       group by menber_id
     )
  and end_date is not null

Comments

[saider_san]

sazi様

コメントありがとうございます！
not existsを使えば完結に取れることがわかりました。
とても勉強になりました。

ありがとうございます！

[sazi]

もう一つパターン追加しておきました。

[saider_san]

ありがとうございます！こちらも参考にさせていただきます！