MySQL 过程 - 开始工作

Question

以下是我编写的程序，用于找出每位客户每天购买的前 10 件商品。

这是我尝试过的第一个 PL/SQL 操作。它没有达到我预期的效果。

我使用的逻辑是接受开始日期、结束日期以及我对每个客户感兴趣的前“x”销售额。然后循环访问唯一的客户表 - cust_table 并找到每个客户每天排名前列的“x”。

我可以请求指导以使其正常工作吗？

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 1

您实际上根本不需要存储过程。相反，您可以使用一个使用用户定义变量的select语句吗？

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

• SQL Fiddle Demo