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

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

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

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

Q&A

解決済

2回答

1629閲覧

【SQL】複数のcase文を組み合わせて条件を抽出したい

iki

総合スコア12

SQL

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

0グッド

0クリップ

投稿2018/03/16 11:50

編集2018/03/19 09:43

【実現したいこと】
ユーザーのポイントの履歴テーブルと施策キャンペーンの2つのテーブルから、
特定施策において、キャンペーン期間中にポイント利用があった方の数を属性別に抽出したい。

 〈仕様〉
・テーブルの項目について:
テーブル項目
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
-kihon_id:ユーザーidになります
-sex:1-男性、2-女性、3-不明となります
・キャンペーン期間:1/26~2/9です
・ポイント利用があった方 =fuyo_pt_tj あるいは kan_pt_tj のカラムに数字が入っている(0や-40などの数値も入ります)
・属性別:下記のようなM1、F2といった条件ごとに数を表示したいです
F1:女性、20~34歳
F2:女性、34~49歳
F3:女性、50歳以上
M1:男性、20~34歳
M2:男性、34~49歳
M3:男性、50歳以上

・キャンペーンテーブルにおいて、2/6までの応募日でデータを絞りたいと思っています。

〈テーブルの内容〉

lang

1CREATE TABLE point_rireki2 2 ("p_date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "gender" int, "area" varchar(3)) 3; 4 5INSERT INTO point_rireki2 6 ("p_date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "gender", "area") 7VALUES 8 ('2018-01-01', 1001, 10, 200, 10, 1, '東京'), 9 ('2018-01-17', 1005, 100, 50, 22, 3, '千葉'), 10 ('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'), 11 ('2018-01-25', 1001, 10, 200, NULL, 1, '東京'), 12 ('2018-01-26', 1015, 190, 20, 40, 3, '東京'), 13 ('2018-01-28', 1010, 120, 90, 30, 2, '神奈川'), 14 ('2018-02-01', 1010, 170, 80, 30, 2, '神奈川'), 15 ('2018-02-03', 1015, 40, 280, -3, 1, '東京') 16 17 18CREATE TABLE campaign 19 ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date) 20; 21 22INSERT INTO campaign 23 ("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date") 24VALUES 25 (10001, 'AYC100', 'C施策', 1001, '2018-01-25'), 26 (10002, 'AYC100', 'C施策', 1005, '2018-01-17'), 27 (10003, 'AYC100', 'C施策', 1010, '2018-01-28'), 28 (10004, 'AYB100', 'B施策', 1015, '2018-01-26'), 29 (10005, 'AYB100', 'B施策', 1010, '2018-02-01'), 30 (10006, 'AYC100', 'C施策', 1001, '2018-01-01') 31;

 〈テーブルの件数〉
・point_rireki2:400万行  
・campaign   :1万行

【わからないこと】
現状に記載している、サブクエリ内のcase文の書き方が分からず困っています。
2つのcase文にて、それぞれの条件の絞り込みに必要なテーブルが異なる場合、どう記述すればよいのでしょうか。
(また、サブクエリ内の定義も下記のような形で上記の内容を実現できているかも教えていただきたいです)

【現状】

lang

1with param as ( 2select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 3) 4 5 6select shisaku_id, shisaku_name, shohi_flg,demogra_flg, count(*) as patarn_count 7from ( 8 select * 9 , case when exists( 10 select 1 from point_rireki2 11 where kihon_id = cmpgn.kihon_id 12 and "p_date" between param.campaign_Start and param.campaign_End 13 and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 14 ) then '消費' else '消費なし' 15 end as shohi_flg 16 from campaign cmpgn cross join param 17 where cmpgn.apply_date < param.apply_date_limit 18 19 , case when gender = 1 and old >= 20 and old <= 34 20 then'M1' 21 when gender = 1 and old >= 35 and old <= 49 22 then'M2' 23 when gender = 1 and old >= 50 24 then'M3' 25 when gender = 2 and old >= 20 and old <= 34 26 then'F1' 27 when gender = 2 and old >= 35 and old <= 49 28 then'F2' 29 when gender = 2 and old >= 50 30 then'F3' 31 else null 32 end as demogra_flg 33 from point_rireki2 34) step1 35group by shisaku_id, shisaku_name,shohi_flg,demogra_flg 36order by shisaku_id, new_or_existing,shohi_flg

