勉強日記

チラ裏

閃乱カグラのデータで勉強するSQL 1 -- Case式

SQL 勉強メモ 閃乱カグラ

senrankagura.marv.jp

りんごとかバナナとかで練習しても一つも面白くないので、
閃乱カグラのキャラクターのスリーサイズ等、現実的なデータで練習をしていく

SQL置き場

マスタデータ

name team grade birthday age blood_type height boob waist hip cup
雪泉 月閃 3 0/12/31 17 A 167 92 56 84 G
叢 月閃 3 0/10/8 17 B 172 96 58 85 H
夜桜 月閃 2 0/4/5 16 O 159 90 53 82 H
四季 月閃 1 0/3/25 15 AB 161 95 54 83 I
美野里 月閃 1 0/2/14 15 AB 144 86 50 75 G
飛鳥 半蔵 2 0/9/8 16 A 155 90 57 85 F
斑鳩 半蔵 3 0/7/7 18 A 168 93 59 90 G
葛城 半蔵 3 0/11/5 17 B 165 95 57 90 H
柳生 半蔵 1 0/12/23 15 O 158 85 60 83 E
雲雀 半蔵 1 0/2/18 15 B 160 80 55 73 E
雅緋 蛇女 3 0/8/15 20 B 169 90 56 87 G
紫 蛇女 2 0/10/26 16 A 162 105 59 88 K
忌夢 蛇女 3 0/6/9 21 AB 157 88 60 82 E
両備 蛇女 1 0/1/19 15 O 160 69 56 90 AAA
両備(転身後) 蛇女 1 0/1/19 15 O 160 95 56 90 H
両奈 蛇女 1 0/1/19 15 O 160 98 56 88 I
焔 紅蓮 2 0/1/3 16 B 163 87 57 85 E
詠 紅蓮 2 0/2/10 16 B 160 95 58 90 H
日影 紅蓮 3 0/9/9 17 O 160 85 57 85 E
未来 紅蓮 1 0/12/28 15 A 150 62 48 59 A
春花 紅蓮 3 0/7/20 18 AB 169 99 55 88 I
大道寺先輩 半蔵 0/11/11 26 B 170 100 58 98 H
凛 蛇女 0/9/30 27 A 160 97 57 90 H
神楽 DLC 173 95 55 87 I
奈楽 DLC 0/7/15 16 O 151 92 56 82 G
両姫 千年祭 0/7/26 17 171 94 55 89 H
蓮華 千年祭 0/7/20 18 A 168 93 58 82 G
華毘 千年祭 0/10/10 17 O 163 96 57 84 H
華風流 千年祭 0/5/5 16 B 152 73 52 60 C
  • あちこち駆けずり回って用意した
  • NULLがいい感じにあってなかなかよい題材だと思う
    • しかもE.F.コッド先生の2つの分類が両方ともある
      • UNKNOWN ... 神楽のbirthday
        • いつかはわからないが生まれた日は存在するはず、単にわからないだけ
      • Not Applicable ... 両姫のblood_type
        • 他界済のキャラなので血液型がない

既存のコード体系を新しい体系に変換する

-- 勢力=>善忍/悪忍/抜忍
SELECT `name`,
       CASE WHEN `team` = '月閃' THEN '善忍'
            WHEN `team` = '半蔵' THEN '善忍'
            WHEN `team` = '蛇女' THEN '悪忍'
            WHEN `team` = '紅蓮' THEN '抜忍'
            ELSE NULL
            END AS shinobi_category
 FROM kagura
WHERE grade IS NOT NULL;  -- 学年が定義されているキャラのみ
name shinobi_category
雪泉 善忍
叢 善忍
夜桜 善忍
四季 善忍
美野里 善忍
飛鳥 善忍
斑鳩 善忍
葛城 善忍
柳生 善忍
雲雀 善忍
雅緋 悪忍
紫 悪忍
忌夢 悪忍
両備 悪忍
両備(転身後) 悪忍
両奈 悪忍
焔 抜忍
詠 抜忍
日影 抜忍
未来 抜忍
春花 抜忍

異なる条件の集計を1つのクエリで行う

