A data query method, device, apparatus and storage medium

Abstract

The application discloses a data query method, device and equipment and a storage medium, and comprises the following steps: when it is identified that a specified element is included in a query statement SQL, a PartitionTopN operator is added at a specified position of an initial plan to obtain a latest plan; original data obtained by executing the latest plan is filtered by using the PartitionTopN operator to obtain effective data; and a query result of a database is determined according to the effective data obtained after filtering. The PartitionTopN operator is added in the initial plan corresponding to the initial query statement SQL, so that invalid data is filtered in advance when the latest plan is executed, the amount of data that needs to be processed by a window operator is greatly reduced, the use of memory and the occupation of CPU resources are reduced, the initial query statement is not changed in the optimization process, the results before and after optimization are consistent, and the efficiency of data query and the overall performance of the system are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a data query method, device, equipment and storage medium. Background Art

[0002] With the continuous deepening of database applications, a TopN query mode combining window functions such as RANK(), ROW_NUMBER(), etc., PARTITION BY partitioning, ORDER BY sorting, and LIMIT or WHERE rk < N conditions is widely adopted in more and more business scenarios. In scenarios such as user behavior analysis, leaderboard construction, and recommendation systems, such queries have become a common data processing paradigm.

[0003] However, when the traditional query engine processes the above query mode, the traditional execution engine usually sorts all the data in each partition. Even if only the first N records need to be retained, it still processes all the data, resulting in a high calculation cost. Moreover, a large number of intermediate results are generated during the sorting process, occupying a large amount of memory and CPU resources, which not only affects the overall system performance but also reduces the data query efficiency. Summary of the Invention

[0004] The present invention provides a data query method to achieve accurate and efficient data query while saving costs.

[0005] According to the first aspect of the present invention, a data query method is provided, including: obtaining an initial database query statement SQL and an initial plan corresponding to the initial query statement SQL;

[0006] When it is recognized that the query statement SQL includes specified elements, a PartitionTopN operator is added at a specified position in the initial plan to obtain a latest plan, where the specified elements include a window function and a filtering condition associated with the result of the window function;

[0007] The raw data obtained by executing the latest plan is filtered by using the PartitionTopN operator to obtain valid data;

[0008] The query result of the database is determined according to the valid data obtained after filtering.

[0009] According to another aspect of the present invention, a data query device is provided, including: an initial plan obtaining module, configured to obtain an initial database query statement SQL and an initial plan corresponding to the initial query statement SQL;

[0010] The latest plan acquisition module is used to add a PartitionTopN operator at a specified position in the initial plan to acquire the latest plan when the query statement SQL is found to include a specified element. The specified element includes a window function and a filtering condition associated with the result of the window function.

[0011] The effective data acquisition module is used to filter the raw data obtained from executing the latest plan using the PartitionTopN operator to obtain effective data;

[0012] The query result determination module is used to determine the query result of the database based on the valid data obtained after filtering.

[0013] According to another aspect of the present invention, a mobile device is provided, the mobile device comprising:

[0014] At least one processor; and

[0015] A memory communicatively connected to the at least one processor; wherein,

[0016] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the method described in any embodiment of the present invention.

[0017] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the method described in any embodiment of the present invention.

[0018] According to another aspect of the present invention, a computer program product is provided, comprising a computer program that, when executed by a processor, implements the method described in any embodiment of the present invention.

[0019] The beneficial technical effect of this invention is that by adding the PartitionTopN operator to the initial plan corresponding to the initial query statement SQL, invalid data is filtered in advance when executing the latest plan, which greatly reduces the amount of data that the window operator needs to process, thereby reducing memory usage and CPU resource consumption. The initial query statement is not changed during the optimization process, ensuring that the results before and after optimization are consistent, thus improving the efficiency of data query and the overall performance of the system.

[0020] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 This is a flowchart of a data query method provided according to Embodiment 1 of the present invention;

[0023] Figure 2 This is a flowchart of a data query method provided according to Embodiment 2 of the present invention;

[0024] Figure 3 This is a schematic diagram of the structure of a data query device according to Embodiment 3 of the present invention;

[0025] Figure 4 This is a schematic diagram of the structure of the mobile device provided in Embodiment 4 of the present invention. Detailed Implementation

[0026] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0027] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or devices. Additionally, the collected information is information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure, and application of related data all comply with the relevant laws, regulations, and standards of the relevant countries and regions, necessary confidentiality measures have been taken, public order and good morals have not been violated, and corresponding operation interfaces are provided for users to choose to authorize or refuse.

