【SQL編】データサイエンス100本ノック解説【1~20】

# 始めに
こちらは筆者がデータサイエンス100ノックをSQLをやるにあたって、必用になった知識をまとめながら解説したものになります。一部、実際の解答と表記が異なる場合があります。（ほとんどは、SQL構文を大文字にしたもの）

データサイエンス100ノックの始め方を解説したものではありません。

筆者はSQLの学習をしたばかりなので、間違いなどがあるかもしれません。その場合はコメントにて教えていただけると幸いです。

# 行・列の操作[1~9]

## 1【全項⽬指定】
>
S-001: レシート明細テーブル（receipt）から全項目を10件抽出し、どのようなデータを保有しているか目視で確認せよ。

```
%%sql
SELECT * FROM receipt LIMIT 10;
```

- **SELECT**でカラムを選択する。"*"をつけるとテーブルの全カラムを選択する。
- **FROM**で選択したカラムがあるテーブルを指定する。
- **LIMIT**で表示するデータの行数を指定。

## 2【列指定】
>
S-002: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。

```
%%sql
SELECT sales_ymd, customer_id, product_cd, amount FROM receipt LIMIT 10
```

- 列は指定した順番で表示される。

## 3【列名変更】
>
S-003: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。

```
%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount FROM receipt LIMIT 10
```

- SELECTでカラムを選択するとき、カラム名の後に"AS"を加えることでカラム名を変更できる。

## 4【単一条件】
>
S-004: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ
>
顧客ID（customer_id）が"CS018205000001"

```
%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001'
```

- WHERE句でカラムの条件を指定することができる。

## 5【複数条件】
>
S-005: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。
>
顧客ID（customer_id）が"CS018205000001"
売上金額（amount）が1,000以上

```
%%sql

SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND amount >= 1000
```

- WHERE句で複数の条件を指定するときは条件同士を"AND"でつなげる

## 6【複数条件】
S-006: レシート明細テーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001"
売上金額（amount）が1,000以上または売上数量（quantity）が5以上

```
%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, quantity, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND (amount >= 1000 OR quantity >= 5)
```

- WHERE句ではANDのほかに、ORや()が使用できる。


## 7【範囲指定】
S-007: レシート明細のテーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。

顧客ID（customer_id）が"CS018205000001"
売上金額（amount）が1,000以上2,000以下

```
%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND amount BETWEEN 1000 AND 2000
```

- WHERE句でA≦ｘ≦Bを表現するとき、BTWEENで区間を指定できる。

## 8【不一致】
S-008: レシート明細テーブル（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ

顧客ID（customer_id）が"CS018205000001"
商品コード（product_cd）が"P071401019"以外

```
%%sql
SELECT sales_ymd as sales_date, customer_id, product_cd, amount
FROM receipt
WHERE customer_id = 'CS018205000001' AND product_cd != 'P07140101
```

- 否定は"!="で表現できる。

## 9【補集合】
S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。

`select * from store where not (prefecture_cd = '13' or floor_area > 900)`

```
%%sql
SELECT * FROM store WHERE prefecture_cd != '13' AND floor_area <= 900
```
# あいまい検索[10~16]

## 10【前方一致】
S-010: 店舗テーブル（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件だけ表示せよ。

```
%%sql
SELECT * FROM store WHERE store_cd LIKE 'S14%' LIMIT 10;
```

- 部分一致する文字列を取得する場合は、WHERE カラム **LIKE** '単語'
- 任意の0文字以上を許す場合は**%**
- 任意の一文字を許す場合は**'_'**を追加


## 11【後方一致】
S-011: 顧客テーブル（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件だけ表示せよ。

```
%%sql
SELECT * FROM customer WHERE customer_id LIKE '%1' LIMIT 10;
```

## 12【部分一致】
S-012: 店舗テーブル（store）から横浜市の店舗だけ全項目表示せよ
```
%%sql
SELECT * FROM store WHERE address LIKE '%横浜市%';
```

- 住所に'横浜市'を含むすべてのレコードを取得する。

## 13【部分一致】
S-013: 顧客テーブル（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件だけ表示せよ。

```
%%sql
SELECT * FROM customer WHERE status_cd ~ '^[A:F]' LIMIT 10;
```

- POSIX正規表現を使ったパターン一致を使用する場合は正規表現の前に、**~**をつける。
- [A:F]：A~Fのいずれか
- ^：文字列の先頭
- SQLの正規表現についてhttps://www.postgresql.jp/document/9.4/html/functions-matching.html

## 14【部分一致】
S-014: 顧客テーブル（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

```
%%sql
SELECT * FROM customer WHERE status_cd ~ '[1-9]$' LIMIT 10;
```

- [0-9]：0~9のいずれかと一致
- $：文字列の末尾
- SQLの正規表現についてhttps://www.postgresql.jp/document/9.4/html/functions-matching.html

## 15【部分一致】
S-015: 顧客テーブル（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。

```
%%sql
SELECT * FROM customer WHERE status_cd ~ '^[A:F].*[1-9]$';
```


- '.'：任意の一文字
- '*'：0回以上の繰り返し

## 16【フォーマット一致】
S-016: 店舗テーブル（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

```
%%sql
SELECT * FROM store WHERE tel_no ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
```

- {数字}：指定された数字の回数の繰り返し

# ソート

## 17【並び替え（昇順）】
S-017: 顧客テーブル（customer）を生年月日（birth_day）で高齢順にソートし、先頭10件を全項目表示せよ。
```
%%sql
SELECT * FROM customer ORDER BY birth_day ASC LIMIT 10;
```

- ORDER BY カラム　でカラムを並び替え
- ASC:昇順

## 18【並び替え（降順）】
S-018: 顧客テーブル（customer）を生年月日（birth_day）で若い順にソートし、先頭10件を全項目表示せよ。
```
%%sql
SELECT * FROM customer ORDER BY birth_day DESC LIMIT 10;
```

- DESC：降順

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

```
%%sql
SELECT customer_id, amount, RANK() OVER(ORDER BY amount DESC) AS ranking
FROM receipt
LIMIT 10;
```
- まず、ORDER BY amount DESCで売上金額を降順に並べる
- 次に、売上金額を基準にした場合の順位をRANK()で取得する
- **RANK：同率があった場合順位は同じになり、その次の順位を飛ばす**
- **DENS_RANK : 同率があった場合順位は同じになり、その次の順位は飛ばさない**



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

```
%%sql
SELECT customer_id, amount, ROW_NUMBER() OVER(ORDER BY amount DESC) AS ranking
FROM receipt LIMIT 10;
```

- **ROW_NUMBER : 同率があっても同じ順位にはならず、順位をその場合のみランダムでカウント**


## 21~
(https://qiita.com/Strix9289/items/a7b813ade174ebb27409)