SQL購入履歴から購入回数ごとの購入商品の傾向を把握したい

Question

前提・実現したいこと

購入履歴テーブルから回数ごとの商品の購入傾向を把握したいと考えています。
購入履歴として以下のようなテーブルがあります。
顧客ID,購入日,商品,数量,価格

同じ顧客が1回目に購入した商品と2回目以降に購入した商品の割合をクロス集計で知りたいです。
同じ商品を複数購入することもあります。

###出力イメージ
アウトプットのイメージとしては以下のような感じです。
回数/商品 　|　商品A | 商品B | 商品C| 商 品D | 商品E
|:--|:--:|--:|
1回目 | nn% | nn% | nn% | nn% | nn%
2回目 | nn% | nn% | nn% | nn% | nn%
3回目 | nn% | nn% | nn% | nn% | nn%
4回目 | nn% | nn% | nn% | nn% | nn%

###環境
Macローカルpostgresql（pgadmin）

よろしくお願いします。

Comments

[sazi]

「購入した商品の割合」について、何を１００％とした時の割合ですか？
例えば、「回数ごとの商品の購入」の傾向というのが、初回が一番多かったとか、回数を追うごとに増えていくというようなものですか？

[shintaro1001]

ご指摘ありがとうございます。
想定していたのは、初めて（または○回目）購入した人100%の購入した商品の内訳でした。

Answer 1

「N回目を算出する」を実行してみたのですが、10時間以上経っても終わらない状況です。
購入履歴のレコード数は160万件ほどです。

@haru666 さんの回答をいくつかチューニングすれば、高速化出来ると思います。

(1) 第１優先で、顧客IDによる絞り込みを行う

これは私のミスですが、SELECT DISTINCT の方でもwhere文でidを絞り込めば対象行を絞り込めるかもしれません。

@haru666 さんもコメントで書いていますが、テーブルの結合を行う前に、対象行を絞り込みます。

これだけで、劇的に高速化されると思います。

(2) 不等号によるテーブルの結合を回避する

SELECT 
    T1."顧客ID",
    (SELECT COUNT(*) FROM 
      (SELECT DISTINCT "顧客ID", "購入日" FROM sales) T2
       WHERE T2."顧客ID"=T1."顧客ID" AND T2."購入日"<=T1."購入日") AS "購入回数",
    T1."商品"
FROM
    sales T1;

でN回目を算出しようとしていますが、ここの部分はWINDOW関数のDENSE_RANK を使って、連番を振ることが可能です。

(3) クロス集計を、標準SQLのCASE文を使って実現する

ここの部分は、パフォーマンスとは関係ありませんが、@haru666さんの方法では、CREATE EXTENSION tablefunc; が必要であるということと、クロス集計用のテーブルを別途用意（例ではgoodsテーブル）しなければならないので、それを回避します。

WITH SUB1 AS 
(
SELECT *
FROM sales
WHERE 顧客ID = 1  -- ここで対象となる顧客IDを指定する
), SUB2 AS 
(
SELECT 
  DENSE_RANK() OVER (ORDER BY 購入日) AS 購入回数
, 商品
, 数量::float / SUM(数量) OVER (PARTITION BY 商品) AS 割合
FROM SUB1
)

SELECT
  購入回数
, MAX(CASE WHEN 商品 = '商品A' THEN 割合 ELSE 0 END) AS 商品A 
, MAX(CASE WHEN 商品 = '商品B' THEN 割合 ELSE 0 END) AS 商品B 
, MAX(CASE WHEN 商品 = '商品C' THEN 割合 ELSE 0 END) AS 商品C 
, MAX(CASE WHEN 商品 = '商品D' THEN 割合 ELSE 0 END) AS 商品D 
FROM SUB2
GROUP BY 購入回数

Answer 2

「N回目を算出する」を実行してみたのですが、10時間以上経っても終わらない状況です。
購入履歴のレコード数は160万件ほどです。

取り扱うデータ数が多く、ご希望の表示も簡単とは言い難いですから
まずは、出力イメージの構想を見直しましょう。

• 顧客ID毎に購入する商品の種類は毎回異なる
• 顧客ID毎に購入する商品の量( レコード数 )は毎回異なる

こういったレコードをクロス集計、いわゆる横持ちで表示させる場合、
全顧客で購入対象となった商品の種類だけ表示用の列が必要になります。
少なくとも、数十、数百といった単位にはなるでしょう。

