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

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

新規登録して質問してみよう
ただいま回答率
85.50%
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

1回答

1270閲覧

SQL:既存の条件に別の条件を抽出するフラグを追加したい

iki

総合スコア12

PostgreSQL

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

SQL

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

1グッド

0クリップ

投稿2018/03/20 08:12

編集2022/01/12 10:55

【実現したいこと】
既にある年齢&性別のフラグに、下記のフラグを加えて
特定応募日(2/6)以前までの期間における  性別年代別のキャンペーン利用の有無に関して対象者数を抽出したい

  〈付け加えたい対象のフラグ〉 
・ポイント履歴のテーブルとキャンペーンのテーブルを突き合わせて、下記を表すフラグを追記したい
・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴と一致する場合 → "キャンペーン利用有り"
・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴にはデータがない場合 → "キャンペーン利用無し"
(ポイント履歴内には)同一ユーザーでキャンペーン利用をしているケースもあれば、していないケースもあると思うので、
その場合はそれぞれ1カウント扱いとしたいです。


〈仕様〉
・テーブルについて:
⓵ポイント履歴のテーブル(point_rireki2 ):利用者へのポイント付与及び、利用者のポイント消費 に関する履歴データが入ったテーブルです。
(キャンペーンの利用/非利用に関係なく履歴が入っています)

⓶キャンペーンのテーブル(campaign):キャンペーン施策によるユーザーの応募履歴が入ったテーブルです。

                       apply_id ,shisaku_id にNULLは存在しないため、施策外のデータ(=キャンペーン非利用のデータ)は入っていません。
apply_idがPKになります。

   テーブルの項目について
-fuyo_pt_tj, kan_pt_tj:ポイントのカラムになります。
-kihon_id:ユーザーidになります
-gender: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までの応募日でデータを絞りたいと思っています。

【アウトプットイメージ】
下記のような 2つのフラグを掛け合わせた数の抽出を行おうと思っています。 
(*2つのフラグを掛け合わせて、各々ユニークユーザー数ごとに集計
→ユーザー集計例:1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)

キャンペーン利用の有無  | ユーザー属性  | 対象者数(下記のテーブル内容の数を試算した値です)
"キャンペーン利用有り"      F1 1
"キャンペーン利用有り"      F2 0
"キャンペーン利用有り"      F3 0
"キャンペーン利用有り"      M1 1
"キャンペーン利用有り"      M2 0
"キャンペーン利用有り"      M3 0
"キャンペーン利用無し"      F1 1
"キャンペーン利用無し"      F2 1
"キャンペーン利用無し"      F3 0
"キャンペーン利用無し"      M1 0
"キャンペーン利用無し"      M2 1
"キャンペーン利用無し"      M3 0

〈テーブルの内容〉

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, 20, 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, 20, 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, 40, 3, '東京'), 16 ('2018-02-03', 1020, 10, 190, 40, 2, '神奈川'), 17 ('2018-02-03', 1030, 70, 20, 30, 3, '神奈川'), 18 ('2018-02-04', 1045, 50, 180, 44, 1, '東京')   19 20 21 22CREATE TABLE campaign 23 ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date) 24; 25 26INSERT INTO campaign 27 ("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date") 28VALUES 29 (10001, 'AYC100', 'C施策', 1001, '2018-01-25'), 30 (10002, 'AYC100', 'C施策', 1005, '2018-01-17'), 31 (10003, 'AYC100', 'C施策', 1010, '2018-01-28'), 32 (10004, 'AYB100', 'B施策', 1015, '2018-01-26'), 33 (10005, 'AYB100', 'B施策', 1010, '2018-02-01'), 34 (10006, 'AYC100', 'C施策', 1001, '2018-01-01') 35;

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

【わからないこと】
キャンペーン利用の有無のフラグを付ける条件式で迷っています。
case when exists 式を用いて、
・select 1で数を抽出
(where条件内で)
・kihon_idとp_date が、kihon_idとapply_dateで一致する場合→キャンペーン利用有り
・そうでない場合→キャンペーン利用無し
とすればよいのではと思ったのですが、、
その場合、ユニークユーザーのカウント(それぞれの条件に該当する場合は、それぞれ1カウント)にならないのではないかとも思い、悩んでおります。。。
どういった形で抽出方法を考えるのがよいでしょうか。。(また、検討中の内容だとキャンペーンなしが該当しないため、その部分の抽出についても悩んでいます)

【検討中のフラグの仮案】

lang

1 , case when 2 exists( 3 select 1 from point_rireki2 pnt left join campaign cmpgn 4 on pnt.kihon_id = cmpgn.kihon_id 5 where pnt.p_date = cmpgn.apply_date 6 ) 7 then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg

【現状の全体の抽出文】

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 6 7select shisaku_id, shisaku_name, demogra_flg, count(distinct kihon_id) as patarn_count 8from ( 9 select cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id 10 11 12--【ここに、上記のキャンペーン利用の有無を判定するフラグを入れようと思っています】 13 14 , case gender when 1 then 'M' when 2 then 'F' end || 15 case when old between 20 and 34 then '1' 16 when old between 35 and 49 then '2' 17 when old >= 50 then '3' 18 end as demogra_flg 19 from param left join campaign cmpgn 20 on cmpgn.apply_date < param.apply_date_limit 21 left join point_rireki2 pnt 22 on cmpgn.kihon_id=pnt.kihon_id 23) step1 24group by shisaku_id, shisaku_name,demogra_flg 25order by shisaku_id,demogra_flg

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

hihijiji👍を押しています

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

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

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

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

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

退会済みユーザー

退会済みユーザー

2018/03/21 09:32

質問のタグにPostgreSQLを追加すると良さげ。
iki

2018/03/22 00:21

ありがとうございます!やってみます!!
sazi

2018/03/22 01:47 編集

【検討中のフラグの仮案】は少なくとも文法エラーにならないようにして下さい。それからサンプルデータから、どういう結果にしたいかを具体的なデータ例で記述した方が良いですね。
iki

2018/03/22 02:01

ありがとうございます!検討中のフラグの仮案の内容、およびアウトプットイメージに関して修正・追記をさせて頂きました!ご指摘ありがとうございます。
sazi

2018/03/22 02:16 編集

結果は仮置きではなくサンプルのデータと合わせて下さい。 それが結果的に、バリエーションを含むことになり、無駄な遣り取りを防ぎます。
iki

2018/03/22 03:25

ありがとうございます!確かにそうですよね。 ポイント履歴のテーブルのデータを改めて更新し、アウトプットイメージの数と合わせるように致しました。
guest

回答1

0

先ず【検討中のフラグの仮案】ですが、case文中で完結しているので、相関問い合わせとなっていません。
次に、求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんので、基準となるものが必要です。

以下は「pattern」として組合せ表を内部的に生成し、それとポイント履歴の集計表を結合して結果を求めるようにしています。

SQL

1with 2 param as (-- パラメータ 3 select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End 4 ) 5, pattern as (--組合せ表 6 select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg 7 from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg) 8 cross join unnest(array['M','F']::text[]) as y(demogra_flg1) 9 cross join unnest(array['1','2','3']::text[]) as z(demogra_flg2) 10 ) 11, point_attribute as (--ポイント履歴属性追加 12 select kihon_id 13 , case when exists( 14 select 1 from campaign where apply_date < param.apply_date_limit and kihon_id=pnt.kihon_id and apply_date=pnt.p_date 15 ) 16 then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg 17 , case gender when 1 then 'M' when 2 then 'F' end || 18 case when old between 20 and 34 then '1' 19 when old between 35 and 49 then '2' 20 when old >= 50 then '3' 21 end as demogra_flg 22 from param cross join point_rireki2 pnt 23 ) 24, point_agg as (--ポイント履歴属性集計 25 select shisaku_flg, demogra_flg, count(distinct kihon_id) as pattern_count 26 from point_attribute 27 group by shisaku_flg, demogra_flg 28 order by shisaku_flg, demogra_flg 29 ) 30-- パターン別集計表(組合せ表とポイント履歴属性集計を結合し件数を取得) 31select ptn.*, coalesce(pntagg.pattern_count,0) 32from pattern ptn left join point_agg pntagg 33 on ptn.shisaku_flg=pntagg.shisaku_flg 34 and ptn.demogra_flg=pntagg.demogra_flg 35order by shisaku_flg, demogra_flg 36;

※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。

追記

「キャンペーン期間:1/26~2/9」を条件に含めると、求めたい結果と異なるので含めていません。
必要なら、point_attribute内で条件を直接追加すれば確認できます。

投稿2018/03/22 05:11

編集2018/03/23 08:23
sazi

総合スコア25138

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

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

iki

2018/03/22 08:14 編集

ありがとうございます! >case文中で完結しているので、相関問い合わせとなっていません。 上記、回答頂き、自身の回答を改めて見返すことでご指摘頂いた点を理解することができました。ありがとうございます。 >求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんので、基準となるものが必要です。 上記がまだよく理解できていないのですが、2つのテーブルをjoinするだけでは、"ポイント履歴にポイントがあり・キャンペーンのテーブルにはデータがないもの"は抽出できない、ということになるのでしょうか。。 また、pattern内の "from unnest"は未定義でも利用できる仮テーブルということなのでしょうか。 (as x(shisaku_flg),as y(demogra_flg1),as z(demogra_flg2)は、仮で定義していて、後半の句では使わないもの、ということでしょうか) 頂いたものを一つずつ解読しているのですが、 ・基準となる句 ・flg作成のための条件式 ・ユニーク数の定義 ・最終的な数の抽出 という順になってるかと思うのですが、0件の場合も表示するために、ユニーク数の定義→最終的な数の抽出というステップになっている(1ステップではなく)という理解で大丈夫でしょうか。
iki

2018/03/22 08:13

>求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんの >で、基準となるものが必要です。 >上記がまだよく理解できていないのですが、 上記部分ですが、 point_attribute内で条件の各項目も定義していますが、別途基準となるpatternにも同様に項目の条件を設定する必要がある、、という所が分かっていないため、上記のようなご質問をさせて頂いているのかもしれません。(理解が追い付いておらず、恐縮です。。)
iki

2018/03/22 08:15

>「キャンペーン期間:1/26~2/9」を条件に含めると >求めたい結果と異なるので、含めていません。 >必要なら、point_attribute内で条件を直接追加すれば確認できます。 上記ありがとうございます!前回解説頂いた内容を見返しながら確認しております!
sazi

2018/03/22 08:33 編集

パターンを取り出せるということは、件数は0ではありません。 0のものも表示させたいということなら、そのパターン表を件数の取り出しとは別に用意する必要があります。 unnest()は配列関数で、配列を行に展開します。 from句で使用するときは、 as 表名(項目名)のようにして定義する必要があります。 表名は項目名で一意なので省略しています。
sazi

2018/03/22 08:43

SQLにコメント追記しました。
iki

2018/03/22 08:58 編集

>パターンを取り出せるということは、件数は0ではありません。0のものも表示させたいということなら、 >そのパターン表を件数の取り出しとは別に用意する必要があります ありがとうございます。なるほど、 大きく4つ構成されているものは、 ・基準となる句 ・flg作成のための条件式 ・取り出すパターンの設定  ・最終的な数の抽出 になる、ということですね! >unnest()は配列関数で、配列を行に展開します。 >from句で使用するときは、 as 表名(項目名)のようにして定義する必要があります。 >表名は項目名で一意なので省略しています。 上記ありがとうございます! このような手法があることを今回学んだので、今後も同様のケースの際は使おうと思います。 組み合せ表を使うケースとしては、掛け合わせるフラグを今回のようなテーブル全体に対して適用させる場合、という形になりますでしょうか。
sazi

2018/03/22 09:29 編集

集計表のような類では、定義されているパターンで出力するケースが多いですね。 今回追加した属性の内訳については、コードとして定義されるもので、コードの定義をテーブルにマスター化しているような場合には、それを利用します。 パターン数が多いような場合には、問い合わせのコストを下げるためにインデックスを付加したテーブルにしたりすることもありますけど。
iki

2018/03/22 09:26

>今回追加した属性の内訳については、コードとして定義されるもので 上記、下記のアレイの箇所のことであっておりますでしょうか。 >select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg) cross join unnest(array['M','F']::text[]) as y(demogra_flg1) cross join unnest(array['1','2','3']::text[]) as z(demogra_flg2) >パターン数が多いような場合には、問い合わせのコストを下げるためにインデックスを付加したテーブルにしたりすることもありますけど。 上記は、"array['1','2','3']"の箇所の中身が多い(例:都道府県のような項目等)ような場合に、組合せ表で定義している項目を掛け合わせたインデックスを作ったうえで実行する、ということで合っておりますでしょうか。 with句→ポイント履歴属性追加のような条件文指定→パターン別集計表の流れは以前もあったかと思い、徐々に理解できるようになったので、今回で新たに教えて頂いた 組み合わせ表の部分と パターンを抽出する属性集計部分を理解して使えるようになれればと思います。ありがとうございます!
sazi

2018/03/22 09:46 編集

>アレイの箇所のことであっておりますでしょうか。 あってますよ。定義されるコードを列挙しています。 >組合せ表で定義している項目を掛け合わせたインデックスを作ったうえで実行する 組合せ表そのものを物理的なテーブルとして作成した上でそのテーブルにインデックスを定義するということです。 組合せ表の件数だけでの判断はできないので、組合せ表の実テーブルを作成してコストを下げるのに貢献するかどうか判断します。 今回程度であれば、実テーブル化する必要は無いと思われますが。
iki

2018/03/22 10:20

都道府県のような項目等でパターン数が多い場合であっても、(コード自体の記述は長くなりますが)アレイを作って定義をすることには変わりなく、 物理的なテーブルを創り(create tableをして、その中に項目を定義する)、インデックスを定義するということですね!ありがとうございます。 また今回の場合は、テーブルは作らずに処理されている、ということも把握しました!ありがとうございます。
sazi

2018/03/22 14:58 編集

>都道府県のような項目等でパターン数が多い場合であっても、(コード自体の記述は長くなりますが)アレイを作って定義をすることには変わりなく、物理的なテーブルを創り いえ、今回の例で言えば、patternを実体化するので、withでの定義は無くなります。 create table でテーブルを作成するときにアレイを利用するというのはありですけど。 ※認識は一致しているような気もしますが、敢えてコメントしておきます。
iki

2018/03/23 08:02

ありがとうございます! >いえ、今回の例で言えば、patternを実体化するので、withでの定義は無くなります 今回はwith句内で定義されていますが、  "array['1','2','3']"の箇所の中身が多い場合は、pattern箇所をテーブルとして作成し(そこにインデックスを設定した上で)、そのテーブルをjoin(今回であれば、pattern部分を作ったテーブル名へ変更)する、ということですね。ありがとうございます! ひとまず、頂いたものに キャンペーン期間や応募日等の条件を point_attribute内に加えながら挙動を確かめようと思います。ありがとうございます!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問