【現在の環境】
Windows
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています

【追記】
やりたい内容: キャンペーン利用の有無によるポイント消費者を区分けするフラグを作りたい
〈定義〉
キャンペーン利用をせずにポイント利用がある人:"施策利用なし"と定義したい 
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
→キャンペーンテーブルにある、施策の履歴がない(=キャンペーンテーブルとは紐づかない形でのポイント利用がある)

    キャンペーン利用にてポイント利用がある人:"施策利用あり"と定義したい 
→ポイント履歴のテーブルに、利用履歴がある(fuyo_pt_tj, あるいはkan_pt_tjが0でない)
→キャンペーンテーブルにある、施策利用に紐づいたポイント利用となっている

lang

1 2with param as ( 3select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 4) 5select shisaku_id, shisaku_name, shohi_flg, shisaku_umu_flg , demogra_flg, count(distinct kihon_id) as patarn_count 6from ( 7 select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id 8 , case when p_date between param.campaign_Start and param.campaign_End 9 and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 10 then '消費' else '消費なし' 11 end as shohi_flg 12 13 14 15       , case when p_date between param.campaign_Start and param.campaign_End 16          and (fuyo_pt_tj is not null or kan_pt_tj is not null ) 17 and (shisaku_id is not null) 18 then '施策利用なし' else '施策利用あり' 19 end as shisaku_umu_flg 20 21 22 23 , case gender when 1 then 'M' when 2 then 'F' end || 24 case when old between 20 and 34 then '1' 25 when old between 35 and 49 then '2' 26 when old >= 50 then '3' 27 end as demogra_flg 28 from param left join campaign cmpgn 29 on cmpgn.apply_date < param.apply_date_limit 30 left join point_rireki2 pnt 31 on cmpgn.kihon_id=pnt.kihon_id 32) step1 33group by shisaku_id, shisaku_name,shohi_flg,shisaku_umu_flg, demogra_flg 34order by shisaku_id,shohi_flg,shisaku_umu_flg,demogra_flg 35 36 37 38

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

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

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

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

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

Orlofsky

2018/03/16 11:54

