２組のデータから成績をつけるSQL

２組のデータから成績をつける処理をSQLで作るにはどうするかを考える。


たとえば、以下のような仕組みをソフトで実現したいとする。

• PとQの2種類の持ち点がある。（PもQも1点から６点、１点が成績良、６点が悪い）
• ２つの組み合わせでAからDまでの成績がつく。
• 成績のルールは下表の通り。縦軸がP、横軸がQです。

たとえば、Pが２点でQが４点なら成績はCとなる。なかなか厳しい。PとQは成績がよいほど点数は小さい。（ちょっと直観的でないかも）

このソフトをC#で作るとなると、おそらく、上の表を２次元配列にして、成績を得るメソッドが配列を引いて結果を返すような作りにすると思う。

ではSQLで作るとしたらどうするか。

CASE文で書くのが一つの方法。

select [P], [Q],
    case
    when P in (5,6) and q >= 1 then 'D'
    when p = 4 and q >= 3 then 'D'
    when p = 4 and q >= 1 then 'C'
    when p = 3 and q >= 4 then 'D'
    when p = 3 and q >= 3 then 'C'
    when p = 3 and q >= 1 then 'B'
    when p in (1,2) and q >= 4 then 'C'
    when p in (1,2) and q >= 3 then 'B'
    when p in (1,2) and q >= 1 then 'A'
    end
from [dbo].[aTestCkd重症度分類]
order by P, Q

正しい結果は出るだろうけど、まあ、げんなりですね。

＊

SQLならデータの組合せと成績のマスタテーブルを作るというのが、王道なような気がするので、マスタテーブルを作ってみます。

これをleft joinすれば成績表をゲットできる。

select d.[P], d.[Q],
    m.[成績]
from [dbo].[aTestCkd重症度分類] as [d]
left outer join [dbo].[aTestCkd成績結果マスタ] as [m] on d.P = m.P and d.Q = m.Q
order by P, Q

とはいえ、成績のマスタテーブルを作るのは、やはりげんなりですね。

＊

最後に少し冴えた（？）方法を。

いちいちCASE式やマスタテーブルを作るのは面倒なので、計算式でぱっと成績が出せればうれしい訳です。成績のルール表がもう少し単純なら嬉しいので、ちょっとルール表を変更してみます。

Pの１と２、５と６の行は同じなので、これをまとめます。また、Qの１と２、４から６の列は同じなのでこれもまとめます。

まとめたルール表がこちら。

おお、これなら計算式で成績を計算できそう。

まとめたPの行番号（１から４）とまとめたQの列番号（１から３）を足した値を使うと、成績をゲットできます。たとえば、Pの2行目とQの2列目なら２＋２＝４で成績はCという具合に。

With文を使って中間テーブルを作って、PとQのデータを変換して（たとえばPの5点を４点に変えるなど）、それをもとに成績を計算するSQLを書いてみる。

with mmData as
(
 select
  P, Q,
  case
   when P in (1,2) then 1
   when P in (3) then 2
   when P in (4) then 3
   when P in (5,6) then 4
  end [PP],
  case
   when Q in (1,2) then 1
   when Q in (3) then 2
   when Q in (4,5,6) then 3
  end [QQ]
 from
  [dbo].[aTestCkd重症度分類]
)
 
select
 *,
 case
  when [PP] + [QQ] = 2 then 'A'
  when [PP] + [QQ] = 3 then 'B'
  when [PP] + [QQ] = 4 then 'C'
  when [PP] + [QQ] >= 5 then 'D'
 end
from mmData
order by P,Q

ちょっと長いSQLになってしまった。それに、この方法はルール表が変わると対応できなくなる可能性が高いので、あまりお薦めはできないような。