参考文献
- https://www.oreilly.co.jp/books/9784873119779/
- https://qiita.com/revvve44/items/9846de91ecabd5813361
- https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/sql_for_erogamer_form.php
前回の記事
-
https://qiita.com/yaminabeworks/items/a502ca90ededae438960
- レコードの取得について、いくつか課題は残したがほとんどのレシピに基づいてクエリを書くことができた。
- 今回は、2章の「クエリ結果のソート」のレシピに基づいて批評空間実行フォーラムでクエリを実行する。
以下では、参考文献1.にあるレシピa.bから考えた実行内容のレシピa.beを実行するためのクエリと、各クエリのポイントについて簡単にまとめる。整理した範囲は、2章クエリ結果のソートのレシピ2.1から2.3、2.5から2.6までとなっている。
以下では、もしレシピa.be内で取り出す列名を具体的に書いていなかった場合*により全部の列を要求することとする。
レシピ2.1 クエリ結果を指定の順序で返す
→レシピ2.1e テーブルbrandlistからidが小さい順に並び替えたレコードを取得する
ポイントは、ORDER BY句による並び替えの部分である。
select *
from brandlist
order by id;
idが大きな順で並び替える場合、ORDER BY句の最後にDESCを入れることで実行できる点も重要であると思う。
select *
from brandlist
order by id desc;
レシピ2.2 複数のフィールドでソートする
→レシピ2.2e テーブルbrandlistからkindが小さい順(昇順)、medianが大きい順(降順)に並び替えたレコードを取得する。結果の見やすさのために、取得する列はid、brandname、kind、medianに絞って取得すること。
ポイントは、以下の２点であるように思う。
-
ORDER BY句による複数列を参照したソートが可能であること - 複数の列を指定したときの
DESCの付け方
まず、kindとmedianについて昇順に並べたレコードを取得してみる。この場合、以下のようなクエリを使えば取得できる。
select id, brandname, kind, median
from brandlist
order by kind, median;
先頭3行の結果は以下のようになっている。並び替えの際に、kind、つまりORDER BY句で先に記述した列のほうが優先される項目であることがわかる。
id brandname kind median
1737 みるくどろっぷ CIRCLE 3
6838 犬穂月 CIRCLE 5
5447 DARK CASTLE CIRCLE 5
これを修正して、レシピ2.2eを満たすようなクエリをつくる。そのためには、ポイントにも記載した通りDESCをつけて以下のようにすればよい。
select id, brandname, kind, median
from brandlist
order by kind, median desc;
先頭3行は以下のようになっているのだが、見事にmedianがNULLのものを引っかけてきてしまっている。これに対する解決策は、この記事のレシピの部分で言及する。
id brandname kind median
7055 ぽんずカフェ CIRCLE
5265 UNDEAD WORLD CIRCLE
3890 吐鬼滅奇狂団 CIRCLE
おまけに、DESCをkindにも同様につけた場合のクエリと、その出力結果についてもここに記載する。
select id, brandname, kind, median
from brandlist
order by kind desc, median desc;
結果を見るに、medianは同じようにNULLを引っかけてきているのに対してkindには値CORPORATIONが入っているためkindはNULLの値をとらないような列であるということがわかる。kindはブランドが同人サークルならCIRCLE、企業ならCORPORATIONが入っているはずの列なのだが、どのブランドに対しても一応値が入っている列のようである。なんでＴＶ朝日が出てきたんだろう。
id brandname kind median
791 ＴＶ朝日 CORPORATION
5640 COSMIC SOFT CORPORATION
2480 HOPEMOON CORPORATION
レシピ2.3 部分文字列でソートする
→レシピ2.3e テーブルbrandlistから、列brandnameの先頭1文字のみとmedian列で昇順に並べてソートした結果を取得する。結果の見やすさのために、取得する列はid、brandname、kind、medianに絞って取得すること。
ポイントは、SUBSTR関数の利用にある。
以下のクエリでは、ORDER BY句の中でSUBSTR関数の2つめの引数を1とすることで最初の1文字のみを利用している。
select id, brandname, kind, median
from brandlist
order by substr(brandname, 1, 1), median;
以下は、実行結果となっている。
id brandname kind median
1190 #define CIRCLE 75
4712 (有)ありす CIRCLE 50
1393 (無)パワフルテクニック。 CIRCLE 56
おまけに、上のクエリをmedianの順番のみ降順となるように変更し、クエリと実行結果を記載する。結果から、brandnameの最初の1文字のみを参照して昇順に並び替えた後にmedianを参照していることがわかる。
select id, brandname, kind, median
from brandlist
order by substr(brandname, 1), median desc;
id brandname kind median
1190 #define CIRCLE 75
1559 (猫)milkcat CIRCLE 70
1393 (無)パワフルテクニック。 CIRCLE 56
レシピ2.4 英数字の混合データをソートする
(工事中)
REPLACE関数とTRANSLATE関数を使うのがポイントとなっているが、あまり理解が及んでいないので後回しにする。
レシピ2.5 ソート時にNULLを扱う
→レシピ2.5e テーブルbrandlistからkindが小さい順(昇順)、medianが大きい順(降順)に並び替えたレコードを取得する。この際に、medianがNULLのものは除いてソートして取得する。結果の見やすさのために、取得する列はid、brandname、kind、medianに絞って取得すること。
レシピ2.2eで発生した、medianについて降順にソートするとNULLのレコードを先頭に表示してしまう問題をここで解決する。今回は、そのために2.2eで用いたクエリを修正する。ポイントは、以下の通りである。
-
CASE句でmedianのNULLを具体的な数値に変更する - 具体的な数値を
0にすることで、降順に並び替えたとき最後に来るように調整する
select id, brandname, kind, median_null
from (
select id, brandname, kind, median,
case when median is null then 0
else median
end as median_null
from brandlist
) x
order by kind, median_null desc;
レシピ2.6 データ依存のキーに対してソートする
→レシピ2.6e テーブルbrandlistから、kindの値がCIRCLEの場合idについて昇順、kindの値がCORPORATIONの場合medianについて昇順に並び替えたレコードを取得する。結果の見やすさのために、取得する列はid、brandname、kind、medianに絞って取得すること。
ポイントは、ORDER BY句の中でCASE句を用いることである。以下のようなクエリを書けば、望みの結果が得られる。
select id, brandname, kind, median
from brandlist
order by case when kind = 'CIRCLE' then id else median end;
感想
- とりあえず見切り発車的に2章のレシピについても書いてしまった。
- レシピ2.4については重要そうではないものの、ぱっと見でわからないなと思ったので改めてやっておきたい。
-
SUBSTR関数の引数について、一回では覚え切れていないので馴染むまでクエリを作っていきたい。 - 次に書く記事は、おそらくSQLではなくて
numpyやpandasで何か作る系のになりそう。
次にやること、積み残し
次にやること
- 参考文献1.の3章
積み残し
- レシピ1.6に関する詳細な説明
- レシピ1.7について
- レシピ2.4について