MYSQL； 存储过程，游标 ；根据时间，计算插值，将结果插入另一个表中

最新推荐文章于 2024-04-24 16:25:27 发布
最新推荐文章于 2024-04-24 16:25:27 发布
阅读量258 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/laidianjilu/p/10673612.html
版权

存储过程，游标 固定格式

DELIMITER $$DROP PROCEDURE IF EXISTS `historical_data_period`$$
CREATE PROCEDURE `historical_data_period`(IN start_time_cur VARCHAR(32),IN end_time_cur VARCHAR(32))
BEGIN    
  -- declare声明，声明顺序（由前往后）：1.变量，2.游标，3.异常
  -- 1.
  DECLARE stopFlag INT DEFAULT 0;-- 游标默认循环
  -- 2.
  DECLARE start_time_count CURSOR FOR   -- 游标绑定一个表的所有字段
     SELECT *
     FROM 表名 ; 
  -- 3.
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;-- 当查不到数据时，关闭游标
 
 -- 游标结构体
 -- 打开游标
  OPEN start_time_count;
       read_loop: LOOP
            -- 取值 取多个字段
         FETCH start_time_count INTO min_time,station_code2;
             IF  stopFlag =1 THEN
                LEAVE read_loop;
             END IF;
        -- 你自己想做的操作
         
     END LOOP read_loop;
    CLOSE start_time_count; 
  SET stopFlag=0; 
-- 游标结构体
  END$$
DELIMITER ;

关于表

/**
本例未考虑主键,如果考虑主键，值非空，对数据操作（插入，更新）优先操作；未考虑计算的单位
创建表注意：末尾的 逗号，无符号 
DEFAULT NULL：也可以 DECLARE day_count INT(8) DEFAULT TO_DAYS(end_time_cur) - TO_DAYS(start_time_cur);
*/
CREATE TABLE `historical_data` ( -- 要查询的表
  `heat_station_code` CHAR(32) DEFAULT NULL,-- 热站编码
  `heat_value` DECIMAL(32,2) DEFAULT NULL,-- 最小表值
  read_time  DATETIME DEFAULT NULL, -- 数据读取时间
  heat_area_design DECIMAL(32,2) DEFAULT NULL  -- 计算耗热量
) 
CREATE TABLE `period_data` (  -- 要插入的表
  `heat_station_code` CHAR(32) DEFAULT NULL,-- 热站编码
  `heat_meter_start_value` DECIMAL(32,2) UNSIGNED DEFAULT NULL,
  `heat_meter_end_value` DECIMAL(32,2) UNSIGNED DEFAULT NULL,
  `heat_consumption` DECIMAL(32,2) DEFAULT NULL,-- 表值差，耗热量
   heat_area_design DECIMAL(32,2) DEFAULT NULL, -- 计算耗热量
  `heat_rate` DECIMAL(32,2) DEFAULT NULL -- 耗热率  耗热量/ 面积 
)
-- ALTER TABLE `period_data` ADD day_count INT(32,2); 可加表字段
-- CALL historical_data_period('2019-03-25 20:55:00','2019-03-26 22:55:00',50);-- 插入表数据，注意'时间'datetime格式

historical_data

需求1：取【historical_data表】的【两个read_time值】，【两个read_time值】对应【两个heat_value值】。计算最早一天和最后一天的耗热量（heat_consumption）和耗热率（heat_rate）
假使3个热站，会查出3条记录
 

DELIMITER $$DROP PROCEDURE IF EXISTS `historical_data_period`$$
CREATE PROCEDURE `historical_data_period`(IN start_time_cur VARCHAR(32),IN end_time_cur VARCHAR(32))
BEGIN    
 -- 定义变量
