前提・実現したいこと
顧客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|
このテーブルから購入回数1回目にAAAまたはBBBという特定の2種類の商品を買った人が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で求めたい結果が得られるのかの判断がつかないのですが、目的としては上述の通り「購入回数1回目にAAAまたはBBBという特定の2種類の商品を買った人が2回目以降にどの商品を購入しているかを求める」です。
どなたかご教示いただけませんでしょうか。
よろしくお願いします。
環境
Macローカルpostgresql(pgadmin)
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答3件
0
ベストアンサー
上記のSQLを実施したところ、なかなか値が返ってきません。
SQL購入履歴から購入回数ごとの購入商品の傾向を把握したい
の関連質問ですね。
ビューを何段かネストした表に対して
抽出条件に 同じ表に対するサブクエリ を指定なさっているので
高パフォーマンスにはならないかもしれないですね。
ご提示いただいたSQL
は、ビューにする前の表を用いて
チューニングすると高速に処理できます。
以下のように書き換えてはいかがでしょう。
( サンプルなので snt-fさんの環境では CREATE .. INSERT... は不要です )
SQL
1CREATE TABLE 購入履歴テーブル 2( 3 顧客ID varchar(20) 4 , 購入日 date 5 , 商品名 varchar(10) 6 , 数量 float 7); 8 9CREATE INDEX idx_buy_history 10 ON 購入履歴テーブル ( 顧客ID, 購入日 ); 11 12INSERT INTO 購入履歴テーブル 13( 顧客ID, 購入日, 商品名, 数量 ) 14VALUES 15( '00', '2021-01-01', 'AAAA', 5 ), 16( '00', '2021-01-01', 'GAGA', 1 ), 17( '00', '2021-01-02', 'CCCD', 10 ), 18( '00', '2021-01-31', 'XXXX', 4 ), 19( '11', '2021-01-02', 'IAAA', 19 ), 20( '11', '2021-01-02', 'DDDT', 11 ), 21( '11', '2021-01-16', 'WWWP', 4 ), 22( '22', '2021-01-08', 'BBBQ', 7 ), 23( '22', '2021-01-08', 'HBHB', 13 ), 24( '22', '2021-01-11', 'BBBA', 8 ), 25( '22', '2021-01-14', 'CMMA', 9 ), 26( '22', '2021-01-14', 'XXXX', 5 ), 27( '22', '2021-01-31', 'EEEN', 11 ), 28( '33', '2021-02-04', 'CCCD', 5 ), 29( '33', '2021-02-07', 'CCCD', 10 ), 30( '44', '2021-01-15', 'BBBA', 10 ), 31( '44', '2021-01-15', 'QQQX', 7 ), 32( '44', '2021-01-18', 'AXAX', 9 ); 33 34-- EXPLAIN ( costs off, analyze on ) 35WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS 36( 37 SELECT 顧客ID 38 , 購入日 39 , row_number() over( partition by 顧客ID order by 購入日 ) 40 FROM 購入履歴テーブル 41 GROUP BY 顧客ID 42 , 購入日 43), 44 step2 ( 顧客ID, 購入日border ) AS 45( 46 SELECT x.顧客ID 47 , max( x.購入日 ) 48 FROM 購入履歴テーブル x 49 JOIN step1 y 50 USING ( 顧客ID, 購入日 ) 51 WHERE y.購入回数 = 1 52 AND ( x.商品名 like 'AAA%' OR x.商品名 like 'BBB%' ) 53 GROUP BY x.顧客ID 54) 55 56SELECT x.* 57FROM 購入履歴テーブル x 58JOIN step2 y 59USING ( 顧客ID ) 60WHERE x.購入日 > y.購入日border 61;
以下に多段クエリを個々に分離して、どういった過程を経て最終結果となるのか
途中の経過も掲載しておきますね。参考になればいいのですけど。
result
1■step1 2 3※row_number関数で 顧客id, 購入日ごとのグループ連番を付与 4 5顧客id 購入日 購入回数 6------------------------------ 7 00 2021-01-01 1 8 00 2021-01-02 2 9 00 2021-01-31 3 10 11 2021-01-02 1 11 11 2021-01-16 2 12 22 2021-01-08 1 13 22 2021-01-11 2 14 22 2021-01-14 3 15 22 2021-01-31 4 16 33 2021-02-04 1 17 33 2021-02-07 2 18 44 2021-01-15 1 19 44 2021-01-18 2 20 21■step2 22 23※初回購入時に 商品AAA% または 商品BBB% を購入した 24 顧客id と 最終購入日を結果セットとします 25 26※審査対象が常に初回購入なら step1での集約は min(購入日) で事足りますし 27 step2 ではグループ化も不要なのですけど 28 購入回数の上限を自由に指定できないようでは汎用性に欠けます 29 30顧客id 購入日border 31------------------------- 32 00 2021-01-01 33 22 2021-01-08 34 44 2021-01-15 35 36■最終結果 37 38顧客id 購入日 商品 数量 39------------------------------------- 40 00 2021-01-02 CCCD 10 41 00 2021-01-31 XXXX 4 42 22 2021-01-11 BBBA 8 43 22 2021-01-14 CMMA 9 44 22 2021-01-14 XXXX 5 45 22 2021-01-31 EEEN 11 46 44 2021-01-18 AXAX 9
回答文中のSQL
で、150万行以上の行を返すような抽出条件を付与してみたところ
私の環境では1秒未満
で結果が表示されました。以下はその実行計画です。
EXPLAIN
1Nested Loop (actual time=0.055..777.994 rows=1511056 loops=1) 2 Planning Time: 0.163 ms 3 Execution Time: 810.484 ms
追記:
takanaweb5さんから
結合を使用しなくても実行できる方法を紹介します。
との発言がありましたので回答に補足しておきます。
私が集計したビューとの結合を用い、且つ
審査対象が常に初回購入なら step1での集約は min(購入日) で事足ります
購入回数の上限を自由に指定できないようでは汎用性に欠けます
と発言した意図は
shintaro1001さんの職種や職務にもよるでしょうけど
もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。
たとえば
初回購入時 または 2回目の購入時に
特定の商品( AAA%, BBB% )をお買い上げいただいた
リピーターを調査対象として
3回目~5回目購入の全レコードを表示する
といった命題は、以下のようなSQL
( 応用 )で表現が可能です。
また、実行計画を分析した限りINDEX
は必須だと考えています。
SQL
1-- ・購入履歴テーブルに「購入回数」フィールドは無い前提 2-- ・データは先に記述したINSERT文のを再利用 3 4WITH step1 ( 顧客ID, 購入日, 購入回数 ) AS 5( 6 SELECT 顧客ID 7 , 購入日 8 , row_number() over( partition by 顧客ID order by 購入日 ) 9 FROM 購入履歴テーブル 10 GROUP BY 顧客ID 11 , 購入日 12), 13 step2 ( 顧客ID, 購入日from, 購入日to, 常連level ) AS 14( 15 SELECT x.顧客ID 16 , min( x.購入日 ) filter( where y.購入回数 = 3 ) 17 , max( x.購入日 ) 18 , max( y.購入回数 ) 19 FROM 購入履歴テーブル x 20 JOIN step1 y 21 USING ( 顧客ID, 購入日 ) 22 WHERE y.購入回数 <= 5 23 GROUP BY x.顧客ID 24 HAVING sum( ( y.購入回数 <= 2 AND x.商品名 ~ '^[AB]{3,}' )::int ) > 0 25 AND max( y.購入回数 ) > 2 26) 27 28SELECT x.* 29 , y.常連level 30FROM 購入履歴テーブル x 31JOIN step2 y 32USING ( 顧客ID ) 33WHERE x.購入日 between y.購入日from and y.購入日to 34;
result
1顧客id 購入日 商品名 数量 常連level 2------------------------------------------------ 3 00 2021-01-31 XXXX 4 3 4 22 2021-01-14 CMMA 9 4 5 22 2021-01-14 XXXX 5 4 6 22 2021-01-31 EEEN 11 4
投稿2021/09/25 12:12
編集2021/09/25 23:37総合スコア335
0
すでに解決済みですが
結合を使用しなくても実行できる方法を紹介します。
また、わざわざこのSQLのために、INDEXを作成する必要もないと思います。
SQL
1WITH SUB AS 2( 3SELECT 4 顧客ID 5, 購入日 6, 商品名 7, 購入回数 8-- 各行に、顧客IDごとに購入回数順にソートした先頭のレコードの値を追加します(=購入回数1回目の商品) 9, FIRST_VALUE(商品名) OVER( partition by 顧客ID order by 購入回数 ) AS 初回商品 10FROM 購入履歴テーブル 11) 12 13SELECT 14 顧客ID 15, 購入日 16, 商品名 17, 購入回数 18FROM SUB 19WHERE 購入回数 > 1 20 AND (初回商品 LIKE 'AAA%' OR 初回商品 LIKE 'BBB%')
投稿2021/09/25 22:19
編集2021/09/25 22:31総合スコア359
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/09/26 00:18
2021/09/26 00:32
2021/09/26 00:44
2021/09/26 06:15
0
書き方はいろいろあると思いますが、たとえば以下のようなSQLで課題は達成できるかと思います。
SQL
1SELECT * 2FROM 購入回数テーブル A 3WHERE EXISTS ( 4 SELECT * 5 FROM 購入回数テーブル B 6 WHERE A.顧客ID = B.顧客ID 7 AND B.購入回数 = 1 8 AND (B.商品名 LIKE 'AAA%' OR B.商品名 LIKE 'BBB%') 9);
投稿2021/09/25 12:16
総合スコア2349
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/09/25 12:24 編集
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/09/25 17:37