【SQL初心者】履歴状況のテーブルからフラグ作成し、主テーブルへ反映させたい

Question

【やりたいこと】＊修正しました
下記のような2つのテーブルから、
施策ごとに(施策に反応した)新規ユーザーと既存ユーザーの数を把握したいです。

新規、既存の判定は、
キャンペーンテーブルにあるuser_idの応募日を確認し、その応募日から3カ月以内に同一ユーザーの利用履歴があるかどうか
(=ポイント履歴のテーブルにおいて、応募日から3カ月以内のポイントA,Bがともに(全て)0またはNULL⇒新規、 ポイントが入っている⇒既存)
で区別したいと思っております。

↓
《やりたいことの修正》
・キャンペーン利用者の新規/既存の数を把握したい
・新規/既存の判定については、
キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないかです。
*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。

〈テーブル概要〉　　
・キャンペーン テーブル(実施施策と施策に応募したユーザー、応募日時があるテーブル)　
⇒対象データは、1万行。100KB程度の大きさです。
・ポイント履歴　テーブル(ユーザーごとの利用履歴があるテーブル)
⇒対象データは、1050000行。150,000KB程度の大きさです。
〈テーブル例〉
http://sqlfiddle.com/#!17/ee6ad/2

〔point_rireki Table〕
CREATE TABLE point_rireki
("date" timestamp, "user_id" int, "point_A" int, "point_B" int, "old" int, "area" varchar(3))
;
INSERT INTO point_rireki
("date", "user_id", "point_A", "point_B", "old", "area")
VALUES
('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
('2017-10-07 00:00:00', 1005, 100, 50, 22, '神奈川'),
('2017-10-10 00:00:00', 1010, 99, 10, 30, '東京'),
('2017-10-01 00:00:00', 1001, 10, 200, 10, '東京'),
('2017-10-22 00:00:00', 1015, 190, 20, 40, '千葉'),
('2017-10-11 00:00:00', 1010, 120, 90, 30, '東京'),
('2017-10-27 00:00:00', 1010, 170, 80, 30, '東京'),
('2017-10-28 00:00:00', 1015, 40, 280, 40, '千葉')

〔campaign Table〕
CREATE TABLE campaign
("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "user_id" int, "apply_date" timestamp)
;

INSERT INTO campaign
("apply_id", "shisaku_id", "shisaku_name", "user_id", "apply_date")
VALUES
(10001, 'AYC100', 'C施策', 1001, '2017-10-01 00:00:00'),
(10002, 'AYC100', 'C施策', 1005, '2017-10-07 00:00:00'),
(10003, 'AYC100', 'C施策', 1010, '2017-10-10 00:00:00'),
(10004, 'AYB100', 'B施策', 1015, '2017-10-22 00:00:00'),
(10005, 'AYB100', 'B施策', 1010, '2017-10-11 00:00:00'),
(10006, 'AYC100', 'C施策', 1001, '2017-10-25 00:00:00')
;

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

【困っている点】
テーブルを結合した際の条件の表現方法がわからず困っております。
・キャンペーンテーブルにあるuser_idの応募日を確認し、その応募日から3カ月以内に同一ユーザーのポイント履歴でフラグを作成
⇒この表現方法で悩んでいます。

【現状】
下記で記述しているのですが、
point_A,point_Bが、データとして0が入っている場合、"既存"としたいのですが"新規"となってしまい、
困っています。

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(point_A,0)+coalesce(point_B,0))
from point_rireki2
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where oubo_date < '2018-02-07'
--oubo_date < '2018-02-07' が施策実行条件により絞り込み
) step1
)step2
group by shisaku_id, shisaku_name,new_or_existing

【やりたい内容の追加⓶と現状】3/15 18:15

・やりたい内容
・キャンペーン三ヵ月前までのポイント利用履歴から新規/既存のフラグを作り、その各々の数を抽出　→前回までの内容
・その新規/既存の数の中で、キャンペーン期間中にポイント利用があったかた(新規/既存のそれぞれにて)の数の抽出　→追記した内容

　〈完成イメージ〉施策ID/施策名/新規,既存フラグ/新規,既存の数/(新規,既存の各々で)施策期間中のポイント消費者数
〈仕様〉
・キャンペーン期間：2018/1/26～2018/2/9
・ポイントの消費があった方：
fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている(0も含める)方
〈考えていること〉
・with句とcount文の間に更にSelect文を書き、サブクエリを増やす。
・with句の中に、キャンペーン開始日と終了日の期間を定義し、条件内で利用(前回、with句内で定義したキャンペーンスタート日は、キャンペーン前日を定義した方が都合がよかったため、1/25をStartPreとして修正致しました)
といった方向で記述を試みているのですが、
"前回頂いたフラグを活かしたまま、さらにその条件の中でキャンペーン期間にポイント消費があった"という表現がわからず、教えて頂けないでしょうか。

