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

Question

お世話になっております。
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 ...
─────────────
1   │2017/03│ xxx  
2   │2017/05│ xxx  
3   │2017/06│ xxx  
4   │2017/06│ xxx  
5   │2017/06│ xxx
...

こう集計したいです。

month   │ 合計数
───────────
2017/03 │ 1
2017/04 │ 0      ← この様に0件の行も取得したい。
2017/05 │ 1
2017/06 │ 9

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

Answer 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行は余裕で超えるかと。。。

Comments

[rx5rra]

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

[kitaji0306]

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

Answer 2

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

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
;

Comments

[rx5rra]

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

Answer 3

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

Comments

[rx5rra]

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

Answer 4

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

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

Comments

[rx5rra]

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