teratail
回答率
85.29%
質問するログイン新規登録
トップに関する質問SQL:既存の条件に別の条件を抽出するフラグを追加したい

編集履歴

質問編集履歴

10

文字追記

2018/03/22 03:49

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -40,7 +40,7 @@
40
40
  【アウトプットイメージ】
41
41
  下記のような　2つのフラグを掛け合わせた数の抽出を行おうと思っています。　
42
42
  (＊2つのフラグを掛け合わせて、各々ユニークユーザー数ごとに集計
43
- 　→1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
43
+ 　→ユーザー集計例：1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
44
44
 
45
45
  キャンペーン利用の有無　　| ユーザー属性　　| 対象者数(下記のテーブル内容の数を試算した値です)
46
46
  "キャンペーン利用有り"　　　　　　F1 1

9

文字修正

2018/03/22 03:49

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -40,7 +40,7 @@
40
40
  【アウトプットイメージ】
41
41
  下記のような　2つのフラグを掛け合わせた数の抽出を行おうと思っています。　
42
42
  (＊2つのフラグを掛け合わせて、各々ユニークユーザー数ごとに集計
43
- 　→キャンペーン利用有り,1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
43
+ 　→1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
44
44
 
45
45
  キャンペーン利用の有無　　| ユーザー属性　　| 対象者数(下記のテーブル内容の数を試算した値です)
46
46
  "キャンペーン利用有り"　　　　　　F1 1

8

データの修正を行い、アウトプットイメージのデータ数を合わせるようにしました

2018/03/22 03:43

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -20,7 +20,7 @@
20
20
  　　　　　　　　　　　　　　　　　　　　　　　apply_idがPKになります。
21
21
 
22
22
 
23
- 　　　[テーブルの項目について](http://sqlfiddle.com/#!17/c090a/1)
23
+ 　　　[テーブルの項目について](http://sqlfiddle.com/#!17/9012a/1)
24
24
  　　　-fuyo_pt_tj, kan_pt_tj：ポイントのカラムになります。
25
25
  -kihon_id：ユーザーidになります
26
26
  -gender：1-男性、2-女性、3-不明となります
@@ -36,22 +36,25 @@
36
36
  　　・キャンペーンテーブルにおいて、2/6までの応募日でデータを絞りたいと思っています。
37
37
 
38
38
 
39
+
39
40
  【アウトプットイメージ】
40
41
  下記のような　2つのフラグを掛け合わせた数の抽出を行おうと思っています。　
42
+ (＊2つのフラグを掛け合わせて、各々ユニークユーザー数ごとに集計
43
+ 　→キャンペーン利用有り,1010ユーザーは、履歴上は"あり"該当2回、"なし"該当1回ですが、対象者数としては、それぞれに"1"ずつを計上)
41
44
 
42
- キャンペーン利用の有無　　| ユーザー属性　　| 対象者数(*データ数は仮置きしています)
45
+ キャンペーン利用の有無　　| ユーザー属性　　| 対象者数(下記のテーブル内容の数を試算した値です)
43
- "キャンペーン利用有り"　　　　　　F1 30
46
+ "キャンペーン利用有り"　　　　　　F1 1
44
- "キャンペーン利用有り"　　　　　　F2 10
47
+ "キャンペーン利用有り"　　　　　　F2 0
45
- "キャンペーン利用有り"　　　　　　F3 40
48
+ "キャンペーン利用有り"　　　　　　F3 0
46
- "キャンペーン利用有り"　　　　　　M1 10
49
+ "キャンペーン利用有り"　　　　　　M1 1
47
- "キャンペーン利用有り"　　　　　　M2 50
50
+ "キャンペーン利用有り"　　　　　　M2 0
48
- "キャンペーン利用有り"　　　　　　M3 30
51
+ "キャンペーン利用有り"　　　　　　M3 0
49
- "キャンペーン利用無し"　　　　　　F1 30
52
+ "キャンペーン利用無し"　　　　　　F1 1
50
- "キャンペーン利用無し"　　　　　　F2 40
53
+ "キャンペーン利用無し"　　　　　　F2 1
51
- "キャンペーン利用無し"　　　　　　F3 40
54
+ "キャンペーン利用無し"　　　　　　F3 0
52
- "キャンペーン利用無し"　　　　　　M1 20
55
+ "キャンペーン利用無し"　　　　　　M1 0
53
- "キャンペーン利用無し"　　　　　　M2 60
56
+ "キャンペーン利用無し"　　　　　　M2 1
54
- "キャンペーン利用無し"　　　　　　M3 20
57
+ "キャンペーン利用無し"　　　　　　M3 0
55
58
 
56
59
 
57
60
  〈テーブルの内容〉
@@ -63,16 +66,20 @@
63
66
  INSERT INTO point_rireki2
64
67
  ("p_date", "kihon_id", "fuyo_pt_tj", "kan_pt_tj", "old", "gender", "area")
65
68
  VALUES
66
- ('2018-01-01', 1001, 10, 200, 10, 1, '東京'),
69
+ ('2018-01-01', 1001, 10, 200, 20, 1, '東京'),
67
70
  ('2018-01-17', 1005, 100, 50, 22, 3, '千葉'),
68
71
  ('2018-01-22', 1010, 99, 10, 30, 2, '神奈川'),
69
- ('2018-01-25', 1001, 10, 200, NULL, 1, '東京'),
72
+ ('2018-01-25', 1001, 10, 200, 20, 1, '東京'),
70
73
  ('2018-01-26', 1015, 190, 20, 40, 3, '東京'),
71
74
  ('2018-01-28', 1010, 120, 90, 30, 2, '神奈川'),
72
75
  ('2018-02-01', 1010, 170, 80, 30, 2, '神奈川'),
73
- ('2018-02-03', 1015, 40, 280, -3, 1, '東京')
76
+ ('2018-02-03', 1015, 40, 280, 40, 3, '東京'),
77
+ ('2018-02-03', 1020, 10, 190, 40, 2, '神奈川'),
78
+ ('2018-02-03', 1030, 70, 20, 30, 3, '神奈川'),
79
+ ('2018-02-04', 1045, 50, 180, 44, 1, '東京')　　
74
80
 
75
81
 
82
+
76
83
  CREATE TABLE campaign
77
84
  ("apply_id" int, "shisaku_id" varchar(6), "shisaku_name" varchar(3), "kihon_id" int, "apply_date" date)
78
85
  ;

7

悩んでいる点の追記をしました

2018/03/22 03:23

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -105,7 +105,7 @@
105
105
  　　・そうでない場合→キャンペーン利用無し
106
106
  とすればよいのではと思ったのですが、、
107
107
  　その場合、ユニークユーザーのカウント(それぞれの条件に該当する場合は、それぞれ1カウント)にならないのではないかとも思い、悩んでおります。。。
108
- どういった形で抽出方法を考えるのがよいでしょうか。。
108
+ どういった形で抽出方法を考えるのがよいでしょうか。。(また、検討中の内容だとキャンペーンなしが該当しないため、その部分の抽出についても悩んでいます)
109
109
 
110
110
 
111
111
  【検討中のフラグの仮案】

6

アウトプットイメージの対象者数の仮置きの旨を記述

2018/03/22 02:08

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -39,7 +39,7 @@
39
39
  【アウトプットイメージ】
40
40
  下記のような　2つのフラグを掛け合わせた数の抽出を行おうと思っています。　
41
41
 
42
- キャンペーン利用の有無　　| ユーザー属性　　| 対象者数
42
+ キャンペーン利用の有無　　| ユーザー属性　　| 対象者数(*データ数は仮置きしています)
43
43
  "キャンペーン利用有り"　　　　　　F1 30
44
44
  "キャンペーン利用有り"　　　　　　F2 10
45
45
  "キャンペーン利用有り"　　　　　　F3 40
@@ -47,11 +47,11 @@
47
47
  "キャンペーン利用有り"　　　　　　M2 50
48
48
  "キャンペーン利用有り"　　　　　　M3 30
49
49
  "キャンペーン利用無し"　　　　　　F1 30
50
- "キャンペーン利用無し"　　　　　　F2 10
50
+ "キャンペーン利用無し"　　　　　　F2 40
51
51
  "キャンペーン利用無し"　　　　　　F3 40
52
- "キャンペーン利用無し"　　　　　　M1 10
52
+ "キャンペーン利用無し"　　　　　　M1 20
53
- "キャンペーン利用無し"　　　　　　M2 50
53
+ "キャンペーン利用無し"　　　　　　M2 60
54
- "キャンペーン利用無し"　　　　　　M3 30
54
+ "キャンペーン利用無し"　　　　　　M3 20
55
55
 
56
56
 
57
57
  〈テーブルの内容〉

5

ご指摘頂いた点(追記内容部分、アウトプットイメージ)に関して、修正を行いました

2018/03/22 01:59

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -6,7 +6,8 @@
6
6
  　　・ポイント履歴のテーブルとキャンペーンのテーブルを突き合わせて、下記を表すフラグを追記したい
7
7
  　　　　　・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴と一致する場合　→　"キャンペーン利用有り"
8
8
  　　　　　・ポイント履歴のテーブルに利用履歴があり、キャンペーンのテーブルの(応募)履歴にはデータがない場合　→　"キャンペーン利用無し"
9
- 　　　　同一ユーザーでキャンペーン利用をしているケースもあれば、していないケースもあると思うので、その場合はそれぞれ1カウント扱いとしたいです。
9
+ 　　　　(ポイント履歴内には)同一ユーザーでキャンペーン利用をしているケースもあれば、していないケースもあると思うので、
10
+ 　　　　その場合はそれぞれ1カウント扱いとしたいです。
10
11
  　　　　
11
12
  　　　　　　
12
13
  　　〈仕様〉
@@ -15,7 +16,7 @@
15
16
  　　　　　　　　　　　　　　　　　　　　　　　　　　(キャンペーンの利用/非利用に関係なく履歴が入っています)
16
17
 
17
18
  ⓶キャンペーンのテーブル(campaign):キャンペーン施策によるユーザーの応募履歴が入ったテーブルです。
18
- 　　　　　　　　　　　　　　　　　　　　　　　apply_id ,shisaku_id にNULLは存在しないため、施策外のデータは入っていません。
19
+ 　　　　　　　　　　　　　　　　　　　　　　　apply_id ,shisaku_id にNULLは存在しないため、施策外のデータ(=キャンペーン非利用のデータ)は入っていません。
19
20
  　　　　　　　　　　　　　　　　　　　　　　　apply_idがPKになります。
20
21
 
21
22
 
@@ -35,7 +36,24 @@
35
36
  　　・キャンペーンテーブルにおいて、2/6までの応募日でデータを絞りたいと思っています。
36
37
 
37
38
 
39
+ 【アウトプットイメージ】
40
+ 下記のような　2つのフラグを掛け合わせた数の抽出を行おうと思っています。　
38
41
 
42
+ キャンペーン利用の有無　　| ユーザー属性　　| 対象者数
43
+ "キャンペーン利用有り"　　　　　　F1 30
44
+ "キャンペーン利用有り"　　　　　　F2 10
45
+ "キャンペーン利用有り"　　　　　　F3 40
46
+ "キャンペーン利用有り"　　　　　　M1 10
47
+ "キャンペーン利用有り"　　　　　　M2 50
48
+ "キャンペーン利用有り"　　　　　　M3 30
49
+ "キャンペーン利用無し"　　　　　　F1 30
50
+ "キャンペーン利用無し"　　　　　　F2 10
51
+ "キャンペーン利用無し"　　　　　　F3 40
52
+ "キャンペーン利用無し"　　　　　　M1 10
53
+ "キャンペーン利用無し"　　　　　　M2 50
54
+ "キャンペーン利用無し"　　　　　　M3 30
55
+
56
+
39
57
  〈テーブルの内容〉
40
58
  ```lang-sql
41
59
  CREATE TABLE point_rireki2
@@ -88,15 +106,15 @@
88
106
  とすればよいのではと思ったのですが、、
89
107
  　その場合、ユニークユーザーのカウント(それぞれの条件に該当する場合は、それぞれ1カウント)にならないのではないかとも思い、悩んでおります。。。
90
108
  どういった形で抽出方法を考えるのがよいでしょうか。。
91
- 　　
92
109
 
110
+
93
111
  【検討中のフラグの仮案】
94
112
  ```lang-sql
95
113
  , case when
96
114
  exists(
97
115
  select 1 from point_rireki2 pnt left join campaign cmpgn
98
- where pnt.kihon_id = cmpgn.kihon_id
116
+ on pnt.kihon_id = cmpgn.kihon_id
99
- and pnt.p_date = cmpgn.apply_date
117
+ where pnt.p_date = cmpgn.apply_date
100
118
  )
101
119
  then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
102
120
  ```

4

タグの追加

2018/03/22 01:57

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
File without changes

3

質問文を簡略化させました

2018/03/22 00:22

投稿

iki
iki

スコア12

title CHANGED
@@ -1,1 +1,1 @@
1
- 【SQL】既存の条件フラグに、ユーザー履歴テーブルから別テーブルにある条件の有無を抽出するフラグを追加したい
1
+ SQL:既存の条件に別の条件を抽出するフラグを追加したい
body CHANGED
File without changes

2

文字修正

2018/03/21 09:00

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -10,7 +10,7 @@
10
10
  　　　　
11
11
  　　　　　　
12
12
  　　〈仕様〉
13
- 　　・テーブルの項目について：
13
+ 　　・テーブルについて：
14
14
  　　　　　⓵ポイント履歴のテーブル(point_rireki2 )：利用者へのポイント付与及び、利用者のポイント消費　に関する履歴データが入ったテーブルです。
15
15
  　　　　　　　　　　　　　　　　　　　　　　　　　　(キャンペーンの利用/非利用に関係なく履歴が入っています)
16
16
 

1

文字修正

2018/03/20 08:19

投稿

iki
iki

スコア12

title CHANGED
File without changes
body CHANGED
@@ -90,7 +90,7 @@
90
90
  どういった形で抽出方法を考えるのがよいでしょうか。。
91
91
  　　
92
92
 
93
- 【検討中の仮案】
93
+ 【検討中のフラグの仮案】
94
94
  ```lang-sql
95
95
  , case when
96
96
  exists(