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

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

ただいまの
回答率

88.64%

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

受付中

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,185

iki

score 12

【実現したいこと】
既にある年齢&性別のフラグに、下記のフラグを加えて
特定応募日(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

〈テーブルの内容〉

CREATE TABLE point_rireki2
    ("p_date" date, "kihon_id" int, "fuyo_pt_tj" int, "kan_pt_tj" int, "old" int, "gender" int, "area" varchar(3))
;

INSERT INTO point_rireki2
    ("p_date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "gender", "area")
VALUES
    ('2018-01-01', 1001, 10, 200, 20, 1, '東京'),
    ('2018-01-17', 1005, 100, 50, 22, 3, '千葉'),
    ('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'),
    ('2018-01-25', 1001, 10, 200, 20, 1, '東京'),
    ('2018-01-26', 1015, 190, 20, 40, 3, '東京'),
    ('2018-01-28', 1010, 120, 90, 30, 2, '神奈川'),
    ('2018-02-01', 1010, 170, 80, 30, 2, '神奈川'),
    ('2018-02-03', 1015, 40, 280, 40, 3, '東京'),
    ('2018-02-03', 1020, 10, 190, 40, 2, '神奈川'),
    ('2018-02-03', 1030, 70, 20, 30, 3, '神奈川'),
    ('2018-02-04', 1045, 50, 180, 44, 1, '東京')  



CREATE TABLE campaign
    ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date)
;

INSERT INTO campaign
    ("apply_id", "shisaku_id", "shisaku_name", "kihon_id", "apply_date")
VALUES
    (10001, 'AYC100', 'C施策', 1001, '2018-01-25'),
    (10002, 'AYC100', 'C施策', 1005, '2018-01-17'),
    (10003, 'AYC100', 'C施策', 1010, '2018-01-28'),
    (10004, 'AYB100', 'B施策', 1015, '2018-01-26'),
    (10005, 'AYB100', 'B施策', 1010, '2018-02-01'),
    (10006, 'AYC100', 'C施策', 1001, '2018-01-01')
;

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

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

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

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

【現状の全体の抽出文】

with param as (
select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)



select shisaku_id, shisaku_name, demogra_flg, count(distinct kihon_id) as patarn_count
from (
    select  cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id


--【ここに、上記のキャンペーン利用の有無を判定するフラグを入れようと思っています】          

          , case gender when 1 then 'M' when 2 then 'F' end ||
            case when old between 20 and 34 then '1'
               when old between 35 and 49 then '2'
               when old >= 50 then '3'
            end as demogra_flg
            from param left join campaign cmpgn
                 on cmpgn.apply_date < param.apply_date_limit
                 left join point_rireki2 pnt
                 on cmpgn.kihon_id=pnt.kihon_id
) step1
group by shisaku_id, shisaku_name,demogra_flg
order by shisaku_id,demogra_flg

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • iki

    2018/03/22 11:01

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

    キャンセル

  • sazi

    2018/03/22 11:14 編集

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

    キャンセル

  • iki

    2018/03/22 12:25

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

    キャンセル

回答 1

+2

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

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

with 
  param as (-- パラメータ
    select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
  )
, pattern as (--組合せ表
    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)
  )
, point_attribute as (--ポイント履歴属性追加
    select  kihon_id
          , case when exists(
              select 1 from campaign where apply_date < param.apply_date_limit and kihon_id=pnt.kihon_id and apply_date=pnt.p_date
            )
            then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
          , case gender when 1 then 'M' when 2 then 'F' end ||
            case when old between 20 and 34 then '1'
                 when old between 35 and 49 then '2'
                 when old >= 50 then '3'
            end as demogra_flg
    from  param cross join point_rireki2 pnt
  )
, point_agg as (--ポイント履歴属性集計
    select shisaku_flg, demogra_flg, count(distinct kihon_id) as pattern_count
    from point_attribute
    group by shisaku_flg, demogra_flg
    order by shisaku_flg, demogra_flg
  )
-- パターン別集計表(組合せ表とポイント履歴属性集計を結合し件数を取得)
select ptn.*, coalesce(pntagg.pattern_count,0)
from  pattern ptn left join point_agg pntagg
      on    ptn.shisaku_flg=pntagg.shisaku_flg
        and ptn.demogra_flg=pntagg.demogra_flg
order by shisaku_flg, demogra_flg
;


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

追記

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/03/22 19:20

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

    キャンセル

  • 2018/03/22 19:37 編集

    >都道府県のような項目等でパターン数が多い場合であっても、(コード自体の記述は長くなりますが)アレイを作って定義をすることには変わりなく、物理的なテーブルを創り

    いえ、今回の例で言えば、patternを実体化するので、withでの定義は無くなります。
    create table でテーブルを作成するときにアレイを利用するというのはありですけど。
    ※認識は一致しているような気もしますが、敢えてコメントしておきます。

    キャンセル

  • 2018/03/23 17:02

    ありがとうございます!

    >いえ、今回の例で言えば、patternを実体化するので、withでの定義は無くなります
    今回はwith句内で定義されていますが、 
    "array['1','2','3']"の箇所の中身が多い場合は、pattern箇所をテーブルとして作成し(そこにインデックスを設定した上で)、そのテーブルをjoin(今回であれば、pattern部分を作ったテーブル名へ変更)する、ということですね。ありがとうございます!

    ひとまず、頂いたものに キャンペーン期間や応募日等の条件を point_attribute内に加えながら挙動を確かめようと思います。ありがとうございます!

    キャンセル

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

  • ただいまの回答率 88.64%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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