〈追記作成を行ったクエリ〉＊現状はエラーになります
with param as (
select '2018-01-25':: date campaign_StartPre, '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)

select shisaku_id, shisaku_name, new_or_existing, new_or_existing_count　, shohi_flg, count() as shohi_flg
from (
select *
,case when
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id
and "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
from campaign cmpgn cross join param
where cmpgn.oubo_date < param.oubo_date_limit
) step1
from(
select shisaku_id, shisaku_name, new_or_existing, count() as new_or_existing_count
from (
select *
, case when
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id
and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
and (fuyo_pt_tj is not null or kan_pt_tj is not null)
)
then '既存' else '新規' end as new_or_existing
from campaign cmpgn cross join param
where cmpgn.oubo_date < param.oubo_date_limit
) step2
)step3
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg

Answer 1

前回のサブクエリーの相関元が変わるだけです。

select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
from (
  select *
        ,(select sum(coalesce("point_A",0)+coalesce("point_B",0)) 
          from point_rireki 
          where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
         ) as exist_point
  from campaign cmpgn
) step1
order by user_id, apply_date

※尚、テーブル定義上"で囲った場合、大文字と小文字が識別されるので、SQLの記述上面倒ですよ。
dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
（そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど）
付録C SQLキーワード

追記（施策別集計)

select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
	select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
	from (
	  select *
		,(select sum(coalesce("point_A",0)+coalesce("point_B",0)) 
		  from point_rireki 
		  where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
		 ) as exist_point
	  from campaign cmpgn
	) step1
) step2
group by shisaku_id, shisaku_name, new_or_existing

追記（条件変更）

応募日からみて過去３カ月以内のポイント履歴があるものを識別するように変更。
※項目名が変わっているので、そちらに合わせました。

select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
    select *
         , case when 
            exists(
                select 1 from point_rireki 
                where kihon_id=cmpgn.kihon_id and "date" between cmpgn.oubo_date - '3 months'::interval and cmpgn.oubo_date
            )
           then '既存' else '新規' end as new_or_existing
      from campaign cmpgn
      where oubo_date < '2018-02-07'	
    ) step1
group by shisaku_id, shisaku_name, new_or_existing

追記（条件変更２）

・キャンペーン日から３カ月前までを対象とした。
・ポイント内容も判定条件に加える
※キャンペーン日、応募日をCTEにして、変更箇所を局所化

with param as (
	select '2018-01-25':: date campaign_Start,'2018-02-07':: date oubo_date_limit 
)
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
    select *
         , case when 
            exists(
                select 1 from point_rireki
                where kihon_id=cmpgn.kihon_id 
                 and "date" between param.campaign_Start - '3 months'::interval and param.campaign_Start
                 and (fuyo_pt_tj is not null or kan_pt_tj is not null)
            )
           then '既存' else '新規' end as new_or_existing
      from campaign cmpgn cross join param 
      where cmpgn.oubo_date < param.oubo_date_limit
    ) step1
group by shisaku_id, shisaku_name, new_or_existing

追記（集計パターンの追加）

