批評空間SQL実行フォーラムで読むSQLクックブック(レシピ2.1~2.3、2.5~2.6)

# 参考文献
1. https://www.oreilly.co.jp/books/9784873119779/
2. https://qiita.com/revvve44/items/9846de91ecabd5813361
3. 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```句による並び替えの部分である。

```{sql}
select *
  from brandlist
 order by id;
```

```id```が大きな順で並び替える場合、```ORDER BY```句の最後に```DESC```を入れることで実行できる点も重要であると思う。

```{sql}
select *
  from brandlist
 order by id desc;
```

## レシピ2.2 複数のフィールドでソートする
→レシピ2.2e テーブル```brandlist```から```kind```が小さい順(昇順)、```median```が大きい順(降順)に並び替えたレコードを取得する。結果の見やすさのために、取得する列は```id```、```brandname```、```kind```、```median```に絞って取得すること。

ポイントは、以下の２点であるように思う。

- ```ORDER BY```句による複数列を参照したソートが可能であること
- 複数の列を指定したときの```DESC```の付け方

まず、```kind```と```median```について昇順に並べたレコードを取得してみる。この場合、以下のようなクエリを使えば取得できる。

```{sql}
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```をつけて以下のようにすればよい。

```{sql}
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```にも同様につけた場合のクエリと、その出力結果についてもここに記載する。

```{sql}
select id, brandname, kind, median
  from brandlist
 order by kind desc, median desc;
```

結果を見るに、```median```は同じように```NULL```を引っかけてきているのに対して```kind```には値```CORPORATION```が入っているため```kind```は```NULL```の値をとらないような列であるということがわかる。```kind```はブランドが同人サークルなら```CIRCLE```、企業なら```CORPORATION```が入っているはずの列なのだが、どのブランドに対しても一応値が入っている列のようである。<del>なんでＴＶ朝日が出てきたんだろう。</del>

```{}
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文字のみを利用している。

```{sql}
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```を参照していることがわかる。

```{sql}
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```にすることで、降順に並び替えたとき最後に来るように調整する

```{sql}
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```句を用いることである。以下のようなクエリを書けば、望みの結果が得られる。

```{sql}
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について