特定の商品を買った人が２回目以降にどの商品を購入しているかを求めたい

Question

前提・実現したいこと

顧客ID,購入日,購入回数,商品コード,枝番,商品名という項目で構成された購入回数テーブルがあります。
この購入回数というのは、同じ顧客IDで複数回購入した場合のN回目という値が入っています。

|行数|顧客ID|購入日|購入回数|商品名|
|:--|:--:|--:|
|1|001|2020/1/1|1|AAA|
|2|001|2020/1/2|2|CCC|
|3|001|2020/1/3|3|DDD|
|4|002|2020/1/1|1|BBB|
|5|002|2020/1/2|2|CCC|
|6|003|2020/1/1|1|DDD|
|7|003|2020/1/2|2|EEE|

このテーブルから購入回数１回目にAAAまたはBBBという特定の2種類の商品を買った人が２回目以降にどの商品を購入しているかを求めるSQLを書きたいのですが、うまくいきません。

試したこと

select 顧客ID,購入日,商品名,購入回数
from 購入回数テーブル
where 購入回数 not in(
select 購入回数 from 購入回数テーブル 
where
購入回数=1 and 商品名 like 'AAA%'
and
購入回数=1 and 商品名 like 'BBB%'
) 

上記のSQLを実施したところ、なかなか値が返ってきません。
limit 100 をつけると1秒程度
limit 1000 だと 10秒くらい
limit 10000 だと 120秒くらい
で返ってきます。
購入回数テーブルのレコード数は118万ほどです。

書き方によるパフォーマンスの問題なのか、そもそも上記サブクエリ付きのSQLで求めたい結果が得られるのかの判断がつかないのですが、目的としては上述の通り「購入回数１回目にAAAまたはBBBという特定の2種類の商品を買った人が２回目以降にどの商品を購入しているかを求める」です。
どなたかご教示いただけませんでしょうか。
よろしくお願いします。

環境

Macローカルpostgresql（pgadmin）

Answer 1

上記のSQLを実施したところ、なかなか値が返ってきません。

SQL購入履歴から購入回数ごとの購入商品の傾向を把握したい
の関連質問ですね。

ビューを何段かネストした表に対して
抽出条件に 同じ表に対するサブクエリ を指定なさっているので
高パフォーマンスにはならないかもしれないですね。

ご提示いただいたSQLは、ビューにする前の表を用いて
チューニングすると高速に処理できます。
以下のように書き換えてはいかがでしょう。
( サンプルなので snt-fさんの環境では CREATE .. INSERT... は不要です )

CREATE TABLE 購入履歴テーブル
(
      顧客ID varchar(20)
    , 購入日 date
    , 商品名 varchar(10)
    , 数量   float
);

CREATE INDEX idx_buy_history 
          ON 購入履歴テーブル ( 顧客ID, 購入日 );

INSERT INTO 購入履歴テーブル
( 顧客ID, 購入日, 商品名, 数量 )
VALUES
( '00', '2021-01-01', 'AAAA',  5 ),
( '00', '2021-01-01', 'GAGA',  1 ),
( '00', '2021-01-02', 'CCCD', 10 ),
( '00', '2021-01-31', 'XXXX',  4 ),
( '11', '2021-01-02', 'IAAA', 19 ),
( '11', '2021-01-02', 'DDDT', 11 ),
( '11', '2021-01-16', 'WWWP',  4 ),
( '22', '2021-01-08', 'BBBQ',  7 ),
( '22', '2021-01-08', 'HBHB', 13 ),
( '22', '2021-01-11', 'BBBA',  8 ),
( '22', '2021-01-14', 'CMMA',  9 ),
( '22', '2021-01-14', 'XXXX',  5 ),
( '22', '2021-01-31', 'EEEN', 11 ),
( '33', '2021-02-04', 'CCCD',  5 ),
( '33', '2021-02-07', 'CCCD', 10 ),
( '44', '2021-01-15', 'BBBA', 10 ),
( '44', '2021-01-15', 'QQQX',  7 ),
( '44', '2021-01-18', 'AXAX',  9 );

