时序数据 mysql存储_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;

ENDWHILE;

END;

//

DELIMITER ;

CALL DayRangeProc ('2010-09-01', '2010-09-10');

SELECT * FROMtmptb;

从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 FROMtest2); -- 保存平台，类似于数组操作

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;

ENDWHILE;

SET j = j + 1;

ENDWHILE;

SET i = i + 1;

ENDWHILE;

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

FROMresult 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 ;

-- SELECTCONCAT('共插入了', J, '个值,请确认');

END$$

DELIMITER ;

CALL Pr_Rand_insert ('231,24,1114,151,7831241,9134,989');

SELECT * FROMTEMP;