Access学習メモ 7-6. SQL基礎学習⑥

テーブル上の全フィールドを表示させ

特定のフィールド順に、レコードの並べ替えをおこないます。

特定のフィールドとは、「視聴数(万回)」です。

アニメの放送話ごとの再生数を記録したフィールドです。

「視聴数(万回)」フィールドの場所：

既存のマスタテーブルに追加する方法と

新たにトランザクションテーブルを追加し

マスタテーブルに内部結合させる方法がある。

再生回数の集計結果は日々変化し得る情報であり

トランザクションテーブルで管理を行うのが王道なので

今回はトランザクションテーブルを新たに設け

既存のマスタテーブルと結合(JOIN)させる方法を採用する。

1.トランザクションテーブルの構成を決める

名称：T_視聴

目的：[視聴数(万回)]を記録したい

↓

[視聴数(万回)]フィールドを置くことは確定。

[視聴数(万回)]の記録は複数行(レコード)必要か？

↓

動画の再生数なので、集計結果は刻々と変化し得る。

毎日せっせと集計したいほどではないけど、ないよりはあった方がいいかな・・・

↓

集計日を記録するための「記録日」フィールドを設けておこう。

今回のトランザクションテーブル(T_視聴)は時系列データの保管場所となり

「記録日」を軸に進行するため、「記録日」フィールドを主役とする。

【注】

1レコード1記録日であれば、データ型が日付型であっても頓着することなく

「記録日」フィールドを主キーに設定することは可能だが

今回のように、複数レコード(放送話)にわたって

同じ記録日の入力が想定される場合（他と重複する場合）

主キーにはできない。

主キーを設けたい場合は「記録ID」など一意の値が入るフィールドを

別途、用意する。

2.マスタテーブル(M_配信リスト)と

トランザクションテーブル(T_視聴)を繋ぐ

各テーブルに共通するフィールドであれば結合できるが、

他レコードと重複したデータを記入可能なフィールド同士（たとえば性別や血液型）で結合した場合、

レコード数×レコード数のぶんだけ、データが増幅してしまうので要注意。

アニメの放送話ごとに年度別として[Season]フィールドを設けているので

試してみる。

マスタテーブル(M_配信リスト)とトランザクションテーブル(T_視聴)を

[Season]フィールドで繋ぐ。

マスタに格納済の話数データは全114レコードであることに間違いないが

結合済みクエリでは4,494レコードとなっていた。

どういうことか。クエリの中身をチェック。

※各レコード数は放送話の数(話数)を示す。

Season1のレコード数：13

Season1に分類された放送話13件(レコード)に対し、Season1に分類されたすべてのレコードが紐づいている。

Season2のレコード数：52

Season2に分類された放送話52件(レコード)に対し、Season2に分類されたすべてのレコードが紐づいている。

Season3のレコード数：39

Season3に分類された放送話39件(レコード)に対し、Season3に分類されたすべてのレコードが紐づいている。

Season4のレコード数：10

Season4に分類された放送話10件(レコード)に対し、Season4に分類されたすべてのレコードが紐づいている。

マスタテーブル(M_配信リスト)Season1

＊トランザクションテーブル(T_視聴)Season1

 = 169レコード

マスタテーブル(M_配信リスト)Season2

 * トランザクションテーブル(T_視聴)Season2 

= 2,704レコード

マスタテーブル(M_配信リスト)Season3

 * トランザクションテーブル(T_視聴)Season3 

= 1,521レコード

マスタテーブル(M_配信リスト)Season4

 * トランザクションテーブル(T_視聴)Season4

 = 100レコード

そんなこんなで、沢山レコードが作られても困ってしまうので、

他レコードの値と重複の恐れがない「話数」フィールドを

橋渡し役の結合キーと決定。

マスタテーブル「M_配信リスト」内[話数]フィールドを

トランザクションテーブル「T_視聴」に対し、外部キーとして保有させる。

双方のテーブルを「話数」フィールドでJOINさせる。

3.主人公となるテーブルはどちらかを決めておく

Excelの列がユーザーの視界から向かって左から右へ進むのと同様に

Accessも左側を先頭(優先)とする。

クエリデザイン画面でテーブルやフィールドを配備する場合

優先したいデータを左から順に置く。

マスタテーブル(M_配信リスト)とトランザクションテーブル(T_視聴)、

どちらを主役とするか事前に決め、画面の左側に配備する。

結合の方法は3通り。

①INNER JOIN(内部結合)・・・左右のテーブルのフィールドをすべて結合する

②LEFT JOIN(左外部結合)・・・左テーブルのフィールドを基準に、右テーブルの値入りフィールドのみ結合する

③RIGHT JOIN(右外部結合)・・・右テーブルのフィールドを基準に、左テーブルの値入りフィールドのみ結合する

試験でも実務でもよく使われるのが①②。主役としたテーブルのフィールドは全表示される。

③は「主テーブルは左側」と決めてしまえば不要。

Microsoftとしては「左があるなら右も作っておくか」みたいな感覚かなあ。

【New】SELECT テーブル名.*　に, テーブル名.[フィールド名]

役割:SELECT句にあるテーブルに加え、結合先の他テーブルも表示させる。

詳細:

SELECT M_配信リスト.*, T_視聴.[視聴数(万回)]

【New】INNER JOIN句 

役割:

主テーブルと従テーブルの共通フィールドをもとに、従テーブルを主テーブルへ内部結合させる。

[主テーブル] INNER JOIN [従テーブル] ON [主テーブル.共通フィールド名] = [従テーブル.共通フィールド名]

詳細:

FROM M_配信リスト INNER JOIN T_視聴 ON M_配信リスト.話数 = T_視聴.話数

【7-5再掲】ORDER BY 句

ORDER BY テーブル名.[フィールド名] DESC (降順)またはASC (昇順);

役割:フィールド内のデータ表示並べ替え

詳細:

ORDER BY T_視聴.[視聴数(万回)] DESC;

視聴数フィールドを基準に、視聴数の降順(大きい順)でレコード(行)の並べ替えを行う。

【作成したSQL】

SELECT

M_配信リスト.*, T_視聴.[視聴数(万回)]

FROM

M_配信リスト INNER JOIN T_視聴 ON M_配信リスト.話数 = T_視聴.話数

ORDER BY

T_視聴.[視聴数(万回)] DESC;

【ORDER BY句に関する補足①】

T_視聴.[視聴数(万回)] DESC;

並べ替え用のフィールドを表示させデータシートビューで確認すると、

学習メモ7-5では[視聴数(万回)]フィールドが二重表記されビミョ～

なので非表示にしたが、今回それをやるとクエリが表示されない。

前回はマスタテーブル側に[視聴数(万回)]を持たせていたので

ORDER BY句側を非表示にしても

マスタテーブル側のフィールドがそれを補ってくれていたが

今回はORDER BY句を持つトランザクションテーブル側にしか

[視聴数(万回)]データが存在しないため、消してはいけない。

【ORDER BY句に関する補足②】

今回のSQL(クエリ)をもとにフォームを作る場合は、

補足①の[視聴数(万回)]フィールドの表示対応後におこなう。

非表示のままそれをレコードソースにしてしまうと

[視聴数(万回)]のコントロールソースが行方不明のままなので

フォームが作成できない。

クエリのデザイングリッドにて[視聴数(万回)]フィールドを表示設定すると

コントロールソースも無事に機能し始める。

完成したSQL(クエリ)のデータシートビューです。