また、特定のお客様お一人だけが購入された商品に至っては
値が記載されているのが１行のみ、あとは全てNullという結果セットになってしまい
見映えや実行効率が良いとは言えないですね。
そのため、この段階での結果セットは縦持ちをお薦めします。

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

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

INSERT INTO 購入履歴テーブル
( 顧客ID, 購入日, 商品, 数量 )
VALUES
( '1x', '2021-01-01', 'B',  5 ),
( '1x', '2021-01-01', 'D',  1 ),
( '1x', '2021-01-01', 'H',  2 ),
( '1x', '2021-01-01', 'G',  1 ),
( '1x', '2021-01-02', 'C', 10 ),
( '1x', '2021-01-02', 'G',  3 ),
( '1x', '2021-01-02', 'A', 11 ),
( '2y', '2021-01-02', 'I', 19 ),
( '2y', '2021-01-02', 'D', 11 ),
( '3z', '2021-01-01', 'A',  7 ),
( '3z', '2021-01-05', 'B',  8 ),
( '3z', '2021-01-10', 'C',  9 ),
( '3z', '2021-01-10', 'X',  5 ),
( '3z', '2021-01-20', 'D', 10 ),
( '3z', '2021-01-31', 'E', 11 );

-- EXPLAIN ANALYZE
WITH step1 ( 顧客ID, 購入日, 購入回数, 合計 ) AS
(
    SELECT 顧客ID
         , 購入日
         , row_number() over( partition by 顧客ID order by 購入日 )
         , sum( 数量 )
    FROM 購入履歴テーブル
    GROUP BY 顧客ID
           , 購入日
),
   step2 ( 顧客ID, 購入日, 購入回数, 合計, 誤差, 吸収対象 ) AS
(
    SELECT x.顧客ID
         , x.購入日
         , min( y.購入回数 )
         , min( y.合計 )
         , 1::numeric - sum( trunc( x.数量::numeric / y.合計::numeric, 3 ) )
         , max( x.商品 )
    FROM 購入履歴テーブル x
    JOIN step1            y
    USING ( 顧客ID, 購入日 )
    WHERE y.購入回数 <= 4
    GROUP BY x.顧客ID
           , x.購入日
)

SELECT x.顧客ID
     , x.購入日
     , y.購入回数
     , x.商品
     , x.数量
     , y.合計
     , row_number() 
           over( partition by x.顧客ID, x.購入日 
                     order by x.数量 desc, x.商品 ) 購入日順位
     , (
           trunc( x.数量::numeric / y.合計::numeric, 3 )
               +
           case
               when x.商品 = y.吸収対象 then y.誤差
               else 0
           end
       )::numeric * 100 割合
FROM 購入履歴テーブル x
JOIN step2            y
USING ( 顧客ID, 購入日 )
ORDER BY x.顧客ID
       , x.購入日
       , x.数量   desc
       , x.商品
;

顧客id    購入日    購入回数   商品    数量   合計  購入日順位    割合
-----------------------------------------------------------------------
 1x     2021-01-01      1       B       5       9       1        55.5
 1x     2021-01-01      1       H       2       9       2        22.3
 1x     2021-01-01      1       D       1       9       3        11.1
 1x     2021-01-01      1       G       1       9       4        11.1
 1x     2021-01-02      2       A      11      24       1        45.8
 1x     2021-01-02      2       C      10      24       2        41.6
 1x     2021-01-02      2       G       3      24       3        12.6
 2y     2021-01-02      1       I      19      30       1        63.4
 2y     2021-01-02      1       D      11      30       2        36.6
 3z     2021-01-01      1       A       7       7       1       100.0
 3z     2021-01-05      2       B       8       8       1       100.0
 3z     2021-01-10      3       C       9      14       1        64.2
 3z     2021-01-10      3       X       5      14       2        35.8
 3z     2021-01-20      4       D      10      10       1       100.0

＃ 購入数の割合は、合計しても100%になるとは限らず、
＃ 誤差の吸収処理を追加しても実用的な速度で結果が出たため、
＃ そのロジックも組み込んでいます。

以下、164万行のデータで 先に掲載したSQLを発行した実行計画です。
10回実行を繰り返したところ、私の環境では平均1.5秒で結果が表示されました。

実行環境:

• PostgreSQL 13.4 (x86_64)
• CentOS 8.4
• Memory: 4GB
• 顧客ID数: 18,000

GroupAggregate  (cost=120797.14..120988.05 rows=4492 width=88) (actual time=1043.515..1221.966 rows=70304 loops=1)
   Group Key: x."顧客id", x."購入日", y."購入回数", y."合計"
   ->  Sort  (cost=120797.14..120808.37 rows=4492 width=35) (actual time=1043.494..1059.543 rows=141359 loops=1)
         Sort Key: x."顧客id", x."購入日", y."購入回数", y."合計"
         Sort Method: external merge  Disk: 6232kB
         ->  Hash Join  (cost=84930.06..120524.63 rows=4492 width=35) (actual time=642.622..1000.066 rows=141359 loops=1)
               Hash Cond: (((x."顧客id")::text = (y."顧客id")::text) AND (x."購入日" = y."購入日"))
               ->  Seq Scan on "購入履歴テーブル" x  (cost=0.00..26950.98 rows=1646398 width=19) (actual time=0.006..65.692 rows=1646398 loops=1)
               ->  Hash  (cost=84106.86..84106.86 rows=54880 width=24) (actual time=642.580..642.581 rows=70304 loops=1)
                     Buckets: 65536 (originally 65536)  Batches: 2 (originally 1)  Memory Usage: 3585kB
                     ->  Subquery Scan on y  (cost=0.43..84106.86 rows=54880 width=24) (actual time=0.018..628.655 rows=70304 loops=1)
                           Filter: (y."購入回数" <= 4)
                           Rows Removed by Filter: 748897
                           ->  WindowAgg  (cost=0.43..82048.86 rows=164640 width=24) (actual time=0.018..598.971 rows=819201 loops=1)
                                 ->  GroupAggregate  (cost=0.43..79167.66 rows=164640 width=16) (actual time=0.013..374.164 rows=819201 loops=1)
                                       Group Key: "購入履歴テーブル"."顧客id", "購入履歴テーブル"."購入日"
                                       ->  Index Scan using idx_buy_history on "購入履歴テーブル"  (cost=0.43..65173.27 rows=1646398 width=16) (actual time=0.008..154.654 rows=1646398 loops=1)
 Planning Time: 0.244 ms
 Execution Time: 1224.494 ms

なお、最終的には
購入回数の上限をパラメータにした結果セットをご希望なのでしょうから
そういう場合は
N回をパラメータにしたSTORED FUNCTIONを作っておけば
柔軟にクエリを発行できます。ついでなので ご紹介しますね。

＃ こういったSQL文の解析・チューニングが、ご自身で ある程度可能になれば
＃ 次のステップとしてクロス集計に挑戦されるといいのではないでしょうか。

/*
-- 呼び出し例( 使い方 ):

SELECT * FROM get_ratio_data( 4 )
ORDER BY 顧客ID
       , 購入日
       , 数量   desc
       , 商品
;

*/
CREATE OR REPLACE FUNCTION get_ratio_data
                               ( N  bigint )
    RETURNS TABLE
    (
          顧客ID      varchar
        , 購入日      date
        , 購入回数    bigint
        , 商品        varchar
        , 数量        float
        , 合計        float
        , 購入日順位  bigint
        , 割合        numeric
    )
    LANGUAGE PLpgSQL
AS
$$
BEGIN
    RETURN QUERY
    (
        WITH step1 ( 顧客ID, 購入日, 購入回数, 合計 ) AS
        (
            SELECT t.顧客ID
                 , t.購入日
                 , row_number() over( partition by t.顧客ID order by t.購入日 )
                 , sum( t.数量 )
            FROM 購入履歴テーブル t
            GROUP BY t.顧客ID
                   , t.購入日
        ),
           step2 ( 顧客ID, 購入日, 購入回数, 合計, 誤差, 吸収対象 ) AS
        (
            SELECT x.顧客ID
                 , x.購入日
                 , min( y.購入回数 )
                 , min( y.合計 )
                 , 1::numeric - sum( trunc( x.数量::numeric / y.合計::numeric, 3 ) )
                 , max( x.商品 )
            FROM 購入履歴テーブル x
            JOIN step1            y
            USING ( 顧客ID, 購入日 )
            WHERE y.購入回数 <= $1
            GROUP BY x.顧客ID
                   , x.購入日
        )
        
        SELECT x.顧客ID
             , x.購入日
             , y.購入回数
             , x.商品
             , x.数量
             , y.合計
             , row_number() 
                   over( partition by x.顧客ID, x.購入日 
                             order by x.数量 desc, x.商品 )
             , (
                   trunc( x.数量::numeric / y.合計::numeric, 3 )
                       +
                   case
                       when x.商品 = y.吸収対象 then y.誤差
                       else 0
                   end
               )::numeric * 100
        FROM 購入履歴テーブル x
        JOIN step2            y
        USING ( 顧客ID, 購入日 )
    )
    ;
