usert4jju7 usert4jju7 - 4 months ago 12
MySQL Question

MySQL procedure - Getting to work

Here's a procedure I've written to find out the top 10 items purchased per customer per day.

This is the first ever PL/SQL thing I've attempted.It doesn'tdo what I'm expecting it to do.

The logic I've used is to accept start date, end date & top 'x' sales that I'm interested in per customer. Then to loop through the unique customers table -

cust_table
& find the top 'x' per customer per day.

Could I please request guidance to get this working please?

CREATE PROCEDURE `at _the _top`( in stDate date, in edDate date, in lim int)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE cust TEXT;
DECLARE custNames CURSOR FOR SELECT CUSTOMER FROM cust_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN custNames;

read_loop: LOOP
FETCH custNames INTO cust;

IF done THEN
LEAVE read_loop;
END IF;

WHILE(stDate <= edDate) DO
#insert into top_cust(EVE_DATE,CUST_NAME,ITEM_ID,ITEMS_PURCHASED) (select EVE_DATE, CUST_NAME, ITEM_ID, SUM(ITEM_BUNDLE_SIZE) as ITEMS_PURCHASED_TOTAL from cust_sales_hist where EVE_DATE = stDate and CUST_NAME = cust group by EVE_DATE, ITEM_ID order by ITEMS_PURCHASED_TOTAL desc limit lim);
SELECT concat('Custome - ', cust);
SELECT CONCAT('Start Date - ', stDate);
SELECT CONCAT('End date - ', edDate);
SELECT CONCAT('Limit - ', lim);
SELECT
EVE_DATE,
CUST_NAME,
ITEM_ID,
SUM(ITEM_BUNDLE_SIZE) AS ITEMS_PURCHASED_TOTAL
FROM
cust_sales_hist
WHERE
EVE_DATE = stDate
AND CUST_NAME = cust
GROUP BY
EVE_DATE ,
ITEM_ID
ORDER BY
ITEMS_PURCHASED_TOTAL DESC
LIMIT
LIM;
SET stDate = date_add(stDate, INTERVAL 1 DAY);
end WHILE;
END LOOP;

CLOSE custNames;
END

Answer

You don't actually need a stored procedure at all. Instead, you could use a single select statement using user-defined variables?

select eve_date, cust_name, item_id, sum(item_bundle_size) sumsize
from (
  select eve_date, cust_name, item_id, item_bundle_size, 
          @rn := if(@prevDate=eve_date and @prevItem=item_id, @rn + 1,
                    if (@prevDate:=eve_date, 
                        if (@prevItem:=item_id, 1, 1)
                        , 1)
                   ) rn
  from cust_sales_hist cross join 
       (select @rn:=0, @prevDate:=null, @prevItem:=null) t
  where cust_name = 'Cust 1' and
    eve_date between '2016-08-01' and '2016-08-02'
  order by eve_date, item_id
  ) t
where rn <= 2
group by eve_date, cust_name, item_id