SQL：存储过程

注意存储过程的建立需要事先授权操作， 不然无法创建

语句

DELIMITER $$

USE `bi2_cache`$$

DROP PROCEDURE IF EXISTS `mapping_date_type`$$

CREATE DEFINER=`donewsbi`@`4x.2xx.38.2x2` PROCEDURE `mapping_date_type`(IN start_time INTEGER, IN end_time INTEGER, IN vari_db VARCHAR(100), IN vari_tb VARCHAR(100), IN vari_platform VARCHAR(100), IN vari_channel VARCHAR(100), IN limit_num INTEGER)
BEGIN
	/*select start_time, end_time,vari_db, vari_tb, vari_channel;*/

	SET @day_num = 1;
	SET @day_num_str = 'one';
	SELECT DATEDIFF(FROM_UNIXTIME(end_time, '%Y-%m-%d'),FROM_UNIXTIME(start_time, '%Y-%m-%d')) INTO @day_num;  
	IF @day_num=1 THEN 
	    SET @day_num_str='one';
	ELSEIF @day_num=2 THEN 
	    SET @day_num_str='two';  
	ELSEIF @day_num=3 THEN 
	    SET @day_num_str='three';  
	ELSEIF @day_num=4 THEN 
	    SET @day_num_str='four';
	ELSEIF @day_num=5 THEN 
	    SET @day_num_str='five';
	ELSEIF @day_num=6 THEN 
	    SET @day_num_str='six';
	ELSEIF @day_num=7 THEN 
	    SET @day_num_str='seven';
	ELSEIF @day_num=8 THEN 
	    SET @day_num_str='eight';
	ELSEIF @day_num=9 THEN 
	    SET @day_num_str='nine';
	ELSEIF @day_num=10 THEN 
	    SET @day_num_str='ten';
	ELSEIF @day_num=11 THEN 
	    SET @day_num_str='eleven';
	ELSEIF @day_num=12 THEN 
	    SET @day_num_str='twelve';
	ELSEIF @day_num=13 THEN 
	    SET @day_num_str='thirteen';
	ELSEIF @day_num=14 THEN 
	    SET @day_num_str='fourteen';
	ELSEIF @day_num=15 THEN 
	    SET @day_num_str='fifteen';
	ELSEIF @day_num=16 THEN 
	    SET @day_num_str='sixteen';
	ELSEIF @day_num=17 THEN 
	    SET @day_num_str='seventeen';
	ELSEIF @day_num=18 THEN 
	    SET @day_num_str='eighteen';
	ELSEIF @day_num=19 THEN 
	    SET @day_num_str='nineteen';
	ELSEIF @day_num=20 THEN 
	    SET @day_num_str='twenty';
	ELSEIF @day_num=21 THEN 
	    SET @day_num_str='twentyone';
	ELSEIF @day_num=22 THEN 
	    SET @day_num_str='twentytwo';
	ELSEIF @day_num=23 THEN 
	    SET @day_num_str='twentythree';
	ELSEIF @day_num=24 THEN 
	    SET @day_num_str='twentyfour';
	ELSEIF @day_num=25 THEN 
	    SET @day_num_str='twentyfive';
	ELSEIF @day_num=26 THEN 
	    SET @day_num_str='twentysix';
	ELSEIF @day_num=27 THEN 
	    SET @day_num_str='twentyseven';
	ELSEIF @day_num=28 THEN 
	    SET @day_num_str='twentyehgit';
	ELSEIF @day_num=29 THEN 
	    SET @day_num_str='twentynine';
	ELSEIF @day_num=30 THEN 
	    SET @day_num_str='thirty';
	ELSE 
	    SET @day_num_str='one';
	END IF;  

	SET @end_time_new = CONCAT("'", LEFT(FROM_UNIXTIME(end_time),10), "'");
	SET @start_time_new = CONCAT("'", LEFT(FROM_UNIXTIME(start_time),10), "'");
	SET @vari_platform_new = CONCAT("'", vari_platform, "'");
	SET @vari_channel_new = CONCAT("'", vari_channel, "'");
	SET @day_num_str_new = CONCAT("'", @day_num_str, "'");
	SET @sqlStmt = CONCAT("SELECT date_type '日期类型', data_time '日期', channel '渠道', user_new '新增用户', user_active '活跃用户', startup_total '总启动次数', 
	startup_avg '平均启动次数', duration_total '总使用时长', duration_avg '平均使用时长' FROM ",vari_tb," WHERE data_time>=", @start_time_new, 
	" AND ", "data_time<=", @end_time_new, " AND ", "platform=", @vari_platform_new, " AND ","channel=", @vari_channel_new, " AND ", 
	"date_type=", @day_num_str_new, " ORDER BY data_time DESC LIMIT ", limit_num, ";");
	
	PREPARE stmt FROM @sqlStmt;
	EXECUTE stmt;

END$$

DELIMITER ;

调用过程

在grafana传入变量

call bi2_cache.mapping_date_type($__unixEpochFrom(),$__unixEpochTo(), '$db_name','$table_name', '$platform', '$channel', 100)