データサイエンス100本ノック（構造化データ加工編）解説_S011-S020

## はじめに
第三回です。前回はこちら。

https://qiita.com/sato_ryo/items/1155cb423cd077bd2b4b
### 凡例
- [XXX]：　XXXは省略可能
- (XXX)：　XXXの論理的な名前
- 本来は、`%%sql`を答えの1行目に記載する必要があります。
## 問題
### 11問目：後方一致
#### S-011: 顧客データ（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件表示せよ。
<details><summary>S-011答え</summary>

```sql
SELECT
  *
FROM
  store s
WHERE 
   customer_id LIKE '%1'
LIMIT 10
;
```
</details>

後方一致ですから、「%+ヒットさせたい文字列」とすればよいです。
2文字以上の文字列としたい場合は、`%_1` の形で`_`と組み合わせます。

### 12問目 部分一致
#### S-012: 店舗データ（store）から、住所 (address) に"横浜市"が含まれるものだけ全項目表示せよ。

<details><summary>S-012答え</summary>

```sql
SELECT
  *
FROM
  store s
WHERE 
   address LIKE '%横浜市%'
;
```
</details>

部分一致なので、`% + 検索文字列 + %` とすればよいでしょう。


### 13問目 正規表現を用いた検索
#### S-013: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件表示せよ。

<details><summary>S-013答え</summary>

```sql
SELECT
  *
FROM
  customer c
WHERE 
   status_cd REGEXP '^[A-F].*'
LIMIT 10;
;
```
</details>


構文
```sql
 WHERE '文字列' REGEXP '正規表現パターン'
```
正規表現を用いて、複数の特定の文字列と一致させたい場合は、`[一致対象文字たち]`　とするのでした。
また、正規表現で開始を表すのは、`^`です。
加えて、 `REGEXP` は一部でも一致すれば1を返すため、下記でもOKです。

<details><summary>開始部分だけの正規表現</summary>

```sql
status_cd REGEXP '^[A-F]' 
```
</details>

今回の問題では、`[A-F]` を `[ABCDEF]`と記載しても正解できます。
また、↓のように、[]を使わずとも表現することはできます。

<details><summary>[]を使わない</summary>

```sql
SELECT
  *
FROM
  customer c
WHERE 
   status_cd LIKE '%A%'
   OR status_cd LIKE 'B%'
   OR status_cd LIKE 'C%'
   OR status_cd LIKE 'D%'
   OR status_cd LIKE 'E%'
   OR status_cd LIKE 'F%'
LIMIT 10;
;
```
</details>


### 14問目：特定の複数文字の検索
#### S-014: 顧客データ（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

<details><summary>S-014答え</summary>

```sql
SELECT
  *
FROM
  customer c
WHERE 
   status_cd REGEXP '.*[1-9]$'
LIMIT 10;
;
```
</details>

13問目の末尾版です。
末尾の正規表現は `$` です。

### 15問目：複雑な検索①
#### S-015: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

<details><summary>S-015答え</summary>

```sql
SELECT
  *
FROM
  customer s
WHERE 
   status_cd REGEXP '^[A-F].*[1-9]$'
LIMIT 10;
;
```
</details>

13,14問目の複合です！

### 16問目：複雑な検索
### S-016: 店舗データ（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

任意の1文字は`_` ですから、3文字は`___`とすればよいですね。

<details><summary>S-016答え(数値であるとは限らない)</summary>

```sql
SELECT
  *
FROM
  store s
WHERE 
   tel_no LIKE '___-___-____'
;
```
</details>

<details><summary>正規表現</summary>

```sql
SELECT
  *
FROM
  store s
WHERE 
   tel_no REGEXP '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
;
```
</details>

[一致させたい文字たち] でその文字1字にマッチするのでした。3桁数値-3桁数値-4桁数値のパターン文字列はサクッと作れるといいですね。
DBMSによっては [0-9]{3}のように{回数}で前のパターンが何回続くかを指定できるものもあるようです。（正規表現のように使えます。）

### 17問目：ソート
#### S-017: 顧客データ（customer）を生年月日（birth_day）で高齢順にソートし、先頭から全項目を10件表示せよ。
<details><summary>S-017答え</summary>

