teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問【SQL初心者】日付カラムを基点に条件を絞る方法

編集履歴

質問編集履歴

5

現状困っている点の更新

2018/03/13 02:51

投稿

iki
iki

スコア12

test CHANGED
File without changes
test CHANGED
@@ -48,6 +48,34 @@
48
48
 
49
49
 
50
50
 
51
+ ・年月日のカラム内の日時から3カ月以内をフラグ付与の期間としたいです。
52
+
53
+ 　⇒テーブルには同一ユーザーIDが複数あるため、その各々の"各行"の年月日で抽出したいと思っております。
54
+
55
+
56
+
57
+ ・フラグの付与は、どのようにつけますか？ & フラグ付けの要件について
58
+
59
+ 　⇒各Ptがすべて0orNULLでない場合　⇒　新規会員
60
+
61
+ 　　各PtでどこかのPtカラムに値あり　⇒　既存会員
62
+
63
+ 　　といった新規/既存会員のカラムをテーブルに追加したいです
64
+
65
+
66
+
67
+ 現状、下記のように、初回日付で必ず"新規"の扱いとなってしまっているため、原因を教えて頂きたいです。(初回日付であっても、ポイントがある場合は"既存"の扱いとしたいです)
68
+
69
+ 　date user_id pt flag
70
+
71
+ 20171001 a 10 新規 　
72
+
73
+ 20171007 a 20 既存
74
+
75
+ 20171015 a '' 既存
76
+
77
+ 20171025 a 40 既存
78
+
51
79
 
52
80
 
53
81
  【実行環境】
@@ -64,31 +92,45 @@
64
92
 
65
93
  【現状】
66
94
 
67
- 頂いた回答をもとに、下記のように実装し、処理を行たところ、5分以上クエリが動いている状況が続き、回答が返ってきません
95
+ 皆様から頂いた回答より、
68
96
 
97
+ ・serialをPKに設定したテーブルを作り直し
98
+
99
+ ・インデックスをdateとuser_idに設定
100
+
69
- (ファイルサイズは、4万KBなのですが、量が大きすぎるのでしょうか？)
101
+ ・下記クエリを実行(処理時間はかかりますが、結果はでます)
102
+
103
+ ↓
104
+
105
+ 困っている点　が現在の状態になります。
106
+
107
+
108
+
109
+
70
110
 
71
111
 
72
112
 
73
113
  〈現時点での実装内容〉
74
114
 
75
- select
115
+ select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
76
116
 
77
- main.*,
117
+ from (
78
118
 
79
- case when exists(
119
+ select *
80
120
 
81
- select 1
121
+ ,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0)+coalesce(kan_pt_kg,0))
82
122
 
83
- from point_rireki sub
123
+ from point_rireki2
84
124
 
85
- where sub.date < main.date - interval'3 months'
125
+ where user_id=main.user_id and "date" >= main."date" - interval '3 months' and "date" < main."date"
86
126
 
87
- and main.user_id = sub.user_id
127
+ ) as exist_point
88
128
 
89
- )then '既存' else '新規' end as new_or_existing
129
+ from point_rireki2 main
90
130
 
131
+ ) step1
132
+
91
- from point_rireki main;
133
+ order by user_id, date
92
134
 
93
135
 
94
136
 

4

実行計画とインデックスの追加

2018/03/13 02:50

投稿

iki
iki

スコア12

test CHANGED
File without changes
test CHANGED
@@ -21,6 +21,18 @@
21
21
  ・ユーザーの属性
22
22
 
23
23
  (・作成したいフラグ)
24
+
25
+
26
+
27
+
28
+
29
+ また、実行計画としては、
30
+
31
+ ・ポイント履歴からフラグを追記
32
+
33
+ ・他のテーブルに、ユーザーiDやキャンペーンID、キャンペーンデバイスなどのキャンペーン周りのテーブルがあるので、ユーザーidにて紐づけて、キャンペーン内容、キャンペーン期間ごとのユーザーの抽出を行いたいと思ってます。
34
+
35
+
24
36
 