with param as (
select '2018-01-25':: date campaign_StartPre, '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)
select shisaku_id, shisaku_name, new_or_existing, shohi_flg, count(*) as patarn_count
from (
    select  *
          , case when exists(
                select 1 from point_rireki2
                where kihon_id = cmpgn.kihon_id 
                  and "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 when exists(
                select 1 from point_rireki2
                where kihon_id = cmpgn.kihon_id 
                  and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
                  and (fuyo_pt_tj is not null or kan_pt_tj is not null)
              ) then '既存' else '新規' 
            end as new_or_existing
    from  campaign cmpgn cross join param 
    where cmpgn.oubo_date < param.oubo_date_limit
) step1
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg
order by shisaku_id, new_or_existing,shohi_flg

Comments

[iki]

ありがとうございます！

＞※尚、テーブル定義上"で囲った場合、大文字と小文字が識別されるので、SQLの記述上面倒ですよ。
こちら、ありがとうございます。"point_A"周り、反映させる際は"無い形で記載致します!!
予約語の件もありがとうございます。今後はDBのカラム作るときから、予約語を避ける形で作ろうと思います。

[iki]

new_or_existingで定義した'既存'と'新規'のそれぞれの数をカウントしようといているのですが、
その場合、どうすればよいでしょうか。
(上記をさらにサブクエリ化するイメージでいるのですが、その場合のfromの扱い(fromの先が何を記述すればよいかわからず...)、'既存'と'新規'をそれぞれ出す(それぞれ別々にwhereで絞る...?)という点で悩んでおります....)

[sazi]

（編集項目を名前だけで扱いやすようにネストさせ）単にgroup by で集計するだけです。
追記しました。

[iki]

集計の件、ありがとうございます！！

また、
＞where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
上記なのですが、キャンペーンテーブルの応募日から 同一ユーザーのポイント利用履歴が3カ月以内にあるかの場合は、
betweenの中のdateが、

(現在)"date" between cmpgn.oubo_date - '3 months'::interval and cmpgn.oubo_date
↓
(仮)cmpgn.oubo_date between "date" - '3 months'::interval and "date"
　(*"date":ポイント履歴のテーブル内の利用日付)

になるかと思ったのですが、違いますでしょうか。(ポイント利用履歴の中に、該当する応募日から三ヵ月以内のポイント履歴を探してくるイメージになります)
(変更してしまうと、全て"新規"扱いとなってしまってます...)

[sazi]

それだと、計算を逆にしないとなりません。
応募日 between 利用日付 and 利用日付の3か月後

提示したコードは、
利用日付 between 応募日の3か月前 and 応募日
です。

[iki]

ありがとうございます。

＞応募日 between 利用日付 and 利用日付の3か月後
ご指摘頂いたように、
下記のように記載したところ(user_idをkihon_idとしています)、
全件が"新規"扱いとなってしまったのですが、下記の文で何かおかしい点がありますでしょうか。

select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0)+coalesce(kan_pt_kg,0))
from point_rireki2
where kihon_id=cmpgn.kihon_id and cmpgn.oubo_date between "date" - '3 months'::interval and "date"
) as exist_point
from campaign cmpgn
) step1
order by kihon_id,oubo_dat

[sazi]

コメントした通りです。
項目名を実際の名称に合わせようとして、誤った変更になっています。
マイナスしていますから３カ月前です。３か月後なら足さないと駄目です。

[iki]

＞応募日 between 利用日付 and 利用日付の3か月後
(例)応募日　10/19の場合　(UserがAさん)
　　↓
　　ポイント履歴のテーブルから、10/19のAさんの履歴を探し出し、そこから3カ月以内の間(7/19～10/19のポイント履歴)の内容を探すため、
　7/19・・・・・→3カ月前ということでは、ないのでしょうか？

"date"(ポイント履歴の日付)は、この場合、10/19ではなく7/19を指しますでしょうか？

[sazi]

利用日からみると有効な応募日は３か月後ですよ。
利用日から見ると応募日は未来日ですから加算です。
応募日から見ると利用日は過去日ですから減算です。

[iki]

＞マイナスしていますから３カ月前です。３か月後なら足さないと駄目です。
取り急ぎ、下記のように書き直したところ、新規,既存は抽出できました。
(ただ、上記のように日付期間の意味合いを理解できていないので、なぜ上記の形でないのか教えて頂きたいです)

select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0)+coalesce(kan_pt_kg,0))
from point_rireki2
where kihon_id=cmpgn.kihon_id and cmpgn.oubo_date between "date" and "date" + '3 months'::interval
) as exist_point
from campaign cmpgn
) step1

[sazi]

between を使わずに表すと、
元は、
利用日>=応募日-３カ月 and 利用日<=応募日
でしたね。
これの右辺と左辺を入れ替えたのですから符号は逆になります。
利用日+３カ月>=応募日 and 利用日<=応募日

[iki]

＞利用日からみると有効な応募日は３か月後ですよ。
10/19に応募があった場合に、
ポイント履歴のテーブルにも　　'10/19　Aさん 'の履歴が残るかと思います。
過去三ヵ月の(ポイント)履歴におけるポイント有無で、新規/既存を判断したい場合は、

ポイント履歴で探し出す有効範囲は、7/19～10/19 とはならないのでしょうか...
(初歩的な所がよくわかっておらず、すいません...)

[iki]

＞これの右辺と左辺を入れ替えたのですから符号は逆になります。
＞利用日+３カ月>=応募日 and 利用日<=応募日
上記理解致しました(親切にありがとうございます!)

下記は、betweenを使ってますが、頂いた内容と同義の範囲はので、出力されているんですね。ありがとうございます。
where kihon_id=cmpgn.kihon_id and cmpgn.oubo_date between "date" and "date" + '3 months'::interva

[sazi]

因みに、インデックスで定義されている項目に演算（今回の加算のように）すると、インデックスは使用されません。※インデックスを有効にするにはファンクションインデックスとする必要があります。
なので、キャンペーンテーブルの方が件数は少ないですし、提示した元の記述の方が良いと思います。

[iki]

なるほど。そうなんですね。
between箇所の+(-)3カ月の所ですね！　了解致しました。

