SELECT ITEM_A,ITEM_B,
round(cast(COUNT(ITEM_A || ITEM_B) as real)/SUB_ALL_NUM.NUM*100,5) AS SUPPORT,
round(cast(COUNT(ITEM_A || ITEM_B) as real)/SUB_ITEM_A_NUM.NUM*100,5) AS CONFIDENCE,
round((cast(COUNT(ITEM_A || ITEM_B) as real)/SUB_ITEM_A_NUM.NUM)/(SUB_ITEM_B_NUM.NUM/SUB_ALL_NUM.NUM),5) AS LIFT
FROM (
    SELECT 
        SESSION_ID
        ,MAX(ITEM_A) AS ITEM_A
        ,MAX(ITEM_B)  AS ITEM_B
        ,MAX(MEMBER_CODE) AS MEMBER_CODE
    FROM (
            SELECT
                SESSION_ID,
                CASE WHEN ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY SESSION_ID) = 1 THEN ITEM_CODE ELSE NULL END AS ITEM_A,
                CASE WHEN ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY SESSION_ID) = 2 THEN ITEM_CODE ELSE NULL END AS ITEM_B,
                MEMBER_CODE
            FROM PURCHASE_HISTORY
    ) AS SUB
    GROUP BY SESSION_ID
) AS SUB2
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
WHERE ITEM_B IS NOT NULL
GROUP BY ITEM_A,ITEM_B
ORDER BY LIFT DESC,SUPPORT DESC,CONFIDENCE DESC,ITEM_A DESC