参考文献

前回の記事

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について