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

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

ただいまの
回答率

90.45%

  • SQL

    3094questions

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

  • Oracle

    704questions

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

Oracle12 SQL group byしてcountした結果に0件の結果も含めたい

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 2,628

rx5rra

score 8

お世話になっております。
Oracle12を使用して、表題のことを実現したいのですが、group byは0件を集計しないので、困っております。
月の表を作りJOINすると言うのも考えましたが、月をずらっと並べただけの表はなんだか不自然に感じています。

select to_char(tl.DATE, 'YYYY/MM') month, count(NUM) as 合計数
from テーブル tl
where to_char(tl.DATE, 'YYYY/MM') > to_char(ADD_MONTH(SYSDATE, -6), 'YYYY/MM')
group by to_char(tl.DATE, 'YYYY/MM') month
order by month


この様なイメージの表を

NUM │ DATE  │ xxxx ...
─────────────
12017/03│ xxx  
22017/05│ xxx  
32017/06│ xxx  
42017/06│ xxx  
52017/06│ xxx
...


こう集計したいです。

month   │ 合計数
───────────
2017/031
2017/040      ← この様に0件の行も取得したい。
2017/051
2017/069

この場合、どのような方法が考えられますでしょうか?

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 4

checkベストアンサー

+1

6ヶ月分の空の表か擬似的な表をjoinすればいいじゃないですか?
雰囲気はこんな感じでしょうか。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/08/22 16:55

    ありがとうございます。
    LEVEL擬似列は初めて聞きました。
    条件を指定していない木構造のレベルを取得すると、ほぼリスト状になっているので連番になるということなのですか。
    試してみたいと思います。

    キャンセル

+1

月をずらっと並べただけの表はなんだか不自然に感じています。

そんなことないですよ、カレンダーテーブルは普通の仕様です
oracleの細かい仕様は理解していませんがプロシージャのような
仕組みで半自動化することもできると思います

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/08/22 16:48

    ありがとうございます。
    カレンダーテーブルは一般的なのですね。
    管理するテーブルが増えるのが違和感でしたが、調べてみたいと思います!

    キャンセル

+1

共通表式を使ってみてはいかがでしょうか。

WITH cal (
    mon
)
AS (
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -5), 'YYYY/MM') FROM DUAL UNION ALL
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -4), 'YYYY/MM') FROM DUAL UNION ALL
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'YYYY/MM') FROM DUAL UNION ALL
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'YYYY/MM') FROM DUAL UNION ALL
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY/MM') FROM DUAL UNION ALL
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -0), 'YYYY/MM') FROM DUAL
)
SELECT
    cal.mon
    ,COUNT(num) AS 合計
FROM
    cal
    LEFT OUTER JOIN テーブル tl
        ON cal.mon = TO_CHAR(tl.date, 'YYYY/MM')
GROUP BY
    cal.mon
ORDER BY
    cal.mon
;

再帰を使って記述することもできます。

WITH cal (
    ctr
    ,mon
)
AS(
    SELECT
        1 AS ctr
        ,TO_CHAR(SYSDATE, 'YYYY/MM')
    FROM
        DUAL
    UNION ALL
    SELECT
        cal.ctr + 1 AS ctr
        ,TO_CHAR(ADD_MONTHS(SYSDATE, -ctr), 'YYYY/MM')
    FROM
        cal
    WHERE
        cal.ctr <= 5
)
SELECT
    cal.mon
    ,COUNT(num) AS 合計
FROM
    cal
    LEFT OUTER JOIN テーブル tl
        ON cal.mon = TO_CHAR(tl.date, 'YYYY/MM')
GROUP BY
    cal.mon
ORDER BY
    cal.mon
;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/08/22 17:21

    ありがとうございます。
    全文サンプル大変参考になります。
    仮想的な表を使う際はwith句を使ったほうが良さそうですね。
    調べて使ってみたいと思います

    キャンセル

+1

Oracleならrownumを利用することで、必ずレコード分の昇順の数値が得られるので、
下記のような方法で、カレンダー部分を生成してはいかがでしょうか?

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, - rnum), 'YYYY/MM') month
FROM (
  SELECT rownum rnum
  FROM all_catalog -- 6行以上あるはずのテーブル
  WHERE rownum <= 6
)
ORDER BY month

あとは、上記を@SVC34 の通りWITH句に入れてしまえばいかと。
※6行以上あるはずのテーブル部分に不確定要素が含まれるのがいやらしいかもしれませんが
all_catalog システムビューは通常は6行は余裕で超えるかと。。。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/08/22 17:19

    ありがとうございます。
    最初、all_catalogがわかりませんでしたが、oracleDBの管理テーブルの様なものなのですね。
    これならoracleならほぼ必ず存在して、行数もまず大丈夫そうですね!
    ただ、LEVEL疑似列と比べるとレスポンスが少々悪い?らしいのでもう少し調べてみたいと思います。

    キャンセル

  • 2017/08/22 17:36

    そうですね、システムビューとはいえ、内部で表を参照してしまうよりは
    LEVEL疑似列の方がパフォーマンスはいいと思います。
    (再帰クエリとしての用途でしかCONNECT BYを知らなかったのですが、LEVEL疑似列の方法なら実票を参照しないので早いと思います。)

    キャンセル

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

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

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

  • SQL

    3094questions

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

  • Oracle

    704questions

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