PL SQL function that includes multiple tables-CodePudding

I'm new to PL SQL and have to write a function, which has customer_id as an input and has to output a product_name of the best selling product for that customer_id.

The schema looks like this:

I found a lot of simple examples where it includes two tables, but I can't seem to find one where you have to do multiple joins and use a function, while selecting only the best selling product.

I could paste a lot of very bad code here and how I tried to approach this, but this seems to be a bit over my head for current knowledge, since I've been learning PL SQL for less than 3 days now and got this task.

CodePudding user response：

With some sample data (minimal column set):

SQL> select * from products order by product_id;

PRODUCT_ID PRODUCT_NAME
---------- ----------------
         1 BMW
         2 Audi
SQL> select * From order_items;

PRODUCT_ID CUSTOM   QUANTITY UNIT_PRICE
---------- ------ ---------- ----------
         1 Little        100          1
         1 Little        200          2
         2 Foot          300          3

If we check some totals:

SQL> select o.product_id,
  2       o.customer_id,
  3       sum(o.quantity * o.unit_price) total
  4     from order_items o
  5     group by o.product_id, o.customer_id;

PRODUCT_ID CUSTOM      TOTAL
---------- ------ ----------
         2 Little        400
         1 Little        100
         2 Foot          900

SQL>

It says that

for customer Little, product 2 was sold with total = 400 - that's our choice for Little
for customer Little, product 1 was sold with total = 100
for customer Foot, product 2 was sold with total = 900 - that's our choice for Foot

Query might then look like this:

temp CTE calculates totals per each customer
rank_them CTE ranks them in descending order per each customer; row_number so that you get only one product, even if there are ties
- finally, select the one that ranks as the highest

SQL> with
  2  temp as
  3    (select o.product_id,
  4            o.customer_id,
  5            sum(o.quantity * o.unit_price) total
  6     from order_items o
  7     group by o.product_id, o.customer_id
  8    ),
  9  rank_them as
 10    (select t.customer_id,
 11            t.product_id,
 12            row_number() over (partition by t.customer_id order by t.total desc) rn
 13     from temp t
 14    )
 15  select * From rank_them;

CUSTOM PRODUCT_ID         RN
------ ---------- ----------
Foot            2          1  --> for Foot, product 2 ranks as the highest
Little          2          1  --> for Little, product 1 ranks as the highest
Little          1          2

SQL>

Moved to a function:

SQL> create or replace function f_product (par_customer_id in order_items.customer_id%type)
  2    return products.product_name%type
  3  is
  4    retval products.product_name%type;
  5  begin
  6    with
  7      temp as
  8        (select o.product_id,
  9                o.customer_id,
 10                sum(o.quantity * o.unit_price) total
 11         from order_items o
 12         group by o.product_id, o.customer_id
 13        ),
 14      rank_them as
 15        (select t.customer_id,
 16                t.product_id,
 17                row_number() over (partition by t.customer_id order by t.total desc) rn
 18         from temp t
 19        )
 20      select p.product_name
 21        into retval
 22      from rank_them r join products p on p.product_id = r.product_id
 23      where r.customer_id = par_customer_id
 24        and r.rn = 1;
 25
 26      return retval;
 27  end;
 28  /

Function created.

SQL>

Testing:

SQL> select f_product ('Little') result from dual;

RESULT
--------------------------------------------------------------------------------
Audi

SQL> select f_product ('Foot') result from dual;

RESULT
--------------------------------------------------------------------------------
Audi

SQL>

Now, you can improve it so that you'd care about no data found issue (when customer didn't buy anything), ties (but you'd then return a collection or a refcursor instead of a scalar value) etc.

[EDIT] I'm sorry, ORDERS table has to be included into the temp CTE; your data model is correct, you don't have to do anything about it - my query was wrong (small screen late hours issue; not a real excuse, just saying).

So:

with 
temp as
  (select i.product_id,
          o.customer_id,
          sum(i.quantity * i.unit_price) total
   from order_items i join orders o on o.order_id = i.order_id
   group by i.product_id, o.customer_id
  ),

The rest of my code is - otherwise - unmodified.