回答編集履歴
1
SQLの追記
test
CHANGED
|
@@ -273,3 +273,147 @@
|
|
|
273
273
|
Execution Time: 810.484 ms
|
|
274
274
|
|
|
275
275
|
```
|
|
276
|
+
|
|
277
|
+
|
|
278
|
+
|
|
279
|
+
|
|
280
|
+
|
|
281
|
+
**追記:**
|
|
282
|
+
|
|
283
|
+
|
|
284
|
+
|
|
285
|
+
takanaweb5さんから
|
|
286
|
+
|
|
287
|
+
|
|
288
|
+
|
|
289
|
+
> 結合を使用しなくても実行できる方法を紹介します。
|
|
290
|
+
|
|
291
|
+
|
|
292
|
+
|
|
293
|
+
との発言がありましたので回答に補足しておきます。
|
|
294
|
+
|
|
295
|
+
私が集計したビューとの結合を用い、且つ
|
|
296
|
+
|
|
297
|
+
|
|
298
|
+
|
|
299
|
+
> 審査対象が常に初回購入なら step1での集約は min(購入日) で事足ります
|
|
300
|
+
|
|
301
|
+
> 購入回数の上限を自由に指定できないようでは汎用性に欠けます
|
|
302
|
+
|
|
303
|
+
|
|
304
|
+
|
|
305
|
+
と発言した意図は
|
|
306
|
+
|
|
307
|
+
shintaro1001さんの職種や職務にもよるでしょうけど
|
|
308
|
+
|
|
309
|
+
もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。
|
|
310
|
+
|
|
311
|
+
たとえば
|
|
312
|
+
|
|
313
|
+
|
|
314
|
+
|
|
315
|
+
初回購入時 または ２回目の購入時に
|
|
316
|
+
|
|
317
|
+
特定の商品( AAA%, BBB% )をお買い上げいただいた
|
|
318
|
+
|
|
319
|
+
リピーターを調査対象として
|
|
320
|
+
|
|
321
|
+
３回目～５回目購入の全レコードを表示する
|
|
322
|
+
|
|
323
|
+
|
|
324
|
+
|
|
325
|
+
といった命題は、以下のような`SQL`( 応用 )で表現が可能です。
|
|
326
|
+
|
|
327
|
+
また、実行計画を分析した限り`INDEX`は必須だと考えています。
|
|
328
|
+
|
|
329
|
+
|
|
330
|
+
|
|
331
|
+
```SQL
|
|
332
|
+
|
|
333
|
+
-- ・購入履歴テーブルに「購入回数」フィールドは無い前提
|
|
334
|
+
|
|
335
|
+
-- ・データは先に記述したINSERT文のを再利用
|
|
336
|
+
|
|
337
|
+
|
|
338
|
+
|
|
339
|
+
WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS
|
|
340
|
+
|
|
341
|
+
(
|
|
342
|
+
|
|
343
|
+
SELECT 顧客ID
|
|
344
|
+
|
|
345
|
+
, 購入日
|
|
346
|
+
|
|
347
|
+
, row_number() over( partition by 顧客ID order by 購入日 )
|
|
348
|
+
|
|
349
|
+
FROM 購入履歴テーブル
|
|
350
|
+
|
|
351
|
+
GROUP BY 顧客ID
|
|
352
|
+
|
|
353
|
+
, 購入日
|
|
354
|
+
|
|
355
|
+
),
|
|
356
|
+
|
|
357
|
+
step2 ( 顧客ID, 購入日from, 購入日to, 常連level ) AS
|
|
358
|
+
|
|
359
|
+
(
|
|
360
|
+
|
|
361
|
+
SELECT x.顧客ID
|
|
362
|
+
|
|
363
|
+
, min( x.購入日 ) filter( where y.購入回数 = 3 )
|
|
364
|
+
|
|
365
|
+
, max( x.購入日 )
|
|
366
|
+
|
|
367
|
+
, max( y.購入回数 )
|
|
368
|
+
|
|
369
|
+
FROM 購入履歴テーブル x
|
|
370
|
+
|
|
371
|
+
JOIN step1 y
|
|
372
|
+
|
|
373
|
+
USING ( 顧客ID, 購入日 )
|
|
374
|
+
|
|
375
|
+
WHERE y.購入回数 <= 5
|
|
376
|
+
|
|
377
|
+
GROUP BY x.顧客ID
|
|
378
|
+
|
|
379
|
+
HAVING sum( ( y.購入回数 <= 2 AND x.商品名 ~ '^[AB]{3,}' )::int ) > 0
|
|
380
|
+
|
|
381
|
+
AND max( y.購入回数 ) > 2
|
|
382
|
+
|
|
383
|
+
)
|
|
384
|
+
|
|
385
|
+
|
|
386
|
+
|
|
387
|
+
SELECT x.*
|
|
388
|
+
|
|
389
|
+
, y.常連level
|
|
390
|
+
|
|
391
|
+
FROM 購入履歴テーブル x
|
|
392
|
+
|
|
393
|
+
JOIN step2 y
|
|
394
|
+
|
|
395
|
+
USING ( 顧客ID )
|
|
396
|
+
|
|
397
|
+
WHERE x.購入日 between y.購入日from and y.購入日to
|
|
398
|
+
|
|
399
|
+
;
|
|
400
|
+
|
|
401
|
+
```
|
|
402
|
+
|
|
403
|
+
|
|
404
|
+
|
|
405
|
+
```result
|
|
406
|
+
|
|
407
|
+
顧客id 購入日 商品名 数量 常連level
|
|
408
|
+
|
|
409
|
+
------------------------------------------------
|
|
410
|
+
|
|
411
|
+
00 2021-01-31 XXXX 4 3
|
|
412
|
+
|
|
413
|
+
22 2021-01-14 CMMA 9 4
|
|
414
|
+
|
|
415
|
+
22 2021-01-14 XXXX 5 4
|
|
416
|
+
|
|
417
|
+
22 2021-01-31 EEEN 11 4
|
|
418
|
+
|
|
419
|
+
```
|