[iki]

＞sum(coalesce("point_A",0)+coalesce("point_B",0)
上記の箇所で、ご質問させていただきたいのですが、
　①point_Bの数値が負になるデータもある
　②point_Aまたは、point_Bにデータで(デフォルトで)０が入っているケースがあり、それは"既存"の扱いとしたい(0というデータが入っているときは、使用履歴ありと認識させたいと思ってます)
場合、
　①については、　case when abs(exist_point) > 0 then '既存' else '新規'
　　とすることで解決できたのですが、
　②の場合については、どう表現するのがよいでしょうか。
　　　point_Aとpoint_Bのデータが""(Null)になっている(=使用履歴がない)という場合のみ'新規'の扱いとしようと思ってます。
sumの箇所を修正する形になるとは思っているのですが、いい表現方法が浮かばず、ご質問をさせて頂きました。

[iki]

構文自体はエラーなのですが、表現方法で下記のように試していました。(between部分は、施策キャンペーンの3カ月前までの期間で絞っております)

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '新規' else '既存' end as new_or_existing
from (
select *
,(select
case when (fuyo_pt_tj is null and kan_pt_tj is null) then 1 else 0 end
from point_rireki2
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where oubo_date < '2018-02-07'
--oubo_date < '2018-02-07' 　応募している人で絞ってます
) step1
)step2
group by shisaku_id, shisaku_name,new_or_existing

[sazi]

>①point_Bの数値が負になるデータもある
>②point_Aまたは、point_Bにデータで(デフォルトで)０が入っているケースがあり、それは"既存"の扱いとしたい(0というデータが入っているときは、使用履歴ありと認識させたいと思ってます)

結果的にポイント履歴にポイントに関係なく過去３カ月以内にデータがあれば良いという仕様ですよね。

[iki]

＞結果的にポイント履歴にポイントに関係なく過去３カ月以内にデータがあれば良いという仕様ですよね。
キャンペーン期間の3カ月前までの期間において、
　ポイントの履歴が0含めて何かしらある場合⇒既存、何もない場合⇒新規
とできればと思っております。
要件定義部分が度々変わってしまいすいません...

[sazi]

追記しました。
過去質問も含め、要件部分（要求仕様、データ仕様）が二転三転してますね。
今回のが最終なら、前回質問された時点からすると対象テーブルが変わった程度ですね。
何か、焦っておられますか？急がば回れです。落ち着いて考えましょう。

[sazi]

気になる点として、既存/新規としていますが、データの状況としては以下の３つになるかと思います。
1.既存（ポイント履歴が、応募日から見て過去３カ月以内にあるもの）
2.既存（ポイント履歴が、応募日から見て過去３カ月以内には無いが、それより過去にはあるもの）
3.新規（ポイント履歴が、応募日から見て過去にはないもの）
今回は（既存）=(１)で、(新規)=(2,3)となっています。
キャンペーンに対する動向を分析する仕様として、より詳細な方が良い気がしますけど、
纏めておくというのも、それはそれで有りな気もします。

[iki]

1/26～実施していたキャンペーンがあり、
そのキャンペーン期間の3か月前まで(=2017/10/25～2018/1/25)に、
　ポイントの利用履歴がない場合⇒新規、　
　ポイントが0でも-の値でも入っている場合⇒既存
としたいのですが、
その場合　 "date" between '2017-10-25' and '2018-01-25' の記述で大丈夫でしょうか。

[iki]

現状、下記のクエリが最も近しい値を取得できるのですが、
これだと、利用履歴に0が入っているユーザーも新規となってしまうケースがあるのではないかと思い、その部分がクリアになれば、正しい値を取得できるのではないかと思っています。

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0))
from point_rireki2
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where oubo_date < '2018-02-07'
--oubo_date < '2018-02-07' が施策後にもぎられている人の除外に相当
) step1
)step2
group by shisaku_id, shisaku_name,new_or_existing

[sazi]

利用日について、直接値で範囲を指定するのは、応募日を特定日で限定するなら良いですけど、範囲で指定する場合はあくまで応募日からの相対でないと駄目です。
焦らず、追記しているものも確認して下さい。

[iki]

ありがとうございます！

＞利用日について、直接値で範囲を指定するのは、応募日を特定日で限定するなら良いですけど、
今回のケースでは、特定の1つのキャンペーンに関してデータ分析(=キャンペーン施策結果の新規と既存の数の抽出)を行っておりまして、
　キャンペーン期間の3か月前まで　のポイントの利用有無を把握したいのですが、その場合は、
　・"date" between '2017-10-25' and '2018-01-25'
としたほうがよいのではないかと思ったのですが、どうでしょうか。
頂いたクエリの場合、"このキャンペーン期間の3か月前まで"といった具体的なキャンペーン期間内の絞りがないのではないかと思い、お伺いさせて頂いました。