END
$$
VOLATILE;

Comments

[shintaro1001]

ありがとうございます。意図する結果が得られました。
また、後続の作業も参考になりました。感謝します。

Answer 3

Postgresqlの9.6以上であればcrosstab(text source_sql, text category_sql)で表にできます。

しかし、頂いてる情報だけだと以下のことが必要です。

1. N回目を算出する
2. crosstabで行を列に変換する
3. 商品の割合を算出する

初心者マークがついておりますので、1つずつ書きますが、手順は簡単ではありません。
最後に動作させるためのサンプルコードを乗せておきますので、突き合わせて確認下さい。

N回目を算出する

顧客IDと購入日を使ってN回目を表す補助の表を作りましょう。
やり方としては顧客IDと購入日をDISTINCTして表を作り、その表で自身を含めた購入日以下のカウントをそれぞれに取ります。

SELECT 
    T1."顧客ID",
    (SELECT COUNT(*) FROM 
      (SELECT DISTINCT "顧客ID", "購入日" FROM sales) T2
       WHERE T2."顧客ID"=T1."顧客ID" AND T2."購入日"<=T1."購入日") AS "購入回数",
    T1."商品"
FROM
    sales T1;

このオペレーション自体軽くはないので購入回数は必要であれば記録しておく方が良いかもしれません。
もしくは、ビューを使って簡易化しておくといいでしょう。

crosstabを使って行を列に変換する

crosstabを利用するにあたって、tablefuncのEXTENSIONを有効にする必要があります。
有効にするには、クエリでCREATE EXTENSION tablefunc;と入力すればOKです。

crosstab(text source_sql, text category_sql)を使うと列情報を行情報に変換できます。
今回の場合、商品を横方向に並べるために使えます。

数量とのクロス集計の場合以下のようなクエリになります。

SELECT * FROM crosstab(
 'SELECT "購入回数", "商品", "数量" FROM "テーブル" WHERE "顧客ID"=1 ORDER BY "購入回数"',
 'SELECT "商品名" FROM goods'
) AS (
 "購入回数" int,
 "商品A" float,
 "商品B" float,
 "商品C" float,
 "商品D" float
);

crosstabの1行目が表示したい情報の集計(source_sql)、
crosstabの2行目が行から列に変換したい名称の値(category_sql)、
AS句でどのような表にするかを指定します。

source_sqlで指定する後ろから2つ目がcategory_sqlの名称の値（商品A、商品B等）、最後の1つが表示する値です。
それよりも前のカラムは、AS句で一致させる必要があります。今回の場合の購入回数のみですが、2列目、3列目と追加の情報を増やしていくことができます。

また、category_sqlの列数と、AS句の商品A以降の列挙は一致させる必要があります。
必要であればcategory_sqlのクエリでは商品数を絞り込むようにしましょう。

#商品の割合を算出する
数量をサブクエリを利用して全体の割合に変換します。
数量だけだと割合が出せませんので、floatに変換しています。

"数量"::float / (SELECT SUM("数量") FROM sales
   WHERE T3."顧客ID"=T1."顧客ID" AND T3."商品"=T1."商品") AS 割合

#動作サンプル

以下、全体をまとめた動作サンプルです。
新しいデータベースを作って動かしてみられると良いでしょう。

CREATE TABLE sales("顧客ID" int, "購入日" date, "商品" text, "数量" int);
insert into sales values(1, '2020/10/20', '商品A', 5);
insert into sales values(1, '2020/10/20', '商品B', 5);
insert into sales values(1, '2020/10/21', '商品A', 5);
insert into sales values(1, '2020/10/23', '商品C', 5);

