Favorites Basket
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Favorites Basket

  1. #1
    Join Date
    Feb 2005
    Posts
    49

    Favorites Basket

    Hi Developers,
    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

    Thanks
    Aiman Al-Jumoay

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    seems simple enough, just look at past orders for other people who have bought it and find the most bought item from those people

  3. #3
    Join Date
    Feb 2005
    Posts
    49
    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

    Thanks
    Aiman Al-Jumoay

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    Code:
    Select username
    from   orders
    where  ordered_item in 
           (
           select ordered_item
           from   temp_new_order
           )
    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 ...
    Code:
    Select ordered_item
    from
    (
    Select ordered_item, sum(ordered_qty) sum_qty
    From   orders
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    I would be tempted to go with something along the lines of:

    Code:
    SELECT ordered_item
    FROM (SELECT ordered_item, count(*)
          FROM orders
          WHERE order_no IN (SELECT order_no
                             FROM orders
                             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
    to generate the list

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width