複数のテーブルから特定の値をもつIDだけをカウントしたいです。

回答率: 85.48%

質問するログイン新規登録

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

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

新規登録して質問してみよう

ただいま回答率: 85.48%

トップ COUNTに関する質問

Q&A

解決済

2回答

2566閲覧

複数のテーブルから特定の値をもつIDだけをカウントしたいです。

総合スコア10

0グッド

0クリップ

投稿2017/08/28 01:54

編集2017/08/28 07:42

0

0

質問です。

create temp table テーブル1 (会員ID int,住所 text,会員登録日 text, 複数組織コード text[]);
insert into テーブル1 values
(1,'東京','20170801','{AA,BB,CC,DD,EE}'),
(2,'神奈川','20170601','{AA,LL,MM,KK,PP}'),
(3,'千葉','20170701','{SS,PP,CC,ZZ}'),
(4,'東京','20170801','{NO,SS,WO,HO}'),
(5,'東京','20170601','{NO,PO,HO}'),
(6,'東京','20170501','{PO,HO}'),
(7,'東京','20170801','{NO,SS,WO,HO}');

create temp table テーブル2 (会員ID int,アクションフラグ int);
insert into テーブル2 values
(1,0),
(3,1),
(4,0),
(6,0),
(7,1);

create temp table テーブル3 (会員ID int,組織コード text);
insert into テーブル3 values
(1,'AA'),
(1,'AA'),
(2,'AA'),
(4,'KK'),
(6,'SS');

テーブル１はマスターデータで会員IDは更新キーです。テーブル2,テーブル3は履歴データです。

東京に住む、アクションフラグ0で会員登録日が7月1日以降の組織コードがAA,SS,KKのユニーク会員ID数をカウントしたいです。
組織コードはテーブル1かテーブル3に値があれば1とカウントしたいです。両方（テーブル1、テーブル3）に値があっても、片方だけでも1カウントとしたいです。ユニークな会員数を出したいです。

テーブル2のアクションフラグは、テーブル1にもテーブル2にも関係します。

組織コードカウント数
AA 100
SS 120
KK 50

というように、一度に組織コード別にカウント数が出せると嬉しいです。

私の環境はPostgreSQL 9.4.7 です。

よろしくお願いします。

2017/08/28 02:15 編集

