mysql 生成时间序列数据 - 存储过程

 　　由于时间自动转换为int值， 做一步转化，也可在调用时处理

use `test`;

CREATE table test.test1 as 
SELECT state, id, `规格条码`,
`色号条码`, 
`货号`, 
`在售平台`, 
`平台售价`, 
DATE_ADD('1900-01-01', Interval data1.`上架时间` day) as `上架时间`,
`下架时间`,
`操作员`
FROM data1;

CREATE table test.test2 as 
SELECT state, id, 
DATE_ADD('1900-01-01', Interval `时间` day) as `时间`,
`在售平台`, 
`规格条码`, 
`销量`,
`销售额`,
`撤销标志`
FROM data2;

　　

 　　生成时间序列数据

USE `test`;
DROP TABLE IF EXISTS tmptb;
CREATE TEMPORARY TABLE tmptb (
	id INT UNSIGNED AUTO_INCREMENT,
	date DATE NOT NULL,
 	shop VARCHAR(20) NOT NULL DEFAULT 0,
	sales INT UNSIGNED DEFAULT 0,
	PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER //
DROP PROCEDURE IF EXISTS DayRangeProc//
CREATE PROCEDURE DayRangeProc (	IN start_date DATE,	IN end_date DATE)
	BEGIN
		DECLARE i, range_day INT;
		SET i = 0;
		SET range_day = (SELECT DATEDIFF(end_date, start_date));
		WHILE i <= range_day DO
			INSERT INTO tmptb(date) VALUES (ADDDATE(start_date, i));
-- 			SET @sqlcmd = CONCAT('INSERT INTO ', tmptb, ' (date) VALUES (', temp, ')');
-- 			PREPARE stmt FROM @sqlcmd;
-- 			EXECUTE stmt;
-- 			DEALLOCATE PREPARE stmt;
			SET i = i + 1;
		END	WHILE;
	END;
//

DELIMITER ;
CALL DayRangeProc ('2010-09-01', '2010-09-10'); 
SELECT * FROM	tmptb;

　　

　　从test1表与test2表，产生每个产品上架以来每天在每个平台的销售情况，如无销售数据则计销量为0

USE `test`;
DROP TABLE IF EXISTS result;
CREATE TABLE result (
	-- 保存结果数据
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	date DATE NOT NULL,
	product_id VARCHAR(20) NOT NULL,
	shop VARCHAR(20) NOT NULL DEFAULT 0,
	price FLOAT NOT NULL,
	sales INT(8) DEFAULT 0,
	amount DOUBLE DEFAULT 0,
	PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS shop_name_tb;
CREATE TEMPORARY TABLE shop_name_tb AS (SELECT DISTINCT	`在售平台` AS NAME FROM	test2);  -- 保存平台，类似于数组操作



DELIMITER //
DROP PROCEDURE IF EXISTS DayRangeProc//
CREATE PROCEDURE DayRangeProc()
	BEGIN
		DECLARE i, j, t INT;
		DECLARE range_day INT;
		DECLARE shop_num, prod_num INT;
		DECLARE start_date, end_date DATE;
		DECLARE prod_id, shop_name VARCHAR(20);
		DECLARE price FLOAT;

		SET i = 0;
		SET j = 0;
		SET t = 0;
		SET shop_num = (SELECT COUNT(*) FROM shop_name_tb);
		SET prod_num = (SELECT COUNT(*) FROM test1);		
		SET end_date = (SELECT MAX(`时间`) FROM test2 );   -- 由于下架时间均为空，假设都在销
		-- 产品循环
		WHILE i <= prod_num DO		
			SET prod_id = (SELECT `规格条码` FROM test1 LIMIT i,1);  -- 第i个商品名称
			SET start_date = (SELECT `上架时间` FROM test1 WHERE `规格条码` = prod_id);   -- 第i个商品的上架时间
			SET range_day = (SELECT DATEDIFF(end_date, start_date));   -- 第i个商品累计销售天数，以便插入相应长度的数据

				-- 平台循环
				WHILE j <= shop_num DO
				SET shop_name = (SELECT name FROM shop_name_tb LIMIT j,1);   -- 店铺名称
				SET price = (SELECT `平台售价` FROM test1 WHERE `规格条码` = prod_id);  -- 第i个商品售价，假设不同平台售价相同

					-- 时间循环
					WHILE t <= range_day DO
					INSERT INTO result(date,	product_id, shop, price) 
						VALUES (ADDDATE(start_date, t), prod_id, shop_name, price);  -- sales, amount
					SET t = t + 1;
					END	WHILE;

				SET j = j + 1;
				END	WHILE;
			SET i = i + 1;
		END	WHILE;
	END;
//


DELIMITER ;
CALL DayRangeProc (); 

-- 查询数据
SELECT 
	result.id,
	result.date,
	result.product_id,
	result.shop,
	result.price,
	IF(ISNULL(test2.`销量`), result.sales, test2.`销量`) AS sales,
	IF(ISNULL(test2.`销售额`), result.amount, test2.`销售额`) AS amount
FROM	result LEFT JOIN test2 
ON result.date = test2.`时间` 
AND result.shop = test2.`在售平台` 
AND result.product_id = test2.`规格条码`;

　　 

　　给定字符串，拆分后输出一列

USE test;
DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP (
	ID INT (8) NOT NULL AUTO_INCREMENT,
	number VARCHAR(20) NOT NULL,
	PRIMARY KEY (ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '测试';

DELIMITER $$
DROP PROCEDURE IF EXISTS `Pr_Rand_insert`$$
CREATE PROCEDURE `Pr_Rand_insert` (	IN insert_string VARCHAR (10000))  -- 定义输入
	BEGIN
	DECLARE I INT (8) DEFAULT 1 ;
	DECLARE J INT (8) DEFAULT 0 ;
	SET J = CHAR_LENGTH(insert_string) - CHAR_LENGTH(	REPLACE (insert_string, ',', '')) + 1; -- 计算共有多少位为",",则再加上1就表示共有多少个数值需要插入
	WHILE (I <= J) DO
		INSERT INTO TEMP(number) VALUES	(SUBSTRING_INDEX(SUBSTRING_INDEX(insert_string, ',', I), ',', - 1)) ; -- 用到了substring_index()函数
		SET I = I + 1 ;
	END WHILE ; 
	-- SELECT	CONCAT('共插入了', J, '个值,请确认');
	END$$
DELIMITER ;

CALL Pr_Rand_insert ('231,24,1114,151,7831241,9134,989');
SELECT * FROM	TEMP;