【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')
)
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实战】使用存储过程和计算同比环比校验原理

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/19395.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

vite跨域问题,你可能需要看这篇文章

最近在学习项目的时候&#xff0c;使用了vite工具进行构建&#xff0c;然后出现了跨域的问题&#xff0c;中间的曲折不过多叙述&#xff0c;直接进入正题。 前端成功启动后的界面&#xff1a; 然后在后端进行的Controller上使用了如下的配置 然后浏览器就会出现跨域的问题 为什…

【论文复现】基于区块链的分布式光伏就地消纳交易模式研究(Matlab代码实现)

&#x1f4a5;&#x1f4a5;&#x1f49e;&#x1f49e;欢迎来到本博客❤️❤️&#x1f4a5;&#x1f4a5; &#x1f3c6;博主优势&#xff1a;&#x1f31e;&#x1f31e;&#x1f31e;博客内容尽量做到思维缜密&#xff0c;逻辑清晰&#xff0c;为了方便读者。 ⛳️座右铭&a…

UE4及Airsim安装时遇到的问题及解决办法

UE4及Airsim安装时遇到的问题及解决办法 目录 UE4及Airsim安装时遇到的问题及解决办法前言UE4下载慢解决方法 Airsim编译过程中提示&#xff1a;无法打开包括文件: “Eigen/Dense”: No such file or directory [D:\software\Visual_studio2022\2022Community\AirSim\Air解决办…

别不信:这些细节关乎你的物联网设备的命运!

《高并发系统实战派》-- 值得拥有 一、设备接入层网络协议的意义 随着物联网的发展&#xff0c;越来越多的设备需要接入云平台进行远程监控和管理。设备接入层网络协议起到了承担设备接入网络的功能&#xff0c;为物联网平台提供了数据交互的基础。设备接入层网络协议对于物联…

【云原生概念和技术】1.2 云原生技术概括(上)

如果想了解或者学习云原生的友友们&#xff0c;欢迎订阅哦&#xff5e;&#x1f917;&#xff0c;目前一周三更&#xff0c;努力码字中&#x1f9d1;‍&#x1f4bb;…目前第一章是一些介绍和概念性的知识&#xff0c;可以先在脑海里有一个知识的轮廓&#xff0c;从第二章开始就…

AUTOSAR入门

简介 AUTOSAR&#xff08;AUTomotive Open System ARchitecture&#xff09;是一种汽车软件架构标准&#xff0c;由德国大陆、博世、宝马等汽车及零部件制造商共同发起&#xff0c;拥有广泛的行业参与。其目标是为了解决汽车电子和软件系统日益复杂的问题&#xff0c;提高可重…

打工人使用ChatGPT的一天!

众所周知&#xff0c;ChatGPT 自去年OpenAI 推出以来&#xff0c;这款 AI 聊天机器人可以说迅速成为了 AI 界的「当红炸子鸡」 作为一名资深的打工人&#x1f477;&#x1f3fb;‍♂️&#xff0c;我们应该怎样利用ChatGPT提高工作效率呢&#xff1f;今天给大家介绍下打工人使…

关于Kerberos认证的一些攻击手法学习总结

Kerberos认证流程 前言 本文主要分享最近学习的关于域内Kerberos认证的一些攻击手法&#xff0c;以自我的理解为主&#xff0c;从原理理解切入到基本工具利用来阐述&#xff0c;个人的理解分析较为啰嗦&#xff0c;嫌太兀长的可以跳着看就好&#xff0c;还请各位谅解。如有错误…

第三十二章 React路由组件的简单使用

1、NavLink的使用 一个特殊版本的 Link&#xff0c;当它与当前 URL 匹配时&#xff0c;为其渲染元素添加样式属性 <NavLink className"list-group-item" to"/home">Home</NavLink> <NavLink className"list-group-item" to&quo…

JVM运行时数据区

Java和C的区别&#xff0c;体现在自动内存分配和垃圾收集技术。 JVM在执行Java程序时&#xff0c;会将它管理的内存分为若干个不同的数据区域。 这些区域有各自的作用范围以及生命周期&#xff1a; 线程私有的区域&#xff0c;随着用户线程的启动和结束而建立和销毁。线程共…

