赤天化股票

mysql查询每小时数据和上小时数据的差值实现思路详解

 更新时间:2020年04月24日 09:40:44   作者:铁柱同学   我要评论
这篇文章主要介绍了mysql查询每小时数据和上小时数据的差值,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下

一、前言

      需求是获取某个时间范围内每小时数据和上小时数据的差值以及比率。本来以为会是一个很简单的sql,结果思考两分钟发现并不简单,网上也没找到参考的方案,那就只能自己慢慢分析了。

      刚开始没思路,就去问DBA同学,结果DBA说他不会,让我写php脚本去计算,,这就有点过分了,我只是想临时查个数据,就不信直接用sql赤天化股票查不出来,行叭,咱们边走边试。

博主这里用的是笨方法实现的,各位大佬要是有更简单的方式,请不吝赐教,评论区等你!

mysql版本:

mysql> select version();
+---------------------+
| version()  |
+---------------------+
| 10.0.22-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

二、查询每个小时和上小时的差值

1、拆分需求

赤天化股票这里先分开查询下,看看数据都是多少,方便后续的组合。

赤天化股票(1)获取每小时的数据量

这里为了方便展示,直接合并了下,只显示01-12时的数据,并不是bug。。

select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;
+-------+---------------+
| nums | days  |
+-------+---------------+
| 15442 | 2020-04-19 01 |
| 15230 | 2020-04-19 02 |
| 14654 | 2020-04-19 03 |
| 14933 | 2020-04-19 04 |
| 14768 | 2020-04-19 05 |
| 15390 | 2020-04-19 06 |
| 15611 | 2020-04-19 07 |
| 15659 | 2020-04-19 08 |
| 15398 | 2020-04-19 09 |
| 15207 | 2020-04-19 10 |
| 14860 | 2020-04-19 11 |
| 15114 | 2020-04-19 12 |
+-------+---------------+

(2)获取上小时的数据量

select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;
+-------+---------------+
| nums1 | days  |
+-------+---------------+
| 15114 | 2020-04-19 01 |
| 15442 | 2020-04-19 02 |
| 15230 | 2020-04-19 03 |
| 14654 | 2020-04-19 04 |
| 14933 | 2020-04-19 05 |
| 14768 | 2020-04-19 06 |
| 15390 | 2020-04-19 07 |
| 15611 | 2020-04-19 08 |
| 15659 | 2020-04-19 09 |
| 15398 | 2020-04-19 10 |
| 15207 | 2020-04-19 11 |
| 14860 | 2020-04-19 12 |
+-------+---------------+

 

注意:

1)获取上小时数据用的是date_sub()函数,date_sub(日期,interval -1 hour)代表获取日期参数的上个小时,具体参考手册:
2)这里最外层嵌套了个date_format是为了保持格式和上面的一致,如果不加这个date_format的话,查询出来的日期格式是:2020-04-19 04:00:00的,不方便对比。

赤天化股票2、把这两份数据放到一起看看

