質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.45%

  • SQL

    3093questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle

    703questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

  • PL/SQL

    85questions

    PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

  • Apex

    19questions

    Apexは、Salesforce上で動作するアプリケーション作成をサポートするアプリケーション開発プラットフォーム。プログラミング言語であるApexコードと、独自のApex WebサービスAPIなどで構成されています。

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 7,305

rx5rra

score 8

お世話になっております

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 を使用して月ごとに取得しておりましたが、週毎などになるととてつもなく大変なので変えたいと思っております。

--1ヶ月前
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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • alg

    2017/08/23 15:49 編集

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

    キャンセル

  • rx5rra

    2017/08/23 16:32

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

    キャンセル

回答 3

checkベストアンサー

+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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/28 14:20 編集

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

    キャンセル

+1

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
;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/28 14:18

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

    キャンセル

  • 2017/08/28 14:36

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

    キャンセル

  • 2017/08/28 15:42

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/23 16:03

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

    キャンセル

  • 2017/08/23 16:11

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

    キャンセル

  • 2017/08/23 16:16

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

    キャンセル

  • 2017/08/23 16:29 編集

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

    キャンセル

  • 2017/08/23 16:36

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.45%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • SQL

    3093questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle

    703questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

  • PL/SQL

    85questions

    PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

  • Apex

    19questions

    Apexは、Salesforce上で動作するアプリケーション作成をサポートするアプリケーション開発プラットフォーム。プログラミング言語であるApexコードと、独自のApex WebサービスAPIなどで構成されています。