A method, device, equipment and medium for predicting coal mine methane content based on SQL query

Abstract

This invention belongs to the field of coal mine safety monitoring technology, specifically relating to a method, apparatus, equipment, and medium for predicting coal mine methane content based on SQL queries. The method includes: acquiring data from monitoring points and storing it in a relational database; receiving prediction parameters input by a user; obtaining real-time data of the target monitoring point at the current moment and historical data from the past N hours through SQL queries in the relational database; calculating the deviation of methane monitoring values ​​and the wind speed ratio based on the acquired data; calculating the average value of methane monitoring values ​​for each hour in the past N hours from the historical data of the target monitoring point, generating a historical average result set; and calculating the predicted methane content for each hour within the prediction period based on the average value of methane monitoring values, the deviation of methane monitoring values, and the wind speed ratio. Combining real-time and historical data with methane monitoring values ​​and wind speed values ​​for predicting methane content at monitoring points improves the accuracy and reliability of the prediction.

Description

Background Art

[0002] The coal mine safety monitoring system arranges various sensor devices and video cameras at various measuring points (each measuring point has its own measuring point number) to obtain the concentration and distribution of various harmful substances such as oxygen, methane, carbon dioxide, carbon monoxide, dust, etc. in the underground air in real time. When a parameter is detected to exceed the preset threshold (for example, methane concentration exceeds the standard), the system will immediately trigger an alarm and notify relevant personnel to take measures.

[0003] Real-time monitoring of methane concentration at measuring points is a basic and key technical means in coal mine safety management. It can quickly capture the current state of methane concentration and provide instant information for emergency response. However, real-time monitoring alone cannot fully solve all safety issues related to methane concentration. In order to timely detect the potential rising trend of methane concentration and take measures before the concentration exceeds the standard, such as strengthening ventilation and adjusting the operation process to prevent methane accumulation from reaching a dangerous level, it is necessary to predict the methane concentration at the measuring point.

[0004] Existing prediction methods generally use deep learning algorithms to train models for prediction. Deep learning algorithms usually involve complex network structures and a large number of parameters, which means that the model training and prediction process requires a lot of computing resources and time, and the prediction threshold is relatively high. Summary of the invention

[0005] In view of the above problems existing in the existing prediction methods, the present invention provides a method, device, equipment and medium for predicting the methane content in coal mines based on SQL query.

[0006] In a first aspect, the technical solution of the present invention provides a method for predicting coal mine methane content based on SQL query, comprising:

[0007] The acquired measurement point data is stored in a relational database; the measurement point data includes real-time data, historical data, date and time; both the real-time data and historical data include methane monitoring values ​​and wind speed values;

[0008] Receive prediction parameters input by the user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N; N is an integer in hours;

[0009] The real-time data of the target measuring point at the current moment and the historical data of the past N hours are obtained by querying the relational database through SQL; wherein the measuring point corresponding to the measuring point number is the target measuring point;

[0010] Calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data;

[0011] Calculate the average value of methane monitoring value for each hour in the past N hours in the historical data of the target measuring point, and generate a historical average result set;

[0012] The predicted value of methane content in each hour within the prediction period is calculated based on the average value of methane monitoring values, the deviation of methane monitoring values ​​and the wind speed ratio.

[0013] As a further limitation of the technical solution of the present invention, the step of obtaining the measurement point data and storing it in the relational database includes:

[0014] Get the measurement point data;

[0015] The acquired measuring point data is stored in a relational database using a date range partition table; the partition table includes a measuring point history table for storing measuring point history data, a measuring point real-time table for storing measuring point real-time data, a date dimension table and a time dimension table.

[0016] As a further limitation of the technical solution of the present invention, the step of obtaining the real-time data of the target measuring point at the current moment and the historical data of the past N hours by querying the relational database through SQL includes:

[0017] Through SQL querying the real-time table of measuring points in the relational database, the methane monitoring value and wind speed value of the target measuring point at the current moment are obtained, and the measuring point status is normal;

[0018] The measurement point history table in the relational database is queried through SQL to obtain the methane monitoring value and wind speed value of the target measurement point when the measurement point status is normal in the past N hours.

[0019] As a further limitation of the technical solution of the present invention, the step of calculating the deviation of the methane monitoring value and the wind speed ratio based on the acquired data includes:

