teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップSQLに関する質問【SQL初心者】履歴状況のテーブルからフラグ作成し、主テーブルへ反映させたい

編集履歴

回答編集履歴

12

推敲

2018/03/16 00:33

投稿

sazi
sazi

スコア25430

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

追記

2018/03/16 00:33

投稿

sazi
sazi

スコア25430

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

修正

2018/03/15 10:06

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -60,7 +60,7 @@
60
60
  ※キャンペーン日、応募日をCTEにして、変更箇所を局所化
61
61
  ```SQL
62
62
  with param as (
63
- select '2018-01-26':: date campaign_Start,'2018-02-07':: date oubo_date_limit
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

修正

2018/03/14 09:28

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -55,11 +55,12 @@
55
55
  ```
56
56
  追記（条件変更２）
57
57
  ---
58
- キャンペーン日から３カ月前までを対象とした。
58
+ ・キャンペーン日から３カ月前までを対象とした。
59
+ ・ポイント内容も判定条件に加える
59
60
  ※キャンペーン日、応募日をCTEにして、変更箇所を局所化
60
61
  ```SQL
61
62
  with param as (
62
- select '2018-01-25':: date campaign_date,'2018-02-07':: date oubo_date
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
- where kihon_id=cmpgn.kihon_id and "date" between param.campaign_date - '3 months'::interval and param.campaign_date
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.oubo_date
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

修正

2018/03/14 09:25

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -55,8 +55,8 @@
55
55
  ```
56
56
  追記（条件変更２）
57
57
  ---
58
- キャンペーン日からの３カ月前を対象とした。
58
+ キャンペーン日から３カ月前までを対象とした。
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

修正

2018/03/14 07:46

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -59,7 +59,7 @@
59
59
  ※キャンペーン日をCTEにして、変更箇所を局所化
60
60
  ```SQL
61
61
  with param as (
62
- select '2018-02-07' :: date campaign_date
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.campaign_date
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

追記

2018/03/14 07:43

投稿

sazi
sazi

スコア25430

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
+ 追記（条件変更２）
57
+ ---
58
+ キャンペーン日からの３カ月前を対象とした。
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

追記

2018/03/14 06:36

投稿

sazi
sazi

スコア25430

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
+ 応募日からみて過去３カ月以内のポイント履歴があるものを識別するように変更。
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

追記

2018/03/14 04:34

投稿

sazi
sazi

スコア25430

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

追記

2018/03/13 10:30

投稿

sazi
sazi

スコア25430

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

推敲

2018/03/13 08:41

投稿

sazi
sazi

スコア25430

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" 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

推敲

2018/03/13 07:27

投稿

sazi
sazi

スコア25430

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