SQL実践入門(ミック 著)のリスト5.8「ウィンドウ関数による解」の以下のsqlが理解できません。
(MIN ~ AS min_codeの部分の処理)
OVER句での処理詳細とMIN()のなかの処理詳細について解説お願いします。
テーブル名:PostalCode(郵便番号)
カラム名:pcode(郵便番号), district_name(地域名)
4130001 静岡県熱海市泉
4130002 静岡県熱海市伊豆山
4130103 静岡県熱海市綱代
4130041 静岡県熱海市青葉町
4103213 静岡県伊豆市青羽根
4380824 静岡県磐田市赤池
SELECT pcode,
district_name
FROM (SELECT pcode,
district_name,
CASE WHEN pcode = '4130033' THEN 0
WHEN pcode LIKE '413003%' THEN 1
WHEN pcode LIKE '41300%' THEN 2
WHEN pcode LIKE '4130%' THEN 3
WHEN pcode LIKE '413%' THEN 4
WHEN pcode LIKE '41%' THEN 5
WHEN pcode LIKE '4%' THEN 6
ELSE NULL END AS hit_code,
MIN(CASE WHEN pcode = '4130033' THEN 0
WHEN pcode LIKE '413003%' THEN 1
WHEN pcode LIKE '41300%' THEN 2
WHEN pcode LIKE '4130%' THEN 3
WHEN pcode LIKE '413%' THEN 4
WHEN pcode LIKE '41%' THEN 5
WHEN pcode LIKE '4%' THEN 6
ELSE NULL END)
OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0
WHEN pcode LIKE '413003%' THEN 1
WHEN pcode LIKE '41300%' THEN 2
WHEN pcode LIKE '4130%' THEN 3
WHEN pcode LIKE '413%' THEN 4
WHEN pcode LIKE '41%' THEN 5
WHEN pcode LIKE '4%' THEN 6
ELSE NULL END) AS min_code
FROM PostalCode) Foo
WHERE hit_code = min_code;