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;