Shulou(Shulou.com)06/01 Report--

This article mainly explains how to achieve mysql query the difference between hourly data and hourly data, the content is clear and clear, interested friends can learn about it, I believe everyone will be helpful after reading.

I. Foreword

The requirement is to get the difference and ratio between hourly data and last hour data for a certain time range. I thought it would be a very simple sql, but after thinking for two minutes, I found that it was not simple. I didn't find a reference scheme on the Internet, so I could only analyze it slowly.

At first, I didn't have any ideas, so I went to ask DBA students. DBA said he wouldn't, so I wrote php scripts to calculate it. This is a bit too much. I just want to check a data temporarily. I don't believe that I can't find it directly with sql. OK, let's try while walking.

Blogger here is a stupid way to achieve, if you guys have a simpler way, please do not begrudge, comment area waiting for you!

MySQL version:

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

Second, query the difference between each hour and the previous hour

1. Split demand

Here first separate query, see how much data are, convenient for subsequent combination.

(1) Get the amount of data per hour

Here for the convenience of display, directly merged, only show 01-12 when the data, not 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-19 00:00:00' and log_time ='2020-04-19 00:00:00' and log_time ='2020-04-19 00:00:00' and log_time $v){ foreach($arr1 as $k1=>$v1){ }}

In this case, can we find the same value of two circular arrays and then calculate the difference as we usually write programs? It is obvious that the dates here are identical and can be used as conditions for comparison.

Use case... when to calculate the difference

select (case when days = days1 then (numbers- nums1) else 0 end) as difffrom (select count(*) as numbers,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020 - 04 - 19 00:00 'and log_time $v){foreeach ($arr1 as $k1=>$v1){ if($k == $k1){ //find difference}}

There are a lot of zeroes, and there are some calculated results, but if you exclude these zeroes, it looks like there is a play.

4. Filter out the parts with 0 results and compare the final data.

Here we use having to filter the results of the query. The having clause allows us to filter groups of data after grouping. Although our sql does not have group by at the end, there are group by in both subqueries. In theory, it is most appropriate to filter data with having. Try it

select (case when days = days1 then (nums1 - nums) else 0 end) as difffrom (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-19 00:00:00' and log_time 0.1 and (nums1 - nums)/nums1

< 0.2 then 0.2when days = days1 and (nums1 - nums)/nums1 >

0.2 and (nums1 - nums)/nums1

< 0.3 then 0.3when days = days1 and (nums1 - nums)/nums1 >

0.3 and (nums1 - nums)/nums1

< 0.4 then 0.4when days = days1 and (nums1 - nums)/nums1 >

0.4 and (nums1 - nums)/nums1

< 0.5 then 0.5when days = days1 and (nums1 - nums)/nums1 >

0.5 then 0.6 else 0 end as diff,count(*) as diff_numsfrom (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-03-20 00:00:00' and log_time 0;

Results:

+------+-----------+

| diff | diff_nums |

+------+-----------+

| 0.1 | 360 |

| 0.2 | 10 |

| 0.3 | 1 |

| 0.4 | 1 |

+------+-----------+

III. SUMMARY

SQL is actually similar to program code, split requirements step by step combination, most of the requirements can be achieved. If he was cowardly from the beginning, he naturally could not write it.

However, complex calculations are generally not recommended to be written in SQL, and it will be faster to write with programs. The more complex SQL is, the lower the efficiency will be.

3, DBA students sometimes unreliable, or rely on themselves ah

MySQL database time and actual time difference of 8 hours

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

Database configuration followed by &serverTimezone=GMT%2B8

After reading the above content, is there a further understanding of how to achieve mysql query the difference between hourly data and hourly data? If you still want to learn more, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.