Q&A
「購入した商品の割合」について、何を１００％とした時の割合ですか？
例えば、「回数ごとの商品の購入」の傾向というのが、初回が一番多かったとか、回数を追うごとに増えていくというようなものですか？
前提・実現したいこと
購入履歴テーブルから回数ごとの商品の購入傾向を把握したいと考えています。
購入履歴として以下のようなテーブルがあります。
顧客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）
よろしくお願いします。
回答3件
0
「N回目を算出する」を実行してみたのですが、10時間以上経っても終わらない状況です。
購入履歴のレコード数は160万件ほどです。
@haru666 さんの回答をいくつかチューニングすれば、高速化出来ると思います。
(1) 第１優先で、顧客IDによる絞り込みを行う
これは私のミスですが、SELECT DISTINCT の方でもwhere文でidを絞り込めば対象行を絞り込めるかもしれません。
@haru666 さんもコメントで書いていますが、テーブルの結合を行う前に、対象行を絞り込みます。
これだけで、劇的に高速化されると思います。
(2) 不等号によるテーブルの結合を回避する
sql
1SELECT 2 T1."顧客ID", 3 (SELECT COUNT(*) FROM 4 (SELECT DISTINCT "顧客ID", "購入日" FROM sales) T2 5 WHERE T2."顧客ID"=T1."顧客ID" AND T2."購入日"<=T1."購入日") AS "購入回数", 6 T1."商品" 7FROM 8 sales T1;
でN回目を算出しようとしていますが、ここの部分はWINDOW関数のDENSE_RANK を使って、連番を振ることが可能です。
(3) クロス集計を、標準SQLのCASE文を使って実現する
ここの部分は、パフォーマンスとは関係ありませんが、@haru666さんの方法では、CREATE EXTENSION tablefunc; が必要であるということと、クロス集計用のテーブルを別途用意（例ではgoodsテーブル）しなければならないので、それを回避します。
SQL
1WITH SUB1 AS 2( 3SELECT * 4FROM sales 5WHERE 顧客ID = 1 -- ここで対象となる顧客IDを指定する 6), SUB2 AS 7( 8SELECT 9 DENSE_RANK() OVER (ORDER BY 購入日) AS 購入回数 10, 商品 11, 数量::float / SUM(数量) OVER (PARTITION BY 商品) AS 割合 12FROM SUB1 13) 14 15SELECT 16 購入回数 17, MAX(CASE WHEN 商品 = '商品A' THEN 割合 ELSE 0 END) AS 商品A 18, MAX(CASE WHEN 商品 = '商品B' THEN 割合 ELSE 0 END) AS 商品B 19, MAX(CASE WHEN 商品 = '商品C' THEN 割合 ELSE 0 END) AS 商品C 20, MAX(CASE WHEN 商品 = '商品D' THEN 割合 ELSE 0 END) AS 商品D 21FROM SUB2 22GROUP BY 購入回数
投稿2021/09/17 23:37
総合スコア358
0
ベストアンサー
「N回目を算出する」を実行してみたのですが、10時間以上経っても終わらない状況です。
購入履歴のレコード数は160万件ほどです。
取り扱うデータ数が多く、ご希望の表示も簡単とは言い難いですから
まずは、出力イメージの構想を見直しましょう。
- 顧客ID毎に購入する商品の種類は毎回異なる
- 顧客ID毎に購入する商品の量( レコード数 )は毎回異なる
こういったレコードをクロス集計、いわゆる横持ちで表示させる場合、
全顧客で購入対象となった商品の種類だけ表示用の列が必要になります。
少なくとも、数十、数百といった単位にはなるでしょう。
また、特定のお客様お一人だけが購入された商品に至っては
値が記載されているのが１行のみ、あとは全てNullという結果セットになってしまい
見映えや実行効率が良いとは言えないですね。
そのため、この段階での結果セットは縦持ちをお薦めします。
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( '1x', '2021-01-01', 'B', 5 ), 16( '1x', '2021-01-01', 'D', 1 ), 17( '1x', '2021-01-01', 'H', 2 ), 18( '1x', '2021-01-01', 'G', 1 ), 19( '1x', '2021-01-02', 'C', 10 ), 20( '1x', '2021-01-02', 'G', 3 ), 21( '1x', '2021-01-02', 'A', 11 ), 22( '2y', '2021-01-02', 'I', 19 ), 23( '2y', '2021-01-02', 'D', 11 ), 24( '3z', '2021-01-01', 'A', 7 ), 25( '3z', '2021-01-05', 'B', 8 ), 26( '3z', '2021-01-10', 'C', 9 ), 27( '3z', '2021-01-10', 'X', 5 ), 28( '3z', '2021-01-20', 'D', 10 ), 29( '3z', '2021-01-31', 'E', 11 ); 30 31-- EXPLAIN ANALYZE 32WITH step1 ( 顧客ID, 購入日, 購入回数, 合計 ) AS 33( 34 SELECT 顧客ID 35 , 購入日 36 , row_number() over( partition by 顧客ID order by 購入日 ) 37 , sum( 数量 ) 38 FROM 購入履歴テーブル 39 GROUP BY 顧客ID 40 , 購入日 41), 42 step2 ( 顧客ID, 購入日, 購入回数, 合計, 誤差, 吸収対象 ) AS 43( 44 SELECT x.顧客ID 45 , x.購入日 46 , min( y.購入回数 ) 47 , min( y.合計 ) 48 , 1::numeric - sum( trunc( x.数量::numeric / y.合計::numeric, 3 ) ) 49 , max( x.商品 ) 50 FROM 購入履歴テーブル x 51 JOIN step1 y 52 USING ( 顧客ID, 購入日 ) 53 WHERE y.購入回数 <= 4 54 GROUP BY x.顧客ID 55 , x.購入日 56) 57 58SELECT x.顧客ID 59 , x.購入日 60 , y.購入回数 61 , x.商品 62 , x.数量 63 , y.合計 64 , row_number() 65 over( partition by x.顧客ID, x.購入日 66 order by x.数量 desc, x.商品 ) 購入日順位 67 , ( 68 trunc( x.数量::numeric / y.合計::numeric, 3 ) 69 + 70 case 71 when x.商品 = y.吸収対象 then y.誤差 72 else 0 73 end 74 )::numeric * 100 割合 75FROM 購入履歴テーブル x 76JOIN step2 y 77USING ( 顧客ID, 購入日 ) 78ORDER BY x.顧客ID 79 , x.購入日 80 , x.数量 desc 81 , x.商品 82;
result
1顧客id 購入日 購入回数 商品 数量 合計 購入日順位 割合 2----------------------------------------------------------------------- 3 1x 2021-01-01 1 B 5 9 1 55.5 4 1x 2021-01-01 1 H 2 9 2 22.3 5 1x 2021-01-01 1 D 1 9 3 11.1 6 1x 2021-01-01 1 G 1 9 4 11.1 7 1x 2021-01-02 2 A 11 24 1 45.8 8 1x 2021-01-02 2 C 10 24 2 41.6 9 1x 2021-01-02 2 G 3 24 3 12.6 10 2y 2021-01-02 1 I 19 30 1 63.4 11 2y 2021-01-02 1 D 11 30 2 36.6 12 3z 2021-01-01 1 A 7 7 1 100.0 13 3z 2021-01-05 2 B 8 8 1 100.0 14 3z 2021-01-10 3 C 9 14 1 64.2 15 3z 2021-01-10 3 X 5 14 2 35.8 16 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
EXPLAIN
1GroupAggregate (cost=120797.14..120988.05 rows=4492 width=88) (actual time=1043.515..1221.966 rows=70304 loops=1) 2 Group Key: x."顧客id", x."購入日", y."購入回数", y."合計" 3 -> Sort (cost=120797.14..120808.37 rows=4492 width=35) (actual time=1043.494..1059.543 rows=141359 loops=1) 4 Sort Key: x."顧客id", x."購入日", y."購入回数", y."合計" 5 Sort Method: external merge Disk: 6232kB 6 -> Hash Join (cost=84930.06..120524.63 rows=4492 width=35) (actual time=642.622..1000.066 rows=141359 loops=1) 7 Hash Cond: (((x."顧客id")::text = (y."顧客id")::text) AND (x."購入日" = y."購入日")) 8 -> Seq Scan on "購入履歴テーブル" x (cost=0.00..26950.98 rows=1646398 width=19) (actual time=0.006..65.692 rows=1646398 loops=1) 9 -> Hash (cost=84106.86..84106.86 rows=54880 width=24) (actual time=642.580..642.581 rows=70304 loops=1) 10 Buckets: 65536 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3585kB 11 -> Subquery Scan on y (cost=0.43..84106.86 rows=54880 width=24) (actual time=0.018..628.655 rows=70304 loops=1) 12 Filter: (y."購入回数" <= 4) 13 Rows Removed by Filter: 748897 14 -> WindowAgg (cost=0.43..82048.86 rows=164640 width=24) (actual time=0.018..598.971 rows=819201 loops=1) 15 -> GroupAggregate (cost=0.43..79167.66 rows=164640 width=16) (actual time=0.013..374.164 rows=819201 loops=1) 16 Group Key: "購入履歴テーブル"."顧客id", "購入履歴テーブル"."購入日" 17 -> 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) 18 Planning Time: 0.244 ms 19 Execution Time: 1224.494 ms
なお、最終的には
購入回数の上限をパラメータにした結果セットをご希望なのでしょうから
そういう場合は
N回をパラメータにしたSTORED FUNCTIONを作っておけば
柔軟にクエリを発行できます。ついでなので ご紹介しますね。
＃ こういったSQL文の解析・チューニングが、ご自身で ある程度可能になれば
＃ 次のステップとしてクロス集計に挑戦されるといいのではないでしょうか。
PLpgSQL
1/* 2-- 呼び出し例( 使い方 ): 3 4SELECT * FROM get_ratio_data( 4 ) 5ORDER BY 顧客ID 6 , 購入日 7 , 数量 desc 8 , 商品 9; 10 11*/ 12CREATE OR REPLACE FUNCTION get_ratio_data 13 ( N bigint ) 14 RETURNS TABLE 15 ( 16 顧客ID varchar 17 , 購入日 date 18 , 購入回数 bigint 19 , 商品 varchar 20 , 数量 float 21 , 合計 float 22 , 購入日順位 bigint 23 , 割合 numeric 24 ) 25 LANGUAGE PLpgSQL 26AS 27$$ 28BEGIN 29 RETURN QUERY 30 ( 31 WITH step1 ( 顧客ID, 購入日, 購入回数, 合計 ) AS 32 ( 33 SELECT t.顧客ID 34 , t.購入日 35 , row_number() over( partition by t.顧客ID order by t.購入日 ) 36 , sum( t.数量 ) 37 FROM 購入履歴テーブル t 38 GROUP BY t.顧客ID 39 , t.購入日 40 ), 41 step2 ( 顧客ID, 購入日, 購入回数, 合計, 誤差, 吸収対象 ) AS 42 ( 43 SELECT x.顧客ID 44 , x.購入日 45 , min( y.購入回数 ) 46 , min( y.合計 ) 47 , 1::numeric - sum( trunc( x.数量::numeric / y.合計::numeric, 3 ) ) 48 , max( x.商品 ) 49 FROM 購入履歴テーブル x 50 JOIN step1 y 51 USING ( 顧客ID, 購入日 ) 52 WHERE y.購入回数 <= $1 53 GROUP BY x.顧客ID 54 , x.購入日 55 ) 56 57 SELECT x.顧客ID 58 , x.購入日 59 , y.購入回数 60 , x.商品 61 , x.数量 62 , y.合計 63 , row_number() 64 over( partition by x.顧客ID, x.購入日 65 order by x.数量 desc, x.商品 ) 66 , ( 67 trunc( x.数量::numeric / y.合計::numeric, 3 ) 68 + 69 case 70 when x.商品 = y.吸収対象 then y.誤差 71 else 0 72 end 73 )::numeric * 100 74 FROM 購入履歴テーブル x 75 JOIN step2 y 76 USING ( 顧客ID, 購入日 ) 77 ) 78 ; 79END 80$$ 81VOLATILE;
投稿2021/09/16 12:31
総合スコア335
0
Postgresqlの9.6以上であればcrosstab(text source_sql, text category_sql)で表にできます。
しかし、頂いてる情報だけだと以下のことが必要です。
- N回目を算出する
crosstabで行を列に変換する- 商品の割合を算出する
初心者マークがついておりますので、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の説明ページです。
投稿2021/09/13 06:46
編集2021/09/13 07:02
総合スコア1591
ありがとうございます！まさにN回目の出し方がわからずに困っていました。
これから挑戦します。
「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
```
> 購入回数は必要であれば記録しておく方が良いかもしれません。
> もしくは、ビューを使って簡易化しておくといいでしょう。
上記について方法をご教示いただけませんでしょうか。
ビュー作成は調べて試してみたものの、構文エラーになってしまい手詰まってしまいました。
よろしくお願いいたします。
すみません、規模感を考えてなかったですね…
上記のクエリ自体がエラーになるのでしょうか？
パッと見ではエラーになるようには思わないのですが…日本語のフィールド名をダブルクォーテーションでくくっていないからという話ではないですよね…。
SELECT文の実行が成功する場合、ビューの作り方は
CREATE VIEW 名称 AS SELECT～ と続けるだけです。
ビューに対してWhere文をつけ足せば絞り込みが行われるはずですが、それでも160万件規模になると細かい調整が必要だと思います。
速度については、実行計画を見ながらコストを下げていくしかありません。
DBのバックアップイメージを使って色々試されてみるといいと思います。
pgAdminでExplainの機能を使って、実行計画が小さくなる方法を模索していく必要があります。
これは私のミスですが、SELECT DISTINCT の方でもwhere文でidを絞り込めば対象行を絞り込めるかもしれません。
テーブルの列数が多ければインデックスの有無が結果の実行速度に大きく影響します。
今回の場合は、xxx_idと予約日でインデックスを作っておけば回数を導き出す速度は改善するはずです。
160万件のテーブルでこのような操作を行おうとした経験が私の方にも無いので、どこまで効果的かまではわかりませんが…
特に、過去データからの分析を行ってる状態だと思いますので、追加テーブルで対応するのは悪くないステップだと思います。
今後の実行に速度が必要な場合、VIEWのように毎回計算が必要な状態を排除しておくべきです。
顧客ID、購入日、購入回数の項目で新しいテーブルを作成し、そちらにCOUNTの結果を書き込みます。
また、1回の購入で複数商品が買われることが多い場合前提になりますが、購入回数の計算自体が重たい場合はIDと日付だけのテーブルを作成する方法も取れると思います。
商品（公演）毎の購入回数等も必要と思いますが、数量の割合を出すコストも非常に高いので、同様に別テーブルに書き出していってコストを下げてもいいでしょう。
最後に、実行対象の顧客IDが決まっているなら個別テーブルへの書き出しで絞り込めば一旦は時短できるでしょう。
色々試してみてください。
あなたの回答
tips
プレビュー
