はじめに

「独学エンジニアの課題として、ログ解析システムを作った。」というタイトルで、課題の製作物完成までの道のりを投稿しています。

GitHub の Public リポジトリで誰でも閲覧できるようにしています。

github.com

独学エンジニアとは

『独学エンジニア』とは、Web開発（主にサーバーサイド）の動画学習教材です。

dokugaku-engineer.com

連載の目次

「独学エンジニアの課題として、ログ解析システムを作った。」の連載については、以下の順に投稿しています。

ログ解析システムカテゴリーの記事一覧 - 転職したらスマレジだった件

今回は Step4. SELECT 文での検索 です。

Step5. プログラムからのSQLの操作で、算出するコマンドラインプログラムを作成するため、今回は利用する SQL 文を考えます。

環境

	バージョン
macOS Big Sur	11.6
Docker Desktop	4.7.0
PHP	8.1.7
MySQL	8.0.28

インポートしたデータ形式の確認

domain_code	page_title	count_views	total_response_size
ドメインコード	ページタイトル	各時間のページ表示回数	合計レスポンスサイズ
aa	Main_Page	4	0
aa	Wikipedia	1	0

1. 最もビュー数の多い記事

まずは以下の SQL 文を作成します。

最もビュー数の多い記事を、指定した記事数分だけビュー数が多い順にソートし、ドメインコードとページタイトル、ビュー数を提示する

（例）コマンドライン上で2記事と指定した場合、下記を表示する

”en”, “Main_Page”, 120

”en”, ”Wikipedia:Umnyango_wamgwamanda”, 112

SELECT
    domain_code,
    page_title,
    count_views
FROM
    page_views
ORDER BY
    count_views DESC
LIMIT
    2
;

日本語をSQL文の流れに並べながら、以下のように頭の中で考えながら作成しました。

ドメインコード（domain_code）とページタイトル（page_title）、ビュー数（count_views）を提示（SELECT）する。

SELECT
    domain_code,
    page_title,
    count_views
FROM
    page_views

最もビュー数（count_views）の多い記事を、指定した記事数分だけビュー数が多い順（DESC）にソート（ORDER BY）する。

ORDER BY
    count_views DESC
LIMIT
    2

ちなみに、 ORDER BY の並び替えは、以下の通りです。

DESC は、降順（大きい順）に並び替える
ASC は、昇順（小さい順）に並び替える（デフォルトの指定）

想定通りのデータを取得できています。

2. 指定したドメインコードの人気順

続いて、以下の SQL 文を作成します。

指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を提示する

（例）コマンドライン上で「en de」と指定した場合、下記を表示する

”en”, 10700

”de”, 5300

SELECT
    domain_code,
    SUM(count_views)
FROM
    page_views
WHERE
    domain_code
    IN (
        'en',
        'de'
    )
GROUP BY
    domain_code
ORDER BY
    SUM(count_views) DESC
;

指定したドメインコード名での絞り込みについては、 IN 句を用いて、 domain_code カラムの値が、値 en、値 de に当てはまるレコードを取得する、という記述をしています。
GROUP BY 句で、domain_code ごとに合計ビュー数（SUM(count_views)）を算出して提示する記述をしています。