回答編集履歴
12
推敲
answer
CHANGED
@@ -79,6 +79,7 @@
|
|
79
79
|
group by shisaku_id, shisaku_name, new_or_existing
|
80
80
|
```
|
81
81
|
追記(集計パターンの追加)
|
82
|
+
---
|
82
83
|
```SQL
|
83
84
|
with param as (
|
84
85
|
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
|
11
追記
answer
CHANGED
@@ -77,4 +77,32 @@
|
|
77
77
|
where cmpgn.oubo_date < param.oubo_date_limit
|
78
78
|
) step1
|
79
79
|
group by shisaku_id, shisaku_name, new_or_existing
|
80
|
+
```
|
81
|
+
追記(集計パターンの追加)
|
82
|
+
```SQL
|
83
|
+
with param as (
|
84
|
+
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
|
85
|
+
)
|
86
|
+
select shisaku_id, shisaku_name, new_or_existing, shohi_flg, count(*) as patarn_count
|
87
|
+
from (
|
88
|
+
select *
|
89
|
+
, case when exists(
|
90
|
+
select 1 from point_rireki2
|
91
|
+
where kihon_id = cmpgn.kihon_id
|
92
|
+
and "date" between param.campaign_Start and param.campaign_End
|
93
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null or kan_pt_kg is not null)
|
94
|
+
) then '消費' else '消費なし'
|
95
|
+
end as shohi_flg
|
96
|
+
, case when exists(
|
97
|
+
select 1 from point_rireki2
|
98
|
+
where kihon_id = cmpgn.kihon_id
|
99
|
+
and "date" between param.campaign_StartPre - '3 months'::interval and param.campaign_StartPre
|
100
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null)
|
101
|
+
) then '既存' else '新規'
|
102
|
+
end as new_or_existing
|
103
|
+
from campaign cmpgn cross join param
|
104
|
+
where cmpgn.oubo_date < param.oubo_date_limit
|
105
|
+
) step1
|
106
|
+
group by shisaku_id, shisaku_name, new_or_existing,shohi_flg
|
107
|
+
order by shisaku_id, new_or_existing,shohi_flg
|
80
108
|
```
|
10
修正
answer
CHANGED
@@ -60,7 +60,7 @@
|
|
60
60
|
※キャンペーン日、応募日をCTEにして、変更箇所を局所化
|
61
61
|
```SQL
|
62
62
|
with param as (
|
63
|
-
select '2018-01-
|
63
|
+
select '2018-01-25':: date campaign_Start,'2018-02-07':: date oubo_date_limit
|
64
64
|
)
|
65
65
|
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
66
66
|
from (
|
9
修正
answer
CHANGED
@@ -55,11 +55,12 @@
|
|
55
55
|
```
|
56
56
|
追記(条件変更2)
|
57
57
|
---
|
58
|
-
キャンペーン日から3カ月前までを対象とした。
|
58
|
+
・キャンペーン日から3カ月前までを対象とした。
|
59
|
+
・ポイント内容も判定条件に加える
|
59
60
|
※キャンペーン日、応募日をCTEにして、変更箇所を局所化
|
60
61
|
```SQL
|
61
62
|
with param as (
|
62
|
-
select '2018-01-
|
63
|
+
select '2018-01-26':: date campaign_Start,'2018-02-07':: date oubo_date_limit
|
63
64
|
)
|
64
65
|
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
65
66
|
from (
|
@@ -67,11 +68,13 @@
|
|
67
68
|
, case when
|
68
69
|
exists(
|
69
70
|
select 1 from point_rireki
|
71
|
+
where kihon_id=cmpgn.kihon_id
|
70
|
-
|
72
|
+
and "date" between param.campaign_Start - '3 months'::interval and param.campaign_Start
|
73
|
+
and (fuyo_pt_tj is not null or kan_pt_tj is not null)
|
71
74
|
)
|
72
75
|
then '既存' else '新規' end as new_or_existing
|
73
76
|
from campaign cmpgn cross join param
|
74
|
-
where cmpgn.oubo_date < param.
|
77
|
+
where cmpgn.oubo_date < param.oubo_date_limit
|
75
78
|
) step1
|
76
79
|
group by shisaku_id, shisaku_name, new_or_existing
|
77
80
|
```
|
8
修正
answer
CHANGED
@@ -55,8 +55,8 @@
|
|
55
55
|
```
|
56
56
|
追記(条件変更2)
|
57
57
|
---
|
58
|
-
キャンペーン日から
|
58
|
+
キャンペーン日から3カ月前までを対象とした。
|
59
|
-
※キャンペーン日をCTEにして、変更箇所を局所化
|
59
|
+
※キャンペーン日、応募日をCTEにして、変更箇所を局所化
|
60
60
|
```SQL
|
61
61
|
with param as (
|
62
62
|
select '2018-01-25':: date campaign_date,'2018-02-07':: date oubo_date
|
7
修正
answer
CHANGED
@@ -59,7 +59,7 @@
|
|
59
59
|
※キャンペーン日をCTEにして、変更箇所を局所化
|
60
60
|
```SQL
|
61
61
|
with param as (
|
62
|
-
select '2018-02-07'
|
62
|
+
select '2018-01-25':: date campaign_date,'2018-02-07':: date oubo_date
|
63
63
|
)
|
64
64
|
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
65
65
|
from (
|
@@ -71,7 +71,7 @@
|
|
71
71
|
)
|
72
72
|
then '既存' else '新規' end as new_or_existing
|
73
73
|
from campaign cmpgn cross join param
|
74
|
-
where oubo_date < param.
|
74
|
+
where cmpgn.oubo_date < param.oubo_date
|
75
75
|
) step1
|
76
76
|
group by shisaku_id, shisaku_name, new_or_existing
|
77
77
|
```
|
6
追記
answer
CHANGED
@@ -52,4 +52,26 @@
|
|
52
52
|
where oubo_date < '2018-02-07'
|
53
53
|
) step1
|
54
54
|
group by shisaku_id, shisaku_name, new_or_existing
|
55
|
+
```
|
56
|
+
追記(条件変更2)
|
57
|
+
---
|
58
|
+
キャンペーン日からの3カ月前を対象とした。
|
59
|
+
※キャンペーン日をCTEにして、変更箇所を局所化
|
60
|
+
```SQL
|
61
|
+
with param as (
|
62
|
+
select '2018-02-07' :: date campaign_date
|
63
|
+
)
|
64
|
+
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
65
|
+
from (
|
66
|
+
select *
|
67
|
+
, case when
|
68
|
+
exists(
|
69
|
+
select 1 from point_rireki
|
70
|
+
where kihon_id=cmpgn.kihon_id and "date" between param.campaign_date - '3 months'::interval and param.campaign_date
|
71
|
+
)
|
72
|
+
then '既存' else '新規' end as new_or_existing
|
73
|
+
from campaign cmpgn cross join param
|
74
|
+
where oubo_date < param.campaign_date
|
75
|
+
) step1
|
76
|
+
group by shisaku_id, shisaku_name, new_or_existing
|
55
77
|
```
|
5
追記
answer
CHANGED
@@ -33,4 +33,23 @@
|
|
33
33
|
) step1
|
34
34
|
) step2
|
35
35
|
group by shisaku_id, shisaku_name, new_or_existing
|
36
|
+
```
|
37
|
+
追記(条件変更)
|
38
|
+
---
|
39
|
+
応募日からみて過去3カ月以内のポイント履歴があるものを識別するように変更。
|
40
|
+
※項目名が変わっているので、そちらに合わせました。
|
41
|
+
```SQL
|
42
|
+
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
43
|
+
from (
|
44
|
+
select *
|
45
|
+
, case when
|
46
|
+
exists(
|
47
|
+
select 1 from point_rireki
|
48
|
+
where kihon_id=cmpgn.kihon_id and "date" between cmpgn.oubo_date - '3 months'::interval and cmpgn.oubo_date
|
49
|
+
)
|
50
|
+
then '既存' else '新規' end as new_or_existing
|
51
|
+
from campaign cmpgn
|
52
|
+
where oubo_date < '2018-02-07'
|
53
|
+
) step1
|
54
|
+
group by shisaku_id, shisaku_name, new_or_existing
|
36
55
|
```
|
4
追記
answer
CHANGED
@@ -14,6 +14,7 @@
|
|
14
14
|
※尚、テーブル定義上"で囲った場合、大文字と小文字が識別されるので、SQLの記述上面倒ですよ。
|
15
15
|
dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
|
16
16
|
(そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど)
|
17
|
+
[付録C SQLキーワード](https://www.postgresql.jp/document/9.6/html/sql-keywords-appendix.html)
|
17
18
|
|
18
19
|
追記(施策別集計)
|
19
20
|
---
|
3
追記
answer
CHANGED
@@ -13,4 +13,23 @@
|
|
13
13
|
```
|
14
14
|
※尚、テーブル定義上"で囲った場合、大文字と小文字が識別されるので、SQLの記述上面倒ですよ。
|
15
15
|
dateなどの予約語に該当するものだけに留めておいた方が良いと思います。
|
16
|
-
(そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど)
|
16
|
+
(そもそもはdateという項目名をentry_dateなどに変更した方がいいと思いますけど)
|
17
|
+
|
18
|
+
追記(施策別集計)
|
19
|
+
---
|
20
|
+
|
21
|
+
```SQL
|
22
|
+
select shisaku_id, shisaku_name, new_or_existing, count(*) as new_or_existing_count
|
23
|
+
from (
|
24
|
+
select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
|
25
|
+
from (
|
26
|
+
select *
|
27
|
+
,(select sum(coalesce("point_A",0)+coalesce("point_B",0))
|
28
|
+
from point_rireki
|
29
|
+
where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
|
30
|
+
) as exist_point
|
31
|
+
from campaign cmpgn
|
32
|
+
) step1
|
33
|
+
) step2
|
34
|
+
group by shisaku_id, shisaku_name, new_or_existing
|
35
|
+
```
|
2
推敲
answer
CHANGED
@@ -5,7 +5,7 @@
|
|
5
5
|
select *
|
6
6
|
,(select sum(coalesce("point_A",0)+coalesce("point_B",0))
|
7
7
|
from point_rireki
|
8
|
-
where user_id=cmpgn.user_id and "date"
|
8
|
+
where user_id=cmpgn.user_id and "date" between cmpgn.apply_date - '3 months'::interval and cmpgn.apply_date
|
9
9
|
) as exist_point
|
10
10
|
from campaign cmpgn
|
11
11
|
) step1
|
1
推敲
answer
CHANGED
@@ -5,7 +5,7 @@
|
|
5
5
|
select *
|
6
6
|
,(select sum(coalesce("point_A",0)+coalesce("point_B",0))
|
7
7
|
from point_rireki
|
8
|
-
where user_id=cmpgn.user_id and "date" >= cmpgn.apply_date - '3 months'::interval and "date" < cmpgn.apply_date
|
8
|
+
where user_id=cmpgn.user_id and "date" >= cmpgn.apply_date - '3 months'::interval and "date" <= cmpgn.apply_date
|
9
9
|
) as exist_point
|
10
10
|
from campaign cmpgn
|
11
11
|
) step1
|