日付が抜け落ちている時もカウントしたい

Question

以下のようなデータベースがあるとします

id	date
1	2012-04-17
1	2012-04-18
1	2012-04-20
1	2012-04-21
1	2012-04-22

日付順にid を加算した値を取得したいのですが残念ながら 2012-04-19 が抜けているため、普通にやったらこのように 2012-04-19 がかけた値が結果が帰ってきます。

sum	date
1	2012-04-17
2	2012-04-18
3	2012-04-20
4	2012-04-21
5	2012-04-22

ですが僕が欲しいのは

sum	date
1	2012-04-17
2	2012-04-18
2	2012-04-19
3	2012-04-20
4	2012-04-21
5	2012-04-22

こんな風に、かけている日付を補って、１つ前の sum を表示するというコードを挿入するものです。どのようにしたらこの結果をえることができますか？

こちらは実行したコードです

select
	(
		select 
			sum(sub.id) 
		from id_date as sub 
		where sub.date <= id_date.date
	) as sum
	,date
from id_date

Comments

[mattn]

この解は連番を作る機能が無いと実現できません。連番を作る機能は DBMS により異なります。条件を質問文に含めて下さい。

[jm1156]

別途、テーブルは作成できますか？　作成できるのであれば、連続した日付が入ったテーブルを用意して、JOINすればいけそうな気がします。（ためしてないので、回答ではなくこちらに書きますね）

[bouyomisan]

mttn さん、jm1156さん、すみません今、mttn さんの連番について書いている最中でした。連番 mysqlでも作成できるようです。応用して、連続した日付は作成できるので、それでカバーできないでしょうか？

Answer 1

こんにちは。

連続した日付のテーブルをdate_range(clnd)として、番号はsum()ではなくcount()を使用。

select 
      (select count(*) from ID_DATE where date<=date_range.clnd) no
     , clnd
from date_range
order by clnd

Answer 2

例えばcalendar_masterという連続した日付のテーブルを作成して、

ID calendar_date
1 2012/04/17
2 2012/04/18
3 2012/04/19
4 2012/04/20
5 2012/04/21
6 2012/04/22

↓こんなかんじでしょうか？

SELECT
		  ( 
			SELECT
					  SUM(SUB.ID) 
				FROM
					ID_DATE AS SUB 
				WHERE
					SUB.日付 <= CALENDAR_MASTER.CALENDAR_DATE
		) AS SUM
		, CALENDAR_MASTER.CALENDAR_DATE 
	FROM
		CALENDAR_MASTER 
		LEFT JOIN ID_DATE 
			ON CALENDAR_MASTER.CALENDAR_DATE = ID_DATE.日付

MySQLの環境がなかったので、Accessで試しました。
(dateが予約語だったので、「日付」というカラム名にしてます）
↓結果
sum calendar_date
1 2012/04/17
2 2012/04/18
2 2012/04/19
3 2012/04/20
4 2012/04/21
5 2012/04/22