こんにちは。開発ブログ運営担当のktです。

久々の更新になります。

SQLを書くときに条件によってSELECTする内容を変更するのにCASE式を利用したことがあると思いますが、今回の記事はそのCASE式と集計関数のMINを組み合わせた複数レコードに関するチェックの方法を紹介します。

前提

例えばあるアイドルグループのファンサイトで、ユーザーは応援したいグループのメンバーを設定することができ、その中でも最も推しているメンバーを1人設定できる機能があったとします。

それは以下のような[推しメン]テーブルでデータを保持しています。

ユーザーID	メンバーID	最推し
00000001	001
00000001	002	1
00000001	003
00000002	002
00000002	004	1
00000003	001	1
00000003	003
00000003	005
00000003	006

やりたいこと

アイドルメンバーが卒業や脱退したとき、その人を最推しメン(※1)に設定していた人は最推しメンがいない状態になります。そういったユーザーを抽出して、最推しメンが設定されていないよと警告したいです。最推しメンを設定していない人だけを取得するにはどうしたらいいでしょう。

卒業すると[卒業]テーブルにメンバーが登録されます。今回はメンバーID:001が卒業したとします。

この場合、ユーザーID:00000003の人は最推しメンがいない状態になります。

(※1)最推しメン・・・最も推しているメンバーのこと。私が便宜的に付けたもので、一般的に使われている言葉ではありません。神推しや激推しという言い方もあるらしいです。

解決方法

下記SQLで最推しメンを設定していないユーザーIDが取得できます。

with base as ( 
  select
    推しメン.ユーザーID
    , 推しメン.メンバーID
    , 推しメン.最推し 
  from
    推しメン 
    left join 卒業 
      on 推しメン.メンバーID = 卒業.メンバーID 
  where
    卒業.メンバーID is null
) 
, chk_result as ( 
  select
    base.ユーザーID
    , min(case when base.最推し = '1' then 0 else 1 end) 
　　　　　over (partition by base.ユーザーID) as chk 
  from
    base
) 
select distinct
  chk_result.ユーザーID 
from
  chk_result 
where
  chk_result.chk = 1

まずは[推しメン]テーブルと[卒業]テーブルをleft joinで外部結合し、卒業したメンバーを推しに設定しているレコードを省きます。

次にmin関数とcase式を組み合わせて、同じユーザーIDのレコードの中で最推し=1になっていないものは1とするようにしています。

最後にchk_result.chk=1になっているレコードのユーザーIDだけを、distinctで重複を省いて取得しています。

まとめ

SQLで抽出は終わっているので、アプリケーションの処理としては取得した対象のユーザーに警告のメールを送ったり、画面にメッセージを表示するだけです。

もちろんこのようなCASE式を利用しなくても、データを取得した側の処理で対象ユーザーの絞り込みをすることは可能です。

何が何でもSQL一発でデータを取得するべきとは思いません。パフォーマンスやSQLの可読性を考慮して、そのケースではどれがいいのか選択することが大事だと思います。

また今回の記事に記載したアイドルグループは例を示すために用いただけであり、ある特定のグループを揶揄することを目的にしたものではありません。

参考サイト

今回の例は「同じユーザーIDの全ての行で条件を満たさない」を条件としているので、論理学の「全称否定」というものみたいです。

https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html#1-7