25
37
 
26
38
 
@@ -91,3 +103,19 @@
91
103
  まさに、このような感じのデータとなります。(属性は、細かく記載させていただくと年齢や性別などのデータです。userポイントは、その日付時点で付与されたポイントになります)
92
104
 
93
105
  http://sqlfiddle.com/#!17/72bf4/1
106
+
107
+
108
+
109
+
110
+
111
+ 【追記】
112
+
113
+ 対象のオブジェクトとそのバイトですが、
114
+
115
+ 　・object name: point_rireki
116
+
117
+ 　・bytes text : 68,108,288
118
+
119
+ ＊下記参考に、上記情報を追記しました
120
+
121
+ http://dqn.sakusakutto.jp/2011/11/postgresql.html

3

説明の追記

2018/03/12 05:06

投稿

iki
iki

スコア12

test CHANGED
File without changes
test CHANGED
@@ -88,6 +88,6 @@
88
88
 
89
89
  回答頂いた方から、このようなURLで共有できるものを教えていただきました。
90
90
 
91
- まさに、このような感じのデータとなります。(属性は、年齢や性別など。userポイントは、その日付時点で付与されたポイントになります)
91
+ まさに、このような感じのデータとなります。(属性は、細かく記載させていただくと年齢や性別などのデータです。userポイントは、その日付時点で付与されたポイントになります)
92
92
 
93
93
  http://sqlfiddle.com/#!17/72bf4/1

2

説明の追加

2018/03/12 04:11

投稿

iki
iki

スコア12

test CHANGED
File without changes
test CHANGED
@@ -16,7 +16,7 @@
16
16
 
17
17
  ・ユーザーiD
18
18
 
19
- ・ユーザーポイント
19
+ ・ユーザーポイント　　　　　　←日付カラム時点での付与されたポイントを示してます
20
20
 
21
21
  ・ユーザーの属性
22
22
 
@@ -77,3 +77,17 @@
77
77
  )then '既存' else '新規' end as new_or_existing
78
78
 
79
79
  from point_rireki main;
80
+
81
+
82
+
83
+
84
+
85
+
86
+
87
+ 【参照】
88
+
89
+ 回答頂いた方から、このようなURLで共有できるものを教えていただきました。
90
+
91
+ まさに、このような感じのデータとなります。(属性は、年齢や性別など。userポイントは、その日付時点で付与されたポイントになります)
92
+
93
+ http://sqlfiddle.com/#!17/72bf4/1

1

現在状況の追加

2018/03/12 04:10

投稿

iki
iki

スコア12

test CHANGED
File without changes
test CHANGED
@@ -31,3 +31,49 @@
31
31
  【困っている点】
32
32
 
33
33
  各々の各行で日付項目を確認し、その日付範囲から3カ月以内のユーザー履歴を確認する、といったことの実現方法がわからず困ってます。
34
+
35
+
36
+
37
+
38
+
39
+
40
+
41
+ 【実行環境】
42
+
43
+ Windows
44
+
45
+ Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています。
46
+
47
+
48
+
49
+
50
+
51
+
52
+
53
+ 【現状】
54
+
55
+ 頂いた回答をもとに、下記のように実装し、処理を行たところ、5分以上クエリが動いている状況が続き、回答が返ってきません
56
+
57
+ (ファイルサイズは、4万KBなのですが、量が大きすぎるのでしょうか？)
58
+
59
+
60
+
61
+ 〈現時点での実装内容〉
62
+
63
+ select
64
+
65
+ main.*,
66
+
67
+ case when exists(
68
+
69
+ select 1
70
+
71
+ from point_rireki sub
72
+
73
+ where sub.date < main.date - interval'3 months'
74
+
75
+ and main.user_id = sub.user_id
76
+
77
+ )then '既存' else '新規' end as new_or_existing
78
+
79
+ from point_rireki main;