[0020] Based on the obtained methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal, the average value of the methane monitoring value and the average value of the wind speed value of the target measuring point in the past N hours are calculated; wherein the average value of the methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average value of the wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value;

[0021] Calculate the deviation diff between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, defined as the wind speed ratio rate; and generate a deviation ratio result set.

[0022] As a further limitation of the technical solution of the present invention, in the step of calculating the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio, the predicted value calculation formula is as follows:

[0023] predict=hisavg+diff*rate;

[0024] Among them, predict is the predicted value, and hisavg is the average value of methane monitoring values ​​every hour in the past N hours.

[0025] As a further limitation of the technical solution of the present invention, the method further includes:

[0026] Based on the forecast duration, date dimension, and time dimension, a time dimension result set for the next N hours is constructed with the current hour as the starting point;

[0027] Take the time dimension result set as the main table, associate the historical mean result set and the deviation ratio result set, and finally get the predicted result set through the predicted value calculation formula.

[0028] In a second aspect, the technical solution of the present invention also provides a coal mine methane content prediction device based on SQL query, including a data storage module, a parameter receiving module, a query acquisition module and a prediction calculation module;

[0029] A data storage module is used to obtain the measurement point data and store it in a relational database; the measurement point data includes real-time data, historical data, date and time; both the real-time data and historical data include methane monitoring values ​​and wind speed values;

[0030] A parameter receiving module is used to receive prediction parameters input by a user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N, where N is an integer number of hours;

[0031] A query acquisition module is used to query the relational database through SQL to obtain the real-time data of the target measuring point at the current moment and the historical data of the past N hours; wherein the measuring point corresponding to the measuring point number is the target measuring point;

[0032] The prediction calculation module is used to calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data; calculate the average value of the methane monitoring value of each hour in the past N hours in the historical data of the target measuring point to generate a historical mean result set; calculate the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio.

[0033] As a further limitation of the technical solution of the present invention, the data storage module includes a data acquisition unit and a storage execution unit;

[0034] A data acquisition unit, used for acquiring measurement point data;

[0035] The storage execution unit is used to store the acquired measuring point data in a relational database using a date range partition table; the partition table includes a measuring point history table for storing measuring point history data, a measuring point real-time table for storing measuring point real-time data, a date dimension table and a time dimension table.

[0036] As a further limitation of the technical solution of the present invention, the query acquisition module is used to obtain the methane monitoring value and wind speed value of the target measuring point at the current moment when the measuring point status is normal through SQL querying the measuring point real-time table in the relational database; and obtain the methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal through SQL querying the measuring point history table in the relational database.

[0037] As a further limitation of the technical solution of the present invention, the prediction and calculation module is specifically used to calculate the average value of the methane monitoring value and the average value of the wind speed value of the target measuring point in the past N hours based on the methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal; wherein the average value of the methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average value of the wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value; calculate the deviation between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, which is defined as the wind speed ratio; calculate the product of the deviation of the methane monitoring value and the wind speed ratio, sum the calculation result with the average value of the methane monitoring value to obtain the predicted value of the methane content in each hour within the prediction time, and generate a prediction table.

[0038] As a further limitation of the technical solution of the present invention, the device also includes a prediction result set generation module, which constructs a time dimension result set for the next N hours based on the prediction duration, date dimension, and time dimension, taking the hour of the current time as the starting point; taking the time dimension result set as the main table, the prediction table is associated to obtain the final prediction result set.

[0039] In a third aspect, the technical solution of the present invention also provides an electronic device, comprising: at least one processor; and a memory communicatively connected to the at least one processor; the memory stores computer program instructions executable by the at least one processor, and the computer program instructions are executed by the at least one processor so that the at least one processor can execute the coal mine methane content prediction method based on SQL query as described in the first aspect.

[0040] In a fourth aspect, the technical solution of the present invention provides a non-transitory computer-readable storage medium, wherein the non-transitory computer-readable storage medium stores computer instructions, and the computer instructions enable the computer to execute the coal mine methane content prediction method based on SQL query as described in the first aspect.

[0041] It can be seen from the above technical solutions that the present invention has the following advantages: by directly using SQL to query the relational database, the present invention can quickly obtain the real-time data and historical data of the required measuring points, significantly improving the data processing efficiency. Combining the methane monitoring values ​​and wind speed values ​​in the real-time data and historical data, by calculating the deviation of the methane monitoring value and the wind speed ratio, and further using the average value of the methane monitoring value in the past N hours, the present invention can more accurately predict the methane content of the target measuring point in each hour within the prediction time. This method comprehensively considers a variety of influencing factors and improves the accuracy and reliability of the prediction.

