はじめに
リレーショナルデータベース（RDB）についての記事も早くも最終回となりました。今回は前回実装したQuery言語を網羅的に紹介・実装してみようと思います。
今回もdelikaのダミーデータを使用します。
delikaとは、データの収集や分析をオープンにする事で、より効率良く新たな価値創出につながると考え開発されたプラットフォームです。
（ちなみに、今Qiitaでいい感じのイベントも開催中なので要チェックです。）
前回↓
データ概観
前回Joing文の説明はしたので使用するデータテーブルはarticlesだけを使います。
articles
- id (Primary Key)
- created_at（投稿日）
- likes_count（いいね数）
- url (記事のURL)
- users（ユーザー名）
- page_views_count（閲覧数）
それでは早速行ってみましょう
WHERE:条件指定
2022年以降に投稿された記事
SELECT * FROM articles
WHERE created_at >= '2022-01-01';
AND, OR, NOT:条件追加
2022年以降に投稿された記事で閲覧数が5000以上
SELECT * FROM articles
WHERE created_at >= '2022-01-01' AND page_views_count >= 5000;
ORDER BY: 昇順
2022年以降に投稿された記事で閲覧数が5000以上の記事の閲覧数順(desc)
SELECT * FROM articles
WHERE created_at >= '2022-01-01' AND page_views_count > 5000
ORDER BY page_views_count DESC;
MAX, MIN:最大最小
2022年以降に投稿された記事で閲覧数トップ
SELECT MAX(page_views_count) FROM articles
WHERE created_at >= '2022-01-01';
COUNT:カウント
2022年以降に投稿された記事数
SELECT COUNT(id) FROM articles
WHERE created_at >= '2022-01-01';
AVG:平均
2022年以降に投稿された記事の平均閲覧数
SELECT AVG(page_views_count) FROM articles
WHERE created_at >= '2022-01-01';
SUM:合計
2022年以降に投稿された記事の総いいね数
SELECT SUM(likes_count) FROM articles
WHERE created_at >= '2022-01-01';
EXTRACT:抽出 -日付データ(YYYY-MM-DD)から任意の値を抽出
年ごとの投稿記事数（多い順）
SELECT EXTRACT(YEAR FROM created_at), COUNT(id) AS cnt FROM articles
GROUP BY EXTRACT(YEAR FROM created_at)
ORDER BY cnt DESC;
四則演算
いいね数を閲覧数で割ったlike_per_view 数を月毎に比較
$$
like\_per\_view = \dfrac{likes\_count}{page\_views\_count}
$$
SELECT EXTRACT(MONTH FROM created_at) AS m, AVG(likes_count/page_views_count) FROM articles
GROUP BY m
ORDER BY m;
(外れ値あり)
Sub Query
いいね数が平均いいね数以上の記事数
SELECT COUNT(id) FROM articles
WHERE likes_count >(
SELECT AVG(likes_count) FROM articles /*平均いいね数の算出*/
)
平均いいね数以上の記事数の年ごとの内訳
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
CASE WHEN
条件を指定しラベル付け
SELECT id,comments_count,
CASE
WHEN comments_count >= 3 THEN 'moerthan3'
ELSE 'lessthan3'
END
FROM articles
UNION
SELECTされた列同士を同じ列に結合する。ALLをつけると重複は削除されない。
使用例）新しいテーブルをつける時にIDを新しく振る必要があるため、DB全体のIDを確認したい↓
SELECT id FROM articles
UNION ALL
SELECT id FROM tags
UNION ALL
SELECT id FROM articles_tags_map
おわりに
今回はSQLのQuery言語について網羅的に紹介しました。「SQLの道は一日にしてならず」この他にもたくさん便利なQuery言語があります。
また、JOIN文やSub Queryを使いこなすことで、「エクセルで出すのはちょっと大変、、」な数字がわずか数分で算出できます！
関連記事はこちら