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

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

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

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

1628閲覧

SQLにてIDごとのカウント結果をまとめて一つの表にする

inu_inu

総合スコア19

PostgreSQL

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/02/22 00:27

編集2019/02/22 02:26

お世話になっております。
よろしくお願いします。

やりたいこと

下記①②のような表があったとき、IDごとに特定のNumberのカウントを取得するビュー③を作成したいです。
※①のテーブルではNumber=1~3, ②のテーブルではNumber=12だけカウント
①元テーブル1

IDNumber1
田中1
高橋2
高橋4
斉藤1
斉藤3
斉藤3
斉藤12

②元テーブル2

IDNumber2
田中3
2
12
12

③作成したいビュー

IDcountOf_1countOf_2countOf_3countOf_12
田中1000
高橋0100
斉藤1020
0002

試したこと

IDごとのカウントは下記二つの方法で試してみて、取得できました。

SQL

1※方法1 2SELECT DISTINCT t1.ID, COUNT(*) AS countOf_1 3FROM 元テーブル1 t1 4WHERE t1.Number = 1 5GROUP BY t1.ID; 6※別Numberや元テーブル2の取得はFROM句とWHERE句を変更するだけなので省略します

SQL

1※方法2 2①元テーブル1 3SELECT DISTINCT t1.ID, 4 COUNT(CASE WHEN t1.Number = 1 THEN 'X' ELSE NULL END) AS countOf_1, 5 COUNT(CASE WHEN t1.Number = 2 THEN 'X' ELSE NULL END) AS countOf_2, 6FROM 元テーブル1 t1 7GROUP BY t1.ID; 8 9②元テーブル2 10SELECT DISTINCT t2.ID, 11 COUNT(CASE WHEN t2.Number = 3 THEN 'X' ELSE NULL END) AS countOf_3 12FROM 元テーブル2 t2 13GROUP BY t2.ID;

方法1だと、それぞれで取得した表を一つにまとめることができませんでした。
調べた結果、外部結合で実施できるのだと思いましたが、どう書けばいいかわかりませんでした。

また方法2だと、テーブル1から取得した結果は一つの表にまとめられましたが、テーブル2から取得した結果を結合することができませんでした。

ご教授のほど、よろしくお願いします。
追記

正しい例を提示していませんでした。申し訳ございません。
元テーブル1と元テーブル2のカラム"Number"は実際には違うカラム名となります。
またyambejp様、sazi様のご回答を参考に実行すると結合された表は作成されるのですが、countof_1~4のsum結果が実際と異なってしまいました。
コードを記載します(※また実際と異なるといけないため、条件など実際の環境に合わせたコードを載せています)

SQL

1select coalesce(tbl1.ID, tbl2.ID) as ID 2 , sum(case tbl1.Number1 when 1 then 1 else 0 end) as countOf_1 3 , sum(case tbl1.Number1 when 2 then 1 else 0 end) as countOf_2 4 , sum(case tbl1.Number1 when 3 then 1 else 0 end) as countOf_3 5 , sum(case tbl2.Number2 when 12 then 1 else 0 end) as countOf_12 6from TABLE1 tbl1 full join TABLE2 tbl2 7 on tbl1.ID = tbl2.ID 8 and tbl1.Number1 in (1,2,3) 9 and tbl2.Number2 = 12 10group by coalesce(tbl1.ID, tbl2.ID) 11order by coalesce(tbl1.ID, tbl2.ID);

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

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

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

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

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

guest

回答3

0

union か full join ですね。
union はyambejpさんが回答されているので、full joinを。
データはyambejpさんのをお借りしてます。

SQL

1select coalesce(tbl1.id, tbl2.id) as id 2 , sum(case tbl1.Number when 1 then 1 else 0 end) as countOf_1 3 , sum(case tbl1.Number when 2 then 1 else 0 end) as countOf_2 4 , sum(case tbl2.Number when 3 then 1 else 0 end) as countOf_3 5from tbl1 full join tbl2 6 on tbl1.id = tbl2.id 7 and tbl1.number in (1,2) 8 and tbl2.number = 3 9group by coalesce(tbl1.id, tbl2.id) 10order by coalesce(tbl1.id, tbl2.id)

投稿2019/02/22 01:37

編集2019/02/22 01:39
sazi

総合スコア25085

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

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

inu_inu

2019/02/22 02:09

申し訳ございませんが提示させていただいた例に誤りがありました。 また誤りを修正してsazi様のご回答いただいた内容を実施してみましたが、カウント数が実際と異なるようでした。(例と実際が異なることが原因と思います) 正しい内容や別途試した内容を追記しています。よろしければ、ご確認のほどお願いします。
sazi

2019/02/22 02:14

質問のテーブルと得たい結果のデータサンプルも変更して下さい。
inu_inu

2019/02/22 02:25 編集

失礼いたしました。テーブルについても修正させていただきました。もともと記載していた表部分を修正しています。
sazi

2019/02/22 02:38

質問に追加されたSQLの結果については、作成したいビューとIDの並びは違いますが、結果はおかしくないのですが?
inu_inu

2019/02/22 03:02

countOf_12が0ではない場合、値が崩れるようです… countOf_12が0であると正しく表示されます。 sazi様の環境で正しく表示されるのであれば、どこかで記載を間違えているのかもしれませんね、確認してみます。
sazi

2019/02/22 04:02

