Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Apriori Algorithm in SQL
with base as
(
SELECT *
FROM Product_Details_002
where Product_ID is not null
),
inner_joined_cte as
(
select a. Order_ID, a. Product_ID as puller_Art, b. Product_ID as pulled_art
from base as a
inner join
base as b
on
a.Order_ID = b. Order_ID
),
total_txns_N as
-- get the N i.e total transaction count
(
select count(distinct Order_ID ) as total_txns
from inner_joined_cte
),
total_puller_N as
-- get the N puller i.e total transaction count
(
select puller_Art, count ( distinct Order_ID ) as puller_txns
from inner_joined_cte
group by 1
-- order by puller_txns desc
),
total_pulled_N as
(
select Product_ID as pulled_Art, count (Order_ID ) as pulled_txns
from
(
select Order_ID , Product_ID
from base
group by 1,2
)
group by 1
),
total_intersection_N as -- A ∩ B count
(
select pulled_art, puller_art, count (distinct Order_ID) as intersec_txns
from inner_joined_cte
group by pulled_art, puller_art
),
cross_join_table as
(
select puller_art, pulled_art
from inner_joined_cte
group by 1,2
),
affinity_primary as (
select its. puller_art, its. pulled_art,
safe_divide (intersec_txns, total_txns) as Support,
safe_divide (intersec_txns, puller_txns) as Confidence,
safe_divide ((intersec_txns*total_txns),(puller_txns* pulled_txns)) as Lift
from cross_join_table as its
cross join total_txns_N
left join
total_intersection_N as tin
on
its. puller_Art = tin. puller_Art and
its. pulled_Art = tin. pulled_Art
left join
total_puller_N as ant
on
ant. puller_Art = its. puller_Art
left join
total_pulled_N as con
on
con. pulled_Art = its. pulled_Art
)
select * from affinity_primary
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment