2012-07-28

シェル操作課題 SQLによる解答例

シェル操作課題 (cut, sort, uniq などで集計を行う) 設問編 - Yamashiro0217の日記の解答例です。MySQL 5.5です。

準備
mysql> CREATE TABLE log (
    ->   id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    ->   server_host VARCHAR(30),
    ->   access_time DATETIME,
    ->   user_id     INT,
    ->   access_url  VARCHAR(191)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'log.csv'
    -> INTO TABLE log
    -> FIELDS TERMINATED BY ','
    -> (server_host, @unixtime, user_id, access_url)
    -> SET access_time = FROM_UNIXTIME(@unixtime);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
問1 このファイルを表示しろ
mysqlSELECT server_host, access_time, user_id, access_url
    -> FROM log;
+-------------+---------------------+---------+--------------+
| server_host | access_time         | user_id | access_url   |
+-------------+---------------------+---------+--------------+
| server1     | 2012-07-27 13:25:24 |      30 | /video.php   |
| server2     | 2012-07-27 13:25:10 |      20 | /profile.php |
| server3     | 2012-07-27 13:25:15 |       7 | /login.php   |
| server1     | 2012-07-27 13:25:05 |       8 | /profile.php |
| server2     | 2012-07-27 13:26:45 |      35 | /profile.php |
| server2     | 2012-07-27 13:25:10 |      20 | /profile.php |
| server3     | 2012-07-27 13:26:45 |      30 | /login.php   |
| server4     | 2012-07-27 13:27:05 |      12 | /video.php   |
| server1     | 2012-07-27 13:27:45 |       7 | /video.php   |
+-------------+---------------------+---------+--------------+
9 rows in set (0.00 sec)
問2 このファイルからサーバー名とアクセス先だけ表示しろ
mysqlSELECT server_host, access_url
    -> FROM log;
+-------------+--------------+
| server_host | access_url   |
+-------------+--------------+
| server1     | /video.php   |
| server2     | /profile.php |
| server3     | /login.php   |
| server1     | /profile.php |
| server2     | /profile.php |
| server2     | /profile.php |
| server3     | /login.php   |
| server4     | /video.php   |
| server1     | /video.php   |
+-------------+--------------+
9 rows in set (0.00 sec)
問3 このファイルからserver4の行だけ表示しろ
mysql> CREATE INDEX log_ix1 ON log (server_host);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysqlSELECT server_host, access_time, user_id, access_url
    -> FROM log
    -> WHERE server_host = 'server4';
+-------------+---------------------+---------+------------+
| server_host | access_time         | user_id | access_url |
+-------------+---------------------+---------+------------+
| server4     | 2012-07-27 13:27:05 |      12 | /video.php |
+-------------+---------------------+---------+------------+
1 row in set (0.00 sec)

インデックスを作らなかった場合は減点します。

問4 このファイルの行数を表示しろ
mysqlSELECT COUNT(*)
    -> FROM log;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)
問5 このファイルサーバー名、ユーザーIDの昇順で5行だけ表示しろ
mysqlSELECT server_host, access_time, user_id, access_url
    -> FROM log
    -> ORDER BY server_host, user_id
    -> LIMIT 5;
+-------------+---------------------+---------+--------------+
| server_host | access_time         | user_id | access_url   |
+-------------+---------------------+---------+--------------+
| server1     | 2012-07-27 13:27:45 |       7 | /video.php   |
| server1     | 2012-07-27 13:25:05 |       8 | /profile.php |
| server1     | 2012-07-27 13:25:24 |      30 | /video.php   |
| server2     | 2012-07-27 13:25:10 |      20 | /profile.php |
| server2     | 2012-07-27 13:25:10 |      20 | /profile.php |
+-------------+---------------------+---------+--------------+
5 rows in set (0.00 sec)
問6 このファイルには重複行がある。重複行はまとめて数え行数を表示しろ
mysqlSELECT COUNT(DISTINCT server_host, access_time, user_id, access_url)
    -> FROM log;
+---------------------------------------------------------------+
| COUNT(DISTINCT server_host, access_time, user_id, access_url) |
+---------------------------------------------------------------+
|                                                             8 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

COUNT関数の中にDISTINCTを書けるのは覚えておくと便利です。

問7 このログのUU(ユニークユーザー)数を表示しろ
mysqlSELECT COUNT(DISTINCT user_id)
    -> FROM log;
+-------------------------+
| COUNT(DISTINCT user_id) |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.00 sec)
問8 このログアクセス先ごとにアクセス数を数え上位1つを表示しろ
mysqlSELECT access_url, COUNT(*)
    -> FROM log
    -> GROUP BY access_url
    -> ORDER BY COUNT(*) DESC
    -> LIMIT 1;
+--------------+----------+
| access_url   | COUNT(*) |
+--------------+----------+
| /profile.php |        4 |
+--------------+----------+
1 row in set (0.00 sec)
問9 このログのserverという文字列をxxxという文字列に変え、サーバー毎のアクセス数を表示しろ
mysqlSELECT REPLACE(server_host, 'server', 'xxx'), COUNT(*)
    -> FROM log
    -> GROUP BY server_host;
+---------------------------------------+----------+
| REPLACE(server_host, 'server', 'xxx') | COUNT(*) |
+---------------------------------------+----------+
| xxx1                                  |        3 |
| xxx2                                  |        3 |
| xxx3                                  |        2 |
| xxx4                                  |        1 |
+---------------------------------------+----------+
4 rows in set (0.00 sec)
10 このログユーザーID10以上の人のユニークユーザーIDユーザーIDソートして表示しろ
mysqlSELECT DISTINCT user_id
    -> FROM log
    -> WHERE user_id >= 10
    -> ORDER BY user_id;
+---------+
| user_id |
+---------+
|      12 |
|      20 |
|      30 |
|      35 |
+---------+
4 rows in set (0.00 sec)
個人的な感触

記事への反応(ブックマークコメント)

アーカイブ ヘルプ
ログイン ユーザー登録
ようこそ ゲスト さん