-- ITEM_A
-- ITEM_B
-- SUPPORT
-- CONFIDENCE
-- LIFT

SELECT 
    ITEM_A,
    ITEM_B,
    CAST(COUNT(DISTINCT MEMBER_CODE) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY) * 100 AS SUPPORT,
    CAST(COUNT(DISTINCT MEMBER_CODE) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) * 100 AS CONFIDENCE,
    -- (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS uu_b,
    -- CAST((SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY) * 100 AS uu_bb,
    CAST(COUNT(DISTINCT MEMBER_CODE) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_A) / (CAST((SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY WHERE ITEM_CODE = ITEM_B) AS FLOAT) / (SELECT COUNT(DISTINCT MEMBER_CODE) FROM PURCHASE_HISTORY)) AS LIFT
FROM (
    SELECT 
        ITEM_A.MEMBER_CODE,
        ITEM_A.ITEM_CODE AS ITEM_A,
        ITEM_B.ITEM_CODE AS ITEM_B
    FROM (
        SELECT 
            MEMBER_CODE,
            ITEM_CODE
        FROM
            PURCHASE_HISTORY
    ) AS ITEM_A
    INNER JOIN (
        SELECT 
            MEMBER_CODE,
            ITEM_CODE
        FROM
            PURCHASE_HISTORY
    ) AS ITEM_B
    ON
        ITEM_A.MEMBER_CODE = ITEM_B.MEMBER_CODE
        AND ITEM_A.ITEM_CODE != ITEM_B.ITEM_CODE
)
GROUP BY 
    ITEM_A,
    ITEM_B
ORDER BY 
    LIFT DESC,
    SUPPORT DESC,
    CONFIDENCE DESC,
    ITEM_A DESC
-- 算出したリフト値の降順
-- 算出した支持度の降順
-- 算出した信頼度値の降順
-- 商品Aの商品コードの降順