[0042] The user can input prediction parameters according to actual needs, including the measurement point number and the prediction duration N, which makes the prediction method of the present invention highly flexible and applicable. The prediction parameters can be easily adjusted for different users or different application scenarios to meet diverse needs. Since the present invention mainly relies on relational databases and SQL query technology, these technologies are already very mature and popular in the current field of information technology. Therefore, the implementation and maintenance costs of the present invention are relatively low, and it is easy to deploy and apply in various environments. The solution of the present invention helps to timely discover potential safety hazards and provide strong support for safe production in coal mines. This can not only reduce the occurrence of safety accidents, but also improve the production efficiency and economic benefits of coal mines. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0044] Figure 1 is a schematic flow chart of a method according to an embodiment of the present invention.

[0045] Figure 2 is a schematic block diagram of an apparatus according to an embodiment of the present invention. DETAILED DESCRIPTION

[0046] Although real-time monitoring can immediately detect excessive levels, it often only works after the problem has occurred. The methane concentration at the predicted measurement point can predict the changing trend of methane concentration in advance based on historical data and environmental factors (such as ventilation conditions). This foresight allows management to take preventive measures before problems occur, such as adjusting the ventilation system, restricting work activities or evacuating personnel, thereby effectively avoiding or mitigating potential safety risks.

[0047] Predicting methane concentration can help mine managers allocate safety resources more reasonably. For example, based on the prediction results, high-risk areas can be monitored and inspected first, rather than blindly allocating resources evenly in all areas. This can not only improve the efficiency of safety management, but also ensure that there are enough resources to deal with emergencies at critical moments. The prediction of methane concentration can also provide a scientific basis for the long-term planning and decision-making of mines. By analyzing the trend of methane concentration changes, potential safety hazard areas or weak links in the operation process can be identified, thereby guiding the mine to make targeted improvements and upgrades. This data-based decision-making method is more objective and accurate, and helps to improve the overall safety level of the mine. In order to enable people in the field of this technology to better understand the technical solutions in the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described in combination with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should belong to the scope of protection of the present invention.

[0048] like Figure 1 As shown, an embodiment of the present invention provides a coal mine methane content prediction method based on SQL query, comprising:

[0049] Step 1: Obtain the measurement point data and store it in a relational database; the measurement point data includes real-time data, historical data, date and time; both real-time data and historical data include methane monitoring values ​​and wind speed values;

[0050] Step 2: receiving prediction parameters input by the user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N, where N is an integer number of hours;

[0051] Step 3: query the relational database through SQL to obtain the current real-time data of the target measuring point and the historical data of the past N hours; wherein the measuring point corresponding to the measuring point number is the target measuring point;

[0052] Step 4: Calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data;

[0053] Step 5: Calculate the average value of methane monitoring value for each hour in the past N hours in the historical data of the target measuring point to generate a historical average result set;

[0054] Step 6: Calculate the predicted value of methane content in each hour within the prediction time according to the average value of methane monitoring value, the deviation of methane monitoring value and wind speed ratio.

[0055] In some embodiments, the step of acquiring the measurement point data and storing it in a relational database includes:

[0056] Step 11: Get the measurement point data;

[0057] Step 12: Use a date range partition table in a relational database to store the acquired measuring point data; the partition table includes a measuring point history table for storing measuring point history data, a measuring point real-time table for storing measuring point real-time data, a date dimension table, and a time dimension table.

[0058] By using a date range partition table, you can significantly reduce the amount of data that needs to be scanned for queries and improve query speed. When you need to query data within a specific time period, you only need to access the corresponding partition without scanning the entire table. Partitioned tables make data management more flexible and efficient. Partitions can be added or deleted as needed to adapt to the growth or decrease in data volume. Different storage strategies, such as compression, backup and recovery, can also be implemented for different partitions to optimize the use of storage resources. Partitioned tables help simplify data maintenance tasks, such as data archiving, deletion, and migration. Old data can be migrated from active partitions to archive partitions regularly to free up storage space and maintain database performance.

[0059] In some embodiments, the step of obtaining the current real-time data of the target measuring point and the historical data of the past N hours by querying the relational database through SQL includes:

[0060] Through SQL querying the real-time table of measuring points in the relational database, the methane monitoring value and wind speed value of the target measuring point at the current moment are obtained, and the measuring point status is normal;

[0061] The measurement point history table in the relational database is queried through SQL to obtain the methane monitoring value and wind speed value of the target measurement point when the measurement point status is normal in the past N hours.

[0062] In some embodiments, the step of calculating the deviation of the methane monitoring value and the wind speed ratio based on the acquired data includes:

[0063] Based on the obtained methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal, the average value of the methane monitoring value and the average value of the wind speed value of the target measuring point in the past N hours are calculated; wherein the average value of the methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average value of the wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value;

[0064] Calculate the deviation diff between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, defined as the wind speed ratio rate; and generate a deviation ratio result set.

[0065] Obtain the methane monitoring value and wind speed value of the target measuring point in the past N hours (for example, N=24, i.e., the past day) from the methane monitoring system and wind speed monitoring system. Ensure that the timestamps of these data correspond accurately for subsequent calculations. Check the measuring point status mark at each time point and only retain the measuring point data with normal status.

[0066] By calculating the deviation diff, the difference between the current methane concentration and the historical average level can be monitored in real time. When the deviation exceeds a certain threshold, an early warning mechanism can be triggered to remind relevant personnel to take safety measures.

[0067] The calculation of the wind speed ratio rate helps to evaluate the impact of current wind speed on methane diffusion. High wind speed may promote the rapid diffusion of methane, while low wind speed may cause methane to accumulate locally. This is of great significance for understanding and predicting changes in methane concentration.

[0068] The automated calculation process reduces the need for manual intervention, improves monitoring efficiency, and ensures data accuracy and timeliness. Through real-time monitoring and data analysis, potential environmental risks can be discovered in a timely manner, providing strong support for taking preventive measures and reducing environmental pollution and accident risks.

[0069] Accordingly, in the step of calculating the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio, the predicted value calculation formula is as follows:

[0070] predict=hisavg+diff*rate;

[0071] Among them, predict is the predicted value, and hisavg is the average value of methane monitoring values ​​every hour in the past N hours.

[0072] In some embodiments, the method further comprises:

[0073] Based on the forecast duration, date dimension, and time dimension, a time dimension result set for the next N hours is constructed with the current hour as the starting point;

[0074] Take the time dimension result set as the main table, associate the historical mean result set and the deviation ratio result set, and finally get the predicted result set through the predicted value calculation formula.

[0075] Specifically, through sensor networks or other data collection equipment, key data such as methane concentration and wind speed at each measuring point in the coal mine are collected in real time. The data may include real-time data (such as the methane concentration at the current moment) and historical data (records over a period of time in the past). The collected data is cleaned to remove abnormal values, duplicate values, or missing values. The data is formatted into a form suitable for storage in a relational database, such as JSON, CSV, or XML, but it usually needs to be converted into a table structure of a relational database.

[0076] Design the database architecture, including the measurement point history table (as shown in Table 1), the measurement point real-time table (as shown in Table 2), the date dimension table (as shown in Table 3) and the time dimension table (as shown in Table 4).

[0077] Table 1: Measurement point history table

[0078]

[0079] The measuring point history table is used to store past data records, including fields such as measuring point number, methane concentration, wind speed, date and time.

[0080] Table 2: Real-time table of measurement points

[0081]

[0082] The measurement point real-time table is used to store data at the current moment. It has a similar structure but may contain fewer fields to optimize query performance.

[0083] Table 3: Date dimension table

[0084]

[0085] Table 4: Time dimension table

[0086]

[0087] The date dimension table and time dimension table are used to provide detailed information about the date and time, such as year, month, week, hour, etc., to facilitate subsequent data analysis and query.

[0088] A relational database is used to store the historical data of the prediction measuring points and the real-time data of the measuring points. The data source of the measuring points generally comes from the safety monitoring system and gas extraction system of the mine. The historical data of the measuring points and the real-time data of the measuring points are stored in two tables, and the historical table of the measuring points is partitioned by time.

