MS Access Tips/Sample and VBA and Blog customize etc...

クエリで期間内最大在室数を求める

入退室管理のためにこのようなテーブルがあります。

T_在室
在室ID入室時刻退室時刻
112/01 10:0012/01 14:10
212/01 17:1012/01 21:20
312/01 12:3012/01 19:00

このとき、11:00~20:00の間の最大人数をSQLで簡単に求める方法はあるでしょうか。

入退室ガントチャート図

欲しい答えは2名なのですが、COUNTなどで単純に条件抽出すると3名になってしまいます。

かなり前に掲示板であった質問です。他にも、ログイン時刻、ログアウト時刻、が記録してあって最大同時アクセス数を取得したいとか、レンタルの貸し出し履歴から最大貸し出し数を取得したいとかいった要件は結構ありそうです。

難易度:

上記のテーブルを見ると入退室時刻は10分刻みのようです。もし、そのようなデータなら、

T_Num
Num
0
1
9999

というようなテーブルを作成して、このテーブルから10分刻み時刻を計算させて、データテーブルと直積クエリを作成すれば、10分刻みの時刻ごとに在室数人数を出力できます。

このクエリより最大値を求める集計クエリを作成すれば、最大在室数は取得できます。

しかし、入退室時刻が、10分刻みではなく、分刻み、あるいは、秒単位まで格納されていて、瞬間最大在室数を求めたいとなると、秒単位の時刻レコードセットを用意して直積ということになりますので、非常に重くなりそうです。

しかし、期間内全ての時刻レコードセットを別に用意しなくても、元データから、在室数の増える境界の時刻、つまり、入室時刻だけを取り出せば、それで代用できるはずです。

■1段目クエリ Q_時刻

DISTINCT で重複を排除しています。
フォーム(Forms!F期間)から期間を入力する仕様にしてます。

■2段目クエリ
このクエリと元テーブルから直積で、時刻毎の在室数を集計します。

Q_在室数

これで入室時刻毎の在室数を取得できます。在室数の変化(減少時も含めて)を取得するには、Q_時刻に、退室時刻もユニオンクエリで結合しておく必用がありますが、最大在室数を求めるには不要なのでいれてません。

■3段目クエリ
2段目クエリの在室数の降順 TOP 1(最大件数)を取り出します。

これを、一つのSQLに纏めると、

下記のSQLでも同じ最大在室数が得られます。

後者のSQLの方がシンプルですが、入室時刻に重複がある場合は前者の方が若干高速です。重複を排除してから直積してますので。逆に重複がなかったり少なければ、後者の方が高速だと思われます。


拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/281-e5470da3
該当の記事は見つかりませんでした。