辽宁省建设工程质量监督总站网站,教育类网站开发模板,品牌建设的过程,深一集团的网站谁做的背景
同环比#xff0c;是基本的数据分析方法。在各类调研表中屡见不鲜#xff0c;如果人工向前追溯统计数据#xff0c;可想而知工作量是非常大的。 标题复制10行#xff0c;并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比…背景
同环比是基本的数据分析方法。在各类调研表中屡见不鲜如果人工向前追溯统计数据可想而知工作量是非常大的。 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
实战
以任务表为例创建任务表。
-- auto-generated definition
create table app_task
(id bigint auto_increment comment 主键primary key,user_id int null comment 当前执行人,user_name varchar(24) null comment 当前执行人名字,arrival_time datetime null comment 到达时间,start_time datetime null comment 开始时间,finish_time datetime null comment 完成时间,expect_time datetime null comment 预计时间,create_time datetime null comment 创建时间,is_delete tinyint default 0 null comment 0-生效1-失效
)comment 任务表;标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
存储过程插入数据
使用存储过程导入数据开始时间是凌晨结束时间是23点创建时间和到达时间为当前预期时间为变换的字符串。
CREATE PROCEDURE batch_insert_data(IN count INTEGER, -- record参数用来传需要插入数据的条数IN dt timestamp -- dt参数用来传入时间戳开始插入的第一条数据的时间格式为2020-10-24 14:31:44
)
BEGINDECLARE number INTEGER; -- 声明一个number用来控制循环的次数set number 1; -- 将number的值赋值为1代表循环从1开始START TRANSACTION;WHILE number countDO-- 使用while进行循环满足条件进入循环select date_add(dt, interval 1 second) into dt;-- 使用date_add()函数将时间进行转换为秒数并赋值给dt参数
-- 插入数据INSERT INTO app_task -- 库名.表名
-- 由于id是自动增长不需要额外赋值所以需要将剩余的其他字段全部列出进行一一对应赋值插入(user_id,user_name,arrival_time,start_time,finish_time,expect_time,create_time,is_delete)VALUES (FLOOR(RAND() * 10),2,now(),DATE_FORMAT(CURDATE(),%Y-%m-%d %H:%i:%s),DATE_ADD(CURDATE(), INTERVAL 23 HOUR),dt,now(),0);
-- number参数进行自增set number number 1;
-- dt参数进行自增set dt date_add(dt, interval 1 second);end WHILE;COMMIT;
END;标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
日期常用函数重点每周
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY); -- 昨天
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH); -- 上个月
SELECT DATE_ADD(CURDATE(), INTERVAL 9 HOUR); -- 当天9点
SELECT NOW();-- 查询当时
SELECT CURDATE();-- 查询当天
select yearweek(2023-05-12 09:00:00),week(2023-05-12 09:00:00),weekofyear(2023-05-12 09:00:00),weekday(2023-05-12 09:00:00) ; ;
-- 202319,19,19,4select yearweek(now() - INTERVAL 1 DAY) -- 从周1开始本周计算
-- yearweek、WEEK (日期) 是 每把周日作为一周的开始。标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
删除和调用存储过程
-- 删除存储过程
drop procedure batch_insert_data;
-- 调用存储过程
call batch_insert_data(20,now());按照月份和人员id统计每个人每月的完成任务时长延迟数量延迟百分比完成任务数量。
使用left(create_time, 7)按照日期排序
select user_id userId,left(create_time, 7) yyyyMM,ROUND(avg(TIMESTAMPDIFF(MINUTE, start_time, finish_time)), 2) finishDuration,sum(if(arrival_time expect_time, 1, 0)) delayCount,round(sum(if(arrival_time expect_time, 1, 0)) / count(id), 2) delayPercent,count(id) taskCountfrom app_taskgroup by userId, left(create_time, 7)order by userId, left(create_time, 7);标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
计算月环比
计算当月完成的任务量任务量的月环比计算当月完成任务的平均时长平均时长的月环比计算当月的延迟率延迟率的月环比
WINDOW w AS (PARTITION BY userId ORDER BY yyyyMM)指定窗口别名使用窗口函数lag获取分组中的前一条数据。
select *
from (select yyyyMM,userId,taskCount,LAG(taskCount, 1) OVER w AS lastTaskCount,round((taskCount - LAG(taskCount, 1) OVER w) / LAG(taskCount, 1) OVER w, 2) taskCountChain,finishDuration,LAG(finishDuration, 1) OVER w AS lastfinishDuration,round((finishDuration - LAG(finishDuration, 1) OVER w) / LAG(finishDuration, 1) OVER w,2) finishDurationChain,delayCount,LAG(finishDuration, 1) OVER w AS lastDelayCount,delayPercent,LAG(delayPercent, 1) OVER w AS lastDelayPercent,round((delayPercent - LAG(delayPercent, 1) OVER w) / LAG(delayPercent, 1) OVER w, 2) delayPercentChainfrom (select user_id userId,left(create_time, 7) yyyyMM,ROUND(avg(TIMESTAMPDIFF(MINUTE, start_time, finish_time)), 2) finishDuration,sum(if(arrival_time expect_time, 1, 0)) delayCount,round(sum(if(arrival_time expect_time, 1, 0)) / count(id), 2) delayPercent,count(id) taskCountfrom app_taskgroup by userId, left(create_time, 7)order by userId, left(create_time, 7)) t1WINDOW w AS (PARTITION BY userId ORDER BY yyyyMM)) tmp
where tmp.yyyyMM 2023-05;标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
计算月同比
核心是比较前12月的数据LAG(finishDuration, 12) OVER w 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
使用WITH AS简化SQL
WITH AS短语也叫做子查询部分subquery factoring可以让你做很多事情定义一个SQL片断该SQL片断会被整个SQL语句所用到。有的时候是为了让SQL语句的可读性更高些也有可能是在UNION ALL的不同部分作为提供数据的部分。
with statistics as (select user_id userId,left(create_time, 7) yyyyMM,ROUND(avg(TIMESTAMPDIFF(MINUTE, start_time, finish_time)), 2) finishDuration,sum(if(arrival_time expect_time, 1, 0)) delayCount,round(sum(if(arrival_time expect_time, 1, 0)) / count(id), 2) delayPercent,count(id) taskCountfrom app_taskgroup by userId, left(create_time, 7)order by userId, left(create_time, 7)
),tmp as (select yyyyMM,userId,taskCount,LAG(taskCount, 1) OVER w AS lastTaskCount,round((taskCount - LAG(taskCount, 1) OVER w) / LAG(taskCount, 1) OVER w, 2) taskCountChain,finishDuration,LAG(finishDuration, 1) OVER w AS lastfinishDuration,round((finishDuration - LAG(finishDuration, 1) OVER w) / LAG(finishDuration, 1) OVER w,2) finishDurationChain,delayCount,LAG(finishDuration, 1) OVER w AS lastDelayCount,delayPercent,LAG(delayPercent, 1) OVER w AS lastDelayPercent,round((delayPercent - LAG(delayPercent, 1) OVER w) / LAG(delayPercent, 1) OVER w, 2) delayPercentChainfrom statisticsWINDOW w AS (PARTITION BY userId ORDER BY yyyyMM))
select *
from tmp
where tmp.yyyyMM 2023-05;标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理
关联查询
如果在记录表app_task_record记录状态和创建时间当任务开始时插入一条状态30的数据当任务结束时插入一条状态90的数据。计算当前任务的完成时间。
select max(if(task_status 30, create_time, null)) startTime,max(if(task_status 90, create_time, null)) finishTime,TIMESTAMPDIFF(MINUTE, max(if(task_status 30, create_time, null)),max(if(task_status 90, create_time, null))) time_consuming
from app_task_record
where task_id 1;标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理 标题复制10行并且每行大于10个字符【源码解析】SpringBoot接口参数【Mysql实战】使用存储过程和计算同比环比校验原理 文章转载自： http://www.morning.lqzhj.cn.gov.cn.lqzhj.cn http://www.morning.hqzmz.cn.gov.cn.hqzmz.cn http://www.morning.ygbq.cn.gov.cn.ygbq.cn http://www.morning.rdpps.cn.gov.cn.rdpps.cn http://www.morning.rklgm.cn.gov.cn.rklgm.cn http://www.morning.gpxbc.cn.gov.cn.gpxbc.cn http://www.morning.gfkb.cn.gov.cn.gfkb.cn http://www.morning.rbjf.cn.gov.cn.rbjf.cn http://www.morning.ghphp.cn.gov.cn.ghphp.cn http://www.morning.fzlk.cn.gov.cn.fzlk.cn http://www.morning.gxwyr.cn.gov.cn.gxwyr.cn http://www.morning.wngpq.cn.gov.cn.wngpq.cn http://www.morning.kkdbz.cn.gov.cn.kkdbz.cn http://www.morning.lrflh.cn.gov.cn.lrflh.cn http://www.morning.zwfgh.cn.gov.cn.zwfgh.cn http://www.morning.flqkp.cn.gov.cn.flqkp.cn http://www.morning.xtdms.com.gov.cn.xtdms.com http://www.morning.rjxwq.cn.gov.cn.rjxwq.cn http://www.morning.wbhzr.cn.gov.cn.wbhzr.cn http://www.morning.fksyq.cn.gov.cn.fksyq.cn http://www.morning.cwrpd.cn.gov.cn.cwrpd.cn http://www.morning.lzsxp.cn.gov.cn.lzsxp.cn http://www.morning.nzsdr.cn.gov.cn.nzsdr.cn http://www.morning.rrxmm.cn.gov.cn.rrxmm.cn http://www.morning.cmhkt.cn.gov.cn.cmhkt.cn http://www.morning.brlgf.cn.gov.cn.brlgf.cn http://www.morning.rdnpg.cn.gov.cn.rdnpg.cn http://www.morning.khcpx.cn.gov.cn.khcpx.cn http://www.morning.phcqk.cn.gov.cn.phcqk.cn http://www.morning.lqzhj.cn.gov.cn.lqzhj.cn http://www.morning.ghgck.cn.gov.cn.ghgck.cn http://www.morning.mlbn.cn.gov.cn.mlbn.cn http://www.morning.zcqtr.cn.gov.cn.zcqtr.cn http://www.morning.plcyq.cn.gov.cn.plcyq.cn http://www.morning.nlzpj.cn.gov.cn.nlzpj.cn http://www.morning.jbshh.cn.gov.cn.jbshh.cn http://www.morning.wcczg.cn.gov.cn.wcczg.cn http://www.morning.pqkrh.cn.gov.cn.pqkrh.cn http://www.morning.gqnll.cn.gov.cn.gqnll.cn http://www.morning.xllrf.cn.gov.cn.xllrf.cn http://www.morning.hfytgp.cn.gov.cn.hfytgp.cn http://www.morning.kbfzp.cn.gov.cn.kbfzp.cn http://www.morning.zhqfn.cn.gov.cn.zhqfn.cn http://www.morning.sloxdub.cn.gov.cn.sloxdub.cn http://www.morning.bgpch.cn.gov.cn.bgpch.cn http://www.morning.ggfdq.cn.gov.cn.ggfdq.cn http://www.morning.jqzns.cn.gov.cn.jqzns.cn http://www.morning.pqkyx.cn.gov.cn.pqkyx.cn http://www.morning.gtnyq.cn.gov.cn.gtnyq.cn http://www.morning.nsyzm.cn.gov.cn.nsyzm.cn http://www.morning.wzwpz.cn.gov.cn.wzwpz.cn http://www.morning.zbnts.cn.gov.cn.zbnts.cn http://www.morning.ccffs.cn.gov.cn.ccffs.cn http://www.morning.qwyms.cn.gov.cn.qwyms.cn http://www.morning.ctxt.cn.gov.cn.ctxt.cn http://www.morning.zckhn.cn.gov.cn.zckhn.cn http://www.morning.bmtkp.cn.gov.cn.bmtkp.cn http://www.morning.mrlkr.cn.gov.cn.mrlkr.cn http://www.morning.qxwwg.cn.gov.cn.qxwwg.cn http://www.morning.bpmnj.cn.gov.cn.bpmnj.cn http://www.morning.bmncq.cn.gov.cn.bmncq.cn http://www.morning.ffgbq.cn.gov.cn.ffgbq.cn http://www.morning.kphsp.cn.gov.cn.kphsp.cn http://www.morning.gnlyq.cn.gov.cn.gnlyq.cn http://www.morning.rgsgk.cn.gov.cn.rgsgk.cn http://www.morning.whnps.cn.gov.cn.whnps.cn http://www.morning.dkfb.cn.gov.cn.dkfb.cn http://www.morning.gjzwj.cn.gov.cn.gjzwj.cn http://www.morning.smspc.cn.gov.cn.smspc.cn http://www.morning.rdzgm.cn.gov.cn.rdzgm.cn http://www.morning.tqsmc.cn.gov.cn.tqsmc.cn http://www.morning.mmhaoma.com.gov.cn.mmhaoma.com http://www.morning.eronghe.com.gov.cn.eronghe.com http://www.morning.wtsr.cn.gov.cn.wtsr.cn http://www.morning.rpsjh.cn.gov.cn.rpsjh.cn http://www.morning.qzqfq.cn.gov.cn.qzqfq.cn http://www.morning.xxknq.cn.gov.cn.xxknq.cn http://www.morning.xrtsx.cn.gov.cn.xrtsx.cn http://www.morning.tndxg.cn.gov.cn.tndxg.cn http://www.morning.qkgwx.cn.gov.cn.qkgwx.cn
