SQL ある範囲の月ごとのその月までの合計値の取得

Question

お世話になっております

Oracle12 SQLを使用して、ある月からその月までの残課題数の合計を月毎に取得するSQLを書きたいと考えているのですが、これはSQLのみで実現できますでしょうか？

具体的には、ある期間を『1月～3月』と指定したとすると

--データテーブル
ID, 起票日    , 完了日
1,  2016/12/01, 
2,  2017/01/01, 2017/01/02  
3,  2017/02/01, 
4,  2017/02/02,
5,  2017/02/01, 2017/03/01
6,  2017/03/01,
7,  2017/03/02, 2017/04/01
8,  2017/04/01,

というデータがあったとしますと、

月     , 残課題
2017/01, 0
2017/02, 3
2017/03, 4

この様に 1月は1月の、2月は1月2月の、3月は1月3月の結果を返すSQLを書きたいと考えております。

月が範囲ではなく、ひと月であればこのように書けるのですが、月の範囲が動的に変動する場合にどうすればいいのかがわかりません。
最初は、union all を使用して月ごとに取得しておりましたが、週毎などになるととてつもなく大変なので変えたいと思っております。

--１ヶ月前
select to_char(add_months(current_date,-1),'YYYY/MM') month, count (ID) 残課題
fromデータテーブル tbl
where 
起票日 < to_date(last_day(add_months(to_char(current_date,'YYYY/MM/DD'),-1)))+1
  and (完了月 > last_day(add_months(to_char(current_date,'YYYY/MM/DD'),-1)) or 完了日 is null)

また、PL/SQLを使用すればシンプルに実現可能だったりしますでしょうか？

※加筆修正しました

※解決したSQLを参考で記載しておきます。

with calendar
  month
) as (
  select to_char(sysdate -3, 'YYYY/MM')
  from ( 
    select level lel from dual connect by level <= 3
  )
)
select cal.month month, (
  select count (*)
  from TABLE tbl
  where to_char(tbl.起票日,'YYYY/MM') <= cal.month
  and (
    完了日 is null
    or to_char(tbl.完了日, 'YYYY/MM') > cal.month
  )
) 残課題
from calendar cal
order by month

Comments

[alg]

以下の情報が追記されると、回答が付きやすくなるかと思われます。 　①データが格納されているテーブルの定義（列名はわかりますが型が不明です。起票"日"なのに年月？） 　②「残課題」の定義（完了日が空であれば残課題とみなす？）

[rx5rra]

ご指摘ありがとうございます。　質問を編集させて頂きました。

Answer 1

月毎の集計を取りたい場合は、月の末日のみ登録してあるテーブルを用意しておくと便利です。
ここでは、eom_tableにeom(EndOfMonth)カラムがあるものとします。
未検証ですが、だいたいこんな感じで

SELECT TO_CHAR(t1.eom, 'YYYY/MM') 年月, 
(
    SELECT COUNT(1)
    FROM "課題"
    WHERE TO_CHAR(t1.eom, 'YYYY/MM') >= TO_CHAR("起票日", 'YYYY/MM') 
        AND ("完了日" IS NULL OR "完了日" > t1.eom)
) 残課題
FROM eom_table t1
WHERE t1.eom > @date_from AND t1.eom <= @date_to

Comments

[rx5rra]

ありがとうございます。
回答を参考にwith句でカレンダーを作りサブクエリで問い合わせることで実現できました。
また、カレンダー用のテーブルは用意できませんでしたが、次回以降この様なシチュエーションがあった場合に、提案してみたいと思います。

Answer 2

Oracle環境が無いのでcross joinnの書き方が違うかもしれませんのでご参考まで

select tb1.f1, sum(tb2.zan) from
(select distinct f1 from hoge where f1 between '2017/01' and '2017/03') tb1
cross join
( select f1, 1 zan from hoge where f2 is null or f1!=f2
  union all
  select f2, -1 zan from hoge where f2 is not null) tb2
where tb1.f1 >= tb2.f1
group by tb1.f1
order by tb1.f1
;

Comments

[rx5rra]

ありがとうございます。
質問が悪くて申し訳ありません。日付に対する件数ですのでcross joinを使うわけではありませんでした。
しかし、これはいつか使えそうなケースが出てきそうなので調べて覚えておきたいと思います。

[A.Ichi]

postgreとmysqlでは上記結果が得られたので、Oracleもと思いましたが違う様ですね。お手数掛けてすみませんでした。

[rx5rra]

誤解させてしまっていたら申し訳ありません、A.Ichi様のSQLは正しく動作しますが、（私の質問が悪かった為に）質問の内容に沿う内容では無かったというニュアンスです。
次回以降があれば、もっと簡潔にわかりやすく質問できるように気をつけたいと思います。

Answer 3

「起票日」と「月」の相関関係がよくわかりませんが同じものを示しているなら
起票日でgroup byして、完了日がNULLでない（もしくは空でない）ものを
countすればよいのでは？

Comments

[rx5rra]

ありがとうございます
抜粋して書いたために、わかりにくくて申し訳ありませんが
1月なら1月から1月まで、2月なら1月から２月まで、3月なら１月から3月までの数をカウントしたかったのです。

[yambejp]

ちなみに仕様について確認してよいでしょうか？
1月の残課題は完了日がすでにうまっているから0件なんですよね？
2月の残課題は埋まっていないのは2件ですが、命題だと3件になっています
ロジックはどう判断すればよいでしょうか？（3月も同様）

[ryuya_asami]

"NULL以外を数える"ではなく"NULLを数える"だからじゃないでしょうか？
残課題数なので、完了している物を数えたらダメな気が・・・

[rx5rra]

返信ありがとうございます。
２月の結果は、２月時点で、完了していない結果を出したいと考えております。
完了日は埋まっておりますが、３月となっているので２月時点ではしていないというニュアンスです。

[rx5rra]

＞ryuya_asami様
表現が悪くて申し訳ありません、2月は2月時点での、完了していない課題を取得したいと考えておりますので、nullを数えますと、翌月以降に完了したものもカウントされてしまいます。