```sql
SELECT
  *
FROM
  customer s
ORDER BY birth_day ASC
LIMIT 10
;
```
</details>

<b>ORDER BY</b>
出力結果の順番を指定したい場合は `ORDER BY` を使います。
構文
```sql
 SELECT (カラム名) FROM (テーブル名) ORDER BY (ソートする基準のカラム) [ASC/DESC]
```
`ASC`：昇順（小⇨大) 、`DESC`：降順(大⇨小)　です。
また、ソートするカラム名をSELECT句のカラムの位置で指定できます。
例：
```sql
SELECT
  name
  , birth_day
  , gender
FROM
  customer s
ORDER BY 1 ASC
LIMIT 10
;
```
は、1番目のカラム(name)順にソートされます。
加えて、ソートするカラムは複数指定可能です。
```sql
SELECT
  name
  , birth_day
  , id
  , gender
FROM
  customer s
ORDER BY 1 ASC, 2 DESC
LIMIT 10
;
```
とすると、名前の昇順、誕生日の降順にソートされます。

ちなみに、高齢順というのは、誕生日の小さい順なので注意しましょう。
### 18問目：降順
#### S-018: 顧客データ（customer）を生年月日（birth_day）で若い順にソートし、先頭から全項目を10件表示せよ。

<details><summary>S-018答え</summary>

```sql
SELECT
  *
FROM
  customer s
ORDER BY
  birth_day DESC
LIMIT 10
;
```
</details>

S-017問目の逆順にソートします。
ORDER BY は SELECT句の後に実行されます。
SELECT句の結果が抽出されてから、ソートを実行するため、実行時間を要するケースが多いです。
また、`ASC/DESC` は省略すると、ASCとして実行されます。

### 19問目：ランク
#### S-019: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

<details><summary>S-019答え</summary>

```sql
SELECT
  customer_id
  , amount
  ,rank() OVER (ORDER BY amount DESC) AS rank
FROM
  receipt r
LIMIT 10
;
```
</details>

<b>RANK</b>
ランクを付与する際には、RANK関数を使います。

<b>構文</b>

```sql
SELECT RANK() OVER (ORDER BY (ランク付与の基準となる列) [ASC/DESC])
```
RANK()は同じ順位には同じ番号を振ります。
似たような関数にDENSE_RANK() がありますが、こちらは欠番がないように降ります。
例
| amount | 10 | 20 | 20 | 40 | 50 |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 関数名  | 数値  | 数値  | 数値  | 数値  | 数値  |
| RANK  | 1  | 2  | 2  | 4  | 5  |
| DENSE_RANK  | 1  | 2  |2  |3  |4  |

### 20問目：ランク②
#### S-020: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。



<details><summary>S-019答え</summary>

```sql
SELECT
  customer_id
  , amount
  ,ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM
  receipt s
LIMIT 10
;
```
</details>

<b>ROW_NUMBER</b>
ランクを付与し、同一順位にも別の値を採番したい場合には、ROW_NUMBER関数を使います。
構文

```sql
SELECT ROW_NUMBER() OVER (ORDER BY (ランク付与の基準となる列) [ASC/DESC])
```
ROW_NUMBERは同一の順位の場合には、ランダムに別の番号を振ります。
例
| amount | 10 | 20 | 20 | 40 | 50 |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 関数名  | 数値  | 数値  | 数値  | 数値  | 数値  |
| ROW_NUMBER  | 1  | 2  | 3  | 4  | 5  |


## 参考文献・クレジット

https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess?tab=readme-ov-file

本記事では、以下のライセンスのもと提供されている資料を参考にしています。

### MIT License
© 2020 The Japan DataScientist Society  
[MIT Licenseの詳細](https://opensource.org/licenses/MIT)

### Creative Commons Attribution-NoDerivatives 4.0 International（CC BY-ND 4.0）
[ライセンスの詳細](https://creativecommons.org/licenses/by-nd/4.0/deed.ja)

※ 本記事では、元の内容を改変せずに引用・解説を行っています。

### その他参考文献

https://qiita.com/DeepTama/items/85470a66972432e14224

https://techis.jp/guide/sql/sql_wildcards