また、実は、昨夜ポイント履歴テーブルのデータを正しいもの(csvでインポートしていたのですが、インポートしていたcsvがエクセル変換過程で量が多く途中で切れてしまっておりました)に変えた関係で、
行数が400万行に増えておりまして、　頂いたクエリの処理に時間がかかり結果を出せない状況です。その場合、他にもインデックスの指定等行う必要がありますでしょうか？

[sazi]

それだけ多くのデータを全件結果表示するのは無理なので、こういった場合の検証では、母数を減らして確認します。
具体的には、campaignのwhere 条件で、shisaku_idやkihon_idを限定することです。

[sazi]

>"このキャンペーン期間の3か月前まで"といった具体的なキャンペーン期間内の絞りがない
応募日の３カ月前という質問でしたが、そこも違うということですか？
それならキャンペーンの施策日などの情報が別テーブルにありそちらを参照するということでしょうか？

[iki]

やはり、今のままでは結果は出力できないんですね。ありがとうございます。
データ数が少ないcampaignテーブルにて、　shisaku_id：1個　kihon_id:9700個　存在しているので、kihon_idで絞る形にはなると思うのですが、その場合、
where kihon_id =1000 or 10002・・・・といった形で絞っていくしか方法はないのでしょうか。。

[iki]

＞>"このキャンペーン期間の3か月前まで"といった具体的なキャンペーン期間内の絞りがない
＞応募日の３カ月前という質問でしたが、そこも違うということですか？
はい、すいません。。。

＞それならキャンペーンの施策日などの情報が別テーブルにありそちらを参照するということでしょうか？
キャンペーンの施策日については、データとしてないのですが、プロジェクト側で実施日を特定できているため、その日時よりも前の3カ月をポイント利用の判定期間としたいです。
キャンペーンのテーブルに、施策名や施策IDは入っているのですが施策日の情報はなく、date型はユーザーの応募日のみでした。

下記に改めて、要件の部分を修正させて頂きました

《やりたいことの要件箇所修正》
・2つのテーブルから、キャンペーン利用者の新規/既存の数を把握したいです
・新規/既存の判定については、
キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないか(キャンペーンの直前3カ月間にポイント利用がなければ'新規'扱いとしたいです)
*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。

そのため、先ほどの条件式が近いのではないかと思っておりました。。。背景を伝え漏れてしまい、本当に申し訳ありません。。

[sazi]

where kihon_id in (1000,10002) です。
campaignテーブルにインデックスがあれば、kihon_id:9700個程度であればそこまで時間は掛からないと思いますけど。
処理の性質上、日付に関するところは絞り込みの条件としない方が良いですね。

[sazi]

>キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)
期間というのは施策毎に違っていて固定ではないでしょうから、そこはパラメータなどにするつもりですか？

[iki]

campaignテーブルには、連番カラムがあり一意だったため、そこをPK設定のみしており、インデックスの設定はできていなかったので、kihon_idを設定後、もう一度実行させて頂きます。

上記で、要件部分を改めて修正させて頂きました！

[iki]

＞>キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)
＞期間というのは施策毎に違っていて固定ではないでしょうから、そこはパラメータなどにするつもりですか？
こちら、運用でカバーしようと思ってます！

同時に複数施策の内容を取得しにいく、という形ではなく、
個々の施策が独立しているので、一つの施策が完了後にクエリを実行し、別の施策が完了したら日時を変えてもう一度実行し、、といったサイクルで回そうと考えております。
(施策は、週に1度程度の頻度になります)

[sazi]

追記しました。
campaignテーブルのインデックスも複合インデックスです。
oubo_dateを必ず条件にするなら、
(oubo_date,shisaku_id, kihon_id)
oubo_dateを条件にしないなら、
(shisaku_id, kihon_id)
両方あり得るなら両方を作成。

[iki]

ありがとうございます！出力ができました。(上記インデックスを設定したら、すぐに出力ができました)

こちらの内容と下記で行った内容だとかなり数のかい離がある(新規がかなり少なくなっている)のですが、要因は何になりますでしょうか...

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0))
from point_rireki
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where oubo_date < '2018-02-07'
--oubo_date < '2018-02-07' が施策後にもぎられている人の除外に相当
) step1
)step2
group by shisaku_id, shisaku_name,new_or_existing

[sazi]

>重いテーブルはポイント履歴だと思うのですが、
ポイント履歴の参照は相関サブクエリなので、相関元の母数が小さくなれば小さくなります。
※変に条件を入れると誤った検証結果になる恐れがあるので。

