閲覧いただきありがとうございます。
現在、テーブル上に存在する日付ごとの入力データを月別集計データにして抽出して出力しようとしているのですが、データのある箇所のみの抽出しかできておらず困っています。
値が存在しない歯抜けの部分に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
sql
1SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count 2FROM orders WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm')
試したこと
sql
1--対象となる年の月毎データを生成してLEFT JOINしてみる -- 2SELECT '201801' + s.i as months , result.count FROM generate_series(0,11) AS s(i) 3LEFT JOIN ( 4SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count 5FROM orders WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm') 6) AS result ON s.i::text = result.ym 7 8-- 生成したマスタデータはInteger型で素の状態だと結合できないため明示的にtext型にしています --
ですので上記のようにマスタデータを生成して実行しようとすると初めのSELECT内でのs.iが使えず(GROUP BYしろというエラー)、どうすればいいのでしょうというのが現状です。
補足情報
ではs.iを除いてデータだけでも確認してみようと思いSELECTでデータのある月を確認してみるとなぜかデータが取得できておらずそもそもがダメそうな感じもしています
実行SQL
sql
1SELECT '201801' as months , result.count FROM generate_series(0,11) AS s(i) 2LEFT JOIN ( 3SELECT to_char(date, 'YYYYmm') as ym, SUM(insert_count) as count 4FROM orders WHERE to_char(date, '%y') = to_char(DATE '2018-01-01', '%y') GROUP BY to_char(date, 'YYYYmm') 5) AS result ON s.i::text = result.ym
結果
months | count |
---|---|
201801 | 0 |
monthsの部分がunknown型、countの部分がbigint型となっています。
postgresを始めたてでわかりにくい部分もあるかもしれませんがご教授いただけますとうれしいです。よろしくお願いします。
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2018/09/10 01:41
2018/09/10 02:20
2018/09/10 02:25
2018/09/10 02:26
2018/09/10 02:30
2018/09/10 02:51
退会済みユーザー
2018/09/16 06:41
2018/09/16 13:34 編集