with totalsession as (--セッション数
select count(distinct session_id) as sessioncount
from PURCHASE_HISTORY as A
)
,subq2 as (--同一セッション内組み合わせ
select A.session_id as session
        ,A.member_code as member_code
        ,A.item_code as A_item
        ,B.item_code as B_item
        ,C.sessioncount
        
from PURCHASE_HISTORY as A
left outer join PURCHASE_HISTORY as B
on A.session_id = B.session_id
and A.member_code = B.member_code
and A.item_code <> B.item_code
left outer join totalsession as C
)
,subq3 as (--全組み合わせ
select   distinct
         A.item_code as A_item
        ,B.item_code as B_item
from PURCHASE_HISTORY as A
left outer join PURCHASE_HISTORY as B
on A.session_id = B.session_id
and A.member_code = B.member_code
where A.item_code <> B.item_code
)
,support as(
select subq3.A_item,subq3.B_item
        ,count(subq2.A_item)
        , cast(count(subq2.A_item) as float) / C.sessioncount as support
from subq3
left outer join subq2
on subq3.A_item = subq2.A_item
and subq3.B_item = subq2.B_item
left outer join totalsession as C
group by subq3.A_item,subq3.B_item
)
------------------------------------------------------------
--confidence
,conf as(
select distinct A_item,B_item,sessioncount,count(A_item) over (partition by A_item)
        ,count(A_item) over (partition by A_item,B_item)
        ,cast(count(A_item) over (partition by A_item,B_item) as float) / count(A_item) over (partition by A_item) as confidence
        ,cast(count(A_item) over (partition by A_item) as float) / sessioncount as liftyou
from subq2
)
select subq3.A_item  as ITEM_A
        ,subq3.B_item as ITEM_B
        ,coalesce(round(support.support*100,5),0)  as SUPPORT
        ,coalesce(round(conf_A.confidence*100,5),0) as CONFIDENCE
        ,coalesce(round(conf_A.confidence / conf_B.liftyou,5),0) as LIFT
from subq3
left outer join support
on subq3.A_item = support.A_item
and subq3.B_item = support.B_item
left outer join conf as conf_A
on subq3.A_item = conf_A.A_item
and subq3.B_item = conf_A.B_item
left outer join conf as conf_B
on subq3.A_item = conf_B.B_item
and subq3.B_item = conf_B.A_item
--where coalesce(round(support.support*100,5),0) + coalesce(round(conf_A.confidence*100,5),0) + coalesce(round(conf_A.confidence / conf_B.liftyou,5),0) > 0 
order by LIFT desc,SUPPORT desc,CONFIDENCE desc,ITEM_A desc