with total as (
    select
        count(distinct session_id) as total
    from purchase_history
)
,T as (
    select
        A.item_code as item_a
        ,B.item_code as item_b
        ,count(*) as count_sup
    from purchase_history as A
    left outer join purchase_history as B
    on A.session_id = B.session_id
    and A.item_code <> B.item_code
    group by A.item_code,B.item_code
)
,prod_count as(
    select
        item_code
        ,count(*) as count_prod
    from purchase_history 
    group by item_code
)
select
    item_a as ITEM_A
    ,item_b as ITEM_B
    ,round(count_sup*100.0/total,5) as SUPPORT
    ,round(count_sup*100.0/A.count_prod,5) as CONFIDENCE
    ,round((1.0*count_sup/A.count_prod)/(1.0*B.count_prod/total),5)  as LIFT
from T
inner join prod_count as A
on A.item_code = T.item_a
inner join prod_count as B
on B.item_code = T.item_b
cross join total
order by LIFT desc,SUPPORT desc,CONFIDENCE desc,ITEM_A desc;

with tmp1 as (
    select
        count(distinct session_id) as custotal
    from purchase_history
),
tmp2 as (
    select
        A.item_code as item_a
        ,B.item_code as item_b
        ,count(*) as count_sup
    from purchase_history as A
    left outer join purchase_history as B
    on A.session_id = B.session_id
    and A.item_code <> B.item_code
    group by A.item_code,B.item_code
)
select * from tmp2;

with tmp1 as (
    select 
        t1.session_id,
        t1.item_code,
        custotal,
        count(1) over(partition by t1.item_code) as item_cnt
    from purchase_history as t1
    inner join(
        select 
            count(distinct session_id) as custotal
        from purchase_history
    ) as t2
    order by t1.session_id
)
select
    s1.item_code as ITEM_A,
    s2.item_code as ITEM_B,
    s1.item_cnt as A_CNT,
    s2.item_cnt as B_CNT,
    count(1) as AB_CNT,
    s1.custotal
from tmp1 as s1
inner join tmp1 as s2
on s1.session_id=s2.session_id
where s1.item_code<>s2.item_code
group by item_a,item_b;

with tmp1 as (
    /*顧客トータル数*/
    select
        count(distinct session_id) as custotal
    from purchase_history
),
tmp2 as (
    /*商品ごとの顧客数*/
    select
        count(session_id) as cussubtotal,
        item_code
    from purchase_history
    group by item_code
),
tmp3 as (
    /*同じ組み合わせの顧客数*/
    select
        tbl1.item_code as ITEM_A,
        tbl2.item_code as ITEM_B,
        count(tbl1.session_id) as groupcount
    from purchase_history as tbl1
    inner join purchase_history as tbl2
    on tbl1.session_id=tbl2.session_id
    and tbl1.item_code<>tbl2.item_code
    group by item_a,item_b
),
tmp4 as (
    /*商品aの顧客数*/
    select 
        ITEM_A,
        ITEM_B,
        groupcount,
        custotal,
        cussubtotal as cus_a
    from tmp3
    inner join tmp2
    on tmp3.item_a=tmp2.item_code
    inner join tmp1
),
tmp5 as (
    /*商品bの顧客数*/
    select 
        ITEM_A,
        ITEM_B,
        groupcount,
        custotal,
        cus_a,
        cussubtotal as cus_b
    from tmp4
    inner join tmp2
    on tmp4.item_b=tmp2.item_code
)
select 
    ITEM_A,
    ITEM_B,
    /*custotal,
    cus_a,
    cus_b,
    groupcount,*/
    round(cast(groupcount as real)/cast(custotal as real)*100,5) as SUPPORT,
    round(cast(groupcount as real)/cast(cus_a as real)*100,5) as CONFIDENCE,
    round((cast(groupcount as real)/cast(cus_a as real))/(cast(cus_b as real)/cast(custotal as real)),5) as LIFT
from tmp5
order by lift desc,support desc,confidence desc,item_a desc;