select nums ,nums1,days,days1 
from 
(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

+-------+-------+---------------+---------------+
| nums | nums1 | days  | days1  |
+-------+-------+---------------+---------------+
| 15442 | 15114 | 2020-04-19 01 | 2020-04-19 01 |
| 15442 | 15442 | 2020-04-19 01 | 2020-04-19 02 |
| 15442 | 15230 | 2020-04-19 01 | 2020-04-19 03 |
| 15442 | 14654 | 2020-04-19 01 | 2020-04-19 04 |
| 15442 | 14933 | 2020-04-19 01 | 2020-04-19 05 |
| 15442 | 14768 | 2020-04-19 01 | 2020-04-19 06 |
| 15442 | 15390 | 2020-04-19 01 | 2020-04-19 07 |
| 15442 | 15611 | 2020-04-19 01 | 2020-04-19 08 |
| 15442 | 15659 | 2020-04-19 01 | 2020-04-19 09 |
| 15442 | 15398 | 2020-04-19 01 | 2020-04-19 10 |
| 15442 | 15207 | 2020-04-19 01 | 2020-04-19 11 |
| 15442 | 14860 | 2020-04-19 01 | 2020-04-19 12 |
| 15230 | 15114 | 2020-04-19 02 | 2020-04-19 01 |
| 15230 | 15442 | 2020-04-19 02 | 2020-04-19 02 |
| 15230 | 15230 | 2020-04-19 02 | 2020-04-19 03 |

      可以看到这样组合到一起是类似于程序中的嵌套循环效果,相当于nums是外层循环,nums1是内存循环。循环的时候先用nums的值,匹配所有nums1的值。类似于php程序中的:

foreach($arr as $k=>$v){
 foreach($arr1 as $k1=>$v1){

 }
}

赤天化股票      既然如此,那我们是否可以像平时写程序的那样,找到两个循环数组的相同值,然后进行求差值呢?很明显这里的日期是完全一致的,可以作为对比的条件。

3、使用case …when 计算差值

select (case when days = days1 then (nums - nums1) else 0 end) as diff
from 
(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

效果:
+------+
| diff |
+------+
| 328 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| -212 |
| 0 |
| 0 

      可以看到这里使用case..when实现了当两个日期相等的时候,就计算差值,近似于php程序的:

	foreach($arr as $k=>$v){
 foreach($arr1 as $k1=>$v1){
 if($k == $k1){
  //求差值
 }
 }
}

      结果看到有大量的0,也有一部分计算出的结果,不过如果排除掉这些0的话,看起来好像有戏的。

赤天化股票4、过滤掉结果为0 的部分,对比最终数据

      这里用having来对查询的结果进行过滤。having子句可以让我们筛选成组后的各组数据,虽然我们的sql在最后面没有进行group by,不过两个子查询里面都有group by了,理论上来讲用having来筛选数据是再合适不过了,试一试

select (case when days = days1 then (nums1 - nums) else 0 end) as diff
from 
(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n having diff <>0;

结果:
+------+
| diff |
+------+
| -328 |
| 212 |
| 576 |
| -279 |
| 165 |
| -622 |
| -221 |
| -48 |
| 261 |
| 191 |
| 347 |
| -254 |
+------+

赤天化股票      这里看到计算出了结果,那大概对比下吧,下面是手动列出来的部分数据:

赤天化股票当前小时和上个小时的差值: 当前小时  -上个小时

本小时 上个小时  差值
15442 15114  -328
15230 15442  212
14654 15230  576
14933 14654  -279
赤天化股票 14768 14933  165

      可以看到确实是成功获取到了差值。如果要获取差值的比率的话,直接case when days = days1 then (nums1 - nums)/nums1 else 0 end 即可。

5、获取本小时和上小时数据的降幅,并展示各个降幅范围的个数

      在原来的case..when的基础上引申一下,继续增加条件划分范围,并且最后再按照降幅范围进行group by求和即可。这个sql赤天化股票比较麻烦点,大家有需要的话可以按需修改下,实际测试是可以用的。

select case 
when days = days1 and (nums1 - nums)/nums1 < 0.1 then 0.1
when days = days1 and (nums1 - nums)/nums1 > 0.1 and (nums1 - nums)/nums1 < 0.2 then 0.2
when days = days1 and (nums1 - nums)/nums1 > 0.2 and (nums1 - nums)/nums1 < 0.3 then 0.3
when days = days1 and (nums1 - nums)/nums1 > 0.3 and (nums1 - nums)/nums1 < 0.4 then 0.4
when days = days1 and (nums1 - nums)/nums1 > 0.4 and (nums1 - nums)/nums1 < 0.5 then 0.5
when days = days1 and (nums1 - nums)/nums1 > 0.5 then 0.6
 else 0 end as diff,count(*) as diff_nums
from 
(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as m,
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n group by diff having diff >0;

结果:

+------+-----------+
| diff | diff_nums |
+------+-----------+
|  0.1 |       360 |
|  0.2 |        10 |
|  0.3 |         1 |
|  0.4 |         1 |
+------+-----------+

三、总结

1、 sql其实和程序代码差不多,拆分需求一步步组合,大部分需求都是可以实现的。一开始就怂了,那自然是写不出的。
2、 不过复杂的计算,一般是不建议用sql来写,用程序写会更快,sql越复杂,效率就会越低。
3、 DBA赤天化股票同学有时候也不靠谱,还是要靠自己啊

补充介绍:MySQL数据库时间和实际时间差8个小时

url=jdbc:mysql://127.0.0.1:3306/somedatabase?characterEncoding=utf-8&serverTimezone=GMT%2B8

数据库配置后面加上&serverTimezone=GMT%2B8

赤天化股票到此这篇关于mysql查询每小时数据和上小时数据的差值的文章就介绍到这了,更多相关mysql 每小时数据差值内容请搜索脚本之家以前的文章或继续浏览下面的赤天化股票相关的文章希望大家以后多多支持脚本之家!

您可能感兴趣的文章:

赤天化股票相关的文章

  • Mysql数据库从5.6.28版本升到8.0.11版本部署项目时遇到的问题及解决方法

    Mysql数据库从5.6.28版本升到8.0.11版本部署项目时遇到的问题及解

    这篇文章主要介绍了Mysql数据库从5.6.28版本升到8.0.11版本过程中遇到的问题及解决方法,解决办法有三种,每种方法给大家介绍的都很详细,感兴趣的朋友跟随脚本之家小编一起学习吧
    2018-05-05
  • MySQL建立唯一索引实现插入重复自动更新

    MySQL建立唯一索引实现插入重复自动更新

    这篇文章给大家分享的是当向数据插入数据时,判断字段是不是存在,存在执行更新操作,如果不存在就执行插入操作的方法,有需要的朋友们可以参考借鉴。
    2016-09-09
  • 清空mysql 查询缓存的可行方法

    清空mysql 查询缓存的可行方法

    mysql对同一条sql进行了缓存,在第二次运行时, 瞬间就完成了,若要清除缓存,可通过下面的方法来实现
    2014-07-07
  • mysql数据类型和字段属性原理与用法详解

    mysql数据类型和字段属性原理与用法详解

    这篇文章主要介绍了mysql数据类型和字段属性,结合实例形式分析了mysql数据类型和字段属性基本概念、原理、分类、用法及操作注意事项,需要的朋友可以参考下
    2020-04-04
  • 几个缩减MySQL以节省磁盘空间的建议

    几个缩减MySQL以节省磁盘空间的建议

    这篇文章主要介绍了几个缩减MySQL以节省磁盘空间的建议,主要从表结构和存储内容两个方面来谈减容,需要的朋友可以参考下
    2015-05-05
  • MySQL 5.7增强版Semisync Replication性能优化

    MySQL 5.7增强版Semisync Replication性能优化

    这篇文章主要介绍了MySQL 5.7增强版Semisync Replication性能优化,本文着重讲解支持发送binlog和接受ack的异步化、支持在事务commit前等待ACK两项内容,需要的朋友可以参考下
    2015-05-05
  • MySQL的语法及其使用指南

    MySQL的语法及其使用指南

    数据库的选取,创建,丢弃和变更 数据表和索引的创建,变更和丢弃从数据表检索信息
    2008-04-04
  • Linux下Mysql5.6 二进制安装过程

    Linux下Mysql5.6 二进制安装过程

    这篇文章主要介绍了Linux下Mysql5.6 二进制安装过程,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
    2018-06-06
  • Mysql中的count()与sum()区别详细介绍

    Mysql中的count()与sum()区别详细介绍

    本文将介绍Mysql中的count()与sum()区别,需要的朋友可以参考下
    2012-11-11
  • Windows8下mysql 5.6.15 安装配置方法图文教程

    Windows8下mysql 5.6.15 安装配置方法图文教程

    这篇文章主要为大家详细介绍了Windows8下mysql 5.6.15 安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
    2018-09-09

最新评论