WITH
    sub1 AS (
        SELECT
            ph.MEMBER_CODE,
            mc.MEMBER_CNT, --全部で何人いるか
            ph.ITEM_CODE,
            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 (
        SELECT
            s1.ITEM_CODE AS ITEM_A,
            s1.ITEM_CNT AS A_CNT, --Aが何人に買われたか
            s2.ITEM_CODE AS ITEM_B,
            s2.ITEM_CNT AS B_CNT, --Bが何人に買われたか
            COUNT(1) AS AB_CNT, --AとBの両方を買った人数
            s1.MEMBER_CNT AS M_CNT --全部で何人いるか
        FROM
            sub1 AS s1
            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
    )
SELECT
    ITEM_A,
    ITEM_B,
    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;