-- 事务 游标 变量
-- 
  DECLARE station_code2 VARCHAR(32) DEFAULT NULL; -- 热站编码
  DECLARE heat_area_design2 VARCHAR(32) DEFAULT NULL; -- 热站编码
  DECLARE min_time DATETIME DEFAULT NULL;-- 起始时间`read_time`当天最早的一条记录
  DECLARE max_time DATETIME DEFAULT NULL;-- 结束时间`read_time`当天最晚的一条记录
  DECLARE min_heat_value DOUBLE(255,2) DEFAULT NULL;-- min_time对应的表值
  DECLARE max_heat_value DOUBLE(255,2) DEFAULT NULL;-- max_time对应的表值
  DECLARE heat_consumption_count DOUBLE(255,2) DEFAULT NULL; -- 耗热量
  DECLARE day_count INT(8) DEFAULT TO_DAYS(end_time_cur) - TO_DAYS(start_time_cur);-- 定义时间间隔
  DECLARE stopFlag INT DEFAULT 0;-- 设置游标默认循环
-- 定义游标
  DECLARE start_time_count CURSOR FOR   -- 第一天记录的最早一条数据
     SELECT MIN(`read_time`),`heat_station_code`
     FROM `historical_data` 
     WHERE TO_DAYS(start_time_cur) - TO_DAYS(read_time)=0
     GROUP BY `heat_station_code`; -- 按热站编码生成3行数据
     
 DECLARE end_time_count CURSOR FOR   -- 最后一天记录的最晚一条数据 
     SELECT MAX(`read_time`) 
     FROM `historical_data`  
     WHERE TO_DAYS(end_time_cur) - TO_DAYS(read_time)=0 AND heat_station_code=station_code2;
       -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;-- 当查不到数据时，关闭游标
  -- 手动开启事务
  START TRANSACTION;
  -- 清除表中上次存储的查询数据
  TRUNCATE TABLE `period_data`;
  -- 打开游标
  OPEN start_time_count;
       read_loop: LOOP
            -- 取值 取多个字段
         FETCH start_time_count INTO min_time,station_code2;
             IF  stopFlag =1 THEN
                LEAVE read_loop;
             END IF;
        -- 你自己想做的操作 
        SELECT `heat_value` INTO min_heat_value FROM `historical_data` WHERE read_time=min_time AND heat_station_code=station_code2 ;
            
            -- 第二个游标
         OPEN end_time_count;
            next_loop: LOOP
            -- 取值 取多个字段
         FETCH end_time_count INTO max_time;
              IF  stopFlag=1 THEN
            LEAVE next_loop;
             END IF;
        -- 你自己想做的操作   
        SELECT `heat_value` INTO max_heat_value FROM `historical_data` WHERE read_time=max_time AND heat_station_code=station_code2 ;  
        SET heat_area_design2=(SELECT `heat_area_design` FROM `historical_data` WHERE `heat_station_code`=station_code2 AND `read_time`=min_time);
        SET heat_consumption_count= max_heat_value - min_heat_value;
         -- 若计算出来的能耗量为null或为负值,赋值为0
          IF(heat_consumption_count IS NULL||heat_consumption_count <0 ) THEN
             SET heat_consumption_count=0;
          END IF;  -- heat_consumption_count IS NULL
            -- 插入热站信息
          /** 1.如果计算耗热率时为分母为null，设置耗热率为0
              2.TRUNCATE  小数点后两位的数，直接截断
              3.虽然格式是 SELECT..from..,但INSERT INTO的字段可以不是from表中的字段
              4.WHERE中 `read_time`=min_time也可以改为`read_time`=max_time
              因为从historical_data中的SELECT的字段，不受AND `read_time`=min_time;时间限制
              但AND `read_time`=min_time也不可以省略。因为`heat_station_code`=station_code2的记录每天都有，`heat_station_code`=station_code2的记录有很多行，
              AND `read_time`=min_time保证不取重复的热站编码
          */
          IF(heat_area_design2 IS NOT NULL) THEN
          INSERT INTO `period_data`(`heat_station_code`,`heat_meter_start_value`,`heat_meter_end_value`,heat_consumption,heat_area_design,heat_rate) 
          SELECT `heat_station_code`,min_heat_value,max_heat_value,heat_consumption_count,heat_area_design2,TRUNCATE(heat_consumption_count/(heat_area_design2),2)
          FROM `historical_data`    
          WHERE`heat_station_code`=station_code2 AND `read_time`=min_time;
          ELSE 
          INSERT INTO `period_data`(`heat_station_code`,`heat_meter_start_value`,`heat_meter_end_value`,heat_consumption,`heat_area_design`,heat_rate) 
          SELECT `heat_station_code`,min_heat_value,max_heat_value,heat_consumption_count,heat_area_design2,0
          FROM `historical_data`    
          WHERE `heat_station_code`=station_code2 AND `read_time`=min_time;
           END IF;-- day_time_difference!=0
           END LOOP next_loop;
          CLOSE end_time_count; 
          /* 第二个游标 // 内循环游标指到最后一条数据，再继续，便会not found,关闭游标，
          内外循环共用一个stopFlag，
          要进入外循环，必须打开游标，
          */
           SET stopFlag=0;  
     END LOOP read_loop;
    CLOSE start_time_count; 
  COMMIT;
  END$$
DELIMITER ;

 

CALL historical_data_period('2019-03-25 20:55:00','2019-03-26 22:55:00');

period_data

 


需求2：时间改变，【两个read_time值】，即两个时间段，求两个时间段内 每天的耗热量（heat_consumption）和耗热率
假使3个热站，会查出（3*（间隔时间+1））条记录：4月1日与4月3日的间隔时间时3-1=2天，但包括4月1日与4月3日这两天，总天数该是3天

DELIMITER $$DROP PROCEDURE IF EXISTS `historical_data_period`$$
CREATE PROCEDURE `historical_data_period`(IN start_time_cur VARCHAR(32),IN end_time_cur VARCHAR(32))
BEGIN    
 -- 定义变量
-- 事务 游标 变量
  DECLARE station_code2 VARCHAR(32) DEFAULT NULL; -- 热站编码
  DECLARE heat_area_design2 VARCHAR(32) DEFAULT NULL; -- 热站编码
  DECLARE min_time DATETIME DEFAULT NULL;-- 起始时间`read_time`当天最早的一条记录
  DECLARE max_time DATETIME DEFAULT NULL;-- 结束时间`read_time`当天最晚的一条记录
  DECLARE min_heat_value DOUBLE(255,2) DEFAULT NULL;-- min_time对应的表值
  DECLARE max_heat_value DOUBLE(255,2) DEFAULT NULL;-- max_time对应的表值
  DECLARE heat_consumption_count DOUBLE(255,2) DEFAULT NULL; -- 耗热量
  DECLARE day_count INT(8) DEFAULT TO_DAYS(end_time_cur) - TO_DAYS(start_time_cur);-- 定义时间间隔
  DECLARE stopFlag INT DEFAULT 0;-- 设置游标默认循环
  
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;-- 当查不到数据时，关闭游标
  -- 手动开启事务
  START TRANSACTION;
  -- 清除表中上次存储的查询数据
  TRUNCATE TABLE `period_data`;
-- <改变的地方
   /*定位到时间段内的当天时间 ，从前向后搜  
  -- 设置按时间*/
  WHILE( day_count>= 0) DO
  IF(EXISTS(SELECT * FROM `historical_data` WHERE TO_DAYS(end_time_cur) - TO_DAYS(read_time)=day_count)) THEN -- 第一天 
  BEGIN  -- 保证游标定义能在后面！
-- 定义游标
  DECLARE start_time_count CURSOR FOR   -- 当天记录的最早一条数据
     SELECT MIN(`read_time`),`heat_station_code`
     FROM `historical_data` 
     WHERE TO_DAYS(end_time_cur) - TO_DAYS(read_time)=day_count
     GROUP BY `heat_station_code`; -- 按热站编码生成3行数据
     
 DECLARE end_time_count CURSOR FOR   -- 当记录的最晚一条数据 
     SELECT MAX(`read_time`) 
     FROM `historical_data`  
     WHERE TO_DAYS(end_time_cur) - TO_DAYS(read_time)=day_count AND heat_station_code=station_code2;
  -- 改变的地方>
  -- 打开游标
  OPEN start_time_count;
       read_loop: LOOP
            -- 取值 取多个字段
         FETCH start_time_count INTO min_time,station_code2;
             IF  stopFlag =1 THEN
                LEAVE read_loop;
             END IF;
        -- 你自己想做的操作 
        SELECT `heat_value` INTO min_heat_value FROM `historical_data` WHERE read_time=min_time AND heat_station_code=station_code2 ;
            
            -- 第二个游标
        OPEN end_time_count;
            next_loop: LOOP
            -- 取值 取多个字段
         FETCH end_time_count INTO max_time;
              IF  stopFlag=1 THEN
            LEAVE next_loop;
             END IF;
        -- 你自己想做的操作   
        SELECT `heat_value` INTO max_heat_value FROM `historical_data` WHERE read_time=max_time AND heat_station_code=station_code2 ;  
        SET heat_area_design2=(SELECT `heat_area_design` FROM `historical_data` WHERE `heat_station_code`=station_code2 AND `read_time`=min_time);
        SET heat_consumption_count= max_heat_value - min_heat_value;
         -- 若计算出来的能耗量为null或为负值,赋值为0
          IF(heat_consumption_count IS NULL||heat_consumption_count <0 ) THEN
             SET heat_consumption_count=0;
          END IF;  -- heat_consumption_count IS NULL
            -- 插入热站信息
          /** 如果计算耗热率时为分母为0，设置耗热率为0
              TRUNCATE  小数点后两位的数，直接截断
              虽然格式是 SELECT..from..,但INSERT INTO的字段可以不是from表中的字段
              WHERE中 `read_time`=min_time也可以改为`read_time`=max_time
              因为从historical_data中的SELECT的字段，只有heat_station_code，heat_station_code只受WHERE `heat_station_code`=station_code2限制，不受AND `read_time`=min_time;时间限制
              AND `read_time`=min_time不可以省略。因为`heat_station_code`=station_code2的记录每天都有，`heat_station_code`=station_code2的记录有很多行，AND `read_time`=min_time保证不取重复的热站编码
          */
          IF(heat_area_design2 IS NOT NULL) THEN
          INSERT INTO `period_data`(`heat_station_code`,`heat_meter_start_value`,`heat_meter_end_value`,heat_consumption,heat_area_design,heat_rate) 
          SELECT `heat_station_code`,min_heat_value,max_heat_value,heat_consumption_count,heat_area_design2,TRUNCATE(heat_consumption_count/(heat_area_design2),2)
          FROM `historical_data`    
          WHERE`heat_station_code`=station_code2 AND `read_time`=min_time;
          ELSE 
          INSERT INTO `period_data`(`heat_station_code`,`heat_meter_start_value`,`heat_meter_end_value`,heat_consumption,`heat_area_design`,heat_rate) 
          SELECT `heat_station_code`,min_heat_value,max_heat_value,heat_consumption_count,heat_area_design2,0
          FROM `historical_data`    
          WHERE `heat_station_code`=station_code2 AND `read_time`=min_time;
           END IF;-- day_time_difference!=0
           END LOOP next_loop;
          CLOSE end_time_count; 
          /* 第二个游标 // 内循环游标指到最后一条数据，再继续，便会not found,关闭游标，
          内外循环共用一个stopFlag，
          要进入外循环，必须打开游标，
          */
           SET stopFlag=0;  
     END LOOP read_loop;
    CLOSE start_time_count; 
