【やりたいこと】*修正しました
下記のような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
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/03/13 08:17
2018/03/13 08:31 編集
2018/03/13 08:42 編集
2018/03/13 09:34 編集
2018/03/13 09:39 編集
2018/03/13 09:48 編集
2018/03/13 09:51 編集
2018/03/13 09:57
2018/03/13 10:03 編集
2018/03/13 10:02
2018/03/13 10:09
2018/03/13 10:09
2018/03/13 10:14
2018/03/13 10:41 編集
2018/03/13 11:34
2018/03/14 02:34 編集
2018/03/14 03:10 編集
2018/03/14 04:13
2018/03/14 04:24 編集
2018/03/14 04:41
2018/03/14 04:58
2018/03/14 05:13 編集
2018/03/14 05:16
2018/03/14 05:20
2018/03/14 05:41
2018/03/14 05:52
2018/03/14 05:58
2018/03/14 06:01
2018/03/14 06:10 編集
2018/03/14 06:06
2018/03/14 06:15
2018/03/14 06:19
2018/03/14 06:34 編集
2018/03/14 06:40
2018/03/14 07:10 編集
2018/03/14 07:04
2018/03/14 07:37
2018/03/14 07:44
2018/03/14 08:27 編集
2018/03/14 08:41
2018/03/14 08:57
2018/03/14 09:09
2018/03/14 09:25 編集
2018/03/14 09:30
2018/03/14 09:37
2018/03/14 09:39
2018/03/14 09:42
2018/03/14 09:46
2018/03/14 09:49
2018/03/14 09:52
2018/03/14 09:55
2018/03/14 10:04
2018/03/14 14:27
2018/03/15 00:44
2018/03/15 01:28
2018/03/15 07:36 編集
2018/03/15 07:56
2018/03/15 08:22
2018/03/15 08:33
2018/03/15 09:13
2018/03/15 10:09
2018/03/15 10:44
2018/03/15 15:33
2018/03/16 00:21