[0028] Example 1

[0029] Figure 1 This is a flowchart illustrating a data query method provided in Embodiment 1 of the present invention. This embodiment is applicable to querying databases. The method can be executed by a data query device, which can be implemented in hardware and / or software. Figure 1 As shown, the method includes:

[0030] Step S101: Obtain the initial query statement SQL from the database and the initial plan corresponding to the initial query statement SQL.

[0031] Optionally, obtaining the initial query statement SQL and the corresponding initial plan from the database includes: reading all elements of the initial query statement SQL; creating plan units corresponding to each element, wherein the plan unit includes a table read operation unit, a window unit, a filter unit, and a display unit; and assembling the table read operation unit, window unit, filter unit, and display unit in sequence to obtain the initial plan.

[0032] Specifically, in this implementation, after obtaining the initial query statement SQL, the elements in the initial query statement SQL are read. For example, the initial query statement SQL is as follows:

[0033] SELECT b,c,rk FROM (

[0034] SELECT b,c,RANK()OVER(PARTITION BY b ORDER BY c)AS rk FROM pt_low_card)f

[0035] WHERE rk<3;

[0036] In this implementation, "RANK()OVER(PARTITION BY b ORDER BY c)" is a window function that partitions the data by field b and sorts it by field c; "WHERE rk<3" is a filter condition that selects the first three elements in each sequence; and "FROM pt_low_card" is a table read operation that reads data from the pt_low_card table. This implementation is merely illustrative and does not limit the specific content or elements contained in the initial query statement SQL. Furthermore, this implementation will write corresponding logic code for the meaning of each element and obtain the corresponding plan unit based on the written logic code. The plan unit obtained in this implementation may specifically include a table read operation unit, a window unit, a filter unit, and a display unit. For example, the logic code corresponding to the table read operation unit determined for the above read operation is as follows:

[0037] ParalleScan(1):

[0038] -Table:qtree_normal.pt_low_card

[0039] -Filters; "Of course, this embodiment only uses the table reading operation unit as an example for illustration. The writing method of other planning units can be created according to the processing logic of the corresponding elements. This embodiment does not limit them.

[0040] Step S102: When it is detected that the query statement SQL includes the specified element, add the PartitionTopN operator at the specified position of the initial plan to obtain the latest plan.

[0041] Optionally, the middle position between the table read operation unit and the window unit can be used as the specified position; the PartitionTopN operator can be added at the specified position to obtain the latest plan, wherein the execution mode of the PartitionTopN operator includes Normal mode, Rank TopN mode and Direct TopN mode.

[0042] Specifically, in this embodiment, after obtaining the initial query statement SQL, the initial query statement SQL is identified, and it is determined whether window functions, such as RANK() and ROW_NUMBER(), exist, as well as the filtering conditions associated with the window function results. For example, by identifying that the initial query statement SQL includes the window function "RANK() OVER (PARTITION BY b ORDER BY c)" and the filtering condition associated with the window function results is "WHERE rk<3", the PartitionTopN operator is introduced into the initial plan. The PartitionTopN operator includes partitioning and TopN sorting. Specifically, the PartitionTopN operator is added between the table read operation unit and the window unit in the initial plan. Therefore, after the data is filtered and valid data is obtained through the PartitionTopN operator, the valid data is then transmitted to the window unit for subsequent processing.

[0043] It is worth noting that the execution modes of the PartitionTopN operator in this embodiment include Normal mode, Rank TopN mode, and Direct TopN mode. Normal mode partitions the input data and uses the TopNSort algorithm to perform TopN sorting within each partition. Rank TopN mode refers to the use of special rules for TopN sorting when the window function is Rank, as there may be ties in the sorting. Direct TopN mode refers to the use of a direct TopN sorting algorithm when the Partition by column is empty or consists entirely of constant columns, meaning all data belongs to the same partition, and no partitioning is required. Therefore, the specific execution mode of the PartitionTopN operator in this embodiment is related to the window function. When the window function contains partitioning elements, the corresponding PartitionTopN operator execution mode needs to perform partitioning operations; when the window function does not contain partitioning elements, the corresponding PartitionTopN operator execution mode does not need to perform partitioning operations. This embodiment does not limit the specific content of the execution mode.

[0044] Step S103: Use the PartitionTopN operator to filter the raw data obtained from executing the latest plan to obtain valid data.

