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

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

ただいまの
回答率

90.87%

  • PostgreSQL

    904questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

PostgreSQL データの存在しない出力結果を0で埋めたい。

受付中

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 607

souji

score 0

PostgreSQL データの存在しない出力結果を0で埋めたい。

テーブルに存在しない、SQLの出力結果を0で埋めたいと考えております。
以下の様なSQLを作成したのですが、理想とする結果になりません。

SELECT
l.place_id,
sum(case when (l.type = '01') then 1 else 0 end ) as a1,
sum(case when (l.type != '01') then 1 else 0 end ) as a2,
sum(case when (l.type = '02') then 1 else 0 end ) as a3,
sum(case when (l.type = '03') then 1 else 0 end ) as a4,
sum(case when (l.out in ('02','03') ) then 1 else 0 end ) as a5
FROM
l_test l
WHERE
l.day >= '20150441'
and l.day <= '20160331'
GROUP BY
l.place_id
ORDER BY
l.place_id;

出力される結果(結果の値は編集しています。)
place_id |   a1 |   a2 |   a3 |   a4 |   a5
----------+------+------+------+------+------
1 |    6 |    5 |    4 |    3 |    2
4 |    1 |    1 |    1 |    1 |    1
8 |    2 |    3 |    4 |    5 |    6

理想とする結果

place_id |   a1 |   a2 |   a3 |   a4 |   a5
----------+------+------+------+------+------
1 |    6 |    5 |    4 |    3 |    2
2 |    0 |    0 |    0 |    0 |    0
3 |    0 |    0 |    0 |    0 |    0
4 |    1 |    1 |    1 |    1 |    1
5 |    0 |    0 |    0 |    0 |    0
6 |    0 |    0 |    0 |    0 |    0
7 |    0 |    0 |    0 |    0 |    0
8 |    2 |    3 |    4 |    5 |    6
9 |    0 |    0 |    0 |    0 |    0

別テーブルにpriceというテーブルが存在します。

priceテーブル
id | name |
----+------+
1 | 青森 |
2 | 秋田 |
3 | 岩手 |
4 | 山形 |
5 | 宮城 |
6 | 福島 |
7 | 東京 |
8 | 京都 |
9 | 大阪 |

lenテーブルに存在しないplace_id(priceのid)データを0で埋めるようなSQLは、作成可能なのでしょうか。
ご回答お願い致します。

また、回答するにあたって不足情報が有りましたらご指摘お願い致します。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

+1

placeテーブルとしました。

select p.id as place_id,
sum(case when (l.type = '01') then 1 else 0 end ) as a1,
sum(case when (l.type != '01') then 1 else 0 end ) as a2,
sum(case when (l.type = '02') then 1 else 0 end ) as a3,
sum(case when (l.type = '03') then 1 else 0 end ) as a4,
sum(case when (l.out in ('02','03') ) then 1 else 0 end ) as a5
FROM
l_test l
RIGHT JOIN place p ON p.id=l.place_id
AND l.day between '20150441' and '20160331'
GROUP BY
p.id
ORDER BY
p.id;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

placeってテーブルにidが全部入っている、という前提で。

SELECT
  p.id, coalesce(l.a1, 0) as a1, coalesce(l.a2, 0) as a2, coalesce(l.a3, 0) as a3, coalesce(l.a4, 0) as a4, coalesce(l.a5, 0) as a5
FROM (
  SELECT place_id,
    sum(case when (l.type = '01') then 1 else 0 end ) as a1,
    sum(case when (l.type != '01') then 1 else 0 end ) as a2,
    sum(case when (l.type = '02') then 1 else 0 end ) as a3,
    sum(case when (l.type = '03') then 1 else 0 end ) as a4,
    sum(case when (l.out in ('02','03') ) then 1 else 0 end ) as a5
  FROM l_test
  WHERE
    l.day >= '20150441' and l.day <= '20160331'
  GROUP BY l.place_id
) as l
RIGHT JOIN 
  place as p on l.place_id = p.id
ORDER BY
  p.id; 

って形でしょうか。
A.Ichi さんの回答との違いは、
A.Ichi さんのは全行連結してからgroup byしているのに対して、
私のほうが先にgroup byを使って集約した結果を作って、
それにplaceテーブルを結合させていることです。
group byする対象のテーブルのレコード数が膨大になっていると
全行連結してからのgroup byでは必要とするメモリが多くなりがちなので、
先にgroup byしてしまう方が良いと考えました。

coalesce()は結果がNULLだったときに代理で値を与えたりするのに便利です。

参考:9.17. 条件式
http://www.postgresql.jp/document/9.6/html/functions-conditional.html

余談ですが、結合先のテーブルがない場合は、generate_series(start, stop) が有効です。

参考:generate_series()
http://www.postgresql.jp/document/9.6/html/functions-srf.html

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

関連した質問

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

  • PostgreSQL

    904questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。