【SQL】指定期間の合計、ＭＡＸ値取得

質問

レンタルビデオ店や図書館で指定日範囲内の、貸出本数の合計の最大値を取得したいです。
※○月は○日が貸出ている本数が一番多かった…

テーブル上では８桁の日付と貸出本数を持っています。

例えば、下記データがテーブルに登録されている場合に、
where文で'20130101'～'20130131'と期間を指定すると'13'が抽出されます。

１月４日が１３本貸出中で、指定期間内で一番貸出本数が多いからです。

貸出日/返却日/本数
------------------------------------------
20130101/20130104/4
20130102/20130105/6
20130104/20130108/3
20130106/20130109/8
20130110/20130114/5
------------------------------------------
調べてみたのですが、指定日の最大値取得は見つかりましたが、指定期間での抽出方法がわかりませんでした。
使用環境はSQLServerです。

ご協力、お願致します。

回答No.3

root139

全ての貸出日もしくは全ての返却日を調べれば一番貸出本数が多い日が含まれるはずなので、それを利用すれば良いかと。

例) ------------------------------------
SELECT
MAX(合計本数) AS 合計本数
FROM
(
SELECT
(SELECT SUM(本数) FROM 貸出 b WHERE a.貸出日 BETWEEN b.貸出日 AND b.返却日) AS 合計本数
FROM
(SELECT DISTINCT 貸出日 FROM 貸出 WHERE 貸出日 BETWEEN 20130101 AND 20130131) a
) c;
----------------------------------------

別名aのサブクエリーで全ての貸出日(重複なし)の集合を取得しています。
その一つ外側で、全ての貸出日について合計の本数を算出しています。
そして、一番外側で合計の本数が一番多いものを抽出しています。
日付を絞り込むには、一番内側の別名aのサブクエリーで条件を指定すれば良いです。

回答No.2

Gaffgarion

追記。
指定期間内での一番貸出本数が多いのは、order byとlimit使った以下です。

select
calendar.d,sum(number)
from
calendar cross join test
where
calendar.d between test.start and test.end
group by
calendar.d
order by
sum(number) desc limit 1;

回答No.1

Gaffgarion

(1)貸出日返却日本数テーブルをtestとして作って例のデータをインサートします。
create table test(start date, end date,number int(4));

(2)カレンダーテーブル作って、日にちをインサートします。(ひとまず1/1～1/14)
create table calendar(d date);
insert into d values(20130101),(20130102),(20130103),(20130104),(20130105),
(20130106),(20130107),(20130108),(20130109),(20130110),(20130111),(20130112),
(20130113),(20130114);

(3)カレンダーとtestをクロス結合してムニャムニャ(うまく説明できない)。
select
calendar.d,sum(number)
from
calendar cross join test
where
calendar.d between test.start and test.end
group by
calendar.d;

※理解できなければ以下をまずやってみてください。
select * from calendar cross join test;

(5)結果
+------------+-------------+
| d | sum(number) |
+------------+-------------+
| 2013-01-01 | 4 |
| 2013-01-02 | 10 |
| 2013-01-03 | 10 |
| 2013-01-04 | 13 |
| 2013-01-05 | 9 |
| 2013-01-06 | 11 |
| 2013-01-07 | 11 |
| 2013-01-08 | 11 |
| 2013-01-09 | 8 |
| 2013-01-10 | 5 |
| 2013-01-11 | 5 |
| 2013-01-12 | 5 |
| 2013-01-13 | 5 |
| 2013-01-14 | 5 |
+------------+-------------+

手元にあったのがmysqlだったのでmysqlで試しました。
カレンダーテーブルを事前に作ってますが、テンポラリに作って1行で済ませることもできそうです(多分)。
また、そもそもカレンダーテーブル使わなくてもできそうです。
できるんだったら、そのやり方を知りたいですねー