【Mysql实战】使用存储过程和计算同比环比

秋装什么

已于 2023-05-12 18:22:28 修改

阅读量1.5k

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库 java

于 2023-05-12 17:10:11 首次发布

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/qq_42985872/article/details/130646386

版权

MYSQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

背景

同环比，是基本的数据分析方法。在各类调研表中屡见不鲜，如果人工向前追溯统计数据，可想而知工作量是非常大的。

标题复制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'）
)
BEGIN
    DECLARE number INTEGER; -- 声明一个number，用来控制循环的次数
    set number = 1; -- 将number的值赋值为1，代表循环从1开始
    START TRANSACTION;
    WHILE number <= count
        DO
            -- 使用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,4

select 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)                                                     taskCount
      from app_task
      group 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) delayPercentChain
         from (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)                                                       taskCount
               from app_task
               group by userId, left(create_time, 7)
               order by userId, left(create_time, 7)) t1
             WINDOW 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)                                                       taskCount
    from app_task
    group 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) delayPercentChain
         from statistics
             WINDOW 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实战】使用存储过程和计算同比环比校验原理

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

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