SELECT ITEM_A,ITEM_B,
round(AB_CNT/SUB_ALL_NUM.NUM*100,5) AS SUPPORT,
round(AB_CNT/SUB_ITEM_A_NUM.NUM*100,5) AS CONFIDENCE,
round((AB_CNT/SUB_ITEM_A_NUM.NUM)/(SUB_ITEM_B_NUM.NUM/SUB_ALL_NUM.NUM),5) AS LIFT
FROM (
    SELECT 
        A.ITEM_CODE AS ITEM_A,
        B.ITEM_CODE AS ITEM_B, 
        COUNT(1) AS AB_CNT
    FROM PURCHASE_HISTORY AS A 
    INNER JOIN PURCHASE_HISTORY AS B 
    ON A.SESSION_ID = B.SESSION_ID AND A.ITEM_CODE != B.ITEM_CODE
    GROUP BY ITEM_A,ITEM_B
) AS SUB
LEFT JOIN (SELECT CAST(COUNT(DISTINCT MEMBER_CODE) AS real) AS NUM FROM PURCHASE_HISTORY) AS SUB_ALL_NUM
LEFT JOIN (SELECT ITEM_CODE,CAST(COUNT(DISTINCT MEMBER_CODE) AS real) AS NUM FROM PURCHASE_HISTORY GROUP BY ITEM_CODE) AS SUB_ITEM_A_NUM
ON ITEM_A = SUB_ITEM_A_NUM.ITEM_CODE
LEFT JOIN (SELECT ITEM_CODE,CAST(COUNT(DISTINCT MEMBER_CODE) AS real) AS NUM FROM PURCHASE_HISTORY GROUP BY ITEM_CODE) AS SUB_ITEM_B_NUM
ON ITEM_B = SUB_ITEM_B_NUM.ITEM_CODE
GROUP BY ITEM_A,ITEM_B
ORDER BY LIFT DESC,SUPPORT DESC,CONFIDENCE DESC,ITEM_A DESC