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

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

ただいまの
回答率

87.58%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,645

score 13

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

現在、テーブル上に存在する日付ごとの入力データを月別集計データにして抽出して出力しようとしているのですが、データのある箇所のみの抽出しかできておらず困っています。
値が存在しない歯抜けの部分に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を始めたてでわかりにくい部分もあるかもしれませんがご教授いただけますとうれしいです。よろしくお願いします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

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)()を使えば良さそうかなと。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/09/10 11:15

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

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

    キャンセル

0

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')

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/09/10 11:51

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

    キャンセル

  • 2018/09/16 15:41

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

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

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

    キャンセル

  • 2018/09/16 22:07 編集

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

    キャンセル

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

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

関連した質問

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