>処理が重い場合は、下記のような位置にkihon_idを絞り込む条件を入れる形になりますでしょうか。
先ずは、shisaku_idでの絞り込みでしょうね。

[iki]

ポイント履歴テーブルの参照範囲：'2017-10-25' and '2018-01-25'　(キャンペーンが1/26～のため、その前3カ月を指定)
キャンペーンテーブルの参照範囲： oubo_date < '2018-02-07' (応募日が～2/6迄の応募のものを対象。テーブル自体には2/7,8といった履歴はあるのですが、施策後の応募を対象外としたいです)
といったような形で、それぞれのテーブルで抽出したい期間が異なるのですが、その場合どうすればよいでしょうか。
頂いたクエリと上記のクエリの違いを確認し、ポイント履歴のwhereの日数幅が異なることため、数値が異なる(頂いたクエリの方が日程範囲が広いため、"既存"が多くなる)ようです。

[sazi]

修正しました。

[iki]

ありがとうございます！
サブクエリ内の既存となる判定数が多いので、ご相談させてください。

＞キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないか(キャンペーンの直前3カ月間にポイント利用がなければ'新規'扱いとしたいです)
*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。
上記なのですが、
今回、ポイント履歴のテーブルには、ポイントに該当するカラムが3カラムありまして、その中のfuyo_pt_tj, kan_pt_tjについての利用有無に絞ろうと思うのですが、その場合、
select 1 from point_rireki2　の部分を　変更す認識で合っておりますでしょうか。(where句でさらにポイントの項目を絞ろうかと健闘しております...)

[sazi]

existsで判定しているＳＱＬではポイントは一切見ていません。
>ポイントの履歴が0含めて何かしらある場合⇒既存、何もない場合⇒新規
上記になっているので、現状で要件は満たしていると思っているのですが。

[iki]

＞>ポイントの履歴が0含めて何かしらある場合⇒既存、何もない場合⇒新規
＞上記になっているので、現状で要件は満たしていると思っているのですが。
すいません。。下記の点、テーブルの項目及び、期間について修正させて頂きます。

《やりたいことの要件箇所修正》
・2つのテーブルから、キャンペーン利用者の新規/既存の数を把握したいです
・新規/既存の判定については、
キャンペーン期間の3カ月前までの期間(2017/10/25～2018/1/25)において、ポイント利用履歴があるかないか(キャンペーンの直前3カ月間にポイント利用がなければ'新規'扱いとしたいです)
*ポイント履歴内に"0"でもデータがあれば、'既存'の扱いとしたいです。
　・キャンペーン開始日は、1/26～となります。
　・ポイント履歴で、利用履歴の有無を確認したい期間は、2017/10/25～2018/1/25です。
　・キャンペーンテーブルにおいて、有効な応募日を2/6迄(2/7～の応募は対象外)としたいです。
　・ポイント履歴テーブルには、fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類があり、Null,0,-100,5,1000などの値が入ってます。
　　→kan_pt_kgが、期間限定施策時のカラムになるため、今回の新規/既存のフラグ対象条件からは外そうと思います。
→利用履歴がない＝fuyo_pt_tj、kan_pt_tj　のカラムがNUll('')のときを想定しています。

ポイント履歴のテーブル項目について、伝え漏れてしまい、申し訳ないです。
上記理由のため、where句での絞り込みにポイントのカラムも足そうと検討しておりました。

[sazi]

利用履歴がある(=既存)ことについて、以下を満たす条件ということで良いですか？
・キャンペーン期間の3カ月前までの期間において、ポイント利用履歴がある
・ポイント利用履歴のfuyo_pt_tjまたはkan_pt_tjがNULL以外
※上記で無ければ新規

[iki]

はい、大丈夫です！ありがとうございます!!
・キャンペーン期間の3カ月前までの期間において、ポイント利用履歴がある
　→利用履歴の有無を確認したい期間において、履歴があるものが対象になります！
・ポイント利用履歴のfuyo_pt_tjまたはkan_pt_tjがNULL以外
　→"0"も既存扱いでお願いします。(上述で作成した際に、0を新規扱いとしてしまい数が増えてしまったためです)

[sazi]

追記（条件変更２）を修正しています。
パラメータ名称も目的に合わせて名称変更していますが、このあたりは適宜変更して下さい。

[sazi]

尚、fuyo_pt_tjおよびkan_pt_tjについてはテーブルの定義がintegerになっているのでNuLL判定のみとしています。
実際のテーブルでは、文字型(textやvarchar)ということは無いですよね？

[iki]

＞実際のテーブルでは、文字型(textやvarchar)ということは無いですよね？
ポイント履歴の各ポイント箇所ですよね？
こちら、3つともintegerで定義しております。

