この記事でやりたいこと
カテゴリーデータ同士の関係を表で出したいと考えています。
例えば、このようなカテゴリーで選択されたデータがあったときに
| 店舗 | 購入された商品 |
|---|---|
| A | アイス |
| B | おでん |
| A | 肉まん |
| C | アイス |
| $\vdots$ | $\vdots$ |
| C | 飲料水 |
以下のような、1に近いほど売れやすく、-1に近いほど売れにくい関係があるといえる表を作りたいです。
| アイス | おでん | 肉まん | 飲料水 | |
|---|---|---|---|---|
| A | 0.22 | -0.18 | -0.04 | -0.05 |
| B | -0.26 | 0.41 | 0.22 | -0.21 |
| C | -0.04 | -0.12 | -0.12 | 0.22 |
店舗Aでは他の店舗と比べてアイスが多く売れているから多めに仕入れよう、みたいなことが出来ないかというお話です。
また、表形式に直す部分はBIツールに任せるので、DBから上記の値を抜き取ってくるところまでのお話です。
クラメールの連関係数
カテゴリー変数間の相関を出す時には、クラメールの連関係数と呼ばれる値を相関として使用します。
計算式としては、カテゴリー1のカテゴリ数を $k$ 、カテゴリー2のカテゴリ数を $l$ 、カテゴリ1が $i$ でカテゴリ2が $j$ の時の観測数を $x_{ij}$ 、カテゴリ1が $i$ の時の観測数を $x_{i \cdot}$ 、カテゴリ2が $j$ の時の観測数を $x_{\cdot j}$ 、データの総数を $n$ として、以下の値になります。
$$\sqrt{\frac{\chi ^2}{n(\min (k,l)-1)}}
= \sqrt{\frac{\sum_{i=1}^k \sum_{j=1}^l \frac{\left( x_{ij}-\frac{x_{i \cdot}x_{\cdot j}}{n} \right) ^2}{\frac{x_{i \cdot}x_{\cdot j}}{n}}}{n(\min(k,l)-1)}}
= \sqrt{\frac{\sum_{i=1}^k \sum_{j=1}^l \frac{x_{ij}^2}{x_{i \cdot}x_{\cdot j}}-1}{\min(k,l)-1}}$$
具体的に $k=2, l=3$ の場合に各変数が表す個数を表形式で記載すると以下の場所になります。
| カテゴリ2の1つ目 | カテゴリ2の2つ目 | カテゴリ2の3つ目 | 合計 | |
|---|---|---|---|---|
| カテゴリ1の1つ目 | $x_{11}$ | $x_{12}$ | $x_{13}$ | $x_{1 \cdot}$ |
| カテゴリ1の2つ目 | $x_{21}$ | $x_{22}$ | $x_{23}$ | $x_{2 \cdot}$ |
| 合計 | $x_{\cdot 1}$ | $x_{\cdot 2}$ | $x_{\cdot 3}$ | $n$ |
3つの式は同じものを展開したり括ったりしただけなので、持っているデータに応じて使ってください。
各データの個数が分かっているときの具体的な計算
先ほどのデータを数え上げた結果が以下の個数だった時の計算をしてみます。
| アイス | おでん | 肉まん | 飲料水 | 合計 | |
|---|---|---|---|---|---|
| A | 50 | 10 | 15 | 35 | 110 |
| B | 2 | 15 | 10 | 3 | 30 |
| C | 20 | 5 | 5 | 30 | 60 |
| 合計 | 72 | 30 | 30 | 68 | 200 |
\begin{eqnarray}
\sqrt{\frac{\sum_{i=1}^3 \sum_{j=1}^4 \frac{x_{ij}^2}{x_{i \cdot}x_{\cdot j}}-1}{\min(3,4)-1}}
&=& \sqrt{\frac{\frac{x_{Aアイス}^2}{x_{A}x_{アイス}}+\frac{x_{Aおでん}^2}{x_{A}x_{おでん}}+ \cdots +\frac{x_{C飲料水}^2}{x_{C}x_{飲料水}}-1}{3-1}}\\
&=& \sqrt{\frac{\frac{50^2}{110 \times 72}+\frac{10^2}{110 \times 30}+ \cdots +\frac{30^2}{60 \times 68}-1}{2}}\\
&=& \frac{1}{6} \sqrt{\frac{1927}{374}}\\
& \fallingdotseq & 0.3783
\end{eqnarray}
元々取りたかった数字が出るように調整
クラメールの連関係数を計算してみたところ、0.38という1つの数字のみが出てきました。
これは「店舗」と「購入された商品」とに関連がありそうなのかを0~1の値で評価したものになります。
この数値から分かることは「各店舗に対して売れそうな商品はどれか」ではなく、「店舗ごとに売れ筋に違いがあるのか」ということで、各店舗に同じ割合で商品を卸すべきなのか各店舗の店長に仕入れの裁量権を持たせるべきなのか、ということの判断に使える指標になってきます。
ここで元々やりたかったことって何か？を考えなおしてみると、全体を1つの値で纏めたかったのではなく、例えば「店舗Aでアイスは他店舗と比べて売れ筋なの？」といった「店舗A」と「アイス」という2つの関連性でした。
それならば、最初のデータを調整してみます。以下のように「店舗Aの話か」「アイスの話か」という2つのカラムを付けてあげることで「店舗A」と「アイス」との関係を取れそうです。
| 店舗 | 購入された商品 | 店舗Aの話か | アイスの話か |
|---|---|---|---|
| A | アイス | O | O |
| B | おでん | X | X |
| A | 肉まん | O | X |
| C | アイス | X | O |
| $\vdots$ | $\vdots$ | ||
| C | 飲料水 | X | X |
こちらの表を数え上げて、個数換算で書き直してみます。
| アイス | アイス以外 | 合計 | |
|---|---|---|---|
| A | 50 | 60 | 110 |
| A以外 | 22 | 68 | 90 |
| 合計 | 72 | 128 | 200 |
上の表を数式に当てはめて以下のようにクラメールの連関係数が計算できます。
\begin{eqnarray}
\sqrt{\frac{\sum_{i=1}^2 \sum_{j=1}^2 \frac{x_{ij}^2}{x_{i \cdot}x_{\cdot j}}-1}{\min(2,2)-1}}
&=& \sqrt{\frac{\frac{x_{Aアイス}^2}{x_{A}x_{アイス}}+\frac{x_{A他商品}^2}{x_{A}x_{他商品}}+\frac{x_{他店舗アイス}^2}{x_{他店舗}x_{アイス}}+\frac{x_{他店舗他商品}^2}{x_{他店舗}x_{他商品}}-1}{2-1}}\\
&=& \sqrt{\frac{50^2}{110 \times 72}+\frac{60^2}{110 \times 128}+\frac{22^2}{90 \times 72}+\frac{68^2}{90 \times 128}-1}\\
&=& \frac{13}{18} \sqrt{\frac{1}{11}}\\
& \fallingdotseq & 0.2178
\end{eqnarray}
同じように各店舗と商品に対しての値を計算していき各セルに入れれば初めに欲しかった表が作れそうです。
数式の整理
前の段落の通り各店舗と商品ごとに新しく表を作って計算をしていっても良いのですが、毎度表を作っていくにはコストが高そうです。そこで、元々の全体の表でも使っていた値だけで同じ結果になる数式を考えておきます。
ということで、全体の表と店舗Aのアイスだけを抽出した表とを見比べてみます。
| アイス | おでん | 肉まん | 飲料水 | 合計 | |
|---|---|---|---|---|---|
| A | 50★ | 10 | 15 | 35 | 110★ |
| B | 2 | 15 | 10 | 3 | 30 |
| C | 20 | 5 | 5 | 30 | 60 |
| 合計 | 72★ | 30 | 30 | 68 | 200★ |
| アイス | アイス以外 | 合計 | |
|---|---|---|---|
| A | 50★ | 60 | 110★ |
| A以外 | 22 | 68 | 90 |
| 合計 | 72★ | 128 | 200★ |
見比べてみると★を付けた部分は同じ値になっていそうです。
ということは★のところだけで計算ができるようにしておくと個別の表を考えなくても全体の集計表からのみで考えられそうです。
以下のように星マークのところを表のとおり変数で取っておき、残りの部分は虫食い算的に埋めてみました。こちらで式を整理していきます。
| アイス | アイス以外 | 合計 | |
|---|---|---|---|
| A | $x$ | $a-x$ | $a$ |
| A以外 | $b-x$ | $n+x-a-b$ | $n-a$ |
| 合計 | $b$ | $n-b$ | $n$ |
\begin{eqnarray}
&&\sqrt{\frac{\sum_{i=1}^2 \sum_{j=1}^2 \frac{x_{ij}^2}{x_{i \cdot}x_{\cdot j}}-1}{\min(2,2)-1}}\\
&=& \sqrt{\frac{\frac{x_{Aアイス}^2}{x_{A}x_{アイス}}+\frac{x_{A他商品}^2}{x_{A}x_{他商品}}+\frac{x_{他店舗アイス}^2}{x_{他店舗}x_{アイス}}+\frac{x_{他店舗他商品}^2}{x_{他店舗}x_{他商品}}-1}{2-1}}\\
&=& \sqrt{\frac{x^2}{ab}+\frac{(a-x)^2}{a(n-b)}+\frac{(b-x)^2}{(n-a)b}+\frac{(n+x-a-b)^2}{(n-a)(n-b)}-1}\\
&=& \sqrt{\frac{
\begin{array}{l}
x^2(n-a)(n-b)+(a-x)^2(n-a)b\\ \quad
+(b-x)^2a(n-b)+(n+x-a-b)^2ab-ab(n-a)(n-b)
\end{array}
}{ab(n-a)(n-b)}}\\
&=& \sqrt{\frac{
\begin{array}{l}
(n^2x^2 \color{red}{-anx^2} \color{blue}{-bnx^2} \color{green}{+abx^2})\\ \quad
+(\color{violet}{a^2bn}-2abnx \color{blue}{+bnx^2} \color{brown}{-a^3b} \color{orange}{+2a^2bx} \color{green}{-abx^2})\\ \quad
+(\color{teal}{ab^2n}-2abnx \color{red}{+anx^2} \color{purple}{-ab^3} \color{cyan}{+2ab^2x} \color{green}{-abx^2})\\ \quad
+(\color{magenta}{abn^2}+2abnx \color{violet}{-2a^2bn} \color{teal}{-2ab^2n} \color{green}{+abx^2} \color{orange}{-2a^2bx} \color{cyan}{-2ab^2x} \color{brown}{+a^3b}+2a^2b^2 \color{purple}{+ab^3})\\ \quad
+(\color{magenta}{-abn^2} \color{violet}{+a^2bn} \color{teal}{+ab^2n}-a^2b^2)
\end{array}
}{ab(n-a)(n-b)}}\\
&=& \sqrt{\frac{n^2x^2-2abnx+a^2b^2}{ab(n-a)(n-b)}}\\
&=& \sqrt{\frac{(nx-ab)^2}{ab(n-a)(n-b)}}\\
&=& \frac{|nx-ab|}{\sqrt{ab(n-a)(n-b)}}
\end{eqnarray}
ということでだいぶ簡単な式で求められることになりました。
絶対値の話
先ほどの式を確認すると、絶対値が含まれる式になっています。そのため、この値は負になりません。
連続値同士の相関であれば片方が増加すれば、もう一方が増加するのか減少するのかで正負が付くのですが、今の計算はカテゴリーとして使用していてどちらが上というのがないので、方向を表す符号なしに関連の有無だけが分かりました。
もし、〇なら1、×なら0として無理矢理方向を付けてあげると片方が〇になれば、もう片方も〇になるなら増加として、もう片方は×になるなら減少として取れるため、正負をつけることが出来るようになります。
大小があるとみなした時には、連続値の相関と同じように「共分散 / それぞれの標準偏差」で求めることが出来て、同じような式の $\frac{nx-ab}{\sqrt{ab(n-a)(n-b)}}$ を使うことになります。
\begin{eqnarray}
&& \frac{\frac{1}{n} \left(
\begin{array}{l}
x(1- \frac{a}{n} )(1- \frac{b}{n})+(a-x)(1- \frac{a}{n} )(0- \frac{b}{n})\\ \quad
+(b-x)(0- \frac{a}{n} )(1- \frac{b}{n})+(n+x-a-b)(0- \frac{a}{n} )(0- \frac{b}{n})
\end{array}
\right) }
{\sqrt{\frac{a}{n} -(\frac{a}{n})^2} \sqrt{\frac{b}{n} -(\frac{b}{n})^2}}\\
&=& \frac{\frac{1}{n} \left(
\begin{array}{l}
x(n-a)(n-b)+(a-x)(n-a)(-b)\\ \quad
+(b-x)(0-a)(n-b)+(n+x-a-b)(0-a)(0-b)
\end{array}
\right) }
{\sqrt{an-a^2} \sqrt{bn-b^2}}\\
&=& \frac{\frac{1}{n} \left(
\begin{array}{l}
x(n-a)(n-b)+(a-x)(n-a)(-b)\\ \quad
+(b-x)(0-a)(n-b)+(n+x-a-b)(0-a)(0-b)
\end{array}
\right) }
{\sqrt{a(n-a)} \sqrt{b(n-b)}}\\
&=& \frac{\frac{1}{n} \left(
\begin{array}{l}
(n^2x-anx-bnx+abx)+(a^2b-abx-abn+bnx)\\ \quad
+(ab^2-abx-abn+anx)+(abn+abx-a^2b-ab^2)
\end{array}
\right) }
{\sqrt{ab(n-a)(n-b)}}\\
&=& \frac{\frac{1}{n} \left( n^2x-abn \right) }{\sqrt{ab(n-a)(n-b)}}\\
&=& \frac{nx-ab}{\sqrt{ab(n-a)(n-b)}}\\
\end{eqnarray}
SQLでテーブルから取得してみる
数式をいじってどう表を作ってくるかが出来たので、後は集計をするだけです。
計算後が取得できさえすれば後はBIツールでなんとか出来ると信じて、レコードの状態で出力できるクエリを作成します。
また、ここでは正負をつける形式で作成しますので、絶対値が必要な場合は絶対値関数を追加してください。
mysql> SELECT * FROM purchase_data LIMIT 4;
+----+-------+---------+
| id | store | product |
+----+-------+---------+
| 1 | A | アイス |
| 2 | B | おでん |
| 3 | A | 肉まん |
| 4 | C | アイス |
+----+-------+---------+
mysql> SELECT store, product, count(1) AS cnt FROM purchase_data GROUP BY store, product;
+-------+---------+-----+
| store | product | cnt |
+-------+---------+-----+
| A | アイス | 50 |
| A | おでん | 10 |
| A | 肉まん | 15 |
| A | 飲料水 | 35 |
| B | アイス | 2 |
| B | おでん | 15 |
| B | 肉まん | 10 |
| B | 飲料水 | 3 |
| C | アイス | 20 |
| C | おでん | 5 |
| C | 肉まん | 5 |
| C | 飲料水 | 30 |
+-------+---------+-----+
mysql> SELECT
-> store,
-> product,
-> (sum_all * cnt - sum_store * sum_product) / SQRT(sum_store * sum_product * (sum_all - sum_store) * (sum_all - sum_product)) AS correlation
-> FROM (
-> SELECT
-> store,
-> product,
-> cnt,
-> SUM(cnt) OVER (PARTITION BY store) AS sum_store,
-> SUM(cnt) OVER (PARTITION BY product) AS sum_product,
-> SUM(cnt) OVER () AS sum_all
-> FROM (
-> SELECT
-> store,
-> product,
-> COUNT(1) AS cnt
-> FROM purchase_data
-> GROUP BY
-> store,
-> product
-> ) product_cnt
-> ) product_summary
-> ;
+-------+---------+-----------------------+
| store | product | correlation |
+-------+---------+-----------------------+
| A | アイス | 0.2177581933061626 |
| A | おでん | -0.1829534767323246 |
| A | 肉まん | -0.04222003309207491 |
| A | 飲料水 | -0.050919275782836726 |
| B | アイス | -0.2567180154051351 |
| B | おでん | 0.4117647058823529 |
| B | 肉まん | 0.21568627450980393 |
| B | 飲料水 | -0.21283153734900961 |
| C | アイス | -0.036369648372665396 |
| C | おでん | -0.12222646627042817 |
| C | 肉まん | -0.12222646627042817 |
| C | 飲料水 | 0.22111599712911284 |
+-------+---------+-----------------------+
最後に
最後はクエリの説明ほぼ無しで数式通りだよねで押し通してしまいましたが、相関を出すための式がそのまま出てくるようなクエリになっています。
別のSQL言語でも同じようにできると思うので、必要に応じて置き換えていただければと思います。
参考資料
https://digimarl.com/syllabus/data-science-no13/
https://qcplanets.com/method/regression/cramer/
https://bookdown.org/sbtseiji/lswjamoviJ/ch-chisquare.html
https://qiita.com/shngt/items/45da2d30acf9e84924b7