with pa as 
(
    select distinct hi1.item_code ia, hi2.item_code ib 
from PURCHASE_HISTORY hi1,PURCHASE_HISTORY hi2
where hi1.item_code<>hi2.item_code
) 
,ab as (
    select distinct ia,ib,count(distinct h1.MEMBER_CODE) cnt from
    pa join PURCHASE_HISTORY h1 on pa.ia=h1.item_code
    join PURCHASE_HISTORY h2 on pa.ib=h2.item_code
    where h1.MEMBER_CODE=h2.MEMBER_CODE
    group by ia,ib
)
,
a as (
    select item_code,count(distinct MEMBER_CODE) cnt from PURCHASE_HISTORY group by item_code
)
-- b as (
--     select distinct ib,h1.MEMBER_CODE from
--     pa join PURCHASE_HISTORY h1 on pa.ib=h1.item_code
-- )

select ia ITEM_A,
ib ITEM_B,
round(v1*100,5) SUPPORT,
round(v2*100.0,5)  CONFIDENCE,
round(v2/cast((select cnt from a where a.item_code=ib) as real)*cast((select count(distinct MEMBER_CODE) from PURCHASE_HISTORY) as real),5) LIFT
from (
select ab.ia,ab.ib,
cast(ab.cnt as real)/cast((select count(distinct MEMBER_CODE) from PURCHASE_HISTORY) as real) v1,
cast(ab.cnt as real)/cast((select cnt from a where a.item_code=ab.ia) as real) v2

 from ab
)
order by LIFT desc,SUPPORT desc,CONFIDENCE desc,1 desc
  --where ia='P002' 

 ;