2023网络安全十大顶级工具

从事网络安全工作&#xff0c;手上自然离不开一些重要的网络安全工具。今天&#xff0c;分享10大网络安全工具。 一、Kali Linux Kali 是一个基于 Debian 的 Linux 发行版。它的目标就是为了简单&#xff1a;在一个实用的工具包里尽可能多的包含渗透和审计工具。Kali 实现了这…

MySQL基础(二十二)逻辑架构

1.逻辑架构剖析 1.1 第1层&#xff1a;连接层 系统&#xff08;客户端&#xff09;访问MySQL服务器前&#xff0c;做的第一件事就是建立TCP连接。 经过三次握手建立连接成功后&#xff0c;MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。 用户名或密码不对&#…

Redis 五大基本数据类型常见命令

一、redis中的常见数据结构 Redis共有5种常见数据结构&#xff0c;分别字符串&#xff08;STRING)、列表&#xff08;LIST&#xff09;、集合&#xff08;SET)、散列&#xff08;HASH&#xff09;、有序集合&#xff08;ZSET)。 二、redis中五大基本数据类型介绍 字符串(Str…

尚硅谷JUC

文章目录 1. 什么是JUC1.1 JUC简介1.2 进程和线程基本概念2.1 Synchronized2.1.1 Synchronized关键字2.1.2 synchronized实现三个线程卖30张票 2.2 Lock2.2.1 什么是Lock2.2.2 使用Lock实现买票功能2.2.3 两者的区别 3. 线程间通信及定制化通信3.1 使用synchronized实现线程之间…

Hive语言

一、Hive的DDL语言&#xff08;数据库、数据表的增删改查操作) 二、Hive的DQL语言&#xff08;数据库查询语言&#xff09; 2.1Hive七子句 聚合函数&#xff1a;count()、sum()、max()、min()、avg()可以单独使用。(缩写&#xff1a;cs mm a) 2.1.1 分区查询与分区裁剪 SELEC…

女生学习软件测试怎么样?

在IT技术行业&#xff0c;女生学习还是有很大优势的。女生相较于男生更有耐心&#xff0c;包容性强&#xff0c;心思细腻&#xff0c;对细节把控更好&#xff0c;同时还能帮助团队男女平衡&#xff0c;活跃气氛。 编程是一个只要你肯学习就会有回报的行业&#xff0c;不论男生…

ResourceManager HA 原理

简介 为了解决 Yarn 中 ResourceManager 的单点故障问题&#xff0c;在 Hadoop 2.4 中新增了 ResourceManager HA 的能力&#xff0c; 该文章基于 Hadoop 3.1.1 进行讲解。 1.1. 名词定义 全称简称备注ResourceManagerRmZookeeperZK ResourceManager Ha 架构 ResourceMana…

前端工程化 搭建私有组件库 组件从开发到发布私有npm仓库的全过程

前言 基于Vue3.0 TS的组件从开发组件库到发布私有npm仓库的全过程 环境 这里列出本文所使用的环境版本 vue 3.0 vue/cli 4.5.9 nodeJs 14.15.1 npm 6.14.8 vue --version vue/cli 4.5.9 npm -v 6.14.8 node -v v14.15.1 步骤 创建项目 使用 vue-cli 创建一个 vue3 项目&a…

mysql的高级查询语句

1.本文前言 数据库是用来存储数据&#xff0c;更新&#xff0c;查询数据的工具&#xff0c;而查询数据是一个数据库最为核心的功能&#xff0c;数据库是用来承载信息&#xff0c;而信息是用来分析和查看的。所以掌握更为精细化的查询方式是很有必要的。本文将围绕数据的高级查…

C++类和对象(中)

目录 1.类的6个默认成员函数 2.构造函数 2.1构造函数的概念 2.2构造函数的重载 2.3默认构造函数 2.4总结 3.析构函数 3.1析构函数的概念 3.2编译器自动生成的析构函数会做那些事情呢&#xff1f; 3.3析构函数的析构顺序 4.拷贝构造函数&#xff08;复制构造函数&am…