[対応しているMarkdownの機能](https://teratail.com/help#about-markdown)を読んで、質問の中のURLは [リンク]をプログラムコードは[コードを入力]を使いましょう。 CREATE TABLE文も載せてください。
sazi

2018/03/17 05:39 編集

テーブル定義等のリンク先がMySQLになってますよ。 それから、定義とSQLが合っていません。 "date"はSQL上ではdate型ですし、oubo_date もSQL上はdate型でかつ、定義ではapply_dateです
iki

2018/03/17 09:36

>Orlofskyさん このような機能があったんですね!ありがとうございます。教えて頂いた内容にて修正・追記をさせて頂きました。ありがとうございます!
iki

2018/03/17 09:37

>saziさん ご指摘ありがとうございます!!失礼を致しました! 修正をさせて頂きました!ありがとうございます!
sazi

2018/03/17 10:06 編集

oubo_date についてSQLと定義でどちらかに統一してください。それから、kan_pt_kgの定義とデータがないですよ。また、具体的なデータ件数も質問に記述した方が良いですね。
iki

2018/03/17 10:45

ありがとうございます!sqlfiddleで作成した内容に修正させて頂きました! (データの件数も多いので)展開したほうがよいですよね、本当にありがとうございます!
sazi

2018/03/17 11:53 編集

まだ指摘全ては修正されていないようです。 この辺はきっちりしておかないと、回答者が減ってしまいます。
Orlofsky

2018/03/17 14:30

>ikiさん、過去の質問も含めてsaziさんの適切なコメントを良く読んで直された方がいいです。特にdateなどの予約語を列名にするのは止めるべきです。sexってネーミングはデリカシーにかけるのでgenderとするのが多くなりつつあります。意味はググって調べられますよね?
Orlofsky

2018/03/17 14:34

>saziさん、リンク先がCREATE TABLE だとは思いませんでした。teratail事務局には質問時にMarkdownが理解できて使い易くなるように改善依頼を何度も出していますが、対応するきがあるのかどうか?
iki

2018/03/18 08:23 編集

>saziさん 失礼致しました!sqlfiddleで作成した内容とcreate tableの記載内容、更にその下のクエリ文内の項目名が一致しておりませんでした。ご指摘頂き、ありがとうございます!!
iki

2018/03/18 08:07

>Orlofskyさん カラムの件、ご指摘頂きありがとうございます。項目名称を修正させて頂きました!ありがとうございます!!
sazi

2018/03/18 11:02

> Orlofsky さん 閲覧した人が理解しやすいようにするのは、質問する人が行うことの範疇です。ikiさんは指摘したことは対応されるので、立場は理解されています。ただ、指摘したことへの見落としがあるのかなとは思いますけど。
guest

回答2

0

ベストアンサー

サブクエリーを使わずに先に全体表を構成してから、集計する方が記述はシンプルになります。
case文中はgenderとoldの判断は独立しているので、分けてシンプルになるようにしています。
また、paramを駆動表として結合するように変更しています。
※前回の質問では流れからサブクエリーでしたので、混乱するかと思い敢えて提示はしていませんでした。

SQL

1with param as ( 2select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 3) 4select shisaku_id, shisaku_name, shohi_flg,demogra_flg, count(distinct kihon_id) as patarn_count 5from ( 6 select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id 7 , case when p_date between param.campaign_Start and param.campaign_End 8 and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null) 9 then '消費' else '消費なし' 10 end as shohi_flg 11 , case gender when 1 then 'M' when 2 then 'F' end || 12 case when old between 20 and 34 then '1' 13 when old between 35 and 49 then '2' 14 when old >= 50 then '3' 15 end as demogra_flg 16 from param left join campaign cmpgn 17 on cmpgn.apply_date < param.apply_date_limit 18 left join point_rireki2 pnt 19 on cmpgn.kihon_id=pnt.kihon_id 20) step1 21group by shisaku_id, shisaku_name,shohi_flg,demogra_flg 22order by shisaku_id,shohi_flg,demogra_flg

尚、データ状況によってはサブクエリーの方が高速な場合もあります。

追記

よく考えたら、キャンペーンに応募した人の性別や年代での分布ということだから、ポイント履歴での件数じゃ、不味いんじゃないでしょうか。
年齢とかも履歴上で誕生日過ぎたら変わってるとかあるでしょうし。
普通に考えるとユーザーマスタからの件数になるかと思われます。
敢えて履歴で行うとしても、ユーザーIDでグルーピングしたものの件数で無いと駄目な気が。
取り敢えずは、重複なしのユーザーIDでカウントするようにしました。
※仕様について考えるのは、また長くなりそうで、本意ではありませんが・・・

投稿2018/03/17 10:28

編集2018/03/19 00:50
sazi

総合スコア25138

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

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

iki

2018/03/19 01:38 編集

回答の件、ありがとうございます! with句で定義したものに対して、各テーブルをleft joinするという発想がありませんでした!大変参考になりました。(with句の使い勝手の良さに感動しました!) また追記頂いた内容ですが、(実は私もこの業務に入ったばかりではあるのですが)、自社データではなく、委託元より依頼頂いた形のため、(おそらくですが)ユーザーデータのテーブルが閲覧できない(&データ量も多い)といったことに起因しているのではないかと思っております。(念のため、そのあたりも確認しながら今後の作業を行っていこうと思います!)
sazi

2018/03/19 02:35 編集

>with句の使い勝手の良さに感動しました! 直接値を指定してもいいし、サブクエリーとして展開してもいいのですが、今回は可変値を一か所に纏めておいた方が保守効率が良いだろうと思って、そうしています。 withを使用する多くの用途としては、冗長性の排除等の可読性を高めることにあります ※同じ記述のサブクエリーを纏めることであったり、ネストが深いクエリーを分割したり。
iki

2018/03/19 08:16 編集

