CowNew open-source-use SQL to implement Discrete Data Analysis

Source: Internet
Author: User

I am going to add a performance monitoring report function to JDBMonitor. In "report condition", enter the start time, end time, and unit time for statistics, and click query. The program collects statistics from the table T_LOG_SQLLog recorded by DataBaseDBListener and displays the report. For example, you can enter "18:00:00" in the start time and "12:00:00" in the end time, select "5 minutes" in the time unit, and click "query ". The program filters out records with FbeginTime greater than or equal to "18:00:00" and less than or equal to "12:00:00" in T_LOG_SQLLog. Then, the number of records (that is, the number of database executions) is recorded every five minutes and displayed as a report in a time sequence diagram.
For example:
Fid ftime
1 2006-05-05 10:00:00. 000
2 2006-05-05 10:00:01. 000
3 2006-05-05 10:00:10. 000
4 2006-05-05 10:01:10. 000
5 2006-05-05 10:01:20. 000

Such data is discrete data, because the execution time of the database is uncertain. Data Mining is often required to count the number of occurrences of the database. Data Mining is usually implemented by different databases, and JDBMonitor is cross-database. Therefore, we certainly cannot use the unique features of these databases. There are also some open-source cross-Database Data Mining engines, but the size is huge and must be more than 50 MB. JDBMonitor is a small tool. To facilitate deployment and use, a basic principle of JDBMontior is to avoid using classes other than JDK as much as possible, so they must not be used. After careful consideration, I found that the following skills can be used to solve the problem:

Select count (sub. f) as exeCount, min (sub. FBeginTime) as FTime from (
Select cast (DateDiff (ss ,?, FBeginTime )/? As int) as f, FBeginTime from T_LOG_SQLLog
) As sub
Where 1 = 1
And FBeginTime >=?
And FBeginTime <=?
Group by sub. f
Order by sub. f ASC


The first and third parameters are the start time of the selected time period, the second parameter is the selected time interval (in seconds), and the fourth parameter is the end time of the selected time period.
I used the division technique to achieve this effect.
FBeginTime is the start time of SQL statement execution (Here we regard it as the execution time of SQL statements), cast (DateDiff (ss ,?, FBeginTime )/? As int) as f indicates the number of seconds between the execution time of SQL statements and the start time of the selected time period, the time interval selected by the user is then divisible (in seconds ). After division, the data in the same period of time is the same. Then we have a groupby and a count (sub. f), so that the number of SQL statements executed in each period of time comes out.
For example, if the input start time is 10:00:00. 000, the end time is 10:02:00. 000.
So select cast (DateDiff (ss ,?, FBeginTime )/? As int) as f, the result set of FBeginTime from T_LOG_SQLLog is:
F FBeginTime
0 2006-05-05 10:00:00. 000
0 2006-05-05 10:00:01. 000
0 2006-05-05 10:00:10. 000
1 2006-05-05 10:01:00. 000
1 2006-05-05 10:01:00. 000
Then, f is used as the grouping condition to summarize and count the count of f, so that the number of SQL statements executed in each time period comes out:
3 2006-05-05 10:00:00. 000
2 2006-05-05 10:01:00. 000

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.