[0089] Specifically, a date range partition table is used to store historical data of measuring points, and the data items include: data time (timestamp type), measuring point number (string type), measuring point monitoring value (floating point type), measuring point wind speed (floating point type), and measuring point status (string type); real-time test data, the data items include: data time (timestamp type), measuring point number (string type), measuring point monitoring value (floating point type), measuring point wind speed (floating point type), and measuring point status (string type); the date dimension table data items include: date (date type), and the time dimension table data items include: time (string type).

[0090] The input parameter is the prediction point number, string type, ${pCode}, and the input parameter is the prediction duration, integer type, ${pHours}.

[0091] The prediction result is a result set as shown in Table 5. The data items include: data time (timestamp type), measurement point number (string type), and methane prediction value (floating point type).

[0092] Table 5: Result set table

[0093]

[0094] The specific prediction process is as follows:

[0095] S401: query the real-time table of the measuring point through SQL to obtain the methane monitoring value and wind speed value of the current measuring point state of the specified measuring point parameters, and the result set is current;

[0096] S402: query the measuring point history table through SQL to obtain the average methane monitoring value avg(ch4) and the average wind speed avg(wind) of the specified measuring point historical data in the past ${pHours} hours when the status was normal;

[0097] S403: Calculate the deviation between the methane monitoring value of the measuring point at the current moment and the historical average value diff=current(ch4)-avg(ch4), and the ratio of the historical average wind speed to the current moment wind speed rate=current(wind) / avg(wind);

[0098] S404: Obtain the average value hisavg of methane data for each hour in the past ${pHours} hours of historical data of the specified measuring point;

[0099] S405: Based on the prediction duration parameter, date dimension, and time dimension, a time dimension result set dim of the future ${pHours} hours is constructed with the current hour as the starting point;

[0100] S406: Take the time dimension result set dim as the main table, associate the historical average result set hisavg, and associate the deviation result set diff to obtain the final prediction result predict=hisavg+diff*rate.

[0101] It should be noted that the prediction duration set in this application needs to predict the predicted value for each hour within the prediction duration.

[0102] The prediction method relies on a relational database and can run under Windows / Linux / Unix operating systems. It can realize real-time prediction of coal mine methane content, which changes according to the current time, and is of great significance for mine safety production. This application is a prediction algorithm for a specified measuring point. The application can use the prediction algorithm concurrently in a multi-threaded manner to improve the prediction efficiency. It can be widely used in the prediction of methane content in coal mines and metal mines, and can also be used for the prediction of other monitoring gases, providing support and guarantee for the intelligent and digital construction of mines.

[0103] like Figure 2 As shown, an embodiment of the present invention also provides a coal mine methane content prediction device based on SQL query, including a data storage module, a parameter receiving module, a query acquisition module and a prediction calculation module;

[0104] A data storage module is used to obtain the measurement point data and store it in a relational database; the measurement point data includes real-time data, historical data, date and time; both the real-time data and historical data include methane monitoring values ​​and wind speed values;

[0105] A parameter receiving module is used to receive prediction parameters input by a user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N, where N is an integer number of hours;

[0106] A query acquisition module is used to query the relational database through SQL to obtain the real-time data of the target measuring point at the current moment and the historical data of the past N hours; wherein the measuring point corresponding to the measuring point number is the target measuring point;

[0107] The prediction calculation module is used to calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data; calculate the average value of the methane monitoring value for each hour in the past N hours in the historical data of the target measuring point; and calculate the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio.

[0108] In some embodiments, the data storage module includes a data acquisition unit and a storage execution unit;

[0109] A data acquisition unit, used for acquiring measurement point data;

[0110] The storage execution unit is used to store the acquired measuring point data in a relational database using a date range partition table; the partition table includes a measuring point history table for storing measuring point history data, a measuring point real-time table for storing measuring point real-time data, a date dimension table and a time dimension table.

[0111] In some embodiments, the query acquisition module is used to obtain the methane monitoring value and wind speed value of the target measuring point at the current moment when the measuring point status is normal by querying the measuring point real-time table in the relational database through SQL; and obtain the methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal by querying the measuring point history table in the relational database through SQL.

[0112] In some embodiments, the prediction calculation module is specifically used to calculate the average methane monitoring value and the average wind speed value of the target measuring point in the past N hours based on the methane monitoring value and wind speed value obtained when the measuring point status is normal at the target measuring point in the past N hours; wherein the average methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value; calculate the deviation diff between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, which is defined as the wind speed ratio rate; and generate a deviation ratio result set.