プログラムコード(およびエラーメッセージ)は```で囲ってください。（わからなければ質問編集画面でコード部分を選択し<code>ボタンを押してください）また、「Postgre4.0.4.0」は正式名称ではないように思います。なるべく正式名称と正しいバージョンを記載してください。バージョン確認は http://hacknote.jp/archives/1574/ など参照にしてください。

2017/08/28 02:16

postgresV4環境が無いのでエラーでなく正常に終了してしまいます、どのようなエラーがでていますでしょうか？

2017/08/28 02:24

お聞きしたいのですがテーブル３の会員さんはアクションフラグでの選択は必要でしょうか？

2017/08/28 02:33

例示の会員ID：2のように絞り込みたい組織コードを複数含む場合は両方に1ずつ集計ということでよろしいでしょうか？

退会済みユーザー

2017/08/28 02:34

ポスグレ 4.x は1993年（カルフォルニア大学バークレー）におけるものなのでコネクターのバージョンな気がする

2017/08/28 02:39

テーブル3の会員もアクションフラグでの選択が必要です。

2017/08/28 02:43 編集

mts10806様のご質問に回答させていただきます。ユニークな会員ID数を数えたいので、テーブル１、テーブル３両方に含まれていても、片方だけでも会員IDでカウントするので1としてカウントしたいです。

2017/08/28 02:48

バージョンPostgreSQL 9.4.7 でした。失礼しました。

行動規範の内容に同意します

回答2件

0

過去を整理しました

要は、組織コードSSに所属して、'東京'に住んでいてアクションフラグが'0'の人って何人いるの？

SSのみでよろしければ

sql
1SELECT 'SS' 組織コード,  COUNT(DISTINCT t4.会員ID) FROM
2  (SELECT t1.会員ID FROM テーブル1 t1
3   JOIN テーブル2 t2 ON t1.会員ID=t2.会員ID AND t2.アクションフラグ=0
4      WHERE 'SS' = ANY(複数組織コード) AND 住所 ='東京'
5   UNION
6   SELECT t1.会員ID FROM テーブル1 t1
7   JOIN テーブル2 t2 ON t1.会員ID=t2.会員ID AND t2.アクションフラグ=0 AND 住所='東京'
8   LEFT JOIN テーブル3 t3 ON t1.会員ID=t3.会員ID AND t3.組織コード='SS'
9 ) t4
10;

テーブル３の会員の住所の条件を追加しました。

sql
1SELECT ORG, COUNT(DISTINCT T1.会員ID) FROM
2  (SELECT ORG, 会員ID FROM
3    (SELECT 会員ID, UNNEST(t4.複数組織コード) ORG
4       FROM (SELECT t5.* FROM テーブル1 t5
5              JOIN テーブル2 t2 ON t5.会員ID=t2.会員ID AND t2.アクションフラグ=0
6             WHERE 住所 IN ('東京')
7             ) t4
8    ) t0 WHERE ORG IN ('AA','SS','KK')
9   UNION
10   SELECT t6.組織コード, t6.会員ID FROM テーブル3 t6
11     JOIN テーブル2 t2 ON t6.会員ID=t2.会員ID AND t2.アクションフラグ=0
12     JOIN テーブル1 t1 ON t6.会員ID=t1.会員ID AND 住所 IN ('東京')
13  ) t1
14GROUP BY 1;

テーブル1、テーブル3で特定の組織コードを持つ・・・・を特定の組織コードを選ぶと解釈していました、特定の組織コートに属する会員が存在しているですね、修正しました。

SELECT ORG, COUNT(DISTINCT T1.会員ID) FROM
  (SELECT ORG, 会員ID FROM
    (SELECT 会員ID, UNNEST(t4.複数組織コード) ORG
       FROM (SELECT t5.* FROM テーブル1 t5
              JOIN テーブル2 t2 ON t5.会員ID=t2.会員ID AND t2.アクションフラグ=0
             WHERE 住所 IN ('東京')
             ) t4
    ) t0 WHERE ORG IN ('AA','SS','KK')
   UNION
   SELECT t6.組織コード, t6.会員ID FROM テーブル3 t6
     JOIN テーブル2 t2 ON t6.会員ID=t2.会員ID AND t2.アクションフラグ=0
  ) t1
GROUP BY 1;

投稿2017/08/28 02:49

編集2017/08/28 07:37

総合スコア4070

2017/08/28 03:04 編集

バージョンが4.0と言われているので、多分unnest()は使えないかと思います。また、unnest()を使用しないとした場合、array演算子に@>,<@は使えません。

2017/08/28 03:05

失礼しました。いつの間にかバージョンが変わっていますね。

2017/08/28 03:09

私もV4と言われて焦っておりました。テストは低めのV92.9とV9.3.6で行いました。

2017/08/28 03:22

すみません。私のバージョンの見方が間違っておりました。9.4.7が正です。

2017/08/28 03:29

A.Ichi様。実はテーブル１とテーブル3ではカラム名が違うので、今回この板では複数組織コードと組織コードという名前にわけさせていただきました。実際の物理カラム名はテーブル1がORGANIZATIONSでテーブル3がORGANIZATIONになっています。よろしければ、ご回答の組織コードを複数組織コード、組織コードと修正いただけるとたいへんわかりやすくなります。私の知識が足りず申し訳ございません。

2017/08/28 03:31

組織コードと書いていただいている部分が私にはどちらのテーブルの組織コードを指しているかわからないためです。すみません。

2017/08/28 03:41

ちなみにエラーはLINE10の組織コードの部分で出るので、上記理由でわたしの組織コードの記述が間違っているのかなと思っています。

2017/08/28 04:28

やはりLINE10　がエラーになります。エラーは column reference "組織コード" is ambiguous です。私が思っていたカラム名が違うと思った問題ではありませんでした。ちなにあわせて聞きたいのですが、12行目は,ひとつの組織コードだけで問題ないのでしょうか？

2017/08/28 04:43 編集

SELECT t6.ORGANIZATION　と　WHERE t6.組織コード='SS'　としてください、もしかしてテーブル2にも組織コードが存在しているのではないでしょうか？テーブル２に組織コードが有ればSQLを変更しますので教えてください

2017/08/28 05:54

はい。会員IDで紐づけるので必要ないと思っていましたが、テーブル2にもORGANIZATION（組織コード）があります。

2017/08/28 05:54

もし選択条件が複数必要であれば WHERE t6.組織コード IN ('AA','SS','KK')としてください。テーブル１とは別の選択条件と思っておりましたが、同じなのでしょうか？

2017/08/28 06:05

ちなみに同じ質問ですみませんが、上記の式だとテーブル3にSSのコードを持っていることが条件になりませんか？＞12行目は,ひとつの組織コードだけで問題ないのでしょうか？・テーブル1で特定の組織コードを持つ・テーブル3で特定の組織コードを持つ・テーブル1、テーブル3で特定の組織コードを持つの3パターンをあわせて組織コードカウント数 AA 100 SS 120 KK 50 というように一回で出したいです。

2017/08/28 06:24

私からも質問ですみませんが、テーブル３の会員は'東京'に住んでいる必要がありますでしょうか？

2017/08/28 06:31

テーブル3も'東京'に住んでいてアクションフラグが'0'の必要があります。要は、組織コードSSに所属して、'東京'に住んでいてアクションフラグが'0'の人って何人いるの？と聞かれていて、組織コードはテーブル1とテーブル3にあるので私には難しく。。。よろしくお願いします。

2017/08/28 07:17 編集

度たび大変すみません質問です。テーブル３の会員は、テーブル１の会員の中でSSに所属していない会員でも組織コードがSSで東京在住であれば選ばれると言うことでしょうか？特別とは、テーブル１の所属でなくてもテーブル３にSSが登録されているという意味でしょうか？　さらにSSだけカウントを表示して他の所属のカウントは表示しないで良いでしょうか？

2017/08/28 07:53

回答になっているか自信ありませんが、＞テーブル３の会員は、テーブル１の会員の中でSSに所属していない会員でも組織コードがSSで東京在住であれば選ばれると言うことでしょうか？はい。テーブル1の複数組織コードに値がなくても、テーブル3に指定した値があればカウントしたいです。＞特別とは、テーブル１の所属でなくてもテーブル３にSSが登録されているという意味でしょうか？特別ではなく→特定です。指定している組織コードという意味です。＞さらにSSだけカウントを表示して他の所属のカウントは表示しないで良いでしょうか？指定した組織コード（20パターンほど）を一度に表示させたいです。

2017/08/28 07:54

ちなみに以前いただいていたSQLで近い数字がでていたのですが、わたしのミスで会員登録日を設定するのを忘れていたため多く出たのだと思います。質問内容に会員登録日を足したのですが、会員登録日も抽出条件にする場合は、どこに足せばよいでしょうか？後だしですみません。

2017/08/28 08:03

’東京’条件の次に AND 会員登録日＜’YYYYMMDD’ とすればよろしいかと、以前がどのものかが分かりません、要件を聞かずに書きすぎでした。

2017/08/28 08:51

ありがとうございます。大変たすかりました。

行動規範の内容に同意します

0

ベストアンサー

こんにちは。

テーブル２の組織コードの持ち方をテーブル１と同様に整形し、同様の結合になるようにしています。
データ状況によっては負荷が高いかもしれません。

SQL
1select 
2   t0.内製_対象組織コード, count(distinct t4.会員ID)
3from (
4        select t1.*, t3.内製_複数組織コード
5        from テーブル1 as t1
6           inner join テーブル2 t2
7           on t1.会員ID=t2.会員ID and アクションフラグ=0
8           left join (
9             select 会員ID, array_agg(distinct 組織コード) as 内製_複数組織コード
10             from テーブル3 
11             group by 会員ID
12           ) as t3
13           on t1.会員ID=t3.会員ID
14        where t1.住所 in ('東京') and t1.会員登録日>='20170701'
15    ) t4 inner join (select unnest('{AA,SS,KK}'::text[]) as 内製_対象組織コード) t0
16    on t0.内製_対象組織コード=any(t4.複数組織コード) or t0.内製_対象組織コード=any(t4.内製_複数組織コード) 
17group by t0.内製_対象組織コード

※前提として、
・テーブル２およびテーブル３に登録されている会員IDはテーブル１には必ず登録されている
・テーブル２は会員IDにより一意である。
としています。

追記

焦っておられるのかもしれませんが、回答でのポイントを理解して頂いているとは思えないので、纏めておきます。

質問では、抽出条件の対象となるテーブルやカラムの型が相違することによって、抽出条件をどのようにすれば良いか分からないというところがポイントだったと思います。

私の回答もA.Ichiさんの回答もそれらの形を揃えるというのがポイントです。
それぞれアプローチは違っており、A.Ichiさんの回答はunnest()によって配列を展開し、私の回答はarray_agg()で配列化するというものです。

投稿2017/08/28 05:42

編集2017/08/28 08:26

総合スコア25173

2017/08/28 06:41

２行目の t0.対象組織コード, ですが t0.'AA' のように記入するのですか？ココはカラム名ですよね？カラム名はテーブルによって違っています。格納する値（組織コード）は統一されています。

2017/08/28 06:43

いえ、内部で生成している名称です。行数で言うと１５行目です。

2017/08/28 06:50

なるほど。失礼しました。試してみます

2017/08/28 06:57

ちなに、私にはどれが内製した名称でどれが物理カラム名かがわからないのですが、複数組織コード、組織コードと書いていただいている部分は物理カラム名に置き換えればよいでしょうか？

2017/08/28 06:59

では内製した名称には内製と付加しておきます。

2017/08/28 07:01

お手数をおかけします。ありがとうございます。

2017/08/28 07:43

ありがとうございます。出ました。が、なぜか数が多いなと思い考えていたところ私のミスで会員登録日をしぼるのを忘れておりました。質問内容も修正したのですが、いただいたSQLにテーブル1の会員登録日を足すにはどうすればよいでしょうか。後だしすみません。

2017/08/28 07:54

テーブル１の条件については、追記された内容で理解できますが、テーブル２やテーブル３は会員登録日で対象となったテーブル１の会員IDのみが対象となるでよいのでしょうか？

2017/08/28 07:56

テーブル２は会員登録日は関係ありませんが、テーブル3は会員登録日が関係します。

2017/08/28 07:58

すみません。上記質問そのとおりです。会員登録日で対象となったテーブル1の会員IDのみカウントしたいです。

2017/08/28 08:07 編集

やりたいだろうことを推測で修正しました。

2017/08/28 08:50

ありがとうございます。出せました

行動規範の内容に同意します

あなたの回答

tips

プレビュー

行動規範の内容に同意します

質問の解決につながる回答をしましょう。サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。また、読む側のことを考えた、分かりやすい文章を心がけましょう。

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

ただいまの回答率
85.48%

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

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

関連した質問

トップ COUNTに関する質問

複数のテーブルから特定の値をもつIDだけをカウントしたいです。

関連した質問

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

運営からのお知らせ

【重要なお知らせ】いつもteratailをご利用いただきありがとうございます。現在、認証システムの修正により、一部のユーザーが強制的にログアウトされる可能性がございます。お手数おかけしますが、再度ログインを行なっていただきますよう宜しくお願いいたします。ご不便をおかけし申し訳ございません。

過去のお知らせを見る