[0045] Optionally, the PartitionTopN operator is used to filter the raw data obtained from executing the latest plan to obtain valid data, including: dividing the raw data into multiple first-class partitions using the PartitionTopN operator with the same partitioning rules as window functions; sorting the raw data in each first-class partition using heap sort to obtain first-class data sequences; filtering a specified number of data from each first-class data sequence according to the filtering conditions; and taking all the data filtered from each first-class data sequence as valid data.

[0046] Specifically, in this embodiment, when executing the latest plan, it is performed in the following order: table read operation unit, PartitionTopN operator, window unit, filtering unit, and display unit. The table read operation unit extracts raw data from the specified table in the database. After extracting the raw data, the PartitionTopN operator is used to partition the data using the same partitioning rules as the window function. For example, partitioning is performed according to field b. Multiple first-type partitions are obtained through partitioning, such as partition A1 and partition A2. The types of field b in partition A1 and partition A2 are different. When field b is a business type, partition A1 includes 100 loan data and partition A2 includes 80 deposit data. Of course, this embodiment is only an example and does not limit the specific number of first-type partitions. In addition, in this embodiment, heap sort can be used to sort the data in each first-class partition to obtain the first-class data sequence. Heap sort is a comparison sorting algorithm based on a binary heap data structure. It is an efficient sorting algorithm suitable for sorting large amounts of data. It does not require recursion, thus avoiding the additional overhead caused by recursion. For example, the original data in partition A1 is sorted in descending order according to field c to obtain the first-class data sequence X1, and the original data in partition A2 is sorted to obtain the second-class data sequence X2. After sorting, a specified number of data are selected from each first-class data sequence according to the filtering conditions. For example, when the filtering condition is to obtain the first three in each sequence, the data selected from the first-class data sequence X1 are loan data 1, loan data 2, and loan data 3, and the data selected from the second-class data sequence X2 are deposit data 1, deposit data 2, and deposit data 3. The six data obtained above are then transmitted to the window unit as valid data.

[0047] It should be noted that since the execution modes of the PartitionTopN operator include Normal mode, Rank TopN mode, and Direct TopN mode, the PartitionTopN operator in this embodiment will dynamically select the corresponding execution mode according to the content of the window function. For example, when the Rank TopN mode is used, since there may be ties when the PartitionTopN operator sorts, a special rule will be adopted for TopN sorting. For example, when the data contained in the first type of data sequence is as follows: values: [1,1,1,1,2,3], since the partition data contains the same data, the sorting is as follows: rank: [1,1,1,1,5,6]. When taking the first two values ​​in the sequence, the result of rank top2 is [1,1,1,1]. Of course, this embodiment only uses the Rank TopN mode as an example for illustration and does not limit the specific content of the PartitionTopN operator.

[0048] Step S104: Determine the database query results based on the valid data obtained after filtering.

[0049] Optionally, the database query results are determined based on the valid data obtained after filtering, including: dividing the valid data into multiple second-class partitions by window units, wherein the partitioning rules of the second-class partitions are the same as those of the first-class partitions; sorting the valid data in each second-class partition using a full permutation method to obtain a second-class data sequence; extracting the valid data in each second-class data sequence in order, and using the extracted data as the database query results.

[0050] Specifically, because the valid data obtained by the PartitionTopN operator is no longer transmitted to the window unit in the sorted order, the data order is shuffled when the valid data arrives at the window unit. The window unit then divides the obtained valid data again according to the partitioning rules contained in the window function to obtain a second type of partition. For example, when partitioning, it is still based on field b, and the two obtained second type partitions are partition A1' and partition A2'. Since the valid data is greatly reduced compared to the original data, the computational resources consumed in data partitioning are significantly reduced. Partition A1' contains only loan data 1, loan data 3, and loan data 2, and partition A2' contains only deposit data. Based on data 3, 2, and 1, the data in each partition is not currently arranged in order. When sorting the data in each second-type partition in descending order according to field c, a full sort is used. A full sort involves arranging all elements in the dataset according to specific rules, such as numerical size and lexicographical order. Compared to heap sort, full sort can be implemented using various algorithms, such as quicksort, mergesort, and Timsort, while heap sort is a fixed implementation and cannot dynamically adjust its strategy based on data characteristics. Full sort, on the other hand, allows for the selection of stable operators, preserving the original relative order. For example, when sorting by student grades, full sort can maintain the original entry order. Furthermore, by introducing the PartitionTopN operator, the preliminary data filtering significantly reduces the computational resources required when using full sort to sort the valid data in the second-type partition within the window unit. For example, the valid data in partition A1' is sorted in descending order according to field c to obtain the second type of data sequence X1', and the valid data in partition A2' is sorted to obtain the second type of data sequence X2'. The filtering unit filters the data in each second type of data sequence according to the filtering conditions to extract a specified number of data. For example, if the filtering condition is to get the first three in the sequence, then the data extracted from the second type of data sequence X1' in order are deposit data 1, deposit data 2, and deposit data 3, and the data extracted from the second type of data sequence X2' are deposit data 1, deposit data 2, and deposit data 3. The above six extracted data are then displayed as query results through the display unit.