[0113] In some embodiments, the device also includes a prediction result set generation module, which constructs a time dimension result set for the next N hours based on the prediction duration, date dimension, and time dimension, taking the hour of the current time as the starting point; taking the time dimension result set as the main table, the prediction table is associated to obtain the final prediction result set.

[0114] An embodiment of the present invention further provides an electronic device, comprising: a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus. The communication bus can be used for information transmission between the electronic device and the sensor. The processor can call the logic instructions in the memory to execute the following method: Step 1: Obtain the measurement point data stored in the relational database; the measurement point data includes real-time data, historical data, date and time; both the real-time data and the historical data include methane monitoring values ​​and wind speed values; Step 2: Receive the prediction parameters input by the user, and the prediction parameters include the measurement point number to be predicted and the prediction duration N; N is an integer hour; Step 3: Use SQL to query the relational database to obtain the real-time data of the target measurement point at the current moment and the historical data of the past N hours; wherein the measurement point corresponding to the measurement point number is the target measurement point; Step 4: Calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data; Step 5: Calculate the average value of the methane monitoring value of each hour in the past N hours in the historical data of the target measurement point, and generate a historical mean result set; Step 6: Calculate the predicted value of the methane content in each hour within the prediction duration based on the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio.

[0115] In addition, the logic instructions in the above-mentioned memory can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program code.

[0116] An embodiment of the present invention provides a non-transitory computer-readable storage medium, which stores computer instructions, and the computer instructions enable a computer to execute the method provided by the above method embodiment, for example, including: step 1: obtaining measuring point data and storing it in a relational database; the measuring point data includes real-time data, historical data, date and time; both the real-time data and the historical data include methane monitoring values ​​and wind speed values; step 2: receiving prediction parameters input by a user, wherein the prediction parameters include the measuring point number to be predicted and the prediction duration N; N is an integer number of hours; step 3: obtaining the real-time data of the target measuring point at the current moment and the historical data of the past N hours through SQL query of the relational database; wherein the measuring point corresponding to the measuring point number is the target measuring point; step 4: calculating the deviation and wind speed ratio of the methane monitoring value based on the acquired data; step 5: calculating the average value of the methane monitoring value of each hour in the past N hours in the historical data of the target measuring point, and generating a historical mean result set; step 6: calculating the predicted value of the methane content of each hour in the prediction duration according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio.

[0117] It should be understood that the order of execution of the steps in the above embodiment does not necessarily mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention.

[0118] An embodiment of a coal mine methane content prediction device based on SQL query provided in an embodiment of the present invention belongs to the same inventive concept as the coal mine methane content prediction method based on SQL query in the above-mentioned embodiments. For details not described in detail in the embodiment of the coal mine methane content prediction device based on SQL query, reference can be made to the embodiment of the coal mine methane content prediction method based on SQL query.

[0119] The coal mine methane content prediction device based on SQL query is a unit and algorithm step of each example described in combination with the embodiments disclosed in this article, which can be implemented by electronic hardware, computer software or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to the function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0120] Those skilled in the art will appreciate that various aspects of the coal mine methane content prediction device based on SQL query can be implemented as a system, method or program product. Therefore, various aspects of the present disclosure can be specifically implemented in the following forms, namely: a complete hardware implementation, a complete software implementation (including firmware, microcode, etc.), or a combination of hardware and software, which can be collectively referred to as "circuit", "module" or "system".

[0121] Although the present invention has been described in detail by referring to the accompanying drawings and in combination with the preferred embodiments, the present invention is not limited thereto. Without departing from the spirit and essence of the present invention, a person skilled in the art may make various equivalent modifications or substitutions to the embodiments of the present invention, and these modifications or substitutions shall be within the scope of the present invention. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed by the present invention, and they shall be within the scope of protection of the present invention.

Claims

