I have created an Inventory system in the company I am working for, now the next they want me to make is to create something called the Favorite Basket, in which when I am entering a new Order say for example my order goes like this
1- Desktop PC
2- LCD Screen
They want me to create an Analysis from previous orders containing my order items and display ( Employees who Ordered Items 1 & 2 ) have also ordered the following : say for example in this case
1- Mouse :P
2- Pen Drive
Anyway my question is that how can I create this anaysis correctly is this an algorithim for it , please if anyone have done it before or have read about it. inform me.
I wish we can charge them :P
seems simple enough, just look at past orders for other people who have bought it and find the most bought item from those people
yeah but how can i know what is the most item ordered with it.
I mean the concept is clear but programmaticly what is the Algorithim for it ??
should I start counting for each item ordered with this item ?
if anyone have done it before or have read of how to do it, please help ot give links
Evidently there's two stages to this -- find the users who have previously ordered those same items, and find the items most commonly ordered by those users other than those items.
I expect that inserting the ordered items into a temporary table would be helpful.
You could find the users with code similar to ...
... and then find the most popular ordered items for those users with something like ...
where ordered_item in
group by username
having count(distinct ordered_item) =
(select count(*) from temp_new_order)
This may not be the best performing code, but at least it's a workable method to start with.
Select ordered_item, sum(ordered_qty) sum_qty
Where username in (previous query inserted here)
and ordered_item not in
(select ordered_item from temp_new_order)
group by ordered_item
order by 2 desc
Where rownum <= 5
I would be tempted to go with something along the lines of:
to generate the list
FROM (SELECT ordered_item, count(*)
WHERE order_no IN (SELECT order_no
WHERE ordered_item IN (list_from_current_order) and
ordered_item NOT IN (list_from_current_order)
GROUP BY ordered_item
ORDER BY count(*) DESC)
WHERE rownum < however_many_you_want_to_show
Click Here to Expand Forum to Full Width