-- <改变的地方 
    SET stopFlag=0;
    END; -- end后是要加结束符号的
    END IF;
    SET day_count=day_count-1;  
    END WHILE;
-- 改变的地方>
  COMMIT;
  END$$
DELIMITER ;
CALL historical_data_period('2019-03-25 20:55:00','2019-03-27 00:55:44');

period_data

关于游标：游标定义时，未带能计算的字段，要取出参与运算的字段，用select语句设置where，确保查出参与运算的字段与游标在同一行

转载于:https://www.cnblogs.com/laidianjilu/p/10673612.html

weixin_34375054
关注
mysql创建临时表，将查询结果插入已有表中
weixin_33787529的博客
03-03 5368
2019独角兽企业重金招聘Python工程师标准>>> ...
python时间序列数据的对齐和数据库的分批查询
infinitor的博客
06-01 820
python时间序列数据的对齐和数据库的分批查询 欲直接下载代码文件，关注我们的公众号哦！查看历史消息即可！ 0. 前言 在机器学习里，我们对时间序列数据做预处理的时候，经常会碰到一个问题：有多个时间序列存在多个表里，每个表的的时间轴不完全相同，要如何把这些表在时间轴上进行对齐，从而合并成一个表呢？尤其是当这些表都存在数据库里，而且超级超级大的时...
MySQL 游标
qq_54092308的博客
12-26 4315
MySQL 游标 注：未经过，本文作者允许，严禁转载 MySQL 游标概念：游标可以用来依次取出查询结果集中的每一条数据（逐条读取，结果集中的每一条记录） 游标的使用步骤 声明游标语法 1 -- DECLARE 游标名称 CURSOR FOR SQL查询语句; DECLARE cursor_name CURSOR FOR select_statement; 打开游标语法 2 -- 打开游标 -- OPEN 游标名称; OPEN cursor_name; 使用游标语法 3 -- 使用游标
mysql 过程 游标_MySQL过程和游标
weixin_39734987的博客
01-18 56
BEGINDECLARE f_leastCount INT DEFAULT 100;DECLARE f_ratio FLOAT DEFAULT 0.8;DECLARE i_channel VARCHAR(64);DECLARE i_appVersionName VARCHAR(64);DECLARE i_statDate DATE;DECLARE i_accumulateCount INT;DEC...
MYSQL存储过程带游标
qq_35840028的博客
05-08 343
create procedure updateYearAndMonth() BEGIN -- 定义参数 declare id int; declare yearMonth varchar(10); declare done INT DEFAULT FALSE; -- 定义游标 declare cur_income cursor for select income_id,date_format(posting_date,'%Y/%m') from output_inco.
mysql数据库存储过程之游标（光标cursor）
努力是为了让自己不平庸。。。
03-08 3337
本文讲了游标及条件处理程序。
Python读取MySQL数据金融科技应用：驱动金融创新
![Python读取MySQL数据金融科技应用：驱动金融创新]...该模块提供了一个易于使用的接口，允许Python程序与MySQL服务器进行交互。连接参数包括主机、用户名、
PYTHON学习笔记之（一）2020.08
guofeng_0的博客
08-22 2144
PYTHON学习笔记之（一）2020.08 Python基础 数据类型 1 列表 list 1.1 列表转换字符串 stu = ['王一', '李二', '张三'] print(stu) nStu = ' - '.join(stu) print(nStu) [‘王一’, ‘李二’, ‘张三’] 王一 - 李二 - 张三 1.2 字符串转列表 strT = 'a good day' lst = strT.split(' ') print(lst) [‘a’, ‘good’, ‘day’] 2 字典
存储过程加游标-循环sql结果集插入数据
liuqinen的博客
05-31 2849
工作中遇到数据库迁移问题，需要先查询出一张表的ID，然后添加到另外的三方表里面，所以写了这个存储过程，在此做笔记。 DROP PROCEDURE IF EXISTS useCursor; delimiter // /*建立 存储过程 create */ CREATE PROCEDURE useCursor () BEGIN /*局部变量的定义 开始*/ DECLARE tmpName...
MySQL中的游标
伤心的辣条
04-20 3025
游标（cursor）是一个存储在MySQL服务器上的数据库查询， 它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游 标之后，应用程序可以根据需要滚动或浏览其中的数据。注意：MySQL游标只能用于 存储过程（和函数）。
使用游标循环一个表的查询结果批量更新另一个表
tonglei111的博客
02-24 369
create or replace procedure proc_a is g_ varchar(50); x_ varchar(50); y_ varchar(50); cursor myCursor is SELECT b.gridcode, b.x_center,b.y_center FROM T_GRID_NEW_CENTER b;--这里查询取了三个字段 begin open myCursor;--打开游标 loop fetch myCursor INTO g_,x_,y_;--这里就可以int.
MySQL学习笔记8——游标
最新发布
qq_44778824的博客
04-24 2326
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
mysql 用存储过程查询一张表集合更新到另一张表
qq_30667039的博客
07-19 1752
经常有朋友问存储过程怎么写，有什么好处。 1、通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。 2、存...
mysql游标循环的使用_实例详解mysql游标循环的使用
weixin_31308407的博客
01-19 2213
这篇文章主要介绍了mysql游标循环的使用，有一定的参考价值，感兴趣的朋友可以参考一下，希望对你有所帮助！CREATE PROCEDURE `test`.`new_procedure` ()BEGIN-- 需要定义接收游标数据的变量DECLARE a CHAR(16); -- 游标DECLARE cur CURSOR FOR SELECT i FROM test.t; -- 遍历数据结束标志D...
10、游标（Cursor）的定义及使用
you can you up up up的博客
10-17 8369
在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。 关系数据库管理系统实质是面向集合的，在 MySQL 中并没有一种描述表中单一记录的表达形式，除非使用 WHERE 子句来限制只有一条记录被选中。所以有时我们必须借助于游标来进行单条记录的数据处理。 一般通过游标定位到结果集的某一行进行数据修改。 结果集是符合 SQL 语句的所有记录的集合。
MySQL之什么是游标以及如何使用游标
白话机器学习
08-21 996
这就是使用游标的原因。游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条 SELECT 语句,而是被该语句检索出来的结果集。使用简单的 SELECT 语句,例如,没有办法得到第一行、下一行或前10行,也不存在每次一行地处理所有行的简单方法(相对于成批地处理它们)。用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义。本章介绍了什么是游标以及为什么要使用游标,举了演示基本游标使用的例子,并且讲解了对游标结果进行循环以及逐行处理的技术。
mysql把游标数据存入表中_MySQL； 存储过程，游标 ；根据时间，计算插值，将结果插入另一个表中...
weixin_39983912的博客
12-18 177
DELIMITER $$DROP PROCEDURE IF EXISTS`historical_data_period`$$CREATE PROCEDURE `historical_data_period`(IN start_time_cur VARCHAR(32),IN end_time_cur VARCHAR(32))BEGIN--定义变量--事务 游标 变量DECLARE station_c...
Mysql 存储过程，开启游标循环后，重复循环插入多次数据。
wangliuqi123的博客
11-22 5611
今天在写存储过程，给其他表插入数据时，发现，我游标取出来的数据明明只有一条，但是在循环过程中，却发现插入了两条数据。下面是代码 BEGIN #Routine body goes here... DECLARE event_content1 VARCHAR(255); DECLARE event_id1 INT; DECLARE event_name VARCHAR(255); DEC...
mysql存储过程游标插入数据
05-20
在上面的示例中，我们创建了一个名为`insert_data`的存储过程，该过程使用游标遍历了`persons`表中的所有数据，并将每行数据插入到`new_persons`表中。请注意，`persons`表和`new_persons`表需要自行创建。 要调用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值