1. A coal mine methane content prediction method based on SQL query, characterized in that: include: The acquired measurement point data is stored in a relational database; the measurement point data includes real-time data, historical data, date and time; Both real-time and historical data include methane monitoring values ​​and wind speed values; Receive prediction parameters input by the user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N; N is an integer in hours; The real-time data of the target measuring point at the current moment and the historical data of the past N hours are obtained by querying the relational database through SQL; wherein the measuring point corresponding to the measuring point number is the target measuring point; Calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data; Calculate the average value of methane monitoring value for each hour in the past N hours in the historical data of the target measuring point, and generate a historical average result set; Calculate the predicted value of methane content in each hour within the prediction time according to the average value of methane monitoring value, the deviation of methane monitoring value and the wind speed ratio; The steps of querying the relational database through SQL to obtain the current real-time data of the target measuring point and the historical data of the past N hours include: Through SQL query of the real-time table of measuring points in the relational database, the methane monitoring value and wind speed value of the target measuring point at the current moment are obtained, and the measuring point status is normal; Use SQL to query the measurement point history table in the relational database to obtain the methane monitoring value and wind speed value of the target measurement point in the past N hours when the measurement point status is normal; The steps of calculating the deviation of the methane monitoring value and the wind speed ratio based on the acquired data include: Based on the obtained methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal, the average value of the methane monitoring value and the average value of the wind speed value of the target measuring point in the past N hours are calculated; wherein the average value of the methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average value of the wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value; Calculate the deviation diff between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, which is defined as the wind speed ratio rate; and generate a deviation ratio result set; In the step of calculating the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio, the predicted value calculation formula is as follows: predict=hisavg+diff*rate Among them, predict is the predicted value, and hisavg is the average value of methane monitoring values ​​every hour in the past N hours.

2. The method for predicting coal mine methane content based on SQL query according to claim 1, characterized in that: The steps to obtain the measurement point data and store it in a relational database include: Get the measurement point data; The acquired measuring point data is stored in a relational database using a date range partition table; the partition table includes a measuring point history table for storing measuring point history data, a measuring point real-time table for storing measuring point real-time data, a date dimension table and a time dimension table.

3. The method for predicting coal mine methane content based on SQL query according to claim 2 is characterized in that: The method further includes: Based on the forecast duration, date dimension, and time dimension, a time dimension result set for the next N hours is constructed with the current hour as the starting point; Take the time dimension result set as the main table, associate the historical mean result set and the deviation ratio result set, and finally get the predicted result set through the predicted value calculation formula.

4. A coal mine methane content prediction device based on SQL query for implementing the method according to any one of claims 1 to 3, characterized in that: It includes a data storage module, a parameter receiving module, a query acquisition module and a prediction calculation module; A data storage module is used to obtain the measurement point data and store it in a relational database; the measurement point data includes real-time data, historical data, date and time; both the real-time data and historical data include methane monitoring values ​​and wind speed values; A parameter receiving module is used to receive prediction parameters input by a user, wherein the prediction parameters include the number of the measurement point to be predicted and the prediction duration N, where N is an integer number of hours; A query acquisition module is used to query the relational database through SQL to obtain the real-time data of the target measuring point at the current moment and the historical data of the past N hours; wherein the measuring point corresponding to the measuring point number is the target measuring point; The prediction calculation module is used to calculate the deviation of the methane monitoring value and the wind speed ratio based on the acquired data; calculate the average value of the methane monitoring value of each hour in the past N hours in the historical data of the target measuring point, and generate a historical mean result set; calculate the predicted value of the methane content in each hour within the prediction time according to the average value of the methane monitoring value, the deviation of the methane monitoring value and the wind speed ratio; The prediction calculation module is specifically used to calculate the average methane monitoring value and the average wind speed value of the target measuring point in the past N hours based on the methane monitoring value and wind speed value of the target measuring point in the past N hours when the measuring point status is normal; wherein the average methane monitoring value of the target measuring point in the past N hours is defined as the historical methane average value, and the average wind speed value of the target measuring point in the past N hours is defined as the historical wind speed average value; calculate the deviation diff between the methane monitoring value of the target measuring point at the current moment and the historical methane average value; at the same time, calculate the ratio of the historical wind speed average value to the wind speed value at the current moment, which is defined as the wind speed ratio rate; and generate a deviation ratio result set.

5. An electronic device, characterized in that: The electronic device includes: at least one processor; and a memory communicatively connected to the at least one processor; the memory stores computer program instructions that can be executed by the at least one processor, and the computer program instructions are executed by the at least one processor so that the at least one processor can execute the coal mine methane content prediction method based on SQL query as described in any one of claims 1 to 3.

6. A non-transitory computer-readable storage medium, characterized in that: The non-transitory computer-readable storage medium stores computer instructions, and the computer instructions enable the computer to execute the coal mine methane content prediction method based on SQL query as described in any one of claims 1 to 3.

Images