回答編集履歴
1
SQLの追記
answer
CHANGED
|
@@ -135,4 +135,76 @@
|
|
|
135
135
|
Nested Loop (actual time=0.055..777.994 rows=1511056 loops=1)
|
|
136
136
|
Planning Time: 0.163 ms
|
|
137
137
|
Execution Time: 810.484 ms
|
|
138
|
+
```
|
|
139
|
+
|
|
140
|
+
|
|
141
|
+
**追記:**
|
|
142
|
+
|
|
143
|
+
takanaweb5さんから
|
|
144
|
+
|
|
145
|
+
> 結合を使用しなくても実行できる方法を紹介します。
|
|
146
|
+
|
|
147
|
+
との発言がありましたので回答に補足しておきます。
|
|
148
|
+
私が集計したビューとの結合を用い、且つ
|
|
149
|
+
|
|
150
|
+
> 審査対象が常に初回購入なら step1での集約は min(購入日) で事足ります
|
|
151
|
+
> 購入回数の上限を自由に指定できないようでは汎用性に欠けます
|
|
152
|
+
|
|
153
|
+
と発言した意図は
|
|
154
|
+
shintaro1001さんの職種や職務にもよるでしょうけど
|
|
155
|
+
もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。
|
|
156
|
+
たとえば
|
|
157
|
+
|
|
158
|
+
初回購入時 または ２回目の購入時に
|
|
159
|
+
特定の商品( AAA%, BBB% )をお買い上げいただいた
|
|
160
|
+
リピーターを調査対象として
|
|
161
|
+
３回目～５回目購入の全レコードを表示する
|
|
162
|
+
|
|
163
|
+
といった命題は、以下のような`SQL`( 応用 )で表現が可能です。
|
|
164
|
+
また、実行計画を分析した限り`INDEX`は必須だと考えています。
|
|
165
|
+
|
|
166
|
+
```SQL
|
|
167
|
+
-- ・購入履歴テーブルに「購入回数」フィールドは無い前提
|
|
168
|
+
-- ・データは先に記述したINSERT文のを再利用
|
|
169
|
+
|
|
170
|
+
WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS
|
|
171
|
+
(
|
|
172
|
+
SELECT 顧客ID
|
|
173
|
+
, 購入日
|
|
174
|
+
, row_number() over( partition by 顧客ID order by 購入日 )
|
|
175
|
+
FROM 購入履歴テーブル
|
|
176
|
+
GROUP BY 顧客ID
|
|
177
|
+
, 購入日
|
|
178
|
+
),
|
|
179
|
+
step2 ( 顧客ID, 購入日from, 購入日to, 常連level ) AS
|
|
180
|
+
(
|
|
181
|
+
SELECT x.顧客ID
|
|
182
|
+
, min( x.購入日 ) filter( where y.購入回数 = 3 )
|
|
183
|
+
, max( x.購入日 )
|
|
184
|
+
, max( y.購入回数 )
|
|
185
|
+
FROM 購入履歴テーブル x
|
|
186
|
+
JOIN step1 y
|
|
187
|
+
USING ( 顧客ID, 購入日 )
|
|
188
|
+
WHERE y.購入回数 <= 5
|
|
189
|
+
GROUP BY x.顧客ID
|
|
190
|
+
HAVING sum( ( y.購入回数 <= 2 AND x.商品名 ~ '^[AB]{3,}' )::int ) > 0
|
|
191
|
+
AND max( y.購入回数 ) > 2
|
|
192
|
+
)
|
|
193
|
+
|
|
194
|
+
SELECT x.*
|
|
195
|
+
, y.常連level
|
|
196
|
+
FROM 購入履歴テーブル x
|
|
197
|
+
JOIN step2 y
|
|
198
|
+
USING ( 顧客ID )
|
|
199
|
+
WHERE x.購入日 between y.購入日from and y.購入日to
|
|
200
|
+
;
|
|
201
|
+
```
|
|
202
|
+
|
|
203
|
+
```result
|
|
204
|
+
顧客id 購入日 商品名 数量 常連level
|
|
205
|
+
------------------------------------------------
|
|
206
|
+
00 2021-01-31 XXXX 4 3
|
|
207
|
+
22 2021-01-14 CMMA 9 4
|
|
208
|
+
22 2021-01-14 XXXX 5 4
|
|
209
|
+
22 2021-01-31 EEEN 11 4
|
|
138
210
|
```
|