勉強日記

チラ裏

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

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