-- 勢力ごとに、カップ別人数を集計する
SELECT `team`,
       -- 行持ちのカップ情報を列持ちに展開
       SUM( CASE WHEN `cup` = 'AAA' THEN 1 ELSE 0 END ) as AAA,
       SUM( CASE WHEN `cup` = 'A'   THEN 1 ELSE 0 END ) as A  ,
       SUM( CASE WHEN `cup` = 'B'   THEN 1 ELSE 0 END ) as B  ,
       SUM( CASE WHEN `cup` = 'C'   THEN 1 ELSE 0 END ) as C  ,
       SUM( CASE WHEN `cup` = 'E'   THEN 1 ELSE 0 END ) as E  ,
       SUM( CASE WHEN `cup` = 'F'   THEN 1 ELSE 0 END ) as F  , 
       SUM( CASE WHEN `cup` = 'G'   THEN 1 ELSE 0 END ) as G  , 
       SUM( CASE WHEN `cup` = 'H'   THEN 1 ELSE 0 END ) as H  , 
       SUM( CASE WHEN `cup` = 'I'   THEN 1 ELSE 0 END ) as I  , 
       SUM( CASE WHEN `cup` = 'J'   THEN 1 ELSE 0 END ) as J  ,
       SUM( CASE WHEN `cup` = 'K'   THEN 1 ELSE 0 END ) as K  
 FROM kagura
 GROUP BY `team`;
team AAA A B C D E F G H I J K
DLC 0 0 0 0 0 0 0 1 0 1 0 0
千年祭 0 0 0 1 0 0 0 1 2 0 0 0
半蔵 0 0 0 0 0 2 1 1 2 0 0 0
月閃 0 0 0 0 0 0 0 2 2 1 0 0
紅蓮 0 1 0 0 0 2 0 0 1 1 0 0
蛇女 1 0 0 0 0 1 0 1 2 1 0 1

※SUM集約関数がないと行が集約されない

CASE式の中でサブクエリを使う

-- チームごとにバスト最大の子に印をつける
SELECT `name`,
       `team`,      
       `boob`,
       -- 自己相関サブクエリで最大かどうかを判定する
       -- たぶんウィンドウ関数のほうがきれい
       CASE WHEN NOT EXISTS
                    ( SELECT *
                        FROM kagura AS K2
                       WHERE K1.team = K2.team
                         AND K1.boob < K2.boob) THEN '〇'
            ELSE '×'
            END AS 'チーム別バスト最大'
 FROM kagura AS K1
 ORDER BY `team`;
name team boob チーム別バスト最大
神楽 DLC 95 〇
奈楽 DLC 92 ×
両姫 千年祭 94 ×
蓮華 千年祭 93 ×
華毘 千年祭 96 〇
華風流 千年祭 73 ×
飛鳥 半蔵 90 ×
斑鳩 半蔵 93 ×
葛城 半蔵 95 ×
柳生 半蔵 85 ×
雲雀 半蔵 80 ×
大道寺先輩 半蔵 100 〇
雪泉 月閃 92 ×
叢 月閃 96 〇
夜桜 月閃 90 ×
四季 月閃 95 ×
美野里 月閃 86 ×
焔 紅蓮 87 ×
詠 紅蓮 95 ×
日影 紅蓮 85 ×
未来 紅蓮 62 ×
春花 紅蓮 99 〇
雅緋 蛇女 90 ×
紫 蛇女 105 〇
忌夢 蛇女 88 ×
両備 蛇女 69 ×
両備(転身後) 蛇女 95 ×
両奈 蛇女 98 ×
凛 蛇女 97 ×

CASE式の中で集約関数を使う

-- カップごとに人数集計を行い、
-- - 1人なら、その名前を表示
-- - 2人以上なら、「多数」と表示
SELECT `cup`,
       COUNT(`name`) AS 人数,
       CASE WHEN COUNT(`name`) = 1 THEN `name`
            ELSE '多数'
            END AS だれ
 FROM kagura
 GROUP BY `cup`;
cup 人数 だれ
A 1 未来
AAA 1 両備
C 1 華風流
E 5 多数
F 1 飛鳥
G 6 多数
H 9 多数
I 4 多数
K 1 紫

Hカップが一番多い
「爆乳ハイパーバトル」を銘打つだけあって、爆乳側に分布が偏っている

参考

www.shoeisha.co.jp