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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

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

Q&A

解決済

2回答

5724閲覧

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

souji

総合スコア7

PostgreSQL

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

0グッド

0クリップ

投稿2017/01/06 09:11

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は、作成可能なのでしょうか。
ご回答お願い致します。

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

placeテーブルとしました。

SQL

1select p.id as place_id, 2sum(case when (l.type = '01') then 1 else 0 end ) as a1, 3sum(case when (l.type != '01') then 1 else 0 end ) as a2, 4sum(case when (l.type = '02') then 1 else 0 end ) as a3, 5sum(case when (l.type = '03') then 1 else 0 end ) as a4, 6sum(case when (l.out in ('02','03') ) then 1 else 0 end ) as a5 7FROM 8l_test l 9RIGHT JOIN place p ON p.id=l.place_id 10AND l.day between '20150441' and '20160331' 11GROUP BY 12p.id 13ORDER BY 14p.id;

投稿2017/01/06 09:46

A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

sql

1SELECT 2 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 3FROM ( 4 SELECT place_id, 5 sum(case when (l.type = '01') then 1 else 0 end ) as a1, 6 sum(case when (l.type != '01') then 1 else 0 end ) as a2, 7 sum(case when (l.type = '02') then 1 else 0 end ) as a3, 8 sum(case when (l.type = '03') then 1 else 0 end ) as a4, 9 sum(case when (l.out in ('02','03') ) then 1 else 0 end ) as a5 10 FROM l_test 11 WHERE 12 l.day >= '20150441' and l.day <= '20160331' 13 GROUP BY l.place_id 14) as l 15RIGHT JOIN 16 place as p on l.place_id = p.id 17ORDER BY 18 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

投稿2017/01/06 09:49

編集2017/01/06 10:07
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問