CREATE VIEW sales_percent AS
SELECT 
    T1."顧客ID",
    (SELECT COUNT(*) FROM 
      (SELECT DISTINCT "顧客ID", "購入日" FROM sales) T2
       WHERE T2."顧客ID"=T1."顧客ID" AND T2."購入日"<=T1."購入日") AS "購入回数",
    T1."商品",
    T1."数量"::float / (SELECT SUM("数量") FROM sales T3 
			   WHERE T3."顧客ID"=T1."顧客ID" AND T3."商品"=T1."商品") AS "割合"
FROM
    sales T1;

CREATE TABLE goods("商品名" text);
insert into goods values('商品A');
insert into goods values('商品B');
insert into goods values('商品C');
insert into goods values('商品D');

CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
 'SELECT "購入回数", "商品", "割合" FROM sales_percent WHERE "顧客ID"=1 ORDER BY "購入回数"',
 'SELECT "商品名" FROM goods'
) AS (
 "購入回数" int,
 "商品A" float,
 "商品B" float,
 "商品C" float,
 "商品D" float
);

結果

購入回数, 商品A, 商品B,  商品C,  商品D
1        0.5	1      [null] [null]
2        0.5	[null] [null] [null]
3        [null] [null] 1      [null]

#参考

tablefunc - EXTENSIONの説明ページです。

Comments

[shintaro1001]

ありがとうございます！まさにN回目の出し方がわからずに困っていました。
これから挑戦します。

[shintaro1001]

「N回目を算出する」を実行してみたのですが、10時間以上経っても終わらない状況です。
購入履歴のレコード数は160万件ほどです。
丁寧に教えていただいたのに申し訳ありませんが、下記のsqlを添削いただけませんでしょうか。

idのカラム名のみ企業名が入るので"xxx"に置き換えています。"uri_2018_2019"というのが購入履歴に相当するテーブルです。
```
select
T1.xxx_id,
(select count(*)from
(select distinct xxx_id,予約日 from uri_2018_2019)T2
where T2.xxx_id=T1.xxx_id and T2.予約日 <=T1.予約日) as 購入回数,T1.公演名
from uri_2018_2019 T1
```
> 購入回数は必要であれば記録しておく方が良いかもしれません。
> もしくは、ビューを使って簡易化しておくといいでしょう。
上記について方法をご教示いただけませんでしょうか。
ビュー作成は調べて試してみたものの、構文エラーになってしまい手詰まってしまいました。

よろしくお願いいたします。

[haru666]

すみません、規模感を考えてなかったですね…

上記のクエリ自体がエラーになるのでしょうか？
パッと見ではエラーになるようには思わないのですが…日本語のフィールド名をダブルクォーテーションでくくっていないからという話ではないですよね…。

SELECT文の実行が成功する場合、ビューの作り方は
CREATE VIEW 名称 AS SELECT～ と続けるだけです。

ビューに対してWhere文をつけ足せば絞り込みが行われるはずですが、それでも160万件規模になると細かい調整が必要だと思います。

速度については、実行計画を見ながらコストを下げていくしかありません。
DBのバックアップイメージを使って色々試されてみるといいと思います。

pgAdminでExplainの機能を使って、実行計画が小さくなる方法を模索していく必要があります。
これは私のミスですが、SELECT DISTINCT の方でもwhere文でidを絞り込めば対象行を絞り込めるかもしれません。

テーブルの列数が多ければインデックスの有無が結果の実行速度に大きく影響します。
今回の場合は、xxx_idと予約日でインデックスを作っておけば回数を導き出す速度は改善するはずです。
160万件のテーブルでこのような操作を行おうとした経験が私の方にも無いので、どこまで効果的かまではわかりませんが…

特に、過去データからの分析を行ってる状態だと思いますので、追加テーブルで対応するのは悪くないステップだと思います。
今後の実行に速度が必要な場合、VIEWのように毎回計算が必要な状態を排除しておくべきです。
顧客ID、購入日、購入回数の項目で新しいテーブルを作成し、そちらにCOUNTの結果を書き込みます。
また、1回の購入で複数商品が買われることが多い場合前提になりますが、購入回数の計算自体が重たい場合はIDと日付だけのテーブルを作成する方法も取れると思います。

商品（公演）毎の購入回数等も必要と思いますが、数量の割合を出すコストも非常に高いので、同様に別テーブルに書き出していってコストを下げてもいいでしょう。

最後に、実行対象の顧客IDが決まっているなら個別テーブルへの書き出しで絞り込めば一旦は時短できるでしょう。
色々試してみてください。