-- EXPLAIN ( costs off, analyze on )
WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS
(
    SELECT 顧客ID
         , 購入日
         , row_number() over( partition by 顧客ID order by 購入日 )
    FROM 購入履歴テーブル
    GROUP BY 顧客ID
           , 購入日
),
   step2 ( 顧客ID, 購入日border ) AS
(
    SELECT x.顧客ID
         , max( x.購入日 )
    FROM 購入履歴テーブル x
    JOIN step1            y
    USING ( 顧客ID, 購入日 )
    WHERE y.購入回数 = 1
      AND ( x.商品名 like 'AAA%' OR x.商品名 like 'BBB%' )
    GROUP BY x.顧客ID
)

SELECT x.*
FROM 購入履歴テーブル x
JOIN step2            y
USING ( 顧客ID )
WHERE x.購入日 > y.購入日border
;

以下に多段クエリを個々に分離して、どういった過程を経て最終結果となるのか
途中の経過も掲載しておきますね。参考になればいいのですけど。

■step1

※row_number関数で 顧客id, 購入日ごとのグループ連番を付与

顧客id　　購入日　　購入回数
------------------------------
　00　　2021-01-01　　　1
　00　　2021-01-02　　　2
　00　　2021-01-31　　　3
　11　　2021-01-02　　　1
　11　　2021-01-16　　　2
　22　　2021-01-08　　　1
　22　　2021-01-11　　　2
　22　　2021-01-14　　　3
　22　　2021-01-31　　　4
　33　　2021-02-04　　　1
　33　　2021-02-07　　　2
　44　　2021-01-15　　　1
　44　　2021-01-18　　　2

■step2

※初回購入時に 商品AAA% または 商品BBB% を購入した
　顧客id と 最終購入日を結果セットとします

※審査対象が常に初回購入なら step1での集約は min(購入日) で事足りますし
　step2 ではグループ化も不要なのですけど
　購入回数の上限を自由に指定できないようでは汎用性に欠けます

顧客id　　購入日border
-------------------------
　00　　　2021-01-01
　22　　　2021-01-08
　44　　　2021-01-15

■最終結果

顧客id　　購入日　　　商品　　 数量
-------------------------------------
　00　　2021-01-02　　CCCD　　　10
　00　　2021-01-31　　XXXX　　　 4
　22　　2021-01-11　　BBBA　　　 8
　22　　2021-01-14　　CMMA　　　 9
　22　　2021-01-14　　XXXX　　　 5
　22　　2021-01-31　　EEEN　　　11
　44　　2021-01-18　　AXAX　　　 9

回答文中のSQLで、150万行以上の行を返すような抽出条件を付与してみたところ
私の環境では1秒未満で結果が表示されました。以下はその実行計画です。

Nested Loop (actual time=0.055..777.994 rows=1511056 loops=1)
 Planning Time: 0.163 ms
 Execution Time: 810.484 ms

　
追記:

takanaweb5さんから

結合を使用しなくても実行できる方法を紹介します。

との発言がありましたので回答に補足しておきます。
私が集計したビューとの結合を用い、且つ

審査対象が常に初回購入なら step1での集約は min(購入日) で事足ります
購入回数の上限を自由に指定できないようでは汎用性に欠けます

と発言した意図は
shintaro1001さんの職種や職務にもよるでしょうけど
もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。
たとえば

初回購入時 または ２回目の購入時に
特定の商品( AAA%, BBB% )をお買い上げいただいた
リピーターを調査対象として
３回目～５回目購入の全レコードを表示する

といった命題は、以下のようなSQL( 応用 )で表現が可能です。
また、実行計画を分析した限りINDEXは必須だと考えています。

-- ・購入履歴テーブルに「購入回数」フィールドは無い前提
-- ・データは先に記述したINSERT文のを再利用

WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS
(
    SELECT 顧客ID
         , 購入日
         , row_number() over( partition by 顧客ID order by 購入日 )
    FROM 購入履歴テーブル
    GROUP BY 顧客ID
           , 購入日
),
   step2 ( 顧客ID, 購入日from, 購入日to, 常連level ) AS