[sazi]

分かりました。
>→利用履歴がない＝fuyo_pt_tj、kan_pt_tj　のカラムがNUll('')のときを想定しています。
上記のような表現があったので気になりました。
Nullと空文字('')は別物なので判定時の条件式が異なります。
また数値型には空文字('')はあり得ませんので。

[iki]

頂いた式と下記の式を正しい値と比較しながら、比べているのですが、
頂いた式が下記よりも既存が多くなる(サブクエリ内で該当する条件が多い)のですが、これは、値が0を既存とするか(現在)、新規とするか(下記)が主な違いという認識で合っていますでしょうか。

select shisaku_id, shisaku_name, new_or_existing,count(*) as new_or_existing_count
from(
select *, case when abs(exist_point) > 0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0))
from point_rireki
where kihon_id=cmpgn.kihon_id and "date" between '2017-10-25' and '2018-01-25'
) as exist_point
from campaign cmpgn
where oubo_date < '2018-02-07'
--oubo_date < '2018-02-07' が施策後にもぎられている人の除外に相当
) step1
)step2
group by shisaku_id, shisaku_name,new_or_existing

[iki]

＞Nullと空文字('')は別物なので判定時の条件式が異なります。
＞また数値型には空文字('')はあり得ませんので。
すいません。失礼いたしました！
csvで開いた際に、" ,, "のようにカンマで区切られた形でした！

[sazi]

>sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0))
としていますから、ポイントがNullのものも対象に含まれています。
また、absを行うならサブクエリー内で行わないと、合計で0になったものが除外されます。
また、0も有効にするということなので、abs(exist_point) > 0はおかしいですね。

[sazi]

そもそも、仕様を聞いて変更したＳＱＬを提示しているのに、なぜポイント値を合計する以前のＳＱＬを使用しているんですか？

[iki]

＞そもそも、仕様を聞いて変更したＳＱＬを提示しているのに、なぜポイント値を合計する以前のＳＱＬを使用しているんですか？
失礼致しました。
別で正しい数値のみをもらっておりまして、その内容に沿ったものを抽出できるかどうか、ということをしております。
そこで、頂いたクエリ実行の際は、既存の数が、上記で比較した式での抽出 < 正しい数値 < 先ほど頂いた式での抽出　
となっているためです。原因を分析して、正答値に近づけようとしております。

[sazi]

そうですか。あてずっぽうで探るより、相手先に仕様を確認できるといいのですけどね。
では、推測ですがサブクエリー中の以下の条件を変更してみて下さい。
※これもあてずっぽうですけど。
> and (fuyo_pt_tj is not null or kan_pt_tj is not null)
and (coalesce(fuyo_pt_tj,0)>0 or coalesce(kan_pt_tj,0)>0)
または、
and (coalesce(fuyo_pt_tj,0)>0 and coalesce(kan_pt_tj,0)>0)

[iki]

ありがとうございます！(相手側に確認しつつも、ソースを修正しながら値の確認を行っています)
サブクエリ内でwith句のjoinが、直積のcross joinになっていますが、これは普通のjoinではじかれてしまう理由はなぜなのでしょうか。

[sazi]

結合するもの（ON句で指定するもの）が無いからです。

[iki]

ありがとうございます！

付随する形でお伺いさせて頂きたい内容ができたため、ご相談させていただけないでしょうか。
今回、切り分けた新規/既存の方を対象に、
キャンペーン期間中に、ポイントの消費があった発生者数を調べる形となりました。
　〈完成イメージ〉施策ID/施策名/新規,既存フラグ/新規,既存の数/(新規,既存の各々で)施策期間中のポイント消費者数
　〈仕様〉
　・キャンペーン期間：2018/1/26～2018/2/9
　・ポイントの消費があった方：
　　fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている(0も含める)方
　〈考えていること〉
　　・with句とcount文の間に更にSelect文を書き、サブクエリを増やす。
　　・with句の中に、キャンペーン開始日と終了日の期間を定義し、条件内で利用(前回、with句内で定義したキャンペーンスタート日は、キャンペーン前日を定義した方が都合がよかったため、1/25をStartPreとして修正致しました)
　といった方向で記述を試みているのですが、
　"前回頂いたフラグを活かしたまま、さらにその条件の中でキャンペーン期間にポイント消費があった"という表現がわからず、教えて頂けないでしょうか。

[sazi]

>前回頂いたフラグを活かしたまま
の意図が分かりません。
>・ポイントの消費があった方：
>fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている(0も含める)
というのは、
> and (fuyo_pt_tj is not null or kan_pt_tj is not null)
and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
上記修正になりますので、fuyo_pt_tj とkan_pt_tjの２つのポイントだけのステータスも残したいってことですか？

