【SQL】SQLをマスターし、作業効率を200倍にするビジネステクニック-実装編

# はじめに
リレーショナルデータベース（RDB）についての記事も早くも第3段となりました。今回は前回インストールしたPostgreSQLを使い、実装をしてみたいと思います。
今回は<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/6b01b8bccf5994cd3f2a



# CREATE TABLE文（データインポート）
PostgreSQLはcsvファイルを直接読み込ませる事ができないので、読み込ませる前に*CREATE TABLE*文を使い、空のデータテーブルを作成する必要があります。その際に、どのようなデータが入ってくるか、ある程度指定してあげます。
PostgreSQLを開き、*Query Editor*に以下のコードを打ち込みます。
```sql
CREATE TABLE articles (
	id char(15) Primary Key, /*Primary Keyになるデータ*/
	created_at TIMESTAMP NOT NULL, /*時系列データ*/
	likes_count INTEGER NOT NULL, /*数値データ*/
	comments_count INTEGER NOT NULL,
	url VARCHAR(255) NOT NULL, /*255文字以内の文字列データ*/
	users VARCHAR(50) NOT NULL, /*50文字以内の文字列データ*/
	page_views_count INTEGER NOT NULL
);

CREATE TABLE tags (
	id char(15) PRIMARY KEY,
	name char(50) NOT NULL
);

CREATE TABLE articles_tags_map (
	id char(50) PRIMARY KEY,
	article_id char(50) NOT NULL,
	FOREIGN KEY (article_id) /*Foreign Keyになるデータ*/
      REFERENCES articles (id),/*Foreign Keyになるデータの参照データ*/
	tag_id char(50) NOT NULL,
	FOREIGN KEY (tag_id)
      REFERENCES tags (id)
);
```

コードを走らせ、*Database*→*Scheme*→*public*→*Table*の下に上記で指定したテーブルが表示されていれば成功です。
右クリックし、*Import/Export Data*を選択し、インポートしたいファイルを選択します。
![Screen Shot 2022-04-12 at 19.55.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/dad5a4c5-1360-3f98-cc43-2832dfde876f.png)

# データ概観

### Articles テーブル
*SELECT* 文を使って、*articles*のデータセットを呼び出して見ましょう。*は「全て」を意味しています。

```sql
SELECT * FROM articles;
```

![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)

Articleテーブルには以下のデータが入っている事がわかります。　
- id (Primary Key)
- created_at（投稿日）
- likes_count（いいね数）
- url (記事のURL)
- users（ユーザー名）
- page_views_count（閲覧数）

次に、*tags*テーブルを見てみましょう

### Tags テーブル

```sql
SELECT * FROM tags;
```
<img width="400" alt="Screen Shot 2022-04-12 at 20.21.22.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/36b076e6-f74b-f19f-da24-343cfb12148e.png">



*tags*テーブルには以下のデータが入っている事がわかります。　

- id (Primary Key)
- name（タグ名）

次は*articles_tags_map*テーブルです。

### articles_tags_map テーブル 

```sql
SELECT * FROM articles_tags_map;
```

<img width="400" alt="Screen Shot 2022-04-12 at 20.21.40.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/cbf3a085-aae1-dc75-f99c-db3ab561e58d.png">


勘の言い方はもうお気づきかと思いますが、これは、Associative Table（連想テーブル）です。
*article_id* が「１」の記事に*tag_id*が「144」,「7」,「121」のタグがついている事を表します。

# JOIN文

では、*article_id* = 1 の記事のタグ「144」,「7」,「121」は一体何なのか見てみましょう
```sql
SELECT name FROM tags t
JOIN articles_tags_map ON t.id = tag_id
WHERE article_id = '1'
```
![Screen Shot 2022-04-12 at 20.58.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/5fd471f0-2a76-d8e8-e416-29fec26ff38b.png)
なるほど*article_id* = 1 の記事のタグは「Perl」,「初心者」,「Elasticsearch」だったのですね！

コードを1行づつ説明します。

#### 1.SELECT
まず、タグの名前を知りたいので、*SELECT*分を使って*tags*テーブルの*name*の属性を指定します。
```sql
SELECT name FROM tags 
```
![17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/bb62ad7c-eaa1-d31d-5b97-fe6f9d6f936a.png)


#### 2.JOIN
次に*JOIN*分を使い、*articles_tags_map*と繋げます。その際に、
「*articles_tags_map*の**tag_id**と、*tags*の**id**を繋げるヨ」と、指定しています。
（この際「**id**」という属性が、*articles_tags_map*と*tags*テーブル両方に存在しているので、*tags*の方の「**id**」を繋げるように**tags.id**としています）
```sql
JOIN articles_tags_map ON tags.id = tag_id
```

![18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/a61d0e74-a510-0dc3-2b8b-e82424df3300.png)

#### JOIN （結合）後↓
![19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/6e3f3175-850b-189d-c314-87debc042e7e.png)

#### 3.WHERE
最後に、「*article_id*が1の」、と指定してあります。

```sql
WHERE article_id = '1';
```

![20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/d870a85b-062f-d793-61ed-9c23e31bd162.png)

この様な工程を経て、以下のアウトプットがされたのです！

```sql
SELECT name FROM tags t
JOIN articles_tags_map ON t.id = tag_id
WHERE article_id = '1'
```
![Screen Shot 2022-04-12 at 20.58.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/5fd471f0-2a76-d8e8-e416-29fec26ff38b.png)

# GROUP BY 文
作者ごとのいいね数を比較したい時、は*GROUP BY*文が有効です。

```sql
SELECT users, sum(likes_count) as count /*sum関数で合計を計算し、countオブジェクトに定義*/  
FROM articles
GROUP BY users /*グループにするぞ属性を選択*/ 
ORDER BY count desc /*1行目で定義したcountが高い順に表示*/ 
limit 5 /*上位5名を表示*/;
```

![Screen Shot 2022-04-12 at 22.07.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1044781/b6840b7a-409d-8971-a246-c8c24b05efb8.png)
 
「#NAME?」という方がいいねランキング１位ですね、名前からしてデータの欠損値の可能性が高いですね。１位は「t1zOl-Tor」さんとして良いでしょう。



# おわりに
慣れない間はややこしい作業ですが、一度慣れちゃえばすぐできる様になります！<a href = "https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess">SQL100本ノック</a>とやればすぐ慣れます。（昔学校で永遠とSQLを打つという課題あったなぁ…）
次回はSQLのもう少し難しいテクニックを紹介しようかと思います！

次回↓

https://qiita.com/Shu25/items/40e8b86f1e83602e3fee


### 関連記事はこちら
https://qiita.com/Shu25/items/6404cf6041c915f65456

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

https://qiita.com/Shu25/items/40e8b86f1e83602e3fee