網(wǎng)站建設(shè)肆金手指排名8模板建站公司
背景
同環(huán)比，是基本的數(shù)據(jù)分析方法。在各類調(diào)研表中屢見不鮮，如果人工向前追溯統(tǒng)計(jì)數(shù)據(jù)，可想而知工作量是非常大的。
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比校驗(yàn)原理
實(shí)戰(zhàn)
以任務(wù)表為例，創(chuàng)建任務(wù)表。
-- auto-generated definition
create table app_task
(id bigint auto_increment comment '主鍵'primary key,user_id int null comment '當(dāng)前執(zhí)行人',user_name varchar(24) null comment '當(dāng)前執(zhí)行人名字',arrival_time datetime null comment '到達(dá)時(shí)間',start_time datetime null comment '開始時(shí)間',finish_time datetime null comment '完成時(shí)間',expect_time datetime null comment '預(yù)計(jì)時(shí)間',create_time datetime null comment '創(chuàng)建時(shí)間',is_delete tinyint default 0 null comment '0-生效，1-失效'
)comment '任務(wù)表';
存儲(chǔ)過程插入數(shù)據(jù)
使用存儲(chǔ)過程導(dǎo)入數(shù)據(jù)，開始時(shí)間是凌晨，結(jié)束時(shí)間是23點(diǎn)，創(chuàng)建時(shí)間和到達(dá)時(shí)間為當(dāng)前，預(yù)期時(shí)間為變換的字符串。
CREATE PROCEDURE `batch_insert_data`(IN count INTEGER, -- record參數(shù)用來傳需要插入數(shù)據(jù)的條數(shù)IN dt timestamp -- dt參數(shù)用來傳入時(shí)間戳，開始插入的第一條數(shù)據(jù)的時(shí)間（格式為：'2020-10-24 14:31:44'）
)
BEGINDECLARE number INTEGER; -- 聲明一個(gè)number，用來控制循環(huán)的次數(shù)set number = 1; -- 將number的值賦值為1，代表循環(huán)從1開始START TRANSACTION;WHILE number <= countDO-- 使用while進(jìn)行循環(huán)，滿足條件進(jìn)入循環(huán)select date_add(dt, interval 1 second) into dt;-- 使用date_add()函數(shù)將時(shí)間進(jìn)行轉(zhuǎn)換為秒數(shù)，并賦值給dt參數(shù)
-- 插入數(shù)據(jù)INSERT INTO app_task -- 庫(kù)名.表名
-- 由于id是自動(dòng)增長(zhǎng)，不需要額外賦值，所以需要將剩余的其他字段全部列出進(jìn)行一一對(duì)應(yīng)賦值插入(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參數(shù)進(jìn)行自增set number = number + 1;
-- dt參數(shù)進(jìn)行自增set dt = date_add(dt, interval 1 second);end WHILE;COMMIT;
END;
日期常用函數(shù)（重點(diǎn)：每周）
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY); -- 昨天
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH); -- 上個(gè)月
SELECT DATE_ADD(CURDATE(), INTERVAL 9 HOUR); -- 當(dāng)天9點(diǎn)
SELECT NOW();-- 查詢當(dāng)時(shí)
SELECT CURDATE();-- 查詢當(dāng)天
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開始本周計(jì)算
-- yearweek、WEEK (日期) 是 每把周日，作為一周的開始。
刪除和調(diào)用存儲(chǔ)過程
-- 刪除存儲(chǔ)過程
drop procedure batch_insert_data;
-- 調(diào)用存儲(chǔ)過程
call batch_insert_data(20,now());
按照月份和人員id，統(tǒng)計(jì)每個(gè)人每月的完成任務(wù)時(shí)長(zhǎng)，延遲數(shù)量，延遲百分比，完成任務(wù)數(shù)量。
- 使用
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);
計(jì)算月環(huán)比
計(jì)算當(dāng)月完成的任務(wù)量，任務(wù)量的月環(huán)比；計(jì)算當(dāng)月完成任務(wù)的平均時(shí)長(zhǎng)，平均時(shí)長(zhǎng)的月環(huán)比；計(jì)算當(dāng)月的延遲率，延遲率的月環(huán)比
WINDOW w AS (PARTITION BY userId ORDER BY yyyyMM)，指定窗口別名- 使用窗口函數(shù)
lag，獲取分組中的前一條數(shù)據(jù)。
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';
計(jì)算月同比
核心是比較前12月的數(shù)據(jù)，LAG(finishDuration, 12) OVER w
使用WITH AS簡(jiǎn)化SQL
WITH AS短語，也叫做子查詢部分（subquery factoring），可以讓你做很多事情，定義一個(gè)SQL片斷，該SQL片斷會(huì)被整個(gè)SQL語句所用到。有的時(shí)候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供數(shù)據(jù)的部分。
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';
關(guān)聯(lián)查詢
如果在記錄表app_task_record記錄狀態(tài)和創(chuàng)建時(shí)間，當(dāng)任務(wù)開始時(shí)，插入一條狀態(tài)30的數(shù)據(jù)，當(dāng)任務(wù)結(jié)束時(shí)，插入一條狀態(tài)90的數(shù)據(jù)。計(jì)算當(dāng)前任務(wù)的完成時(shí)間。
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;
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比校驗(yàn)原理
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比校驗(yàn)原理
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比校驗(yàn)原理
標(biāo)題復(fù)制10行，并且每行大于10個(gè)字符【源碼解析】SpringBoot接口參數(shù)【Mysql實(shí)戰(zhàn)】使用存儲(chǔ)過程和計(jì)算同比環(huán)比校驗(yàn)原理