[0051] It should be noted that in this embodiment, after data filtering by the PartitionTopN operator, only subsequent valid data is retained. Therefore, the window unit and the filtering unit only need to perform partitioning and sorting on the valid data, which greatly reduces the amount of data that the window operator needs to process. This is particularly effective in scenarios with a large amount of input data and a low cardinality of partition columns. Since the sorting data is reduced, the intermediate results generated during the sorting process are also reduced, reducing the computational overhead of memory and CPU resources and improving the overall throughput of the system. Furthermore, the optimization process does not change the query semantics, ensuring that the results before and after optimization are completely consistent. It supports multiple window functions and filtering modes, is suitable for general relational database systems, and has good application prospects.

[0052] In this embodiment, by adding the PartitionTopN operator to the initial plan corresponding to the initial query statement SQL, invalid data is filtered out in advance when executing the latest plan, which greatly reduces the amount of data that the window operator needs to process, thereby reducing memory usage and CPU resource consumption. The initial query statement is not changed during the optimization process, ensuring that the results before and after optimization are consistent, thus improving the efficiency of data query and the overall performance of the system.

[0053] Example 2

[0054] Figure 2 This is a flowchart of a data query method provided in Embodiment 2 of the present invention. Based on the above embodiments, after determining the query results of the database according to the valid data obtained after filtering, this embodiment further includes: detecting the query results and generating a detection report; and issuing an alarm when the detection report contains abnormal information.

[0055] Step S201: Obtain the initial query statement SQL from the database and the initial plan corresponding to the initial query statement SQL.

[0056] Optionally, obtaining the initial query statement SQL and the corresponding initial plan from the database includes: reading all elements of the initial query statement SQL; creating plan units corresponding to each element, wherein the plan unit includes a table read operation unit, a window unit, a filter unit, and a display unit; and assembling the table read operation unit, window unit, filter unit, and display unit in sequence to obtain the initial plan.

[0057] Step S202: When it is recognized that the query statement SQL includes the specified element, add the PartitionTopN operator at the specified position of the initial plan to obtain the latest plan.

[0058] Optionally, the middle position between the table read operation unit and the window unit can be used as the specified position; the PartitionTopN operator can be added at the specified position to obtain the latest plan, wherein the execution mode of the PartitionTopN operator includes Normal mode, Rank TopN mode and Direct TopN mode.

[0059] Step S203: Use the PartitionTopN operator to filter the raw data obtained from executing the latest plan to obtain valid data.

[0060] Optionally, the PartitionTopN operator is used to filter the raw data obtained from executing the latest plan to obtain valid data, including: dividing the raw data into multiple first-class partitions using the PartitionTopN operator with the same partitioning rules as window functions; sorting the raw data in each first-class partition using heap sort to obtain first-class data sequences; filtering a specified number of data from each first-class data sequence according to the filtering conditions; and taking all the data filtered from each first-class data sequence as valid data.

[0061] Step S204: Determine the database query results based on the valid data obtained after filtering.

[0062] Optionally, the database query results are determined based on the valid data obtained after filtering, including: dividing the valid data into multiple second-class partitions by window units, wherein the partitioning rules of the second-class partitions are the same as those of the first-class partitions; sorting the valid data in each second-class partition using a full permutation method to obtain a second-class data sequence; extracting the valid data in each second-class data sequence in order, and using the extracted data as the database query results.

[0063] Step S205: Detect the query results and generate a detection report. If the detection report contains abnormal information, an alarm will be triggered.

[0064] Specifically, in this implementation, after obtaining the query results, the query results will be checked. The check will specifically detect whether there is obviously erroneous data in the query results and check the matching of the query results with the initial query statement SQL. If obviously erroneous data appears in the query results, such as garbled characters, or the query results do not match the initial query statement SQL, and it is necessary to sort and filter field c, but the query results are sorted and filtered according to field d, then the query results are determined to be abnormal.

