postgresで歯抜けとなっている状態のデータを補完しつつ取得したい

Question

閲覧いただきありがとうございます。

現在、テーブル上に存在する日付ごとの入力データを月別集計データにして抽出して出力しようとしているのですが、データのある箇所のみの抽出しかできておらず困っています。
値が存在しない歯抜けの部分に0と挿入したいのですがよい方法はありますでしょうか。
列はそれぞれdate型、bigint型となっています。

テーブルの状態

id	date	insert_count
1	2018-01-02	30
2	2018-01-07	70
3	2018-02-01	30
4	2018-02-04	100
5	2018-02-11	60
6	2018-02-20	110
7	2018-04-02	30
8	2018-04-13	70
9	2018-07-05	150
10	2018-07-12	70
11	2018-07-20	110
11	2018-07-27	70

現在取得できている状態

ym	count
201801	100
201802	300
201804	100
201807	400

理想

ym	count
201801	100
201802	300
201803	0
201804	100
201805	0
201806	0
201807	400

上記のデータを取得しているSQL文

実行SQL

SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count　
FROM orders  WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm')

試したこと

--対象となる年の月毎データを生成してLEFT JOINしてみる --
SELECT '201801' + s.i as months , result.count FROM generate_series(0,11) AS s(i) 
LEFT JOIN (
SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count　
FROM orders  WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm')
) AS result ON s.i::text = result.ym

-- 生成したマスタデータはInteger型で素の状態だと結合できないため明示的にtext型にしています --

ですので上記のようにマスタデータを生成して実行しようとすると初めのSELECT内でのs.iが使えず（GROUP BYしろというエラー）、どうすればいいのでしょうというのが現状です。

補足情報

ではs.iを除いてデータだけでも確認してみようと思いSELECTでデータのある月を確認してみるとなぜかデータが取得できておらずそもそもがダメそうな感じもしています

実行SQL

SELECT '201801' as months , result.count FROM generate_series(0,11) AS s(i) 
LEFT JOIN (
SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count　
FROM orders  WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm')
) AS result ON s.i::text = result.ym

結果

months	count
201801	0

monthsの部分がunknown型、countの部分がbigint型となっています。

---

postgresを始めたてでわかりにくい部分もあるかもしれませんがご教授いただけますとうれしいです。よろしくお願いします。

Answer 1

generate_series()を使用するのは、m6uさんと同じなのですけど、質問にあるSQLは文法エラーだったり、書式（%yとか）がMySQLのものだったりしています。（dateは予約語なので項目名としては相応しくありませんよ）

以下は、開始の日付と取得する年数をパラメータとして、取得するサンプルです。

with para as(
 select '2018-01-01'::date as start_date, '1'::text as range
)
, ymd as (
 select generate_series(start_date, start_date + (range || ' year')::interval, '+1 month')::date as ymd from para
)
SELECT to_char(ymd, 'YYYYmm') as ym, SUM(coalesce(insert_count,0)) as count　
FROM ymd left join orders on ymd.ymd=orders.date 
GROUP BY to_char(ymd, 'YYYYmm')

Comments

[退会済みユーザー]

あぁ、%Yってそういうことかぁ

[jee]

%yはMySQLの記述法なんですね。お恥ずかしい…
いろいろなRDBMSのサイトを参考にしながら作成していたので気づきませんでした。ありがとうございます！

[sazi]

%yの指定だと意図する結果にはなりません。
テストするサンプルが少なすぎるから検知できていないのです。

[sazi]

to_char(DATE '2018-01-01', '%y')
上記部分は文法エラーですけど、エラーにはなりませんか？

[sazi]

テストデータを増やさないなら、せめて条件部分の記述をselectして意図通りかを確認したほうが良いですね。

[jee]

返信ありがとうございます。
pgAdmin IIIにて実行しているのですが、初めに月別データを取得するSQLとして掲載した文はエラーを吐かずに通って結果が取得できていたので間違いであることに気づきませんでした。

[退会済みユーザー]

select句での集合関数の使用は、非推奨機能で削除予定ですので、マイナス投票します。

https://www.postgresql.jp/document/9.6/html/xfunc-sql.html

>現在、集合を返す関数は問い合わせの選択リスト内でも呼び出すことができます。
>問い合わせ自身によって生成する各行に対し、集合を返す関数が呼び出され、
>関数の結果集合の各要素に対して出力行が生成されます。
>ただし、この機能は現在では推奨されておらず、今後のリリースでは削除される可能性があります

[sazi]

>sql_loverさん
削除される可能性があるから非推奨ということで、削除予定ではありません。
同じ集合関数であるgenerate_subscripts()などは基本的な例ではselect で紹介されています。
集合関数と似た動きのunnest()などは複数項目がある場合には、selectで使用したりしますので、
動作に問題があるという訳ではなく、バージョンアップした場合の移植に手間が発生するという事ですし、可読性を優先させるような場合には、あまり気にする必要は無いかと思いますけどね。

Answer 2

9.24. 集合を返す関数
にある、generate_series()を駆使すれば、連番で値の集合を作れます。

select to_char(s.a, 'YYYYMM') as ym
from (
  select * from generate_series('2018-01-01'::timestamp, '2018-12-01'::timestamp, '+1 month'::interval)
) as s(a)

このテーブルに対してleft joinすればよいです。
値なないことを想定してcoalesce(result.count, 0)などとします。

select d.ym, coalesce(r.count, 0) as count
from (
  select to_char(s.a, 'YYYYMM') as ym
  from (
    select * from generate_series('2018-01-01'::timestamp, '2018-12-01'::timestamp, '+1 month'::interval)
  ) as s(a)
) as d
left join (
  SELECT to_char(orders.date, 'YYYYMM') as ym, SUM(orders.insert_count) as count
  FROM orders
  WHERE date_part('year', orders.date) = 2018
  GROUP BY to_char(orders.date, 'YYYYMM')
) as r on d.ym = r.ym 

で動きますでしょうか。（動作テストできてませんけど。）
2018年のデータだけ集計したいように見えたので、
date_part(text, timestamp)()を使えば良さそうかなと。

Comments

[jee]

回答ありがとうございます。
提示してくださったSQLで実行でき、動作も確認できました！ありがとうございます。

値のない部分には0を追加していくという処理にすればいいんですね