>, sum(case tbl2.Number2 when 12 then 1 else 0 end) as countOf_12 思いつくのは、上記の部分の実際の記述で、参照しているテーブルとカラム名が誤っている位ですかね。
inu_inu

2019/02/22 04:18

見直し・書き直ししてみましたが、やはり合いませんでした… また、改めて結果を見るとcoutof_1~3,12のすべてが0になっているレコードがありました。 countOf_12以外でも書き誤っている可能性もあるため、今一度確認・調査をしてみます。
sazi

2019/02/22 04:26

この質問内にあるSQLに不備があるなら、確認はしていただきたいと思いますが、table1とtable2の内容を比較するような事がないならunionの方が良いと思いますので、そこまで追跡されなくても大丈夫です。
inu_inu

2019/02/22 04:52

比較することはないです。比較せず単純に合わせて表示するならUNIONの方がより適切ということでしょうか。 別の方法もご教授していただきありがとうございます。非常に勉強になりました。
sazi

2019/02/22 05:21

先ずはunionの方が記述がシンプルだという事。 速度的にもunionの方が早そうな気がしますが、それは実行計画で確認されて下さい。
guest

0

ベストアンサー

一つもないのはNULLじゃないくて0でよいのでは?

  • 元データ

SQL

1create table tbl1(ID varchar(10),Number int); 2insert into tbl1 values 3('田中',1), 4('高橋',2), 5('斉藤',1), 6('斉藤',2), 7('斉藤',2), 8('斉藤',3); 9 10create table tbl2(ID varchar(10),Number int); 11insert into tbl2 values 12('田中',3), 13('林',2), 14('林',3), 15('林',3);
  • 集計

SQL

1select ID 2,sum(case Number when 1 then 1 else 0 end) as countOf_1 3,sum(case Number when 2 then 1 else 0 end) as countOf_2 4,sum(case Number when 3 then 1 else 0 end) as countOf_3 5from( 6select * from tbl1 where Number in (1,2) 7union all select * from tbl2 where Number =3 8) as t1 9group by ID 10

追記

UNIONでちがうカラム名のものを結合する場合、1行目のselectのカラム名が優先されます。
必要に応じて適当なエイリアスを振ってあげるとわかりやすくなるでしょう

SQL

1create table tbl1(ID varchar(10),Number1 int); 2insert into tbl1 values 3('田中',1), 4('高橋',2), 5('高橋',4), 6('斉藤',1), 7('斉藤',3), 8('斉藤',3), 9('斉藤',12); 10 11create table tbl2(ID varchar(10),Number2 int); 12insert into tbl2 values 13('田中',3), 14('林',2), 15('林',12), 16('林',12); 17 18 19select ID 20,sum(case Number when 1 then 1 else 0 end) as countOf_1 21,sum(case Number when 2 then 1 else 0 end) as countOf_2 22,sum(case Number when 3 then 1 else 0 end) as countOf_3 23,sum(case Number when 12 then 1 else 0 end) as countOf_4 24from( 25select ID,Number1 as Number from tbl1 where Number1 in (1,2,3) 26union all select ID,Number2 from tbl2 where Number2 =12 27) as t1 28group by ID;

投稿2019/02/22 00:52

編集2019/02/22 02:32
yambejp

総合スコア114505

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

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

inu_inu

2019/02/22 02:08

条件に一致する場合は1、一致しない場合は0として、和を出すことでカウントするのですね! なるほど、とても参考になります、ありがとうございます。 また、申し訳ございませんが提示させていただいた例に誤りがありました。 ご回答いただいた内容だとうまく実行できず、例の誤りに気付きました。申し訳ございません… 正しい内容や別途試した内容を追記しています。よろしければ、ご確認のほどお願いします。
yambejp

2019/02/22 02:22

tbl1はNumber→Number1で1,2,3、 tbl2はNumber→Number2で12 を抜きだすのですね? できれば適当なサンプルを質問に追記してもらえると それにあわせた回答ができると思います
inu_inu

2019/02/22 02:25

ご認識の通りです。 テーブルについても修正させていただきました。もともと記載していた表部分を修正しています。
yambejp

2019/02/22 02:32

追記しておきました
inu_inu

2019/02/22 03:10

ありがとうございます、実現したいデータが出力されました。 UNIONについても、sazi様・Orlofsky様のJOINについても、私の認識していた動きが誤っていたことに気づくことができました。 でUNIONしたカラムについても、UNION前のエイリアスが反映されることも知らなかったため、非常に助かりました。
guest

0

また方法2だと、テーブル1から取得した結果は一つの表にまとめられましたが、テーブル2から取得した結果を結合することができませんでした。

ヒントはJOIN。ひとつのSELECTで複数のテーブルから情報を取得できます。

なお、養子や結婚で苗字が変わった時の対応もできるようにきちんとテーブル設計するのもモラルです。

投稿2019/02/22 00:34

Orlofsky

総合スコア16415

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

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

inu_inu

2019/02/22 01:15

JOINの考え方について、「テーブルから取得した複数の表を作って、複数の表を結合していく」と考えていましたが、「テーブルから取得して一つの大きな表を作り、そこから絞り込む条件によって再度表を成形する」ような考えなのですね。(表現下手ですが) もう少し考えてみたいと思います。 また名字に関して、今回例として上記二つのカラムと値を用いましたが、実際はIDに対して別カラムで名字に該当する値を持たせているため、結婚時なども対応できるようにしています。ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.51%

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

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

質問する

関連した質問