私はFEデータベースを克服したい(3/3)
この記事は前回の続きです。
第1回：https://machi11038004.hatenablog.com/entry/2020/10/19/182604
第2回（前回）：https://machi11038004.hatenablog.com/entry/2020/10/19/182807
前回までは、基本情報処理技術者試験の問題を解くことを最終目的とした上で、
クエリの基本をおさえる試みでした。
前回までで必要な武装はできたはずなので今回は問題に挑みます。
内容は以下の通り。
第一回---------------------------------
1.準備運動
2.とりあえず問題を見てみる
3.クエリの基本的な文
4.アスタリスク(*)
第二回---------------------------------
5.関数、式、述語
6.集合演算、結合
7.その他キーワード
第三回（本記事）---------------------------------
8.問題に挑む
8.1 問題を見る
8.2 問題を解く
8.3 ほんとにそうか？
9.おわりに
8.問題を解く
8.1 問題を見る
以下に問題を貼り付けます。
令和元年秋期のデータベース問題の設問1です。
ちなみに、「ちょっと覗きに来ただけ」という方は読み込まなくてもOKです。
後ほど全体感で話をします。
問題ここから-------------
書籍及び貸出情報を管理する関係データベースの設計及び運用に関する次の記述を読んで，設問1～3に答えよ。
D社の部署である資料室は，業務に関連する書籍を所蔵しており，従業員への貸出しを2015年4月から実施している。
所蔵する書籍を管理するデータベースは，書籍の情報を管理する書籍情報表と貸出状況を管理する貸出表とで構成されている。データベース構成を，図1に示す。下線付きの項目は主キーを表し，下破線付きの項目は外部キーを表す。各書籍は1冊しか所蔵していない。
〔貸出表に関する説明〕
（１）：従業員に書籍を貸し出す際は，一意の貸出番号，貸し出す書籍のISBNコード，従業員番号，貸出日及び返却予定日を設定し，返却日にはNULLを設定したレコードを追加する。
（２）：書籍が返却されたら，対象のレコードの返却日に返却された日付を設定する。
設問1
次のSQL文は，ISBNコードが ISBN978-4-905318-63-7 の書籍の貸出し状態を表示するSQL文である。ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。 □に入れる正しい答えを，解答群の中から選べ。ここで，検索に使用するISBNコードの書籍は必ず所蔵されているものとする。また，返却された書籍はその日のうちに再び貸し出されることはない。
問題ここまで-------------
図書館のような、書籍の貸出のシステムに関する問題です。
目的の本（=ISBNコードが ISBN978-4-905318-63-7 の書籍）が貸出中なら"貸出中"、
そうでないなら"貸出可"と出力するSQL文を完成させなさいと言ってるわけですね。
登場するテーブルは2つで、書籍の情報がまとまっている「書籍情報表」と、
貸出記録が記録されている「貸出表」です。
8.2 問題を解く
まず、おや？と思うのがUNIONの存在です。
これにより、大きく2つのSELECT文が現れて、その二つが足されていることが分かります。
とはいえ、それぞれが何をしようとしているのかよくわかりません。
下のSELECT文2は全貌が見えているので、こいつの仕事を明確にできれば、
上のSELECT文１に必要な記述が見えてきそうです。
というわけで、下のSELECT文2に注目します。
一行目のSELECT句に注目すると、
「SELECT DISTINCT 書籍情報表.ISBNコード, '貸出可' AS 書籍状態
FROM 書籍情報表」
より、貸出可のものを表示しようとしていることがわかります。
さらに、WHERE句以降を見ると、
NOT EXISTS ( SELECT 貸出表.ISBNコード FROM 貸出表
WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7')
という記述があります。これは、
「貸出表にISBN'978-4-905318-63-7'の本が存在しなければ（つまり貸出実積がなければ）」
という意味の一文です。
そのため、SELECT文２の仕事は、
「ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。 」
この部分を担っている文ということがわかります。
ということは、SELECT文１の仕事はこれ以外の部分です。
つまり、「ISBNコードで貸出表を検索し，最も新しい貸出日のレコードの返却日にNULLが設定されている場合は，"貸出中"が表示される。また，最も新しい貸出日のレコードの返却日に日付が設定されている場合，及び貸出実績のない書籍の場合は，"貸出可"が表示される。 」
この部分ということになります。
この部分は
「最も新しい貸出日のレコードの返却日に○○が設定されている場合」という文言が２回も出てきます。
その結果次第で、処理を分けろということです。
ここまで来たら後は気合です。
SELECT文を０から構築する必要はありません。問題と選択肢を見ながら想像力を働かせます。
①貸出表からISBN978-4-905318-63-7の"最も新しい貸出日”のレコードを抽出して、
②場合分けで表示を"貸出中"か"貸出可"に変えて出力すればよさそうです。
①について、貸出表から"ISBN978-4-905318-63-7の最も新しい貸出日”を返すのは、WHERE句の
(SELECT [b] FROM 貸出表 WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7')
の部分です。
[b]には、MAX(貸出表.貸出日)を入れれば、
ISBN978-4-905318-63-7書籍の最も新しい貸出日のデータが一行得られます。
ISBNコードを「かつ」で指定して、MAX関数に日付を入れれば、
該当する書籍の最も新しい日付が返ってくるからです。
②の場合分けについては、問題文に
「（中略）返却日にNULLが設定されている場合は，"貸出中"が表示される。
また，（中略）日付が設定されている場合，（中略）"貸出可"が表示される。」
とはっきり書かれていますので、
「貸出表.返却日 IS NULL THEN '貸出中'
WHEN 貸出表.返却日 IS NOT NULL THEN '貸出可'」
を記述すれば意図通りの場合分けになります。
返却日は、NULLでなければ必ず返却された日が入っているからです。
以上より、「エ、イ」が解答です。
8.3 ほんとにそうか？
正解はこれで間違いないのですが、せっかくなのでSQL文を使って確かめてみます。
問題文からそれぞれのテーブルを再現しました。中身は私が適当に作ったものです。
書籍情報表
isbnコード | 書籍名 | 著者名 | 出版社名 | 出版年 -----------------------+------------------+---------------+------------+-------- ISBN978-4-905318-63-7 | ドラゴソボール | 島山 明 | 集国社 | 1999 ISBN978-4-905319-64-8 | 名探偵ドイル | 赤山 剛昌 | 中学館 | 2000 ISBN978-4-905320-65-9 | 風景水彩画集 | 絵 うま太郎 | 絵乃会社 | 2001 ISBN978-4-905321-66-0 | 静物油絵集 | 絵 うま太郎 | 絵乃会社 | 2002 ISBN978-4-905322-67-1 | 国内絶景写真集 | 写真 撮太郎 | 絵乃会社 | 2003 ISBN978-4-905323-68-2 | 孤独に学ぶRuby | ﾙﾋﾞｰ･ﾀﾞｲｽｷｰ | 技術大好社 | 2005 ISBN978-4-905324-69-3 | 孤独に学ぶPython | ﾊﾟｲｿﾝ・ﾏｼﾞｽｷｰ | 技術大好社 | 2007 (7 行)
貸出表
貸出番号 | isbnコード | 従業員番号 | 貸出日 | 返却予定日 | 返却日 ----------+-----------------------+------------+------------+------------+------------ 1 | ISBN978-4-905319-64-8 | 1111 | 2000-01-01 | 2000-01-07 | 2000-01-07 2 | ISBN978-4-905321-66-0 | 2222 | 2000-01-02 | 2000-01-08 | 2000-01-08 3 | ISBN978-4-905320-65-9 | 1111 | 2000-01-03 | 2000-01-09 | 2000-01-09 4 | ISBN978-4-905322-67-1 | 3333 | 2000-01-04 | 2000-01-10 | 2000-01-10 5 | ISBN978-4-905323-68-2 | 3333 | 2000-01-05 | 2000-01-11 | 6 | ISBN978-4-905319-64-8 | 4444 | 2000-01-06 | 2000-01-12 | ｺｺを変更→7 | ISBN978-4-905318-63-7 | 1111 | 2000-01-01 | 2000-01-07 | (7 行)
コマンド
SELECT 貸出表.ISBNコード, CASE WHEN 貸出表.返却日 IS NULL THEN '貸出中' WHEN 貸出表.返却日 IS NOT NULL THEN '貸出可' END AS 書籍状態 FROM 貸出表 WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7' AND 貸出表.貸出日 = (SELECT MAX(貸出表.貸出日) FROM 貸出表 WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7') UNION ALL SELECT DISTINCT 書籍情報表.ISBNコード, '貸出可' AS 書籍状態 FROM 書籍情報表 WHERE 書籍情報表.ISBNコード = 'ISBN978-4-905318-63-7' AND NOT EXISTS (SELECT 貸出表.ISBNコード FROM 貸出表 WHERE 貸出表.ISBNコード = 'ISBN978-4-905318-63-7');
・ISBN978-4-905318-63-7の書籍が貸出表にあって返却日が入っている
結果
isbnコード | 書籍状態 -----------------------+---------- ISBN978-4-905318-63-7 | 貸出可 (1 行)
・ISBN978-4-905318-63-7の書籍が貸出表にあって返却日がNULL
結果
isbnコード | 書籍状態 -----------------------+---------- ISBN978-4-905318-63-7 | 貸出中 (1 行)
・ISBN978-4-905318-63-7の書籍が貸出表にない
結果
isbnコード | 書籍状態 -----------------------+---------- ISBN978-4-905318-63-7 | 貸出可 (1 行)
なるほど確かに貸出可か貸出中かを判断するクエリです。
UNION ALLがあるのに出力が1行なのは、上のSELECT文か下のSELECT文のどちらかが当てはまると、
もう片方のSELECT文は返すレコードが無いという貸出システム上の仕組みだからです。
おしまい
9.おわりに
お疲れさまでした。
これは数ある問題の中のたった一問にすぎませんし、
奥深～いデータベースの世界の氷山の一角だと思いますが、
アレルギーを払拭することが大事だと思うので、こういった記事を書いてみました。
個人的には勉強になり中々楽しかったです。
ご意見ご指摘あればご連絡ください。
その時もし返信が遅くなりましたらすみません。
参考ウェブページ
https://www.fe-siken.com/
私はFEデータベースを克服したい(2/3)
前回の続きです。
前回（第1回）：
https://machi11038004.hatenablog.com/entry/2020/10/19/182604
次回（第3回）：
https://machi11038004.hatenablog.com/entry/2020/10/19/182908
内容は以下です。
第一回---------------------------------
1.準備運動
2.とりあえず問題を見てみる
3.クエリの基本的な文
4.アスタリスク(*)
第二回（本記事）---------------------------------
5.関数、式、述語
5.1 SELECT句の補足
5.2 関数
5.2.1 COUNT関数
5.2.2 SUM関数
5.2.3 AVG関数
5.2.4 MAX関数
5.2.5 MIN関数
5.3 CASE式
5.4 述語
5.4.1 LIKE述語
5.4.2 IN述語
5.4.3 EXISTS述語
6.集合演算、結合
6.1 UNION
6.2 INNER JOIN
6.3 OUTER JOIN
7.その他キーワード
7.1 AS
7.2 DISTINCT
第三回---------------------------------
8.問題に挑む
9.おわりに
5.関数、CASE式、述語
先に紹介した句だけでなく、SELECT文には他にもいろんなものが含まれます。
その内に、「関数」、「CASE式」、「述語」があり、それぞれ役割が存在しますが、
いずれも戻り値を返すという働きがあります。
ですから、これらが出てきても単なる値だ、という見方をすると、
SELECT文を読むときの脳の負担が減ると思います。
なお、関数とCASE式は値を返し、述語は真理値を返します。
これらはたくさんの種類がありますが、書籍や問題でよく見かけるものを抜粋して示します。
ここでも、3節で使ったbooksテーブルを使います。
・booksテーブル
book_id | title | category | price | release ---------+------------------------+----------+-------+------------ 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 4 | スタジオギブリアート集 | 芸術 | 3800 | 2000-05-01 5 | 風景水彩画集 | 芸術 | 2980 | 2000-05-01 6 | 静物油絵集 | 芸術 | 4800 | 2000-06-01 7 | 国内絶景写真集 | 写真集 | 5600 | 2000-07-01 8 | ヨーロッパ写真集 | 写真集 | 3200 | 2000-08-01 9 | 週刊少年チャンプ | 雑誌 | 280 | 2000-09-01 10 | ファッションkankam | 雑誌 | 980 | 2000-10-01 11 | 科学雑誌アイザック | 雑誌 | 1280 | 2000-11-01 12 | 孤独に学ぶRuby | 技術書 | 3200 | 2000-12-01 13 | 孤独に学ぶPython | 技術書 | 3000 | 2000-12-01 14 | 孤独に学ぶPHP | 技術書 | 3500 | 2001-02-01 (14 行)
その前に、SELECT句について補足をします。
5.1 SELECT句の補足
3.1節でSELECT句の次には列名を続けると書きましたが、
実はSELECT句は単なる「値」も受け付けます。
値とは数値や文字などです。
コマンド
SELECT 1;
結果
?column? ---------- 1 (1 行)
コマンド
SELECT 'aaa';
結果
?column? ---------- aaa (1 行)
これらの場合、SELECTはこの値のデータ1行を返しています。
加えてSELECT句は、これから紹介する「関数」も受け付けます。
SELECT句は値を受け付けるわけですから、戻り値を返す関数を受け付けるのも、
すっとんきょうな話ではないでしょう。
SELECT句に関数を入れると、列名にはその関数が割り当てられ、
データ1行を返します。
例）全ての本の値段の合計を返す
※SUM(price)の部分が合計を出す「関数」です。
コマンド
SELECT SUM(price) FROM books;
結果
sum ------- 34180 (1 行)
SELECTに値や関数を入れると一行のデータが返ってくることがわかりましたが、
複数行返ってくることもあります。
例えばGROUP BYを使って「カテゴリごと」に算出すると、
その「カテゴリごと」のレコードの数だけデータを返します。
例）カテゴリごとの本の値段の合計を出す
コマンド
SELECT category, SUM(price) FROM books GROUP BY category;
結果
category | sum ----------+------- 漫画 | 1560 芸術 | 11580 写真集 | 8800 技術書 | 9700 雑誌 | 2540 (5 行)
5.2 関数
関数は「キーワード()」の形になっています。
カッコには引数として列名を入れます。
5.2.1 COUNT関数
指定した列のレコードの数を数えます。
・全ての書籍の行数を数える
コマンド
SELECT COUNT(*) FROM books;
結果
count ------- 14 (1 行)
「*（アスタリスク）」は4節で触れたとおり、全ての列を意味します。
そしてCOUNT関数はレコードの数を数える関数ですから、
booksテーブル全体の行数を数えたわけですね。
書籍は全部で14個ありましたから、その値がでています。
なお、*（アスタリスク）を引数に使える関数は、COUNT関数だけです。
・値段が1000円未満の本の行数を数える。
コマンド
SELECT COUNT(*) FROM books WHERE price < 1000;
結果
count ------- 5 (1 行)
WHEREの段階で1000円以上のものは行に残りません。
結果、1000円以下の本の行数を数えます。漫画3種類と雑誌2種類あわせて5です。
5.2.2 SUM関数
指定した列の全ての行の合計値を算出します。
・全ての本の値段の合計を返す。
コマンド
SELECT SUM(price) FROM books;
結果
sum ------- 34180 (1 行)
・カテゴリごとの本の値段の合計を表示する。
コマンド
SELECT category, SUM(price) FROM books GROUP BY category;
結果
category | sum ----------+------- 漫画 | 1560 芸術 | 11580 写真集 | 8800 技術書 | 9700 雑誌 | 2540 (5 行)
5.2.3 AVG関数
指定した列の全ての行の平均を算出します。
・全ての本の値段の平均を返す。
コマンド
SELECT AVG(price) FROM books;
結果
avg ----------------------- 2441.4285714285714286 (1 行)
・カテゴリごとの本の値段の平均を表示する。
コマンド
SELECT category, AVG(price) FROM books GROUP BY category;
結果
category | avg ----------+----------------------- 漫画 | 520.0000000000000000 芸術 | 3860.0000000000000000 写真集 | 4400.0000000000000000 技術書 | 3233.3333333333333333 雑誌 | 846.6666666666666667 (5 行)
5.2.4 MAX関数
指定した列の全ての行から最大値を返します。
・書籍の値段が一番高い値を返す
コマンド
SELECT MAX(price) FROM books;
結果
max ------ 5600 (1 行)
・書籍のカテゴリごとに値段が一番高い値を表示する
コマンド
SELECT category, MAX(price) FROM books GROUP BY category;
結果
category | max ----------+------ 漫画 | 600 芸術 | 4800 写真集 | 5600 技術書 | 3500 雑誌 | 1280 (5 行)
DATE型（日付）にも使えます。最も新しい日付を返します。
・書籍のカテゴリごとに発売日が新しいものを表示する
コマンド
SELECT category, MAX(release) FROM books GROUP BY category;
結果
category | max ----------+------------ 漫画 | 2000-03-01 芸術 | 2000-06-01 写真集 | 2000-08-01 技術書 | 2001-02-01 雑誌 | 2000-11-01 (5 行)
5.2.5 MIN関数
指定した列の全ての行から最小値を返します。
・書籍の値段が一番安い値を返す
コマンド
SELECT MIN(price) FROM books;
結果
min ----- 280 (1 行)
・書籍のカテゴリごとに値段が一番安い値を表示する
コマンド
SELECT category, MIN(price) FROM books GROUP BY category;
結果
category | min ----------+------ 漫画 | 480 芸術 | 2980 写真集 | 3200 技術書 | 3000 雑誌 | 280 (5 行)
DATE型（日付）にも使えます。最も古い日付を返します。
・書籍のカテゴリごとに発売日が古いものを表示する
コマンド
SELECT category, MIN(release) FROM books GROUP BY category;
結果
category | min ----------+------------ 漫画 | 2000-01-01 芸術 | 2000-05-01 写真集 | 2000-07-01 技術書 | 2000-12-01 雑誌 | 2000-09-01 (5 行)
5.3 CASE式
指定した列の全ての行に対し条件分岐を行い、指定した処理結果の値を返します。
CASE式は、列を指定するような箇所に記述します。
私が調べた中では、一番よく見るのがSELECT句の後です。
他には、関数の引数に使うパターンもあります。
CASE式の構文は以下です。
CASE 列名 WHEN 列名に関する条件式 THEN 条件式が該当した時の処理
WHEN 列名に関する条件式 THEN 条件式が該当した時の処理
WHEN 列名に関する条件式 THEN 条件式が該当した時の処理
...
ELSE 該当しない場合の処理
END
とりあえずサンプルいきしょう。
例として、booksテーブルのカテゴリを英語にしてみます。
コマンド
SELECT title, CASE category WHEN '漫画' THEN 'comic' WHEN '芸術' THEN 'art' WHEN '写真集' THEN 'photo' WHEN '雑誌' THEN 'magazine' WHEN '技術書' THEN 'Technical' ELSE 'other' END FROM books;
結果
title | case ------------------------+----------- ドラゴソボール | comic 名探偵ドイル | comic ジュラえもん | comic スタジオギブリアート集 | art 風景水彩画集 | art 静物油絵集 | art 国内絶景写真集 | photo ヨーロッパ写真集 | photo 週刊少年チャンプ | magazine ファッションkankam | magazine 科学雑誌アイザック | magazine 孤独に学ぶRuby | Technical 孤独に学ぶPython | Technical 孤独に学ぶPHP | Technical (14 行)
5.4 述語
5節の冒頭で申し上げた通り、述語は真理値を返します。
私が調べた中では、WHERE句の条件式で使われます。
真理値を返すという性質を考えれば自然です。
これまたいくつか種類がありますが、その中の一部を示します。
5.4.1 LIKE述語
いわゆる曖昧検索です。
列名に続けてLIKE '検索したい文字'と書きます。
この時、\%や_を使うことで曖昧検索ができます。
\%は「任意の0文字以上」、_は「任意の1文字」です。
・末尾に"集"がつく書籍を表示する
コマンド
SELECT title FROM books WHERE title LIKE '%集';
結果
title ------------------------ スタジオギブリアート集 風景水彩画集 静物油絵集 国内絶景写真集 ヨーロッパ写真集 (5 行)
5.4.2 IN述語
IN述語で指定した値が含まれているか判断します。
列名に続けてIN(値、値、...)と記述します。
・値段が480円か、1280円か、3200円の書籍のタイトルと値段を表示する
コマンド
SELECT title, price FROM books WHERE price IN(480, 1280, 3200);
結果
title | price --------------------+------- ドラゴソボール | 480 ジュラえもん | 480 ヨーロッパ写真集 | 3200 科学雑誌アイザック | 1280 孤独に学ぶRuby | 3200 (5 行)
NOT IN述語という、IN述語の否定形もあります。
・値段が480円か、1280円か、3200円以外の書籍のタイトルと値段を表示する
コマンド
SELECT title, price FROM books WHERE price NOT IN(480, 1280, 3200);
結果
title | price ------------------------+------- 名探偵ドイル | 600 スタジオギブリアート集 | 3800 風景水彩画集 | 2980 静物油絵集 | 4800 国内絶景写真集 | 5600 週刊少年チャンプ | 280 ファッションkankam | 980 孤独に学ぶPython | 3000 孤独に学ぶPHP | 3500 (9 行)
5.4.3 EXISTS述語
EXISTS述語（イグジスツ述語）は今までの述語とは毛色が違います。
EXISTS述語も括弧を使いますが、引数に渡すのは別のSELECT文です。
さらに言うと、この述語は今調べているテーブルと関係のある別のテーブルから、
関連性を指定して、一致する行があるかどうかを返します。
言葉だけだとアレなのでサンプルを...と言いたいところですが、
今のところサンプル用のテーブルが一つしかないので、新しく作ります。
書籍の出荷情報を管理しているテーブルです。
・書籍の出荷情報を管理するshippingsテーブル
shipping_id | book_id | number | shipping_date -------------+---------+--------+--------------- 1 | 1 | 50 | 2005-01-01 2 | 4 | 150 | 2005-02-10 3 | 2 | 50 | 2005-02-10 4 | 6 | 150 | 2005-03-15 5 | 1 | 100 | 2005-04-01 6 | 7 | 50 | 2005-05-15 7 | 6 | 350 | 2005-08-30 8 | 3 | 550 | 2005-09-05 9 | 1 | 100 | 2005-09-10
book_id列が実際に出荷した書籍のidです。
numberは出荷数、shipping_dateは出荷日としています。
必要なものが揃ったので、サンプルを見てみます。
・出荷済みの本のタイトルを表示する
コマンド
SELECT title FROM books WHERE EXISTS(SELECT * FROM shippings WHERE books.book_id = shippings.book_id);
結果
title ------------------------ ドラゴソボール 名探偵ドイル ジュラえもん スタジオギブリアート集 静物油絵集 国内絶景写真集 (6 行)
SELECT文の中に記述されるSELECT文を、「サブクエリ」といいます。
（ちょっと語弊があるのですが、試験対策程度ならこの理解で問題ないと思います。)
サブクエリで、booksテーブルのbook_idと
shippingsテーブルのbook_idが一致する行を抽出しています。
ここで、新しい表現が出てきました。
「books.book_id」と「shipping.book_id」です。
「.（ドット）」を使用すると、何のテーブルの列かを表せます。
「表名.列名」です。「の」と読むと理解しやすいです。
books.book_idは「booksテーブル"の"book_id」です。
表名は列名の重複がないなら省略して良いので今まで書かなかったのですが、
ここでbook_idが別のテーブルで同じ列名になっているので表名を付けました。
また、サブクエリのSELECTには*が続いていますが、
EXISTS述語で使うサブクエリの場合は、ここは何でも構いません。
例えば、先ほどの「出荷済みの本のタイトルを表示する」コマンドは、
SELECT title FROM books WHERE EXISTS(SELECT book_id FROM shippings WHERE books.book_id = shippings.book_id);
でも、
SELECT title FROM books WHERE EXISTS(SELECT 3.1415 FROM shippings WHERE books.book_id = shippings.book_id);
でも同じ結果になります。
これはEXISTS内では最終的に何を表示するかはどうでもよいからです。
実際、サンプルを見ればわかる通りそもそも表示されません。
EXISTSの使命はテーブルに指定した条件が一致した行があるかどうかを見ることだけです。
なにやら申し上げましたが
大事なのは、EXISTS述語で使うサブクエリのSELECT文の直後に何が来ようと、
振り回される必要はないという事です。
もう一発くらいサンプルをつくってみます。
「AND」は「かつ」という意味です。
・一度の出荷で出荷数が500を超えた書籍のタイトルを表示
コマンド
SELECT title FROM books WHERE EXISTS(SELECT * FROM shippings WHERE books.book_id = shippings.book_id AND shippings.number > 500);
結果
title -------------- ジュラえもん (1 行)
6.集合演算、結合
集合演算はテーブル同士を行で足し引きすることで、
結合はテーブル同士を列で操作することです。
6.1 UNION
UNIONは集合演算のひとつで、行を足します。
booksと同じ列を持つbooks_2ndというテーブルがあったとします。
・books_2nd
book_id | title | category | price | release ---------+--------------------------+----------+-------+------------ 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 15 | 人間合格 | 小説 | 3800 | 2000-05-01 16 | チーズはいずこへ消えた？ | ビジネス | 2980 | 2000-05-01 (5 行)
UNIONを使うと、booksテーブルとbooks_2ndテーブルを足したテーブルを生成できます。
SELECT文同士の間にUNIONと書きます。
この時、二つのテーブルの列の数と列の型は一致している必要があります。
・booksテーブルとbooks_2ndテーブルを足す
コマンド
SELECT * FROM books UNION SELECT * FROM books_2nd;
結果
book_id | title | category | price | release ---------+--------------------------+----------+-------+------------ 9 | 週刊少年チャンプ | 雑誌 | 280 | 2000-09-01 5 | 風景水彩画集 | 芸術 | 2980 | 2000-05-01 13 | 孤独に学ぶPython | 技術書 | 3000 | 2000-12-01 12 | 孤独に学ぶRuby | 技術書 | 3200 | 2000-12-01 14 | 孤独に学ぶPHP | 技術書 | 3500 | 2001-02-01 8 | ヨーロッパ写真集 | 写真集 | 3200 | 2000-08-01 15 | 人間合格 | 小説 | 3800 | 2000-05-01 4 | スタジオギブリアート集 | 芸術 | 3800 | 2000-05-01 10 | ファッションkankam | 雑誌 | 980 | 2000-10-01 16 | チーズはいずこへ消えた？ | ビジネス | 2980 | 2000-05-01 6 | 静物油絵集 | 芸術 | 4800 | 2000-06-01 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 7 | 国内絶景写真集 | 写真集 | 5600 | 2000-07-01 11 | 科学雑誌アイザック | 雑誌 | 1280 | 2000-11-01 (16 行)
ところで、よく見るとbooks_2ndにはbooksと全く同じ行が存在します。
漫画3つです。
そういった場合、UNIONは重複した行を排除します。
実際、上のテーブルには重複行はありません。
重複した行をそのまま出力したければ、UNIONの後ろにALLと書きます。
コマンド
SELECT * FROM books UNION ALL SELECT * FROM books_2nd;
結果
book_id | title | category | price | release ---------+--------------------------+----------+-------+------------ 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 4 | スタジオギブリアート集 | 芸術 | 3800 | 2000-05-01 5 | 風景水彩画集 | 芸術 | 2980 | 2000-05-01 6 | 静物油絵集 | 芸術 | 4800 | 2000-06-01 7 | 国内絶景写真集 | 写真集 | 5600 | 2000-07-01 8 | ヨーロッパ写真集 | 写真集 | 3200 | 2000-08-01 9 | 週刊少年チャンプ | 雑誌 | 280 | 2000-09-01 10 | ファッションkankam | 雑誌 | 980 | 2000-10-01 11 | 科学雑誌アイザック | 雑誌 | 1280 | 2000-11-01 12 | 孤独に学ぶRuby | 技術書 | 3200 | 2000-12-01 13 | 孤独に学ぶPython | 技術書 | 3000 | 2000-12-01 14 | 孤独に学ぶPHP | 技術書 | 3500 | 2001-02-01 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 15 | 人間合格 | 小説 | 3800 | 2000-05-01 16 | チーズはいずこへ消えた？ | ビジネス | 2980 | 2000-05-01 (19 行)
重複した漫画3つがそのまま足されています。
6.2 INNER JOIN
INNER JOINは「内部結合」を行います。列で結合します。
構文は以下です。
SELECT 列名、列名... FROM テーブル1 INNER JOIN テーブル2 ON テーブル1のキー = テーブル2のキー;
キーというのはテーブル同士を紐づけることができる列です。
5.4.3で登場したshippingsテーブルをここでも使います。
・booksとshippingsを列で連結し表示する。
コマンド
SELECT * FROM books INNER JOIN shippings ON books.book_id = shippings.book_id;
結果
booksとshippingsの全ての列が含まれたテーブルが作成されます。
6.3 OUTER JOIN
OUTER JOINは「外部結合」といいます。列で連結します。
INNER JOIN（内部結合）との違いは、片方のテーブルに情報があれば、
もう片側にそれに紐づく行がなくても空欄で出します。
ちなみに、データベースでは空欄のことをNULLと言います。
今「片方」と書きましたが、その片方がどっちのテーブルかは指定する必要があります。
構文内で先に書いた方なら「LEFT」、後に書いた方なら「RIGHT」です。
構文は以下です。
SELECT * FROM テーブル1 LEFTかRIHGT OUTER JOIN テーブル2 ON テーブル1のキー = テーブル2のキー;
・出荷されていようがいまいが書籍情報（books）と出荷情報（shippings）を連結して出力する
SELECT * FROM books LEFT OUTER JOIN shippings ON books.book_id = shippings.book_id;
結果
ちなみに、今左外部結合しましたが、右外部結合するとどうなるかというと、
コマンド
SELECT * FROM books RIGHT OUTER JOIN shippings ON books.book_id = shippings.book_id;
結果
6.2で示した内部結合と同じ結果になりました。
これはshippiingテーブル（右）に有ってbooksテーブル（左）に無い行が存在しないためです。
7.その他キーワード
7.1 AS
別名を与えます。
対象 AS 別名 と書くことで別名を設定できます。
一番よく見かけるのは列名に別名を与えることです。
・列名を変えてタイトルと値段を表示する。
コマンド
SELECT title AS 題名, price AS お値段 FROM books;
結果
題名 | お値段 ------------------------+-------- ドラゴソボール | 480 名探偵ドイル | 600 ジュラえもん | 480 スタジオギブリアート集 | 3800 風景水彩画集 | 2980 静物油絵集 | 4800 国内絶景写真集 | 5600 ヨーロッパ写真集 | 3200 週刊少年チャンプ | 280 ファッションkankam | 980 科学雑誌アイザック | 1280 孤独に学ぶRuby | 3200 孤独に学ぶPython | 3000 孤独に学ぶPHP | 3500 (14 行)
また、テーブルに別名を与えることも可能です。
一度別名を与えれば、続く記述は別名を使用することができます。
特にテーブルが複数出てくる場面で使用されます。
サブクエリが出てくるEXISTS述語や
集合演算、結合、などはテーブルが複数出てくるので遭遇する場面が増えるでしょう。
・出荷済みの書籍のタイトルを表示する（ASを絡めたパターン）
コマンド
SELECT title FROM books AS 書籍情報 WHERE EXISTS(SELECT book_id FROM shippings AS 出荷情報 WHERE 書籍情報.book_id = 出荷情報.book_id);
結果
title ------------------------ ドラゴソボール 名探偵ドイル ジュラえもん スタジオギブリアート集 静物油絵集 国内絶景写真集 (6 行)
・booksとshippingsを列で連結し表示する（ASを絡めたパターン）
SELECT * FROM books AS 書籍情報 INNER JOIN shippings AS 出荷情報 ON 書籍情報.book_id = 出荷情報.book_id;
結果
7.2 DISTINCT
重複を除いて出力します。
UNIONのくだりでも重複を除くと書いたのでごっちゃになるかも知れませんが、
あれはレコードの足し算の話で、今回はSELECT文の話です。
今までのサンプルではそういう出力をしていないので気付かないのは当然なのですが、
実はSELECT句では、GROUP BYを使うといった工夫をしないとたとえ結果が重複してもそのまま出力されます。
・カテゴリを表示する
コマンド
SELECT category FROM books;
結果
category ---------- 漫画 漫画 漫画 芸術 芸術 芸術 写真集 写真集 雑誌 雑誌 雑誌 技術書 技術書 技術書 (14 行)
DISTINCTを使うと重複をなくせます。
使い方は、SELECT句で列名の前にDISTINCTと書きます。
・カテゴリを重複なしで表示する
コマンド
SELECT DISTINCT category FROM books;
結果
category ---------- 漫画 芸術 写真集 技術書 雑誌 (5 行)
クエリの私なりの理解は以上です。
次回はいよいよ問題を解いていきます。
次回（第3回）：
https://machi11038004.hatenablog.com/entry/2020/10/19/182908
参考文献:
キタミ式イラストIT塾 基本情報技術者 令和02年 (情報処理技術者試験)
SQL 第2版 ゼロからはじめるデータベース操作
私はFEデータベースを克服したい(1/3)
今回はデータベースに関する記事を投稿いたします。
ちなみにタイトルのFEはFundamental Information Technology Engineer Examinationの略で、
基本情報処理技術者試験のことです。
私、基本情報処理技術者試験のデータベース問題が苦手です。
あまりに解けないので腹が立ってきました。
そこで、本記事では基本情報処理技術者試験の問題を真っ正面から解きつつ、
クエリの感触をつかむことを目的にします。
「データベースのスペシャリストになるかは今は別として、
基本情報処理技術者試験を合格する必要があるので学習を始めた」といったような方を
対象にしています。
今回も私みたいな駆け出し系に向けた内容です。
もちろん達人のご閲覧も歓迎です。
ちなみに、対策ウェブページに行けば回答も解説もあるんですけど、
まあ、別のアプローチをしているとでも解釈してください。
(対策ウェブページ：https://www.fe-siken.com/)
また、とても長いので3回に分けています。
第2回：
https://machi11038004.hatenablog.com/entry/2020/10/19/182807
第3回：
https://machi11038004.hatenablog.com/entry/2020/10/19/182908
内容は以下の通り。
第一回（本記事）---------------------------------
1.準備運動
2.とりあえず問題を見てみる
3.クエリの基本的な文
3.1 SELECT 句+FROM句の例
3.2 SELECT句 + FROM句 + WHERE句の例
3.3 SELECT句 + FROM句 + GROUP BY句の例
3.4 SELECT句 + FROM句 + GROUP BY句 + HAVING句の例
3.5 SELECT句 + FROM句 + ORDER BY句の例
4.アスタリスク(*)
第二回---------------------------------
5.関数、式、述語
6.集合演算、結合
7.その他キーワード
第三回---------------------------------
8.問題に挑む
9.おわりに
それでは早速参ります。
1.準備運動
そもそも データベースって何ですか？という方もいらっしゃるかもしれませんので、
ざっと全体の話をします。
データベースはデータを管理するシステムで、
多くのアプリケーションの必須構成要素です。
いろんな種類がありますが、中でもリレーショナルデータベースという種類がよく使われます。
基本情報処理技術者試験でもディープな回答を求められるのはこの種類です。
リレーショナルデータベースでは、二次元の表でデータを管理します。
・二次元の表の例
学生番号 | 名前 | 誕生日 | 所属 ----------+-----------+------------+-------- 1000 | 山田 太郎 | 2000-01-01 | 3年A組 1001 | 田中 花子 | 2000-01-02 | 2年B組 1002 | 齋藤 次郎 | 2000-01-03 | 1年C組 1003 | 鈴木 仁子 | 2000-01-04 | 2年D組 1004 | 高橋 三郎 | 2000-01-05 | 3年E組
行のことを「行」や「レコード」と呼びます。
この例でいうと「山田太郎」以下がレコードで、5つのレコードがあります。
列のことを「列」や「カラム」や「フィールド」と呼びます。
この例でいうと列が4つあります。
そしてこの表のことを「テーブル」と呼びます。
実際のリレーショナルデータベースでは複数のテーブルを管理します。
また、複数のレコードから必要なデータを取り出すことを「クエリ」といいます。
クエリとは「問い合わせ」という意味です。
クエリのような操作をする際に使う言語を「SQL」といいます。
Structured Query Languageの略です。
2.とりあえず問題を見てみる
雰囲気をつかむ為に問題の一部をみてみます。令和元年の秋期のものです。
流し見でいいです。
初めに言っておきますが、私含め初学者はほぼ確実にアレルギー出ます笑。
まるで暗号です...。
3.クエリの基本的な文
雰囲気がわかったところで、いったん問題は置いておいて、
ここから私なりのクエリの理解について示します。
クエリは、多くの場合で「SELECT文」のことを指します。
必要なデータを意図通りに選んで取得するということです。
SELECT文は、以下の「句」から成り立ちます。
1. SELECT句（出力するときはどの列を残すの？）
2. FROM句（どのテーブルを使うの？）
3. WHERE句（行を抽出する時の条件は？）
4. GROUP BY句（〇〇ごとに分けて出力したいとかある？）
5. HAVING句（〇〇ごとに分けた場合どれを残したい？）
6. ORDER BY句（出力する時、どういう行の順にする？）
全てが必ず必要ではありません。最小でSELECT句だけで動きます。
各句の組み合わせは必要に応じて、ということになります。
ただし、記述する順番は上の番号順と決まっています。
サンプルを使ってみてみます。
例として、以下のような書籍を管理する「books」テーブルで見ていきます。
（今 私が適当に作ったものです。）
book_id | title | category | price | release ---------+------------------------+----------+-------+------------ 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 4 | スタジオギブリアート集 | 芸術 | 3800 | 2000-05-01 5 | 風景水彩画集 | 芸術 | 2980 | 2000-05-01 6 | 静物油絵集 | 芸術 | 4800 | 2000-06-01 7 | 国内絶景写真集 | 写真集 | 5600 | 2000-07-01 8 | ヨーロッパ写真集 | 写真集 | 3200 | 2000-08-01 9 | 週刊少年チャンプ | 雑誌 | 280 | 2000-09-01 10 | ファッションkankam | 雑誌 | 980 | 2000-10-01 11 | 科学雑誌アイザック | 雑誌 | 1280 | 2000-11-01 12 | 孤独に学ぶRuby | 技術書 | 3200 | 2000-12-01 13 | 孤独に学ぶPython | 技術書 | 3000 | 2000-12-01 14 | 孤独に学ぶPHP | 技術書 | 3500 | 2001-02-01 (14 行)
book_idはid、titleは書籍のタイトル、categoryは書籍のカテゴリ、priceは書籍の値段、
releaseは書籍の発売日です。
3.1 SELECT 句+FROM句の例
「出力するときはどの列を残すの？+どのテーブルを使うの？」です。
SELECT句の後に、列名を書きます。複数あればカンマで区切ります。
実は列名以外も受け付けますが、5節で後述します（次回の記事です）。
FROM句の後は、テーブル名を一つだけ書きます。
・タイトルと値段だけ表示する。
コマンド
SELECT title, price FROM books;
結果
title | price ------------------------+------- ドラゴソボール | 480 名探偵ドイル | 600 ジュラえもん | 480 スタジオギブリアート集 | 3800 風景水彩画集 | 2980 静物油絵集 | 4800 国内絶景写真集 | 5600 ヨーロッパ写真集 | 3200 週刊少年チャンプ | 280 ファッションkankam | 980 科学雑誌アイザック | 1280 孤独に学ぶRuby | 3200 孤独に学ぶPython | 3000 孤独に学ぶPHP | 3500 (14 行)
3.2 SELECT句 + FROM句 + WHERE句の例
「出力するときはどの列を残すの？+どのテーブルを使うの？+行を抽出する時の条件は？」です。
WHERE句の後は、列名に対して条件式を書きます。
・価格が1000円以上の書籍のタイトルと値段を表示する
コマンド
SELECT title, price FROM books WHERE price >= 1000;
結果
title | price ------------------------+------- スタジオギブリアート集 | 3800 風景水彩画集 | 2980 静物油絵集 | 4800 国内絶景写真集 | 5600 ヨーロッパ写真集 | 3200 科学雑誌アイザック | 1280 孤独に学ぶRuby | 3200 孤独に学ぶPython | 3000 孤独に学ぶPHP | 3500 (9 行)
条件式で”等しい”を表現するときは「=」を使います。
・カテゴリが漫画のタイトルと値段とカテゴリーを表示する
コマンド
SELECT title, price, category FROM books WHERE category = '漫画';
結果
title | price | category ----------------+-------+---------- ドラゴソボール | 480 | 漫画 名探偵ドイル | 600 | 漫画 ジュラえもん | 480 | 漫画 (3 行)
3.3 SELECT句 + FROM句 + GROUP BY句の例
「出力するときはどの列を残すの？+どのテーブルを使うの？
+〇〇ごとに分けて出力したいとかある？」です。
GROUP BY句の後には列名を書きます。
・カテゴリごとに表示する。
コマンド
SELECT category FROM books GROUP BY category;
結果
category ---------- 漫画 芸術 写真集 技術書 雑誌 (5 行)
GROUP BY句に続く列名は、カンマで区切って複数指定できます。
その場合、〇〇ごとに区切ったものをさらに△△ごとに区切るという意味になります。
・カテゴリごとに、発売日で分けて表示する。
コマンド
SELECT category, release FROM books GROUP BY category, release;
結果
category | release ----------+------------ 雑誌 | 2000-09-01 雑誌 | 2000-11-01 技術書 | 2000-12-01 写真集 | 2000-08-01 雑誌 | 2000-10-01 漫画 | 2000-03-01 漫画 | 2000-01-01 芸術 | 2000-06-01 芸術 | 2000-05-01 写真集 | 2000-07-01 技術書 | 2001-02-01 (11 行)
3.4 SELECT句 + FROM句 + GROUP BY句 + HAVING句の例
「出力するときはどの列を残すの？+どのテーブルを使うの？
+〇〇ごとに分けて出力したいとかある？+〇〇ごとに分けた場合どれを残したい？」です。
HAVING句はGROUP BY句とセットで使います。
HAVING句の後には、条件式を書きます。
この条件式は、GROUP BY句で区切られたあとのテーブルに対して行われる、
という前提さえ守った内容であればOKです。
・カテゴリごとに、さらに発売日ごとに分けて、かつ漫画だけを表示する
コマンド
SELECT category, release FROM books GROUP BY category, release HAVING category = '漫画';
結果
category | release ----------+------------ 漫画 | 2000-01-01 漫画 | 2000-03-01 (2 行)
3.5 SELECT句 + FROM句 + ORDER BY句の例
「出力するときはどの列を残すの？+どのテーブルを使うの？
+出力する時、どういう行の順にする？」です。
ORDER BY句の後には、並び替えの基準にする列名を書きます。
データベースではデータの型が決まっていますから、
その型に沿って昇順に表示されます。
・値段が安い順に書籍のタイトルと値段を表示する
コマンド
SELECT title, price FROM books ORDER BY price;
結果
title | price ------------------------+------- 週刊少年チャンプ | 280 ジュラえもん | 480 ドラゴソボール | 480 名探偵ドイル | 600 ファッションkankam | 980 科学雑誌アイザック | 1280 風景水彩画集 | 2980 孤独に学ぶPython | 3000 ヨーロッパ写真集 | 3200 孤独に学ぶRuby | 3200 孤独に学ぶPHP | 3500 スタジオギブリアート集 | 3800 静物油絵集 | 4800 国内絶景写真集 | 5600 (14 行)
降順表示も可能です。その場合は、ORDER BY 句の列名の後ろに、「DESC」と書きます。
・値段が高い順に書籍のタイトルと値段を表示する
コマンド
SELECT title, price FROM books ORDER BY price DESC;
結果
title | price ------------------------+------- 国内絶景写真集 | 5600 静物油絵集 | 4800 スタジオギブリアート集 | 3800 孤独に学ぶPHP | 3500 ヨーロッパ写真集 | 3200 孤独に学ぶRuby | 3200 孤独に学ぶPython | 3000 風景水彩画集 | 2980 科学雑誌アイザック | 1280 ファッションkankam | 980 名探偵ドイル | 600 ジュラえもん | 480 ドラゴソボール | 480 週刊少年チャンプ | 280 (14 行)
余談ですが、DESCの部分に「ASC」と書くと、明示的に昇順指定したことになります。
ORDER BY句に続く列名は、カンマで区切って複数指定できます。
その場合、最初に指定した列に対して並び替えて、さらにその中で並び替えるという意味になります。
・値段順に表示させた上で、さらにid順に表示する
コマンド
SELECT title, price, book_id FROM books ORDER BY price, book_id;
結果
title | price | book_id ------------------------+-------+--------- 週刊少年チャンプ | 280 | 9 ドラゴソボール | 480 | 1 ジュラえもん | 480 | 3 名探偵ドイル | 600 | 2 ファッションkankam | 980 | 10 科学雑誌アイザック | 1280 | 11 風景水彩画集 | 2980 | 5 孤独に学ぶPython | 3000 | 13 ヨーロッパ写真集 | 3200 | 8 孤独に学ぶRuby | 3200 | 12 孤独に学ぶPHP | 3500 | 14 スタジオギブリアート集 | 3800 | 4 静物油絵集 | 4800 | 6 国内絶景写真集 | 5600 | 7 (14 行)
値段が一緒のものは、さらにidの昇順に並んでいます。
4.アスタリスク
「*」をアスタリスクといいます。データベースにおいては「全ての列」という意味です。
ですから、テーブルを表示するときにすべての列を表示させたければ、
SELECTに続けて*を書くだけで済みます。
コマンド
SELECT * FROM books;
結果
book_id | title | category | price | release ---------+------------------------+----------+-------+------------ 1 | ドラゴソボール | 漫画 | 480 | 2000-01-01 2 | 名探偵ドイル | 漫画 | 600 | 2000-01-01 3 | ジュラえもん | 漫画 | 480 | 2000-03-01 4 | スタジオギブリアート集 | 芸術 | 3800 | 2000-05-01 5 | 風景水彩画集 | 芸術 | 2980 | 2000-05-01 6 | 静物油絵集 | 芸術 | 4800 | 2000-06-01 7 | 国内絶景写真集 | 写真集 | 5600 | 2000-07-01 8 | ヨーロッパ写真集 | 写真集 | 3200 | 2000-08-01 9 | 週刊少年チャンプ | 雑誌 | 280 | 2000-09-01 10 | ファッションkankam | 雑誌 | 980 | 2000-10-01 11 | 科学雑誌アイザック | 雑誌 | 1280 | 2000-11-01 12 | 孤独に学ぶRuby | 技術書 | 3200 | 2000-12-01 13 | 孤独に学ぶPython | 技術書 | 3000 | 2000-12-01 14 | 孤独に学ぶPHP | 技術書 | 3500 | 2001-02-01 (14 行)
この場合、
SELECT * FROM books;
は、
SELECT book_id, title, category, price, release FROM books;
と同じです。
次回に続きます。
続き：
https://machi11038004.hatenablog.com/entry/2020/10/19/182807
参考文献:
キタミ式イラストIT塾 基本情報技術者 令和02年 (情報処理技術者試験)
SQL 第2版 ゼロからはじめるデータベース操作
自作アプリケーションに関する説明
こちらの記事では、私が作ったWebアプリケーションの紹介をします。
アプリケーションのリポジトリ（データ）は以下URLをご参照願います。
https://github.com/machi6/imanani
内容
1)本アプリケーションについて
1-1) 概要
1-2) 開発環境
1-3) 使用技術
1-4) 推奨ブラウザ
2) 使用画面
3) 何ができるのか
4) こんな問題を解決します
5) 実装と処理についての解説
5-1) 構築環境
5-2) データベース設計
5-3) 要素の動的配置処理
5-4) 要素のスケジュールシフト機能処理
1)本アプリケーションについて
1-1)概要
本アプリケーションは、「製品設計者向け課題潰し込みスケジュールツール」です。
業務効率を向上し、流動的な状況下でも働きやすい環境を実現することを目的に作られました。
ブラウザ上で動作するWebアプリケーションです。
名前を「imanani(イマナニ)」といいます。
具体的には週間業務計画をデジタル化したもので、
開発中の製品に対し課題及びやる事を管理できます。
表示される要素は時系列に沿って最適化され動的に配置されます。
またチーム内の自分以外の方のスケジュール共有と編集が可能です。
日程の見える化により、上長や同僚との業務進行に関わるコミュニケーションを支援します。
1-2)対象のユーザー
ものづくり業界で活躍する設計者。
担当部品を１〜複数個持ち、中長期（半年〜2年程度）の製品開発プロジェクトに携わる方。
1-3)開発環境
ハードウェア...mac book air （メモリ8GB）, ソフトウェア...Visual Studio Code
1-4)使用技術
HTML, CSS, Ruby on rails, Javascript, MySQL, Devise, Active Record, AWS S3及びEC2
1-5)推奨ブラウザ
2)使用画面
ログイン画面
スケジュール画面
課題編集画面
チームメンバー 一覧画面
3)何ができるのか
- 担当開発部品の登録・編集・削除
- 担当開発部品に属する課題の登録・編集・削除
- 担当開発部品に属する課題に対するやる事の登録・編集・削除
- チームメンバーのスケジュール共有
4)こんな問題を解決します
- 課題登録機能により、開発中製品の課題の抜け・漏れを防ぐ
- スケジュール共有機能により、チーム各人の進捗状況確認の手間を排除する
- やる事の動的なシフト機能により、突発業務発生時、全ての予定していた業務が何分後ろ倒しになるか即座に確認可能になる
- 課題とやる事の見える化により、上長や有識者へ業務の進め方の相談をする際に、工数と優先順位の認識の相違を最小限にする
5)実装と処理についての解説
5-1) 構築環境
構築環境の図を以下に記します。
本アプリケーションのデプロイ先は、AWS（Amazon Web Services）で利用できるEC2仮想サーバです。
WebサーバではNginx, アプリケーションサーバではUnicorn, データベースサーバではMaria DBをそれぞれ使用しています。
本アプリケーションは、Ruby on railsによるWebアプリケーションフレームワークを利用し作られています。
5-2) データベース設計
ユーザーは0以上の製品を持ち、製品は0以上の課題を持ち、課題は0以上のやる事を持ちます。
ER図を以下に示します。
5-3) 要素の動的配置処理
このアプリケーションの特徴の一つは、登録した製品、課題、やる事を動的に時系列順に配置する事です。
例として、登録した「やる事」について示します。
やる事を登録すると、以下の図のように、設定した時間の位置に予定した工数の幅を確保し配置されます。
要素の配置は、Javascriptを用いて実現しています。
登録した各情報はHTML要素として生成される際、自身の情報をid及びdata属性に確保します。
以下はやる事（task）のHTML要素を生成する時の記述です。
<div class = "task" data-start=<%= "#{l task.start, format: :time_only}" %> data-time=<%= "#{l task.time, format: :time_only}" %> data-wday=<%= "#{task.start.wday}" %> id = <%= "task_#{task.issue.product.id}_#{task.issue.id}|#{task.id}" %>> (中略)
data属性に、自身の開始時間、予定工数、曜日を設定しています。
Javascriptではページが読み込まれるとイベントが発火し、taskを適切な位置に配置します。
本アプリケーションでは1分を1ピクセルと決めています。
各task要素が持つdata属性から開始時間を取得し時間をピクセルに変換して位置を変更します。
let tasks = document.getElementsByClassName("task");
let parent_issue_id_stack = [];
for (i = 0; i<tasks.length; i++){
（中略）
//横位置の調整
start = tasks[i].dataset.start.split(':');
let start_time_H = Number(start[0]);//15:30開始なら15
let start_time_M = Number(start[1]);//15:30開始なら30
let start_time_wday = Number(tasks[i].dataset.wday);
let start_pos = (start_time_H * 60) + (start_time_M) +
(start_time_wday * 1440);
document.getElementById(tasks[i].id).setAttribute
("style", `top: ${task_top}px; left: ${start_pos}px; visibility: visible;`);
（中略）
}
他にも読み込み時の要素配置について動的な処理は存在しますが（Y位置の調整など）、基本的な実装方法は同様です。
それぞれの要素は生成時に自分と先祖までの親のidを情報として持つので、互いに要素の位置や幅を適切に取得し設定できます。
5-4) 要素のスケジュールシフト機能処理
このアプリケーションの特徴の一つは、やる事が新たに割り込む形で挿入された際、既存のやる事を自動的に未来へシフトすることです。
この時、やる事の開始順序は崩さず、隙間の時間は埋められ、かつ勤務時間外には作業開始時間が重複しません。
これは、やる事がシフトした先に既に別のやる事が存在した場合、それも未来へシフトさせることを再起的に行うことで実現しています。
例として、以下のような新たなやる事「task N」を登録する場合を示します。
この場合、目指す姿は以下のようなものです。
これは、shift_taskと名付けたメソッドにより実装されています。
流れは以下のようになります。
既存のやる事（task）は重複が続く限り連鎖的に未来へシフトします。
この処理のコードは以下です。
def shift_task(ids) #開始時間が重複したタスクは時間をシフトする wrapped_task_ids = get_ids_in_time_range(ids) if wrapped_task_ids.length > 0 #どれだけ重複したか difference = get_latest_end_time(ids) - get_fastest_start_time(wrapped_task_ids) wrapped_task_ids.each do |id| task = Task.find(id) task.update(start: task.start + difference) end #移動した先で開始時間が重複したタスクはそれも時間をシフトする shift_task(wrapped_task_ids) end end
この節の冒頭で、やる事は勤務時間外には作業開始時間が重複しませんと書きました。
この機能もshift_taskメソッドを利用し実現しています。
具体的には、まず勤務時間外に作業開始時間が重複したやる事をチェックします。
そして、該当したやる事を翌日の勤務開始時間に移動します。
その後でこのshift_taskメソッドを呼び出し、重複したやる事を未来へシフトさせます。
本アプリケーションの紹介は以上です。
サブネットマスクに関する備忘録
今のご時世アウトプットしてナンボ...という新(?)常識にならい、
練習を兼ねて私も記事を投稿します。
結論から言うと、大変長い文章になってしまいました。
まとめる難しさを痛感しています。
その分、私みたいな初心者向けになっているので、
もういいやってなるまでは読んでみてくれると嬉しいです。
記念となる第一弾は、基本情報処理技術者試験対策にこないだ個人的に調べた内容を、
備忘録的に残してみます。
ご覧いただいた方のご参考になれば幸いです。
テーマは「サブネットマスク」です！
・ある過去問（令和元年秋期の午後の問題）を解くことを目的にしたものです。
・私のような初学者の方を対象にしています（達人のご閲覧も歓迎）
■テーマとする問題
https://www.fe-siken.com/kakomon/01_aki/pm01.html
問題見るのめんどい、ちょっと興味あるだけって方は、
URLクリックしなくて大丈夫です。
ざっくり全体的に話すので。
ちなみに上記URLには問題だけでなく解答も解説もあるのですが、
私は一目見ただけではわからなかった(というより色々知らなすぎた)ので調べました。
内容は以下の通り。
1.そもそも何の話？
2.数字の羅列の見方
2-1 IPアドレスの表し方
2-2 ネットワークアドレス部とホストアドレス部
2-3 サブネットマスク
2-4 IPアドレスプレフィックス
3.問題を解く
3-1 問われていること
3-2 具体的な回答
1.そもそも何の話？
広いくくりで言えば、ネットワークの話です。
そして、テーマである「サブネットマスク」というのは、
「IPアドレス」に関するものです。
IPアドレスというのは、ネットワークにおける住所で、
コンピュータの一つ一つに割り当てられています。
この割り当ては、「ルータ」がうまいことやってくれています。
ルータはwifiでネットをご利用の方であればおそらく家のどこかにあるでしょう。
インターネットでデータのやりとりができるのは、
コンピュータの住所が存在するおかげです。
さて、IPアドレスは具体的な例としては、
192.168.1.1
とか
192.168.1.3
とか、そういう数字になっています。
ちなみに、ご自身のコンピュータのIPアドレスを確認できます。
windowsでは、コマンドプロンプトを立ち上げて「ipconfig」と入力後Enterキー、
macでは左上のリンゴマークから「システム環境設定」→「ネットワーク」から、
それぞれ確認が可能です。
2.数字の羅列の見方
2-1.IPアドレスの表し方
192.168.1.3というIPアドレスを例にとります。
IPアドレスは、実は32桁の「2進数」からできています。
2進数の詳細な説明は省きますが、簡単に言うと0と1のみで数を数えることです。
32桁の2進数を、8桁ごとに区切って表したものが、IPアドレスです。
（※ここで達人級の方は、「ならIPv4が前提って言えよ」
とおっしゃるかもしれませんが、まあ、その通りです。）
具体的には、
192 = 11000000（←2進数）
168 = 10101000（←2進数）
1 = 00000001（←2進数）
3 = 00000011（←2進数）
なので、
192.168.1.3 というIPアドレスは、
11000000 10101000 00000001 00000011(←8桁ごとに区切った2進数)
と同じです。
0と1だけで表されている桁の一つを、「ビット」と呼びます。
つまり、32"桁"の2進数は、32"ビット"の2進数ということです。
2-2.ネットワークアドレス部とホストアドレス部
この32ビットの2進数は、
さらに「ネットワークアドレス部」と「ホストアドレス部」に分けられています。
こんな感じです。
11000000 10101000 00000001 | 00000011 (←192.168.1.3 というIPアドレス)
ネットワークアドレス部 ホストアドレス部
この例では、下8ビットのところで区切りましたが、
どこで区切るかというのは、実際には「クラス」というもので決められています。
クラスA, クラスB, クラスCなどといいます。
上記の区切り方は、クラスCです。
(一応申し上げると、プログラミングのクラスとは無関係です)
さて、ネットワークアドレス部とは、
インターネットから「LAN」の住所を探すときに使うもの。
ホストアドレス部とは、
「LAN」内で個々のコンピュータの住所を探すときに使うもの。
といったイメージで良いと思います。
LANとは、Local Area Networkの略で、
ご家庭内や会社内だけでのネットワークのことです。
例えば、何かのデータが送られてくるときは、
まずネットワークアドレス部でLANを特定し、
さらにホストアドレス部で個々のコンピュータを特定する、
という感じです。
2-3.サブネットマスク
先ほどの
11000000 10101000 00000001 | 00000011 (←192.168.1.3 というIPアドレス)
ネットワークアドレス部 ホストアドレス部
の場合、ホストアドレス部は(クラスCを前提とすると)8ビットあります。
8ビットの2進数は0～255まで表現できますので、
そのまま考えればLAN内256台分のコンピュータの住所を表現できそうですが、
ホストアドレス部がすべて0もしくはすべて1というのは特別な意味を持つので、
実際にはLAN内における254台分のコンピュータの住所を表せます。
ここで、ある会社が254台分を部署ごとに分けて管理したいと考えたとします。
っていうか、事実そういう必要が世間的にあるのでしょう。
この254台をさらに分割する方法が存在します。
それが、「サブネットマスク」です！（やっと出てきた）
サブネットマスクも、32ビットの2進数です。
具体的には、こんな感じです。
11111111 11111111 11111111 11110000
サブネットマスクは、
必ず左側に1が続くようにして寄り、右側に0が続くように寄ります。
つまり、
11111111 11111111 11111111 11000000
とか、
11111111 11111111 11111111 00000000
といった感じですね。
このサブネットマスクを、IPアドレスと重ねて、
ホストアドレス部でサブネットマスクの1が重複したところを、
「サブネット」として分けられます。
具体的に、例として
192.168.1.3に
11111111 11111111 11111111 11110000というサブネットマスクを用いてみましょう。
ネットワークアドレス部 ホストアドレス部
11000000 10101000 00000001 | 00000011 ←IPアドレス(192.168.1.3)
11111111 11111111 11111111 11110000 ←サブネットマスク
なので、
ホストアドレス部かつサブネットマスクが1のところ、つまり
11000000 10101000 00000001 | 00000011 ←IPアドレス(192.168.1.3)
ここがサブネット(=部署とか事業所ごとの割り当て)です。
ホストアドレス部は、サブネットマスクにより
11000000 10101000 00000001 | 00000011 ←IPアドレス(192.168.1.3)
この部分となります。
この例ですと、サブネットとホストアドレス部はそれぞれ4ビットあります。
4ビットの2進数は0～15の数字を表現できますので、
16の部署と16のコンピュータの住所を表現できるIPアドレスというわけです。
今までは192.168.1.3を具体例としてきましたが、
実際にはサブネットとホストアドレス部にはその会社の部署と
コンピュータに応じて好きに値を入れられますから、
上記の例のサブネットマスクなら、
11000000 10101000 00000001 | YYYYXXXX
YYYYに部署ごとのサブネットを、
XXXXに各コンピュータの住所を入れられるわけですね。
192.168.1.3はそのうちの一つだったということです。
2-4. IPアドレスプレフィックス
サブネットマスクをいちいち
11111111 11111111 11111111 11110000
とか表現するのは冗長なので、スマートに表す方法があります。
それが「IPアドレスプレフィックス」です。
/を用いて、続けて数字を書くと、
「サブネットマスクは、左から"数字分のビット数"が1ね。」
という意味になります。
例えば、先ほどの16の部署と16のコンピュータのくだりを例に適用すると、
192.168.1.3/28（←/28の部分がIPアドレスプレフィックス）
と書けば、
「サブネットマスクは、左から28ビットが1ね。」
という意味になりますから、
11000000 10101000 00000001 | 00000011 ←IPアドレス(192.168.1.3)
11111111 11111111 11111111 11110000 ←サブネットマスク
という表現と同じになります。
実際は、IPアドレスを一般的に表現する場合は、
192.168.1.0/24
(11000000 10101000 00000001 00000000)
赤字がサブネットとホストアドレス部
といったように、サブネット以降の右側は
0とすることが多いようです。
3.問題を解く
3-1. 問われていること
問題文ははしょって、
無理やりまとめるとこんな内容です。
「 ある会社で、
部署A 192.168.64.0/24から サーバー 192.168.128.0/20へのアクセスはNGだけど、
部署B 192.168.65.0/24から サーバー 192.168.128.0/20へのアクセスはOKにしたい。
で、ルールとしてその辺をまとめてたら、間違って
192.168.64.0/23(IPアドレスSとします)からサーバー 192.168.128.0/20をOKって
書いちゃった。
これ、何がどうヤバいかわかる？どうすればいいと思う？」
3-2 具体的な回答
クラスが明記されていないので、どこまでがネットワークアドレス部かは
わかりませんが、IPアドレスプレフィックスが書いてあるので
少なくとも会社が部署ごとに好きに割り当てられるビットはわかります。
そのビットをZとすると、
部署Aは、192.168.64.0/24、すなわち
11000000 10101000 01000000 ZZZZZZZZ となり、
部署Bは 192.168.65.0/24、すなわち
11000000 10101000 01000001 ZZZZZZZZ となり、
IPアドレスSは、192.168.64.0/23、すなわち
11000000 10101000 0100000Z ZZZZZZZZ となります。
IPアドレスSに、例えば
11000000 10101000 01000000 00000000というIPアドレスが入ってきたとしましょう。
これは、部署Aのコンピュータのうちの一つです。
ところがルール上これをOKと書いてしまっているわけですから、
「部署A 192.168.64.0/24から サーバー 192.168.128.0/20へのアクセスはNG」
という意向と矛盾している状態です。
ではどうすればよいかというと、
単に部署BのIPアドレスを正しく書いてあげればよいです。
つまり、
192.168.64.0/23→192.168.65.0/24からサーバー 192.168.128.0/20をOKにする。
です。
おしまい
おわりに
お疲れさまでした。
ここまでお付き合いいただきありがとうございます。
知った風に書きましたが私も勉強中です。
ご意見ご指摘ありましたら是非お願いします。
（言い方は優し目でお願いします笑）
その場合、回答が遅れましたらすみません。
参考サイト：https://wa3.i-3-i.info/word12216.html
千里の道も一歩から...というわけで
皆様はじめまして！
machiといいます。
ウェブエンジニアを目指しています。
この度、自身の技術向上を目的にブログをはじめました！
ちょこちょこ投稿していこうと思いますので、
感想、酷評、なんでもください。
ここから少し自分のことを...
もともとは学生時代、情報工学を専攻していました。
（決して真面目な学生ではありませんでしたが...）
ただIT業界への就職は叶わず、
いろいろあって自動車業界で設計をやっていましたが、
一念発起してやっぱITやろう！と人生を見直しました。今31です。
ここから果たしてこいつは這い上がれるのか、是非このブログで確かめてください！笑
