質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.35%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

Q&A

解決済

3回答

2373閲覧

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

shintaro1001

総合スコア7

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

0グッド

5クリップ

投稿2021/09/25 09:41

前提・実現したいこと

顧客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ページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答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
mayu-

総合スコア335

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shintaro1001

2021/09/25 17:37

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

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
takanaweb5

総合スコア359

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mayu-

2021/09/25 23:45

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

2021/09/26 00:18

コメントありがとうございます > このテーブルから購入回数1回目にAAAまたはBBBという特定の2種類の商品を買った人が2回目以降に 質問文からは、初日以降 および 2日目以降とは読み取れずに あくまで購入日に関係なく 購入回数1回目 および2回目以降と読み取れます。
mayu-

2021/09/26 00:32

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

2021/09/26 00:44

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

2021/09/26 06:15

お二方ともご提案ありがとうございます。 質問の情報が不足していたかもしれません。 mayuさんが補足で推測されているように > もしかしたら、4C分析や4P分析を必要とされているのではないかと推測したからです。 まさに上記のような分析を行っております。 とはいえまだまだスキルが不足しており、ご提示いただいたSQLのカラム名やテーブル名を実際のものに書き換えて実行して結果が出ればOK程度の状態ですので、様々な角度からいただいた回答を元に理解を深めていっているところです。 引き続きよろしくお願いします。
guest

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

neko_the_shadow

総合スコア2349

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shinoharat

2021/09/25 12:24 編集

> 1回目にAAAまたはBBBという特定の2種類の商品を買った人が2回目以降にどの商品を購入しているかを求めるSQL なので、EXISTS の後に "and A.購入回数 > 1" が必要な気がしますが、いかがでしょうか?
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問