[iki]

＞>前回頂いたフラグを活かしたまま
＞の意図が分かりません。
前回定義した新規/既存の条件、とその抽出数は保持したまま、その右側に今回の抽出条件を定義しようと思っていました。
〈完成イメージ〉施策ID/施策名/新規,既存フラグ/新規,既存の数/(新規,既存の各々で)施策期間中のポイント消費者数
の列で表現したかったので、新規,既存フラグ/新規,既存の数　を今の出力内容で保持しておきたい、という意図になります。(今回出したい各新規と既存の数が、前回の定義した新規と既存の数の内、どれぐらいの方がキャンペーン期間中に消費したかの数を把握したいためです。)

＞> and (fuyo_pt_tj is not null or kan_pt_tj is not null)
＞and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
＞上記修正になりますので、fuyo_pt_tj とkan_pt_tjの２つのポイントだけのステータスも残したいってことですか？
はい、そうなります！
前回の定義(fuyo_pt_tj is not null or kan_pt_tj is not null))で、集計した新規/既存の数の内、
今回の条件(fuyo_pt_tj、kan_pt_tj、kan_pt_kgの3種類の内、いずれかのカラムに数値が入っている)に該当する方を出したいためです。
　　・新規のポイント消費者：キャンペーン前日から三ヵ月前までの期間では、2つのポイントカラムで履歴はなかったが、キャンペーン期間中に３つのポイントカラムの内、いずれかのカラムにポイントが入っている方
　　・既存のポイント消費者：キャンペーン前日から三ヵ月前までの期間で、利用履歴があり、キャンペーン期間中にも３つのポイントカラムの内、いずれかのカラムにポイントが入っている方
というイメージです。そのため、前回条件も保持しなければと思っております。

[sazi]

じゃあ、
新しい条件のサブクエリーを追加するとともに、step2のselect項目を増やす。
select 項目を増やすので、group by項目も同様に追加
です。

[iki]

下記のように作成していたのですが、不適な点を教えていただけないでしょうか。
(new_or_existingフラグがあるサブクエリの外側に新規のshohi_flgフラグがあるサブクエリを作ったことで、消費フラグが　キャンペーン前日から三ヵ月前までの期間でのポイント利用履歴による新規/既存の条件を保持した上で、キャンペーン期間の条件で絞れていると思っているのですが、意図した条件になっておりますでしょうか)

〈追記作成を行ったクエリ〉
with param as (
select '2018-01-25':: date campaign_StartPre, '2018-02-07':: date oubo_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
)

select shisaku_id, shisaku_name, new_or_existing, new_or_existing_count　, shohi_flg, count(*) as shohi_flg
from (
select *
,case when
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id
and "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
from campaign cmpgn cross join param
where cmpgn.oubo_date < param.oubo_date_limit
) step1
from(
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
from (
select *
, case when
exists(
select 1 from point_rireki2
where kihon_id = cmpgn.kihon_id
and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
and (fuyo_pt_tj is not null or kan_pt_tj is not null)
)
then '既存' else '新規' end as new_or_existing
from campaign cmpgn cross join param
where cmpgn.oubo_date < param.oubo_date_limit
) step2
)step3
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg

[sazi]

追記しました。
new_or_existing_countとshohi_flg_countの目的が良くわからなかったので、記述していません。
既存の数や消費の数ということなら、提示したSQLの結果を元に集計する必要があります。

[iki]

すいません。ありがとうございます！！

サブクエリ内の実行順番でお伺いしたいのですが、
step1内の2つあるcase when文で処理の順番は、
　shohi_flg　と　new_or_existing　では、どちらが先行して処理されるのでしょうか。

patarn_countの数で(例えば、既存　消費となっている行において)、
キャンペーン期間の三ヵ月前の利用有無で利用有の方の内、キャンペーン期間に利用している方、といった前後の文脈を考慮する必要があるのではと気になってしまいました。(2つあるcase whenの順番を変えても出力される数は同じなため、　定義の文脈上の前後関係は関係ないのかなとも、思っているのですが、念のため、教えていただきたいです。。(初歩的で恐縮です。。。))

[sazi]

同一のレベルでは前後はありません。
結果を利用するような場合には、処理をネストさせます。

それから、長いスレッドは情報量がありすぎて利用者にとってはあまり有用ではありません。
質問のポイントを絞って別質問にして下さい。

[iki]

ありがとうございます！

＞それから、長いスレッドは情報量がありすぎて利用者にとってはあまり有用ではありません。
＞質問のポイントを絞って別質問にして下さい。
すいません。了解致しました！観点を絞って、立てさせて頂きます！ありがとうございます。