【SQL/Query一覧】SQLをマスターし、作業効率を200倍にするビジネステクニック-応用編

# はじめに
リレーショナルデータベース（RDB）についての記事も早くも最終回となりました。今回は前回実装したQuery言語を網羅的に紹介・実装してみようと思います。
今回も<a href = "https://delika.connecto-data.com/">delika<a/>の<a href = "https://delika.io/qiita_delika_article_campaign/QiitadelikaDummy">ダミーデータ</a>を使用します。
> delikaとは、データの収集や分析をオープンにする事で、より効率良く新たな価値創出につながると考え開発されたプラットフォームです。
（ちなみに、今Qiitaでいい感じの<a href = "https://qiita.com/official-events/30be12dd14c0aad2c1c2">イベント</a>も開催中なので要チェックです。）

前回↓

https://qiita.com/Shu25/items/8a5d3293fc177689ee9c

### データ概観
前回Joing文の説明はしたので使用するデータテーブルはarticlesだけを使います。

#### articles
- id (Primary Key)
- created_at（投稿日）
- likes_count（いいね数）
- url (記事のURL)
- users（ユーザー名）
- page_views_count（閲覧数）

![Screen Shot 2022-04-12 at 20.38.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/42414bea-e09e-f1ea-6ec2-989cd89859d6.png)

それでは早速行ってみましょう

# WHERE:条件指定
2022年以降に投稿された記事
```sql
SELECT * FROM articles
WHERE created_at >= '2022-01-01';
```
![Screen Shot 2022-04-13 at 20.20.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/af1ab00f-0e18-e741-81ea-e910095d3458.png)

# AND, OR, NOT:条件追加
2022年以降に投稿された記事で閲覧数が5000以上
```sql
SELECT * FROM articles
WHERE created_at >= '2022-01-01' AND page_views_count >= 5000;
```
![Screen Shot 2022-04-13 at 20.26.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/6677f41c-db8f-6895-9a73-a1136786f354.png)

# ORDER BY: 昇順
2022年以降に投稿された記事で閲覧数が5000以上の記事の閲覧数順(desc)
```sql
SELECT * FROM articles
WHERE created_at >= '2022-01-01' AND page_views_count > 5000
ORDER BY page_views_count DESC;
```
![Screen Shot 2022-04-13 at 20.29.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/19b51b0b-66a2-77e3-429e-d2c431733b21.png)


# MAX, MIN:最大最小
2022年以降に投稿された記事で閲覧数トップ
```sql
SELECT MAX(page_views_count) FROM articles
WHERE created_at >= '2022-01-01';
```
![Screen Shot 2022-04-13 at 20.33.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/27ac3387-3977-3471-51c3-0fcb630f7626.png)


# COUNT:カウント
2022年以降に投稿された記事数
```sql 
SELECT COUNT(id) FROM articles
WHERE created_at >= '2022-01-01';
```
![Screen Shot 2022-04-13 at 20.34.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/080361f3-6af0-fac7-b8dd-c6b8568d8aba.png)

# AVG:平均
2022年以降に投稿された記事の平均閲覧数
```sql
SELECT AVG(page_views_count) FROM articles
WHERE created_at >= '2022-01-01';
```
![Screen Shot 2022-04-13 at 20.40.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/256e2ec5-8a0f-0c95-6d41-784ad946d207.png)


# SUM:合計
2022年以降に投稿された記事の総いいね数
```sql
SELECT SUM(likes_count) FROM articles
WHERE created_at >= '2022-01-01';
```
![Screen Shot 2022-04-13 at 20.41.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/08688d3b-9940-590b-1290-188b5c0fff5c.png)

# EXTRACT:抽出　-日付データ(YYYY-MM-DD)から任意の値を抽出
年ごとの投稿記事数（多い順）
```sql
SELECT EXTRACT(YEAR FROM created_at), COUNT(id) AS cnt FROM articles
GROUP BY EXTRACT(YEAR FROM created_at) 
ORDER BY cnt DESC;
```
<img width="300" alt="Screen Shot 2022-04-12 at 20.21.40.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/29ad4ac6-e4e2-596a-07f1-b9133347734f.png">


# 四則演算
いいね数を閲覧数で割ったlike_per_view 数を月毎に比較
$$
like\\_per\\_view = \dfrac{likes\\_count}{page\\_views\\_count}
$$

```sql
SELECT EXTRACT(MONTH FROM created_at) AS m, AVG(likes_count/page_views_count) FROM articles
GROUP BY m
ORDER BY m;
```
<img width="300" alt="Screen Shot 2022-04-12 at 20.21.40.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/350a7e06-c014-e94c-2e94-13b914d57d16.png">

(外れ値あり)

# Sub Query
いいね数が平均いいね数以上の記事数
```sql
SELECT COUNT(id) FROM articles
WHERE likes_count >(
	SELECT AVG(likes_count) FROM articles /*平均いいね数の算出*/
)
```
![Screen Shot 2022-04-13 at 22.12.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/6a9534a6-4043-95bc-ee58-cd5b99e01149.png)


平均いいね数以上の記事数の年ごとの内訳
```sql 
SELECT EXTRACT(YEAR FROM created_at) AS y, COUNT(id) FROM articles
WHERE likes_count >(
	SELECT AVG(likes_count) FROM articles /*平均いいね数の算出*/
)
GROUP BY y
ORDER BY y
```

<img width="250" alt="Screen Shot 2022-04-12 at 20.21.40.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/386559b7-fb76-5cdc-2e11-fdc341e564a0.png">

# CASE WHEN 
条件を指定しラベル付け

```sql 
SELECT id,comments_count,
	CASE
		WHEN comments_count >= 3 THEN 'moerthan3'
		ELSE 'lessthan3'
	END
FROM articles
```

<img width="450" alt="Screen Shot 2022-04-12 at 20.21.40.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/5be8528e-9c7b-ac1e-09fa-a200b23ca6e1.png">

# UNION 
SELECTされた列同士を同じ列に結合する。ALLをつけると重複は削除されない。
使用例）新しいテーブルをつける時にIDを新しく振る必要があるため、DB全体のIDを確認したい↓
```sql 
SELECT id FROM articles 
UNION ALL
SELECT id FROM tags
UNION ALL
SELECT id FROM articles_tags_map
```

<img width="250" alt="image.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/a87e7840-5d57-a58f-e1ee-e15aeadd5898.png">


# おわりに
今回はSQLのQuery言語について網羅的に紹介しました。「SQLの道は一日にしてならず」この他にもたくさん便利なQuery言語があります。
また、JOIN文やSub Queryを使いこなすことで、「エクセルで出すのはちょっと大変、、」な数字がわずか数分で算出できます！


### 関連記事はこちら

https://qiita.com/Shu25/items/6404cf6041c915f65456

https://qiita.com/Shu25/items/6b01b8bccf5994cd3f2a

https://qiita.com/Shu25/items/8a5d3293fc177689ee9c