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.
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 ...
where ordered_item in
group by username
having count(distinct ordered_item) =
(select count(*) from temp_new_order)
... and then find the most popular ordered items for those users with something like ...
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
This may not be the best performing code, but at least it's a workable method to start with.
I would be tempted to go with something along the lines of:
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