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

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

ただいまの
回答率

90.37%

  • SQL

    3159questions

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

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,885

iki

score 6

【実現したいこと】
ユーザーのポイントの履歴テーブルと施策キャンペーンの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までの応募日でデータを絞りたいと思っています。

〈テーブルの内容〉

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, 10, 1, '東京'),
    ('2018-01-17', 1005, 100, 50, 22, 3, '千葉'),
    ('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'),
    ('2018-01-25', 1001, 10, 200, NULL, 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, -3, 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文の書き方が分からず困っています。
2つのcase文にて、それぞれの条件の絞り込みに必要なテーブルが異なる場合、どう記述すればよいのでしょうか。
(また、サブクエリ内の定義も下記のような形で上記の内容を実現できているかも教えていただきたいです)

【現状】

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, shohi_flg,demogra_flg, count(*) as patarn_count
from (
    select  *
          , case when exists(
                select 1 from point_rireki2
                where kihon_id = cmpgn.kihon_id 
                  and "p_date" between param.campaign_Start and param.campaign_End
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null )
              ) then '消費' else '消費なし' 
            end as shohi_flg
           from  campaign cmpgn cross join param 
            where cmpgn.apply_date < param.apply_date_limit

          , case when gender  = 1 and  old >= 20 and old <= 34
                 then'M1'
                 when gender  = 1 and  old >= 35 and old <= 49
                 then'M2'    
                 when gender  = 1 and  old >= 50
                 then'M3'  
                 when gender  = 2 and  old >= 20 and old <= 34
                 then'F1'    
                 when gender  = 2 and  old >= 35 and old <= 49
                 then'F2'  
                 when gender  = 2 and  old >= 50 
                 then'F3' 
                 else null
            end as demogra_flg
            from point_rireki2
) step1
group by shisaku_id, shisaku_name,shohi_flg,demogra_flg
order 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でない)
→キャンペーンテーブルにある、施策利用に紐づいたポイント利用となっている

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, shohi_flg, shisaku_umu_flg , demogra_flg, count(distinct kihon_id) as patarn_count
from (
    select  cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id
          , case when p_date between param.campaign_Start and param.campaign_End
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null )
              then '消費' else '消費なし' 
            end as shohi_flg



       , case when p_date between param.campaign_Start and param.campaign_End
          and (fuyo_pt_tj is not null or kan_pt_tj is not null )
                  and (shisaku_id is not null)
              then '施策利用なし' else '施策利用あり' 
            end as shisaku_umu_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 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,shohi_flg,shisaku_umu_flg, demogra_flg
order by shisaku_id,shohi_flg,shisaku_umu_flg,demogra_flg
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • iki

    2018/03/18 17:06 編集

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

    キャンセル

  • iki

    2018/03/18 17:07

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

    キャンセル

  • sazi

    2018/03/18 20:02

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

    キャンセル

回答 2

checkベストアンサー

+2

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

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, shohi_flg,demogra_flg, count(distinct kihon_id) as patarn_count
from (
    select  cmpgn.shisaku_id, cmpgn.shisaku_name, cmpgn.kihon_id
          , case when p_date between param.campaign_Start and param.campaign_End
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
              then '消費' else '消費なし' 
            end as shohi_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 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,shohi_flg,demogra_flg
order by shisaku_id,shohi_flg,demogra_flg


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

追記

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/03/20 00:33

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

    キャンセル

  • 2018/03/20 09:41

    >キャンペーンのテーブルと紐づけている情報なんですから、「キャンペーンでの履歴>はない」というのは判断しようがないのではないですか?
    ポイント履歴のテーブルが、キャンペーン施策での応募による履歴と施策とは関係ない履歴を含んでいたため、組み合わせることで、キャンペーン施策でない応募者と切り分けるフラグを作ろうとしておりました。


    >また、内容的に新たな質問にも見えますし、入力データと出力結果のイメージを整理した形の新たな質問として下さい。
    了解致しました!要件を定義した方が皆様にも見えやすいと思いますので、もう少し試行錯誤後に新たにトピックを立てさせて頂こうと思います!ありがとうございます!

    キャンセル

  • 2018/03/22 09:50

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

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

  • SQL

    3159questions

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