--アイテム別購入数
WITH ITEMBUY AS
(SELECT
ITEM_CODE,COUNT(*) AS BUY
FROM PURCHASE_HISTORY
GROUP BY ITEM_CODE)

SELECT
A.ITEM_CODE AS ITEM_A,
B.ITEM_CODE AS ITEM_B,
--支持度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 全顧客数 × 100
ROUND(CAST(COUNT(*) AS FLOAT)*100 /
(SELECT COUNT(*) FROM (SELECT SESSION_ID FROM PURCHASE_HISTORY GROUP BY SESSION_ID)),5) AS SUPPORT,
--信頼度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数 × 100
ROUND(CAST(COUNT(A.ITEM_CODE) AS FLOAT)*100 / IBA.BUY,5) AS CONFIDENCE,
--リフト値 ＝ (商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数) ÷ (商品Bを購買した顧客数÷全顧客数)
ROUND(
(CAST(COUNT(*) AS FLOAT) / IBA.BUY)
/
(CAST(IBB.BUY AS FLOAT) / (SELECT COUNT(*) FROM (SELECT SESSION_ID FROM PURCHASE_HISTORY GROUP BY SESSION_ID))),5) AS LIFT
FROM PURCHASE_HISTORY AS A
JOIN PURCHASE_HISTORY AS B
ON A.ITEM_CODE != B.ITEM_CODE AND A.SESSION_ID = B.SESSION_ID
LEFT JOIN ITEMBUY AS IBA
ON A.ITEM_CODE = IBA.ITEM_CODE
LEFT JOIN ITEMBUY AS IBB
ON B.ITEM_CODE = IBB.ITEM_CODE
GROUP BY 1,2
ORDER BY LIFT DESC,SUPPORT DESC,CONFIDENCE DESC,ITEM_A DESC
;
--総セッションコード数
--SELECT COUNT(*) FROM (SELECT SESSION_ID FROM PURCHASE_HISTORY GROUP BY SESSION_ID)