需求：对商品表按照好评、畅销、特价等维度随机推送给用户.

解决方法:通过split_string结合存储过程的for循环解决该问题.

Mysql：

DROP PROCEDURE IF EXISTS pro_gen_recomondcomm;
CREATE PROCEDURE `pro_gen_recomondcomm`(IN haveorder bit,IN classify int,IN proceeDate varchar(20))
BEGIN
    DECLARE isfestival bit;
    DECLARE havepurchase bit;
    DECLARE category int;
    DECLARE ratevals VARCHAR(20);
    DECLARE nums INT;
    DECLARE selnumrate INT;
    DECLARE commentrate INT;
    DECLARE bargainrate INT;
    DECLARE i INT;
    DECLARE areacnt INT;
    DECLARE localarea VARCHAR(20);
   
	SET isfestival = 0;
  SET havepurchase = haveorder;
  SET category = classify;
   
   /*删除已有的当天的数据**/
  TRUNCATE TABLE shenl_bi_recommend_comm2;
  call pro_gen_area;
    
    IF isfestival = 0 AND haveorder = 0 THEN
    
    SELECT ordered,rates,total INTO haveorder,ratevals,nums 
    FROM shenl_bi_metadata WHERE classify = category AND type = 1 AND ordered = havepurchase;
    
  
    SELECT 
CAST(split_string(ratevals, ',',1) AS decimal(9,2))*nums,
CAST(split_string(ratevals, ',',2) AS decimal(9,2))*nums,
CAST(split_string(ratevals, ',',3) AS decimal(9,2))*nums
    INTO selnumrate,commentrate,bargainrate;
 


 SELECT MAX(rank) INTO areacnt  FROM
			(
			SELECT @row_number:=@row_number+1 AS rank,area FROM 
      (SELECT DISTINCT area AS area FROM shenl_bi_temp_carryable )area,
			(SELECT @row_number:=0) AS t
			ORDER BY area DESC
			) A;

SET i = 1;

while i<=areacnt do			
				SELECT area INTO localarea FROM
			(
			SELECT @row_number:=@row_number+1 AS rank,area FROM 
      (SELECT DISTINCT area AS area FROM shenl_bi_temp_carryable )area,
			(SELECT @row_number:=0) AS t
			ORDER BY area DESC
			) A WHERE A.rank = i;
	
    
    /* add 生成各个城市的逻辑*/
    
     INSERT INTO shenl_bi_recommend_comm2(gid,source,processdate,cityid)/**以热销维度来推荐商品 **/
    SELECT gid,source,proceeDate,localarea FROM
(
  SELECT gid,1 AS source,str_to_date(proceeDate,'%Y-%m-%d') AS proceeDate,localarea FROM
  (SELECT gid,sellnum FROM shenl_bi_temp_carryable WHERE area = localarea
  ORDER BY sellnum DESC LIMIT selnumrate)B
  ORDER BY RAND()
 )C;

/**以特价维度来推 **/
INSERT INTO shenl_bi_recommend_comm2(gid,source,processdate,cityid)
SELECT A.gid,2 as "source", A.proceeDate,A.area  FROM(
    SELECT gid,str_to_date(proceeDate,'%Y-%m-%d') AS proceeDate,localarea as area FROM
      (
        SELECT gid FROM shenl_bi_temp_carryable WHERE bargain=1 AND area = localarea
        ORDER BY RAND() LIMIT bargainrate
      )A
  )A
LEFT JOIN 
 (SELECT gid,processdate,cityid FROM shenl_bi_recommend_comm2 WHERE processdate = proceeDate AND cityid = localarea) B
ON A.gid = B.gid AND A.proceeDate = B.processdate AND A.area = localarea
WHERE B.gid IS NULL;

 /**以好评维度推 **/
INSERT INTO shenl_bi_recommend_comm2(gid,source,processdate,cityid)
SELECT A.gid,3 as "source", A.proceeDate,A.area  FROM(
    SELECT gid,str_to_date(proceeDate,'%Y-%m-%d') AS proceeDate,localarea as area FROM
      (
       SELECT gid FROM
        (SELECT gid,rate FROM shenl_bi_temp_carryable WHERE area = localarea
          ORDER BY rate DESC LIMIT commentrate
         )B
      ORDER BY RAND()
      )A
  )A
LEFT JOIN 
 (SELECT gid,processdate,cityid FROM shenl_bi_recommend_comm2 WHERE processdate = proceeDate  AND cityid = localarea) B
ON A.gid = B.gid AND A.proceeDate = B.processdate AND A.area = localarea
WHERE B.gid IS NULL;

				SET i=i+1;
    END WHILE;
    END IF;
    
    
/**随机追加推,每个city取出12条数据 **/
INSERT INTO shenl_bi_recommend_comm2(gid,source,processdate,cityid)
SELECT  B.gid,5 as "source",str_to_date(proceeDate,'%Y-%m-%d') AS proceeDate,area FROM
           (
              SELECT gid,area FROM
              (
                SELECT A.gid FROM shenl_goods A WHERE A.g_status = 1 AND A.g_num > 0
                ORDER BY RAND() LIMIT 12
              )A
              CROSS JOIN (SELECT DISTINCT area FROM shenl_bi_temp_carryable) B
              ORDER BY area, RAND()
           )B
LEFT JOIN shenl_bi_recommend_comm2 C
ON C.gid = B.gid AND C.cityid = B.area
WHERE C.gid IS NULL;
  
 
  
  END;

代码解读：

1）  设定IN haveorder bit,IN classify int,IN proceeDate varchar(20)如下3个参数

2） call pro_gen_area;调用依赖的存储过程

3）  先删除当天跑的数据TRUNCATETABLE md_bi_recommend_comm2;

4）  如果参数满足某个条件则执行:IF isfestival =0AND haveorder =0THEN

5）  每一种推送数据策略对应一个source,hardcode一个数数字编码

6）  如果left join依次避免本次的数据和上一结果集重复

LEFT JOIN

(SELECT gid,processdate,cityid FROM md_bi_recommend_comm2 WHERE processdate = proceeDate AND cityid =localarea)B

ON A.gid = B.gid AND A.proceeDate = B.processdate AND A.area = localarea

WHERE B.gid IS NULL;