[0065] In this embodiment, a detection report is generated based on the query results. The detection report includes the detection time, the detection object, and the detection results, including whether the detection was successful or abnormal. The detection report is then displayed visually on the user interface. When the detection report includes abnormal information, an alarm prompt is generated and displayed in the form of voice or image, so that users can promptly obtain the alarm prompt and perform corresponding maintenance on the relevant hardware or software based on the alarm prompt. Of course, this embodiment is only an example and does not limit the specific display method of the alarm prompt. As long as it can serve as a prompt to the user, it is within the protection scope of this application.

[0066] In this embodiment, by adding the PartitionTopN operator to the initial plan corresponding to the initial query statement SQL, invalid data is filtered out in advance when executing the latest plan, which greatly reduces the amount of data that the window operator needs to process, thereby reducing memory usage and CPU resource consumption. The initial query statement is not changed during the optimization process, ensuring that the results before and after optimization are consistent, thus improving the efficiency of data query and the overall performance of the system.

[0067] Example 3

[0068] Figure 3 This is a schematic diagram of a data query device provided in Embodiment 3 of the present invention. Figure 3 As shown, the device includes: an initial plan acquisition module 310, a latest plan acquisition module 320, a valid data acquisition module 330, and a query result determination module 340.

[0069] The initial plan acquisition module 310 is used to acquire the initial query statement SQL in the database and the initial plan corresponding to the initial query statement SQL.

[0070] The latest plan acquisition module 320 is used to add a PartitionTopN operator at a specified position in the initial plan to obtain the latest plan when the query statement SQL is found to include specified elements. The specified elements include window functions and filtering conditions associated with the results of window functions.

[0071] The effective data acquisition module 330 is used to filter the raw data obtained from the execution of the latest plan using the PartitionTopN operator to obtain effective data;

[0072] The query result determination module 340 is used to determine the database query results based on the valid data obtained after filtering.

[0073] Optionally, the initial plan acquisition module 310 is used to read all elements of the initial query statement SQL;

[0074] Create plan units corresponding to each element, where plan units include table read operation units, window units, filter units, and display units;

[0075] Assemble the table reading operation unit, window unit, filtering unit, and display unit in sequence to obtain the initial plan.

[0076] Optionally, the latest plan acquisition module is used to use the middle position between the table read operation unit and the window unit as the specified position;

[0077] Add the PartitionTopN operator at the specified location to obtain the latest plan. The execution modes of the PartitionTopN operator include Normal mode, Rank TopN mode and Direct TopN mode.

[0078] Optional, an effective data acquisition module is used to divide the original data using the PartitionTopN operator with the same partitioning rules as window functions to obtain multiple first-class partitions;

[0079] The original data in each first-class partition is sorted using heap sort to obtain the first-class data sequence;

[0080] Filter a specified number of data points from each first-class data sequence according to the filtering criteria;

[0081] All data selected from each first-class data sequence will be considered as valid data.

[0082] Optionally, the query result determination module is used to divide the valid data into multiple second-type partitions through window cells, wherein the partitioning rules of the second-type partitions are the same as those of the first-type partitions;

[0083] The valid data in each second-class partition is sorted using a full permutation method to obtain the second-class data sequence;

[0084] The valid data in each second-class data sequence is extracted in sequence, and the extracted data is used as the query result of the database.

[0085] Optionally, the device also includes an alarm notification module for detecting the query results and generating a detection report;

[0086] An alarm will be triggered when the test report contains abnormal information.

[0087] The service referral device provided in this embodiment of the invention can execute the data query method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0088] Example 4

[0089] Figure 4 A schematic diagram of a mobile device 10, which can be used to implement embodiments of the present invention, is shown. The term "mobile device" is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. A mobile device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0090] like Figure 4 As shown, the mobile device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 and a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer programs stored in the ROM 12 or loaded into the RAM 13 from storage unit 18. The RAM 13 can also store various programs and data required for the operation of the mobile device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0091] Multiple components in the mobile device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, optical disk, etc.; and a communication unit 19, such as a network card, modem, wireless transceiver, etc. The communication unit 19 allows the mobile device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0092] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as data querying methods.

[0093] In some embodiments, the data query method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the mobile device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the data query method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the data query method by any other suitable means (e.g., by means of firmware).

[0094] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0095] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0096] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0097] To provide interaction with a user, the systems and techniques described herein can be implemented on a mobile device having: a display device for displaying information to the user (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the mobile device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or haptic feedback); and input from the user can be received in any form (including sound input, voice input, or haptic input).

