row_numberを使って購入順（連番）をつけるSQL

boxcodeadmin

2021年1月2日

顧客分析をする上で、購入順を意識することは多々あるかと思います。

少なくとも初回購入と2回目の購入とが売上データ上、すぐに判断できるなにかしらの項目は持っておいたほうが良いです。

一般的に初回購入をされた顧客がリピートし、2回目の購入に至ることをF2転換とよく言います。

F2のFはFrequency＝頻度や回数を表し、数字の2は2回目の購入を意味します。

もちろん、3回、4回、5回と続けば、F2→F3、F3→F4、F4→F5とそれぞれの分析、場合によっては

F1→F2→F3→F4→F5と継続的にご購入いただく顧客が、全く同じ商品を定期的に購入しているのか

類似商品、あるいは全く別のカテゴライズされた商品を購入されているか

顧客の特性を時系列で持って分析する上で、購入順を明らかにしていることは

後々役立つことになりますし、

顧客育成という観点から商品シリーズの拡大縮小の判断基準としても使うことになります。

そこで今回は「row_number」を使って、売上データの顧客別の購入順をオーダー日の順に連番していくSQLをご紹介します。

なお、環境はPostgreSQL 9.6.2で確認しています。

まずデータの準備。

今回はデータ量が多いので、テーブル定義のみをご紹介します。

データはそのものは以下のGitHubをご確認ください。

missing_dates
sales.csv、product.csvをご利用ください。
https://github.com/Taihei19/missing_dates

sales table　＝　売上データ

No.	column id	data type	column name
1	orderid	varchar(8)	オーダーID
2	orderdate	date	オーダー日
3	customerid	varchar(6)	顧客ID
4	store	varchar(1)	購入店舗
5	productid	varchar(3)	商品ID
6	cost	numeric	仕入単価
7	salesprice	numeric	売上単価
8	quantity	numeric	数量
9	amountcost	numeric	仕入金額
10	amountsales	numeric	売上金額
11	profit	numeric	粗利

product　＝　商品マスタ

No.	column id	data type	column name
1	productid	varchar(3)	商品ID
2	productname	varchar(50)	商品名
3	categoryid	varchar(3)	カテゴリID
4	categoryname	varchar(50)	カテゴリ名
5	cost	numeric	仕入単価
6	salesprice	numeric	売上

こちらのデータはとある架空のボクシングメーカーの2020年の卸売上データです。

正直、先に述べたF2転換の分析をする上ではBtoBデータとなるため適していませんが、今回はあくまでも購入順を連番することが目的となりますので、ご了承ください。

まずは以下のSQLを実行します。

こちらは売上データ（sales）と商品マスタ（product）を結合した大副帳型のフラットな結果となります。

--分析用データの大福帳型のフラットデータ
select s.orderid
       ,s.orderdate
       ,s.customerid
       ,s.productid 
       ,p.productname
       ,p.categoryid
       ,p.categoryname
       ,s.salesprice
       ,s.quantity
       ,s.amountsales
   from sales s
   left join product p
     on s.productid = p.productid
  order by s.customerid
       ,s.orderdate
       ,s.orderid

結果は画像となり、恐縮です。

顧客customeridのA00001が複数に渡って購入していることがわかります。

早速、row_number()を使って、顧客の購入順をつけていきたいと思います。

そのためにはまず、以下の通り、orderid→orderdate→customeridで絞る必要がございます。

今回のデータは商品情報も含む明細データとなっている関係上、group by する必要がありますね。

select orderid
       ,orderdate
       ,customerid
   from sales
  group by orderid
       ,orderdate
       ,customerid
  order by customerid
       ,orderdate
       ,orderid

こちらの結果に購入順をつけていきます。

以下がそのSQLです。

--顧客単位にオーダー日→オーダーID順に1から連番をふる
select orderid
        ,orderdate
        ,customerid
        ,row_number() over (partition by customerid order by orderdate,orderid) as frequency --顧客単位に購入順を連番
    from sales
   group by orderid
        ,orderdate
        ,customerid
   order by customerid
        ,orderdate
        ,orderid

partition by　は連番をつけていくグループの指定を意味します。省略可能ですが、省略した場合、全体に対して連番を振ることになるため、顧客分析する上では必須になります。

order by　はそのままですね。partitionのグループ指定内での連番を振る順番指定ですね。こちらは必須となります。

結果、frequency列に顧客単位でオーダー日、オーダーID順で1から連番が振られたことを確認できます。

そして、初めに作成した大福帳のクエリにrow_numberで顧客単位に連番を振った結果を外部結合します。

select s.orderid
       ,f.frequency
       ,s.orderdate
       ,s.customerid
       ,s.productid 
       ,p.productname
       ,p.categoryid
       ,p.categoryname
       ,s.salesprice
       ,s.quantity
       ,s.amountsales
   from sales s
   left join product p
     on s.productid = p.productid
   left join (select orderid
                    ,orderdate
                    ,customerid
                    ,row_number() over (partition by customerid order by orderdate,orderid) as frequency
                from sales
               group by orderid
                    ,orderdate
                    ,customerid) f
     on s.customerid = f.customerid
    and s.orderid = f.orderid
    and s.orderdate = f.orderdate
  order by s.customerid
       ,s.orderdate
       ,s.orderid

これによりどの伝票が何回目の購入にあたり、どのような商品をどれだけ購入できたかが分かるようになり、分析する上でのデータ構造となりました。

以上、row_numberを使って顧客の購入順を連番する方法でしたが、実際の利用方法については別途ご紹介をいたします。

その際はBtoCデータでもって、F2転換やバスケット分析なども取り扱いやすいデータにいたします。

テストデータ作るプログラムを作成しますので、しばらくお待ちください。

なお、row_numberと似た関数で「rank」というものがございます。

こちらは順位をつけるときに使われますが、row_numberとの違いはrankは同値の場合、同じ順位（同じ値を返す）をつけてしまいます。

以下は例です。

こちらはあるボクサーの勝利回数の表です。

boxer	winfrequency
Saul Alvarez	54
Anthony Joshua	24
Gervonta Davis	24
Naoya Inoue	20
Teofimo Lopez	16
Gennadiy Golovkin	41

こちらについて勝利回数で順位を1位から振っていくSQLでrankとrow_numberとの違いをお見せします。

データは以下の通りです。

--テーブル作成
 create table rankrow(
        boxer varchar(50) --ボクサー選手名
       ,winfrequency numeric --勝利回数
       );

 --データセット
 insert into rankrow (boxer,winfrequency)
 values
 ('Saul Alvarez',54),
 ('Anthony Joshua',24),
 ('Gervonta Davis',24),
 ('Naoya Inoue',20),
 ('Teofimo Lopez',16),
 ('Gennadiy Golovkin',41);

以下のSQLで確認します。

select boxer
       ,winfrequency
       ,row_number() over (order by winfrequency desc)
       ,rank() over (order by winfrequency desc)
   from rankrow

結果は以下の通りです。