外だしできる内容に関しては、with句でくくってしまったほうが分かりやすいですし、サブクエリ内とも紐づけやすそうですね!ありがとうございます。 本件に付随する内容でお伺いしたいことがあるのですが、追記させて頂いてもよろしいでしょうか。 (別途切り出そうとも思ったのですが、質問内容が似ていたため、一旦こちらにて記載させて頂きます) 【追記】にて記載をさせて頂いたのですが、 キャンペーン利用をせずにポイント利用がある人(キャンペーンテーブルに紐づかない形のポイント利用がある人)を キャンペーン利用での方と切り分けたく条件を追加しようと思っております。 頂いたcase文に後述した形で追加すればよいと思っているのですが、うまく抽出されません。この理由を教えていただけないでしょうか。(case when内の絞り込みの条件が足りない、、ということなのでしょうか。)
sazi

2018/03/19 08:23 編集

提供したSQLについては記述されている内容を理解しないと応用できませんよ。 表示されないのは一番外側のselectでその項目を指定していないからです。 group by についても理解して下さい。 ※select してないのが表示されないのは当たり前で、これが理解されていないのは、応用というレベルではなく、初学者が最初に学習するレベルの話です。
iki

2018/03/19 09:45

>提供したSQLについては記述されている内容を理解しないと応用できませんよ。 >表示されないのは一番外側のselectでその項目を指定していないからです >group by についても理解して下さい。。 失礼致しました。 クエリを修正→実行する過程で、表示の前にサブクエリ内でエラーとなってしまったため、その原因をお伺いしようと思い、 selectの表示項目に追記がない状態で記載をしておりました。失礼しました。 お伺いしたかった内容としては、サブクエリ内で条件を抽出する際に、  ・キャンペーンテーブルとは紐づかない形でのポイント利用がある(キャンペーンテーブル内に該当しないポイント履歴を 施策利用なし と定義したい) を表現するのに、 他と同様にcase whenで表現をしようと考えたのですが、  ・ポイント利用がある  ・キャンペーンでの履歴はない  を分岐の条件とすればよいのではないかと考えたのですが、そこでつまづいてしまったため、原因をお伺いさせて頂きたく追加をさせて頂きました。 (case whenの中で条件が複数あるため、その部分の表現が不適ではないかと思いを方法を模索しております)
sazi

2018/03/19 15:33

キャンペーンのテーブルと紐づけている情報なんですから、「キャンペーンでの履歴はない」というのは判断しようがないのではないですか? データ同士の関係の条件が定まっていて、うまく抽出できない、というような質問なら回答もできますけど、この質問についてはちょっと回答は出来かねます。 また、内容的に新たな質問にも見えますし、入力データと出力結果のイメージを整理した形の新たな質問として下さい。
iki

2018/03/20 00:41

>キャンペーンのテーブルと紐づけている情報なんですから、「キャンペーンでの履歴>はない」というのは判断しようがないのではないですか? ポイント履歴のテーブルが、キャンペーン施策での応募による履歴と施策とは関係ない履歴を含んでいたため、組み合わせることで、キャンペーン施策でない応募者と切り分けるフラグを作ろうとしておりました。 >また、内容的に新たな質問にも見えますし、入力データと出力結果のイメージを整理した形の新たな質問として下さい。 了解致しました!要件を定義した方が皆様にも見えやすいと思いますので、もう少し試行錯誤後に新たにトピックを立てさせて頂こうと思います!ありがとうございます!
iki

2018/03/22 00:50

下記に新たにトピックを立たせて頂きました。 もしよろしければ、ご確認頂けると大変ありがたいです。何卒よろしくお願い致します! https://teratail.com/questions/118294
guest

0

最初に全部JOINしてしまうなんていうのはどうでしょうか

sql

1WITH merged_table AS 2 (SELECT pr.date as pr_date, 3 c.apply_id as campaign_apply_id 4 --- 必要なだけ追加 5 FROM point_rireki2 pr JOIN campaign c ON pr.kihon_id = c.kihon_id) 6SELECT * FROM merged_table

投稿2018/03/17 07:48

hiromichinomata

総合スコア294

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

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問