[0098] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0099] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0100] Example 5

[0101] This invention also provides a computer program product, including a computer program that, when executed by a processor, implements the data query method provided in any embodiment of this application.

[0102] In implementing the computer program product, computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof. Programming languages ​​include object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0103] It should be noted that in the embodiments of this application, certain software, components, models and other existing solutions in the industry may be mentioned. These should be regarded as exemplary and are only intended to illustrate the feasibility of implementing the technical solution of this application. However, it does not mean that the applicant has used or necessarily used the solution.

[0104] Note that the above description is merely a preferred embodiment of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments, and may include many other equivalent embodiments without departing from the concept of the present invention, the scope of which is determined by the scope of the appended claims.

Claims

1. A data query method, characterized in that, The methods include: Obtain the initial query statement (SQL) from the database and the initial plan corresponding to the initial query statement (SQL); When it is detected that the initial query statement SQL includes a specified element, a PartitionTopN operator is added at a specified position in the initial plan to obtain the latest plan. The specified element includes a window function and a filtering condition associated with the window function result. The PartitionTopN operator is used to filter the raw data obtained from executing the latest plan to obtain valid data; The query results of the database are determined based on the valid data obtained after filtering. The step of obtaining the initial query statement SQL and the initial plan corresponding to the initial query statement SQL includes: reading all elements of the initial query statement SQL; Create planning units corresponding to each element, wherein the planning unit includes a table reading operation unit, a window unit, a filtering unit, and a display unit; The initial plan is obtained by assembling the table reading unit, the window unit, the filtering unit, and the display unit in sequence. The process of filtering the raw data obtained from executing the latest plan using the PartitionTopN operator to obtain valid data includes: The PartitionTopN operator uses the same partitioning rules as the window function to divide the original data into multiple first-class partitions; The original data in each of the first type of partitions is sorted using heap sort to obtain the first type of data sequence; Filter a specified number of data from each of the first type of data sequences according to the filtering conditions; All data selected from each of the first type of data sequences will be considered as the valid data.

2. The method according to claim 1, characterized in that, The step of adding the PartitionTopN operator at a specified position in the initial plan to obtain the latest plan includes: The midpoint between the table reading operation unit and the window unit is taken as the designated position; Add the PartitionTopN operator at the specified location to obtain the latest plan.

3. The method according to claim 1, characterized in that, Determining the database query results based on the filtered and obtained valid data includes: The effective data is divided into multiple second-type partitions by the window unit, wherein the partitioning rules of the second-type partitions are the same as those of the first-type partitions; The valid data in each of the second-type partitions is sorted using a full permutation method to obtain the second-type data sequence; The valid data in each of the second type of data sequences are extracted in sequence, and the extracted data is used as the query result of the database.

4. The method according to any one of claims 1 to 3, characterized in that, After determining the database query results based on the filtered valid data, the method further includes: The query results are then analyzed to generate a test report. An alarm will be triggered when the test report contains abnormal information.

5. A data query device, characterized in that, The device includes: The initial plan acquisition module is used to acquire the initial query statement SQL from the database and the initial plan corresponding to the initial query statement SQL; The latest plan acquisition module is used to add a PartitionTopN operator at a specified position in the initial plan to acquire the latest plan when the initial query statement SQL is found to include a specified element. The specified element includes a window function and a filtering condition associated with the result of the window function. The effective data acquisition module is used to filter the raw data obtained from executing the latest plan using the PartitionTopN operator to obtain effective data; The query result determination module is used to determine the query result of the database based on the valid data obtained after filtering. The initial plan acquisition module is used to read all elements of the initial query statement SQL; Create planning units corresponding to each element, wherein the planning unit includes a table reading operation unit, a window unit, a filtering unit, and a display unit; The initial plan is obtained by assembling the table reading unit, the window unit, the filtering unit, and the display unit in sequence. The effective data acquisition module is further configured to divide the original data using the PartitionTopN operator with the same partitioning rules as the window function to obtain multiple first-class partitions; The original data in each of the first type of partitions is sorted using heap sort to obtain the first type of data sequence; Filter a specified number of data from each of the first type of data sequences according to the filtering conditions; All data selected from each of the first type of data sequences will be considered as the valid data.

6. A terminal device, characterized in that, The terminal device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-4.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the method of any one of claims 1-4.

8. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method of any one of claims 1-4.

Images