GROUP BYしたレコードにて優先順位が高い項目を取り出すSQLの作成方法

Question

前提・実現したいこと

重複した値を持つ複数のレコードを一つにまとめて見やすく成型するSQLを考えています。
GROUP BYしたレコードにて判定分を書いているのですが、
複数項目があてはまる場合は優先順位が高いほうを項目として取り出したいと考えています。

例）
★「生徒名簿」上のレコード
生徒ID｜学年｜年度｜クラブID｜優先順位｜…
0001｜ 1｜2019｜ 1｜ 1｜…
0001｜ 1｜2019｜ 2｜ 2｜…
0001｜ 2｜2020｜ 2｜ 1｜…

★「クラブ名簿」上のレコード
クラブID｜クラブ名｜…
1｜ 水泳｜…
2｜ 手芸｜…

★「クラブ履歴」
生徒ID｜2019_所属クラブ｜2020_所属クラブ｜…
0001｜ 水泳｜ 手芸｜…

上記の例では、生徒名簿とクラブ名簿というテーブルを、クラブ履歴というテーブルにまとめてみれるようにしたいのです。

試したこと

自力で途中まで書いたSQL↓

select
生徒名簿.生徒ID as 生徒ID
MAX(CASE 生徒名簿.年度 WHEN '2019' THEN クラブ名簿.クラブ名 ELSE NULL END) AS 2019_所属クラブ,
MAX(CASE 生徒名簿.年度 WHEN '2020' THEN クラブ名簿.クラブ名 ELSE NULL END) AS 2020_所属クラブ

FROM 生徒名簿
INNER JOIN クラブ名簿
ON 生徒名簿.クラブID = クラブ名簿.クラブID

GROUP BY 生徒名簿.生徒ID

ここまでは検索して、自力で書くことができましたが、優先順位による判定を、どこに入れ込めばよいかがわからず、困っています。

初歩的な質問で非常に恐縮なのですが、周りに頼れる人がいないため、こちらにて相談させていただきました。
お知恵を貸していただけますと幸いです。

Comments

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

[Orlofsky]

テーブル定義とデータは CREATE TABLE文とINSERT文にできると動作確認し易いので、適切なコメントが付き易いです。 SELECT文も含めて、https://teratail.com/help/question-tips#questionTips3-7 の [コード] を使ってください。

[piyopiyo_pi]

拙い質問に目を通してくださりありがとうございます。
Spark SQLを使用しております。バージョンは後程追記いたします。

Answer 1

こんな感じだと思います。

select  生徒ID
      , Max(CASE 年度 WHEN '2019' THEN クラブ名 END) AS 2019_所属クラブ
      , Max(CASE 年度 WHEN '2020' THEN クラブ名 END) AS 2020_所属クラブ
FROM  生徒名簿 t1 INNER JOIN クラブ名簿 t2
      ON t1.クラブID = t2.クラブID
where 優先度 = (
        select Min(優先度) from 生徒名簿 where 生徒ID=t1.生徒ID and 年度=t1.年度 
      )
group by 生徒ID

尚、正規化はされた方が良いかと思います。
今のデータから正規化されてテーブルを作るのは例えば以下のようにします。

create table 所属クラブ as 
select 生徒ID, 年度, クラブID, 優先順位
from  生徒名簿

create table 生徒名簿_改 as
select 生徒ID, 学年, 年度
from  生徒名簿
group by 生徒ID, 学年, 年度 

Comments

[piyopiyo_pi]

投稿してすぐにご回答いただけて驚きました。
そしてとてもわかりやすい回答をありがとうございました。
正規化についてもご説明くださりありがとうございます。お恥ずかしい話ですが、全く考慮していなかったので、とても勉強になりました。
感謝の言葉しかございません。早速確認してみます。本当にありがとうございました。