WITH SUB1 AS ( 
    -- 商品毎の購買顧客件数を取得
    SELECT
        PH.MEMBER_CODE
        , MC.MEMBER_CNT
        , PH.ITEM_CODE
        -- COUNT OVERで商品毎の購買顧客件数をカウントする
        , COUNT(1) OVER (PARTITION BY PH.ITEM_CODE) AS ITEM_CNT 
    FROM
        PURCHASE_HISTORY AS PH 
        -- 交差結合で、各レコードに会員数合計を持たせる
        CROSS JOIN ( 
            SELECT
                -- 重複なしの会員数を取得
                COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT 
            FROM
                PURCHASE_HISTORY
        ) AS MC
) 
, SUB2 AS ( 
    -- 商品A、Bが同じ組合せの購買顧客数を取得
    SELECT
        S1.ITEM_CODE AS ITEM_A
        , S2.ITEM_CODE AS ITEM_B
        , S1.ITEM_CNT AS A_CNT -- 商品Aの購買顧客数
        , S2.ITEM_CNT AS B_CNT -- 商品Bの購買顧客数
        -- 商品A、Bが同じ組合せの購買顧客数を取得
        , COUNT(1) AS AB_CNT
        , S1.MEMBER_CNT AS M_CNT 
    FROM
        SUB1 AS S1 
        -- 上記SUB1を会員コードで自己結合する
        INNER JOIN SUB1 AS S2 
            ON S1.MEMBER_CODE = S2.MEMBER_CODE 
    WHERE
        -- 同一の商品コードの組合せのレコードは対象外にする
        S1.ITEM_CODE <> S2.ITEM_CODE 
    GROUP BY
        ITEM_A
        , ITEM_B
        , A_CNT
        , B_CNT
        , M_CNT
) 
SELECT
    ITEM_A
    , ITEM_B
    -- 各指標を計算しROUNDで小数点第6位を四捨五入する
    , ROUND(100.0 * AB_CNT / M_CNT, 5) AS SUPPORT
    , ROUND(100.0 * AB_CNT / A_CNT, 5) AS CONFIDENCE
    , ROUND( 
        (1.0 * AB_CNT / A_CNT) / (1.0 * B_CNT / M_CNT)
        , 5
    ) AS LIFT 
FROM
    SUB2 
ORDER BY
    LIFT DESC
    , SUPPORT DESC
    , CONFIDENCE DESC
    , ITEM_A DESC;