--全UUの算出
WITH tmp AS(
SELECT COUNT(DISTINCT MEMBER_CODE) AS all_uu
FROM PURCHASE_HISTORY)

--セッション・ユーザー毎に商品Aと商品Bを表示させるテーブルの作成
,tmp2 AS (
    SELECT
        item_A_table.MEMBER_CODE AS MEMBER_CODE
        ,item_A_table.ITEM_CODE AS item_a
        ,item_B_table.ITEM_CODE AS item_b
    FROM PURCHASE_HISTORY AS item_A_table
    LEFT JOIN PURCHASE_HISTORY AS item_B_table
    USING(MEMBER_CODE)
    WHERE 
    item_A_table.ITEM_CODE <> item_B_table.ITEM_CODE
)

--商品毎の購入数
,tmp3 AS (
    SELECT 
        ITEM_CODE
        ,COUNT(1) OVER(PARTITION BY ITEM_CODE) AS item_cnt 
    FROM PURCHASE_HISTORY

    )
    

--支持度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 全顧客数 × 100    
,tmp4 AS(
    SELECT 
        item_a
        ,item_b
        ,(COUNT(DISTINCT MEMBER_CODE) / (SELECT all_uu FROM tmp))*100 AS SUPPORT --！SUPPORT
    FROM tmp2
    GROUP BY 1,2
    )
    
--信頼度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数 × 100
,tmp5 AS(
    SELECT 
        tmp2.item_a AS item_a
        ,tmp2.item_b AS item_b
        ,tmp2.MEMBER_CODE AS MEMBER_CODE 
        ,tmp3.ITEM_CODE AS ITEM_CODE
        ,tmp3.item_cnt AS item_cnt
    FROM tmp2
    LEFT JOIN tmp3 
    ON tmp2.item_a= tmp3.ITEM_CODE
    WHERE tmp2.item_a=tmp3.ITEM_CODE 
        )

,tmp6 AS(
    SELECT
        item_a
        ,item_b
        ,(COUNT(DISTINCT MEMBER_CODE) /item_cnt)*100 AS CONFIDENCE --！CONFIDENCE
    FROM tmp5
    GROUP BY 1,2
    )
    
--リフト値 ＝ (商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数) ÷ (商品Bを購買した顧客数÷全顧客数)  
--商品Bを購買した顧客数の抽出
,tmp7 AS(
    SELECT 
        tmp2.item_a AS item_a
        ,tmp2.item_b AS item_b
        ,tmp2.MEMBER_CODE AS MEMBER_CODE 
        ,tmp3.ITEM_CODE AS ITEM_CODE
        ,tmp3.item_cnt AS item_cnt
    FROM tmp2
    LEFT JOIN tmp3 
    ON tmp2.item_b= tmp3.ITEM_CODE
    WHERE tmp2.item_b=tmp3.ITEM_CODE 
        )
 
 ,tmp8 AS(
    SELECT
        item_b AS item_b
        ,COUNT(DISTINCT MEMBER_CODE) AS item_b_uu
    FROM tmp7
    GROUP BY 1
    
    )

,tmp9 As(
    SELECT 
        tmp6.item_a AS item_a
        ,tmp6.item_b AS item_b
        ,tmp6.CONFIDENCE/100 AS CONFIDENCE_division-- 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数
        ,tmp8.item_b_uu/(SELECT all_uu FROM tmp) AS rate_of_item_b--商品Bを購買した顧客数÷全顧客数
    FROM tmp6
    LEFT JOIN tmp8
    ON tmp6.item_b=tmp8.item_b
    )
    
,tmp10 AS(
    SELECT
        item_a
        ,item_b
        ,CONFIDENCE_division/rate_of_item_b AS LIFT --!LIFT
    FROM tmp9
    )
    
SELECT
    tmp4.item_a AS  ITEM_A
    ,tmp4.item_b AS ITEM_B
    ,tmp4.SUPPORT AS SUPPORT
    ,tmp6.CONFIDENCE AS CONFIDENCE
    ,tmp10.LIFT AS LIFT
FROM tmp4
LEFT JOIN tmp6
ON tmp4.item_a=tmp6.item_a
LEFT JOIN tmp10
ON tmp6.item_a=tmp10.item_a
ORDER BY LIFT,SUPPORT,CONFIDENCE,ITEM_A DESC;