(
    SELECT x.顧客ID
         , min( x.購入日 ) filter( where y.購入回数 = 3 )
         , max( x.購入日 )
         , max( y.購入回数 )
    FROM 購入履歴テーブル x
    JOIN step1            y
    USING ( 顧客ID, 購入日 )
    WHERE y.購入回数 <= 5
    GROUP BY x.顧客ID
    HAVING sum( ( y.購入回数 <= 2 AND x.商品名 ~ '^[AB]{3,}' )::int ) > 0
       AND max( y.購入回数 ) > 2
)

SELECT x.*
     , y.常連level
FROM 購入履歴テーブル x
JOIN step2            y
USING ( 顧客ID )
WHERE x.購入日 between y.購入日from and y.購入日to
;

顧客id　　購入日　　 商品名　 数量　　常連level
------------------------------------------------
　00　　2021-01-31　　XXXX　　　4　　　　3
　22　　2021-01-14　　CMMA　　　9　　　　4
　22　　2021-01-14　　XXXX　　　5　　　　4
　22　　2021-01-31　　EEEN　　 11　　　　4

Comments

[shintaro1001]

> SQL購入履歴から購入回数ごとの購入商品の傾向を把握したい
> の関連質問ですね。
その節はありがとうございました。
前回教えていただいた方法で出力した結果をテーブルとして取り込んで今回のデータ分析を行いました。
今回も意図するデータを取得することができました。ありがとうございました。

Answer 2

すでに解決済みですが
結合を使用しなくても実行できる方法を紹介します。
また、わざわざこのSQLのために、INDEXを作成する必要もないと思います。

WITH SUB AS
(
SELECT 
  顧客ID
, 購入日
, 商品名
, 購入回数
-- 各行に、顧客IDごとに購入回数順にソートした先頭のレコードの値を追加します(=購入回数１回目の商品)
, FIRST_VALUE(商品名) OVER( partition by 顧客ID order by 購入回数 ) AS 初回商品 
FROM 購入履歴テーブル
)

SELECT
  顧客ID
, 購入日
, 商品名
, 購入回数
FROM SUB
WHERE 購入回数 > 1
  AND (初回商品 LIKE 'AAA%' OR 初回商品 LIKE 'BBB%')

Comments

[mayu-]

顧客ID + 購入日ごとに 商品を１点しか購入しない
という前提でないと、first_value関数では要件を満たさないので
今回のケースでは問題がありそうですよ。

[takanaweb5]

コメントありがとうございます

> このテーブルから購入回数１回目にAAAまたはBBBという特定の2種類の商品を買った人が２回目以降に

質問文からは、初日以降　および　２日目以降とは読み取れずに
あくまで購入日に関係なく　購入回数１回目　および２回目以降と読み取れます。

[mayu-]

申し訳ありません。ちょっと言葉足らずだったかもしれません。
質問文に
> 顧客ID,購入日,購入回数,商品コード,枝番,商品名
というフィールドの構成が掲載されています。

枝番の列があるということは、同一の購入回数で複数のレコードが存在する
ということにはならないでしょうか。

[takanaweb5]

回答者としては考えすぎのような気がします。
回答に問題があれば質問者さんがコメントされるでしょう。

[shintaro1001]

お二方ともご提案ありがとうございます。
質問の情報が不足していたかもしれません。
mayuさんが補足で推測されているように
> もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。
まさに上記のような分析を行っております。

とはいえまだまだスキルが不足しており、ご提示いただいたSQLのカラム名やテーブル名を実際のものに書き換えて実行して結果が出ればOK程度の状態ですので、様々な角度からいただいた回答を元に理解を深めていっているところです。
引き続きよろしくお願いします。

Answer 3

書き方はいろいろあると思いますが、たとえば以下のようなSQLで課題は達成できるかと思います。

SELECT *
FROM 購入回数テーブル A
WHERE EXISTS (
  SELECT *
  FROM 購入回数テーブル B
  WHERE A.顧客ID = B.顧客ID
  AND B.購入回数 = 1
  AND (B.商品名 LIKE 'AAA%' OR B.商品名 LIKE 'BBB%')
);

Comments

[shinoharat]

> １回目にAAAまたはBBBという特定の2種類の商品を買った人が２回目以降にどの商品を購入しているかを求めるSQL

なので、EXISTS の後に "and A.購入回数 > 1" が必要な気がしますが、いかがでしょうか？