Database statement query method and device, equipment, medium and product

Abstract

The invention discloses a database statement query method and device, equipment, a medium and a product, and the method comprises the steps: optimizing an obtained original query statement to obtain a target query statement; in response to a target query statement containing the top keyword, grouping information of the target query statement is determined, and the grouping information comprises a top numerical value and a grouping attribute corresponding to the top keyword; the grouping attributes are used for guiding grouping of innermost-layer complete grouping statements in the target query statements; and executing the target query statement based on the top value and the grouping attribute to obtain a query result. And guiding the innermost-layer complete grouped statements in the target query statements through the grouping information, performing grouping in the grouped data range in the top numerical value, reducing the grouped data range involved when other statements in the target query statements are executed, and obtaining a query result. According to the method, the sensing of the innermost layer grouping item on the top condition is realized, the innermost layer data set is reduced, the calculation times corresponding to the subsequent query are further reduced, and the performance is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database statement query method, device, equipment, medium and product. Background Art

[0002] Grouped query is a common query in databases. In the database SQL (Structured Query Language) statement, the GROUP BY clause can be used to specify that data is grouped by a specified column. For example, there is the following complex query statement: SELECT TOP 3MAX(D3)FROM(SELECT C1 D1,FUNC(C2)D2,C3D3 FROM T1)GROUP BY D1,D2. When extracting records from the FROM item, a subquery of the derived table needs to be executed for each record of the T1 table, and the FUNC() function needs to be calculated for each record of the T1 table. If the T1 table has a large amount of data, the query efficiency will be low.

[0003] The existing method can optimize the query statement by grouping in advance, and obtain SELECT TOP 3MAX(E3)FROM(SELECT D1 E1,FUNC(D2)E2,D3 E3 FROM(SELECT C1D1,C2 D2,MAX(C3)D3 FROMT1 GROUP BY C1,C2))GROUP BY E1,E2; after optimization, it can be seen that the innermost layer will be grouped by (C1,C2), reducing the intermediate result set, so the number of FUNC(D2) rows that need to be calculated will be reduced, and the performance will be improved. That is, assuming that T1 has 10,000 rows of data, the original statement needs to execute the FUNC function 10,000 times. However, grouping in advance can reduce the intermediate result set to 1,000 rows, so only 1,000 FUNC functions need to be executed, and the performance is improved.

[0004] However, there is still a problem with this approach, that is, the top condition TOP 3 limits the final goal to only output the first three groups of data. Although the innermost layer reduces the intermediate result set by grouping in advance, all the rows in the intermediate result set do not use the condition that only the first three groups are needed in the outermost layer. That is, the grouping items in the innermost layer cannot perceive the top condition of the outermost layer. FUNC(D2) of all rows still needs to be calculated, and the outermost layer can calculate the MAX(E3) value according to the (E1, E2) grouping and output the first three groups of data. If the TOP 3 condition is placed in the innermost layer, the overall semantics will change, resulting in abnormal final results. Summary of the invention

[0005] The present invention provides a database statement query method, device, equipment, medium and product to optimize query statements and improve query performance.

[0006] According to a first aspect of the present invention, there is provided a database statement query method, comprising:

[0007] Optimize the obtained original query statement to obtain the target query statement;

[0008] In response to a target query statement containing a top keyword, determining grouping information of the target query statement, wherein the grouping information includes a top value corresponding to the top keyword and a grouping attribute; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement;

[0009] The target query statement is executed based on the top value and the grouping attribute to obtain a query result, and the grouping information is used to guide the grouping of the innermost complete grouping statement in the target query statement.

[0010] According to a second aspect of the present invention, there is provided a database statement query device, comprising:

[0011] A statement determination module is used to optimize the obtained original query statement to obtain a target query statement;

[0012] An information determination module, configured to determine grouping information of a target query statement in response to a target query statement containing a top keyword, wherein the grouping information includes a top value and a grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement;

[0013] A result determination module is used to execute the target query statement based on the top value and the grouping attribute to obtain a query result, and the grouping information is used to guide the grouping of the innermost complete grouping statement in the target query statement.

[0014] According to a third aspect of the present invention, there is provided an electronic device, the electronic device comprising:

[0015] at least one processor; and

[0016] a memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the database statement query method described in any embodiment of the present invention.

[0018] According to a fourth aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the database statement query method described in any embodiment of the present invention when executed.

[0019] According to a fifth aspect of the present invention, an embodiment of the present invention further provides a computer program product, the computer program product comprising a computer program, and when the computer program is executed by a processor, the database statement query method of any embodiment of the present invention is implemented.

[0020] The technical solution of the embodiment of the present invention optimizes the original query statement obtained to obtain the target query statement; in response to the target query statement containing the top keyword, the grouping information of the target query statement is determined, and the grouping information includes the top value and grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement; the target query statement is executed based on the top value and the grouping attribute to obtain the query result. The innermost complete grouping statement in the target query statement is guided by the grouping information, and the grouping data range within the top value is grouped, so as to reduce the grouping data range involved in executing other statements in the target query statement and obtain the query result. The innermost grouping item is aware of the top condition, which reduces the innermost data set, thereby reducing the number of calculations corresponding to subsequent queries and improving performance.

[0021] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0023] Figure 1 is a flowchart of a database statement query method provided according to Embodiment 1 of the present invention;

[0024] Figure 2 is a flowchart of a database statement query method provided according to Embodiment 2 of the present invention;

[0025] Figure 3 is a schematic diagram of the structure of a database statement query device provided according to Embodiment 3 of the present invention;

[0026] Figure 4It is a schematic diagram of the structure of an electronic device implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0027] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction 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 the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0028] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0029] Embodiment 1

[0030] Figure 1 A flowchart of a database statement query method is provided for the first embodiment of the present invention. This embodiment is applicable to the optimized query of query statements containing top keywords and grouping clauses. The method can be executed by a database statement query device. The database statement query device can be implemented in the form of hardware and / or software. The database statement query device can be configured in an electronic device. Figure 1 As shown, the method includes:

[0031] S110: Optimize the obtained original query statement to obtain a target query statement.

[0032] In this embodiment, the original query statement can be understood as an SQL statement containing a from item that is a derived table and a group query clause (ie, a GROUP BY clause, and the columns specified by the group by clause include columns of the derived table).

[0033] Specifically, the processor may obtain the original query statement input by the user.

[0034] In this embodiment, the target query statement can be understood as a statement after the query optimization step.

[0035] Specifically, the processor may optimize the original query statement based on a preset optimization method to obtain a target query statement.

[0036] Exemplarily, the optimization method may be: a) process all query items of the derived table in the original query statement, first determine whether it is an outer layer grouping item, then determine whether it is a parameter of the set function of the outer layer query item, and finally, only those that are not referenced by the outer layer can be directly removed. b) Construct the query items of the inner query: the outer layer grouping items collect the columns of the corresponding derived table; the outer layer set function parameters construct a new set function according to the set function type; if not referenced, no processing is required. c) Construct the rest of the inner query: the FROM item can directly use the FROM item of the derived table in the original statement, and splice it with the query item constructed in step b) to obtain a complete inner query. d) Modify the FROM item of the derived table in the original statement to the inner query constructed in the previous step. e) Modify the query items of the derived table in the original statement, the outer layer grouping items do not need to be processed; the outer layer set function parameters are removed to retain the name; if not referenced, the item is directly removed to construct the middle layer query of the optimized statement. f) The middle-level query obtained in the previous step is used as the FROM item of the outer-level query, and the rest of the outer-level query directly uses the relevant part of the outer-level query of the original statement.

[0037] For example, a specific example is used to illustrate that the original query statement is SELECT TOP 3MAX(D3)FROM(SELECT C1 D1,FUNC(C2)D2,C3 D3 FROM T1)GROUP BY D1,D2. After the above steps are optimized, the target query statement can be obtained: SELECT TOP 3MAX(E3)FROM(SELECT D1 E1,FUNC(D2)E2,D3 E3 FROM(SELECT C1 D1,C2 D2,MAX(C3)D3 FROM T1 GROUP BY C1,C2))GROUP BY E1,E2.

[0038] S120. In response to a target query statement containing a top keyword, determine grouping information of the target query statement, wherein the grouping information includes a top value corresponding to the top keyword and a grouping attribute; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement.

[0039] In this embodiment, the top keyword can be understood as a condition for filtering the top data, i.e., the TOP keyword. The grouping information can be understood as a grouping for guiding the innermost complete grouping statement in the target query statement. The top value can be understood as a filtering value corresponding to the top keyword. The grouping attribute can be understood as an item value for guiding the grouping of the innermost complete grouping statement in the target query statement. The leading grouping item refers to a column that is preferentially used for grouping when grouping.

[0040] Specifically, the processor can first determine whether the outermost layer of the target query statement contains the top keyword (i.e., TOP). In response to the target query statement containing the top keyword, the processor can first determine the top value corresponding to the top keyword, and then determine the query item that is only a column (i.e., a query item that does not contain a calculation function) in the innermost grouping item corresponding to the outer grouping item in the target query statement, determine the grouping attribute, and use the query item as the leading grouping item, and use the grouping attribute, the leading grouping item and the top value as the grouping information for reference in subsequent grouping operations.

[0041] S130: Execute the target query statement based on the top value and the grouping attribute to obtain the query result.

[0042] In this embodiment, the query result can be understood as the result corresponding to the query statement.

[0043] Specifically, the processor can extract data from the table corresponding to the target query statement in batches, first group each batch of data according to the grouping attribute, and then determine the data range from the group according to the top value to reduce the data size involved in the grouping calculation. Then, the complete data corresponding to other items in the target query statement is matched with the data range, and the corresponding data is obtained and used for subsequent calculations to obtain the query results.

[0044] The technical solution of the embodiment of the present invention optimizes the original query statement obtained to obtain the target query statement; in response to the target query statement containing the top keyword, the grouping information of the target query statement is determined, and the grouping information includes the top value and grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement; the target query statement is executed based on the top value and the grouping attribute to obtain the query result. The innermost complete grouping statement in the target query statement is guided by the grouping information, and the grouping data range within the top value is grouped, so as to reduce the grouping data range involved in executing other statements in the target query statement and obtain the query result. The innermost grouping item is aware of the top condition, which reduces the innermost data set, thereby reducing the number of calculations corresponding to subsequent queries and improving performance.

[0045] Embodiment 2

[0046] Figure 2 This is a flowchart of a database statement query method provided by Embodiment 2 of the present invention. This embodiment is a further refinement of the above embodiment. Figure 2 As shown, the method includes:

[0047] S201: Optimize the acquired original query statement to obtain a target query statement.

[0048] S202: In response to a target query statement containing a top keyword, determine a top value corresponding to the top keyword.

[0049] Specifically, the processor may respond to a target query statement including a top keyword and a top value corresponding to the top keyword in the original query statement.

[0050] For example, the above target query statement is used: SELECT TOP 3MAX(E3)FROM(SELECT D1 E1,FUNC(D2)E2,D3 E3 FROM(SELECT C1 D1,C2 D2,MAX(C3)D3 FROM T1 GROUP BY C1,C2))GROUP BY E1,E2. TOP is the top keyword, and 3 is the top value.

[0051] S203: Traverse the outermost grouping items of the target query statement to determine the innermost query item corresponding to the single grouping condition in the grouping item.

[0052] In this embodiment, the outermost grouping item can be understood as the item used for grouping in the outermost layer, that is, the item corresponding to the outermost GROUP BY. The single-item grouping condition can be understood as a condition used for grouping. The innermost query item can be understood as the innermost query item corresponding to the single-item grouping condition.

[0053] Specifically, the processor may traverse the outermost grouping items of the original query statement, determine each individual grouping condition in the grouping items, and then determine the innermost query item corresponding to each individual grouping condition in the derived table.

[0054] For example, in GROUP BY E1,E2, E1 and E2 correspond to single grouping conditions, and the corresponding innermost query item is D1 E1, FUNC (D2) E2.

[0055] S204: Determine the grouping attribute and the leading grouping item according to the innermost query item.

[0056] Specifically, the processor can analyze the innermost query item, determine the query item corresponding to the single grouping condition, and then classify the query item according to the innermost query item expression, mark it according to different types, and determine the number of marks to obtain the grouping attribute. The marked innermost query item is the leading grouping item.

[0057] Further, based on the above embodiment, the step of determining the grouping attribute and the leading grouping item according to the innermost query item can be refined as follows:

[0058] If the innermost query item meets the preset column condition, the innermost query item is marked with the first marking symbol and used as the leading grouping item; otherwise, the innermost query item is marked with the second marking symbol; when traversing to the innermost layer, the number of first marking symbols in the innermost query item in the innermost complete grouping statement is counted to obtain the first item number; the total item number is determined based on the number of second marking symbols and the first item number in all innermost query items in the innermost complete grouping statement; the first item number and the total item number are used as the grouping attributes of the innermost query item.

[0059] In this embodiment, the preset column condition can be understood as a query item that only includes simple columns. The first marking symbol can be understood as a query item that satisfies the preset column condition. The second marking symbol can be understood as a query item that does not satisfy the preset column condition, which is different from the first marking symbol. The first number of items can be understood as the number of query items that indicate that the innermost query item in the innermost layer satisfies the preset column condition. The total number of items can be understood as the number of all innermost query items in the innermost layer.

[0060] Specifically, the processor may compare each innermost query item with a preset column condition, mark the innermost query item that meets the preset column condition with a first marking symbol, and use it as a leading grouping item; otherwise, the processor may mark the innermost query item with a second marking symbol. The processor may count the number of first marking symbols in the innermost query item in the innermost layer to obtain the first number of items, wherein before determining the number of first marking symbols in the innermost layer, the processor will place the items with the first marking symbol in a continuous and front position, so the items with the first marking symbol are expected to be continuous. Determine the total number of items based on the number of second marking symbols and the first number of items; use the first number of items and the total number of items as the grouping attributes of the innermost query item.

[0061] Exemplarily, the innermost query item corresponding to D1 is C1, and the innermost query item corresponding to D2 is FUNC(C2). The first marking symbol can be represented by symbol A, and the item with symbol A is the leading grouping item. C1 is marked by A, and C2 is marked by B. The number of first items is 1, and the total number of items is 2.

[0062] S205: Use the top value, grouping attribute and leading grouping item as the grouping information of the target query statement.

[0063] Specifically, the processor may use the top value, the grouping attribute, and the leading grouping item as the grouping information of the target query statement.

[0064] S206: When the first number of items in the grouping attribute is not zero, determine the sorting method of the target table data in the target query statement, all inner grouping items in the innermost complete grouping statement, and the data in the target table data.

[0065] In this embodiment, the target table data can be understood as the table to be queried. The innermost complete grouping statement can be understood as the innermost statement for grouping. The inner grouping items can be understood as the items used for grouping in the grouping statement. The sorting method can be understood as the sorting method of the data in the target table data when reading the data, which can include an ordered or unordered arrangement.

[0066] Specifically, when the first number of items in the grouping attribute is not zero, the processor can determine the target table data in the target query statement, all inner grouping items in the innermost complete grouping statement, and the sorting method of the data in the table, where the sorting method can be determined based on the use of the lower-level index and the matching of the index key and the grouping item.

[0067] For example, following the above example, after optimization, the target query statement can be obtained: SELECT TOP 3 MAX(E3) FROM(SELECT D1 E1, FUNC(D2) E2, D3 E3 FROM(SELECT C1D1, C2 D2, MAX(C3) D3 FROMT1 GROUP BY C1, C2)) GROUP BY E1, E2. The target table data is the data in the T1 table.

[0068] S207: Determine a target grouping item and other target grouping items from each inner grouping item according to the first item number and the total item number in the grouping attribute.

[0069] In this embodiment, the target grouping item can be understood as an item that meets the preset column condition. Other target grouping items can be understood as all grouping items corresponding to the single grouping condition.

[0070] Specifically, the processor may determine the target grouping items for first grouping from the innermost grouping items based on the first number of items, and then determine the other target grouping items as a whole from each inner grouping item according to the total number of items.

[0071] Exemplarily, the first item number is 1, and the first innermost grouping item determined is the target grouping item, so the target grouping item is C1. The total number of items is 2, and the first two inner grouping items are C1 and C2, and the corresponding other target grouping items are (C1, C2).

[0072] S208. If the sorting method is ordered sorting, the target table data is filtered and processed by group according to the first item number, each target grouping item, the top value and other target grouping items to determine the grouping data.

[0073] In this embodiment, the grouped data may be understood as data obtained after grouping.

[0074] Specifically, when the sorting method is ordered sorting, the processor can filter the data in the target table data according to each target grouping item. When the number of groups reaches the first number, data is selected from the grouped data according to the top value, and the data is filtered from other grouping data corresponding to other target grouping items according to the data to obtain grouped data.

[0075] Further, based on the above embodiment, the target table data can be grouped according to the first item number, each target grouping item, the top value and other target grouping items, and the step of determining the grouped data can be refined as follows:

[0076] The current batch of first data in the target table data is grouped according to each target grouping item to obtain the current first intermediate data, and the current first grouping number is determined; the current first intermediate data is processed according to the set function in the innermost complete grouping statement to obtain the current first grouping data; if the current first grouping number reaches the top value, the grouping data is determined according to each historical first grouping data, the current first grouping data and other grouping data corresponding to other target grouping items; otherwise, the current first grouping data is used as the historical first grouping data, and the step of determining the current first grouping data is returned.

[0077] It can be understood that the data read from the target table data is read in batches in sequence, and the current batch first data can be understood as the batch data currently read from the target table data. The first intermediate data is the grouped data. The current first group number can be understood as the number of groups that have been obtained. The current first group data can be understood as the group data processed by the set function. The historical first group data can be understood as the first group data before the current batch. Other group data can be understood as all the data extracted according to other target group items.

[0078] Specifically, the processor can group the current batch of first data in the target table data according to each target grouping item to obtain the current first intermediate data, and count the existing number of groups to determine the current number of first groups. The processor can process the current first intermediate data according to the set function in the innermost complete grouping statement to obtain the current first group data. If the current number of first groups reaches the top value, the processor can determine the filtering range by using the first group data that has been obtained, that is, through each historical first group data and the current first group data, and filter through the filtering range in other group data to obtain group data. Otherwise, the current first group data is output upward as the historical first group data, and the step of determining the current first group data is returned.

[0079] For example, let's take a specific example. Assume that the other target grouping items are (C1, C2), and the corresponding other grouping data are (1, 2)(1, 3)(2, 4)(2, 5)(3, 6)(4, 7)(5, 8)(5, 9), and they are in order. Before optimization, the innermost layer calculates GROUP BY C1, C2 and outputs 8 rows of data, all of which participate in the calculation of the middle layer FUNC, and finally outputs the top 3 rows in the outermost layer. After optimization, when the innermost layer calculates GROUP BY C1, C2, it first calculates the grouping according to the C1 column, and determines the top 3 (top value) groups (i.e., 1, 2, and 3), and then determines the grouping range for subsequent calculations, i.e., the first grouping data corresponds to the five rows (1)(1)(2)(2)(3). Correspondingly, the data that matches the first grouped data can be determined from other grouped data (C1, C2), that is, (1,2)(1,3)(2,4)(2,5)(3,6), a total of 5 rows of data. That is, through the above steps, 5 rows of data are finally output, which is the grouped data. The above is when the data is ordered, and the range of the target number of groups can be quickly determined based on the comparison of adjacent rows. That is, when the 6th row of data is detected, it is known that the first 3 groups have been obtained, and the subsequent data (7th and 8th rows) do not need to be obtained. It not only reduces the amount of underlying data obtained, but also reduces the data scale involved in the grouping calculation of the inner layer GROUP BY C1, C2, and reduces the data scale output to the middle layer (the middle layer calculates FUNC less times), and the performance is improved.

[0080] S209. Otherwise, perform hash grouping on the target table data according to the first item number, each inner grouping item, each target grouping item and the top value to determine the grouping data.

[0081] Specifically, when the arrangement is non-ordered, the processor can construct two hash tables. The first hash table groups according to the target grouping item, and the second hash table groups according to the complete grouping item, and the two hash tables are maintained synchronously. When the number of groups in the first hash table reaches the top value, the first hash table is stopped from being maintained, and the second hash table ignores other groups except the existing groups in the first hash table, and only processes the existing groups to obtain grouped data.

[0082] Further, based on the above embodiment, the step of performing hash grouping on the target table data according to the first item number, each inner grouping item, each target grouping item and the top value to determine the grouping data can be refined as follows:

[0083] Hash grouping is performed on the current batch of second data in the target table data according to each target grouping item to obtain a first hash table, and the current second grouping number is determined; hash grouping is performed on the current batch of second data according to each inner grouping item to determine a second hash table; if the current second grouping number reaches the top value, the to-be-processed grouping data that matches the existing grouping data in the first hash table is filtered from the second hash table; otherwise, the determination step of the first hash table is returned; the to-be-processed grouping data is processed according to the set function in the innermost complete grouping statement to determine the grouping data.

[0084] In this embodiment, the current batch of second data can be understood as the data of the current batch in a non-ordered arrangement. The first hash table can be understood as a hash table obtained by grouping according to the target grouping item. The second hash table can be understood as a hash table obtained by grouping according to all inner grouping items. The to-be-processed group can be understood as ignoring new groups except the existing groups in the first hash table.

[0085] Specifically, the processor can hash the current batch of second data in the target table data according to each target grouping item to obtain the first hash table and determine the current number of second groups; the processor can simultaneously hash the current batch of second data according to each inner grouping item to determine the second hash table. If the current number of second groups reaches the top value, the new groups except the existing groups in the first hash table are ignored in the second hash table to obtain the pending group data that matches the existing groups in the first hash table. If the current number of second groups does not reach the top value, return to the determination step of the first hash table and continue to obtain the next batch of second data for hash grouping. The processor can process the pending group data according to the set function in the innermost complete grouping statement to determine the group data.

[0086] S210: Continue to execute the grouped data based on other layer statements in the target query statement to obtain a query result.

[0087] In this embodiment, other layer statements can be understood as statements in the target query statement except the innermost layer complete grouping statement.

[0088] Specifically, the processor may continue to execute the grouped data hierarchically based on other layer statements in the target query statement, and the obtained execution result is the query result.

[0089] For example, the above example can be optimized to obtain the target query statement: SELECT TOP 3 MAX(E3) FROM(SELECT D1 E1, FUNC(D2) E2, D3 E3 FROM(SELECT C1D1, C2 D2, MAX(C3) D3 FROMT1 GROUP BY C1, C2)) GROUP BY E1, E2. The grouped data continues to participate in the calculation of the middle layer FUNC, and finally the first three rows of TOP 3 are output in the outermost layer.

[0090] The technical solution of the embodiment of the present invention is to traverse the outermost grouping items of the target query statement, determine the innermost query items corresponding to the single grouping conditions in the grouping items, classify the innermost query items according to whether they meet the preset column conditions, mark them with the first marking symbol and the second marking symbol, use the innermost query items under the first marking symbol as the leading grouping items, and determine the first number of items representing the number of the first marking symbol, as well as the total number of items of the first marking symbol and the second marking symbol, use the top value corresponding to the top keyword, the first number of items and the total number of items as grouping information, and provide guidance for the grouping of the subsequent innermost complete grouping statements through the grouping information. The target grouping items and other target grouping items can be distinguished from all the inner grouping items of the innermost complete grouping statement through the grouping information, and the target table data can be grouped through the grouping method corresponding to the sorting method. First, the grouping range is determined by the target grouping item and the top value, and then the grouping data of the innermost complete grouping statement is determined by combining other target grouping items with the grouping range. The grouping data responds to the top value and realizes the perception of the innermost grouping item to the top condition. Subsequent data does not need to be obtained, which reduces the amount of underlying data obtained, reduces the scale of data involved in the inner layer grouping calculation, and reduces the scale of data output to other layers of statements in the target query statement, improving performance. The grouping data is continued to be calculated through other layers of statements in the target query statement to obtain the query result, which improves the query performance and ensures the accuracy of the query result.

[0091] Embodiment 3

[0092] Figure 3 This is a schematic diagram of the structure of a database statement query device provided by Embodiment 3 of the present invention. Figure 3 As shown, the device comprises:

[0093] A statement determination module 31 is used to optimize the acquired original query statement to obtain a target query statement;

[0094] An information determination module 32, for determining grouping information of a target query statement in response to a target query statement containing a top keyword, wherein the grouping information includes a top value and a grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement;

[0095] The result determination module 33 is used to execute the target query statement based on the top value and the grouping attribute to obtain a query result, and the grouping information is used to guide the grouping of the innermost complete grouping statement in the target query statement.

[0096] The technical solution of the embodiment of the present invention optimizes the original query statement obtained to obtain the target query statement; in response to the target query statement containing the top keyword, the grouping information of the target query statement is determined, and the grouping information includes the top value and grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement; the target query statement is executed based on the top value and the grouping attribute to obtain the query result. The innermost complete grouping statement in the target query statement is guided by the grouping information, and the grouping data range within the top value is grouped, so as to reduce the grouping data range involved in executing other statements in the target query statement and obtain the query result. The innermost grouping item is aware of the top condition, which reduces the innermost data set, thereby reducing the number of calculations corresponding to subsequent queries and improving performance.

[0097] Furthermore, the information determination module 32 includes:

[0098] A value determination unit, used to determine a top value corresponding to the top keyword;

[0099] A query item determination unit, configured to traverse the outermost grouping items of the target query statement and determine the innermost query item corresponding to the single grouping condition in the grouping items;

[0100] A grouping item determination unit, configured to determine a grouping attribute and a leading grouping item according to the innermost query item;

[0101] The information determination unit is used to use the top value, the grouping attribute and the leading grouping item as the grouping information of the target query statement.

[0102] The group item determination unit is specifically used for:

[0103] If the innermost query item meets the preset column condition, the innermost query item is marked with a first marking symbol and used as a leading grouping item;

[0104] Otherwise, marking the innermost query item with a second marking symbol;

[0105] Counting the number of the first marking symbols in the innermost query items to obtain a first item number;

[0106] Determining the total number of items according to the number of the second marking symbols and the first number of items;

[0107] The first number of items and the total number of items are used as grouping attributes of the innermost query item.

[0108] Furthermore, the result determination module 34 includes:

[0109] A first determining unit, configured to determine, when the first number of items in the grouping attribute is not zero, a sorting method of the target table data in the target query statement, all inner grouping items in the innermost complete grouping statement, and the target table data;

[0110] A second determining unit, configured to determine a target grouping item and other target grouping items from each of the inner grouping items according to the first number of items and the total number of items in the grouping attribute;

[0111] a third determining unit, configured to filter and process the target table data by group according to the first number of items, each of the target grouping items, the top value and the other target grouping items to determine grouping data if the sorting mode is ordered sorting;

[0112] A fourth determining unit is used for otherwise, performing hash grouping on the target table data according to the first number of items, each of the inner grouping items, each of the target grouping items and the top value to determine the grouping data;

[0113] The fifth determining unit is used to continue executing the grouped data based on other layer statements in the target query statement to obtain a query result.

[0114] The third determining unit is specifically used for:

[0115] Grouping the current batch of first data in the target table data according to each of the target grouping items to obtain current first intermediate data, and determining the current first grouping number;

[0116] Processing the current first intermediate data according to the set function in the innermost complete grouping statement to obtain current first grouped data;

[0117] If the current first group number reaches the top value, determine the group data according to each historical first group data and the current first group data;

[0118] Otherwise, the current first packet data is used as the historical first packet data, and the process returns to the step of determining the current first packet data.

[0119] The fourth determining unit is specifically configured to:

[0120] Performing hash grouping on the current batch of second data in the target table data according to each of the target grouping items to obtain a first hash table, and determining the current number of second groups;

[0121] Perform hash grouping on the current batch of second data according to each of the inner grouping items to determine a second hash table;

[0122] If the current second packet number reaches the top value, filtering the to-be-processed packet data matching the existing packet data in the first hash table from the second hash table;

[0123] Otherwise, return to the first hash table determination step;

[0124] The to-be-processed grouped data are processed according to the set function in the innermost complete grouping statement to determine the grouped data.

[0125] The database statement query device provided in the embodiment of the present invention can execute the database statement query method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0126] Embodiment 4

[0127] Figure 4 A schematic diagram of the structure of an electronic device 40 that can be used to implement an embodiment of the present invention is shown. The electronic 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. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0128] like Figure 4As shown, the electronic device 40 includes at least one processor 41, and a memory connected to the at least one processor 41, such as a read-only memory (ROM) 42, a random access memory (RAM) 43, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 41 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 42 or the computer program loaded from the storage unit 48 to the random access memory (RAM) 43. In the RAM 43, various programs and data required for the operation of the electronic device 40 can also be stored. The processor 41, the ROM 42, and the RAM 43 are connected to each other through a bus 44. An input / output (I / O) interface 45 is also connected to the bus 44.

[0129] A number of components in the electronic device 40 are connected to the I / O interface 45, including: an input unit 46, such as a keyboard, a mouse, etc.; an output unit 47, such as various types of displays, speakers, etc.; a storage unit 48, such as a disk, an optical disk, etc.; and a communication unit 49, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 49 allows the electronic device 40 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0130] The processor 41 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 41 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 41 executes the various methods and processes described above, such as a database statement query method.

[0131] In some embodiments, the database statement query method may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 48. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 40 via the ROM 42 and / or the communication unit 49. When the computer program is loaded into the RAM 43 and executed by the processor 41, one or more steps of the database statement query method described above may be performed. Alternatively, in other embodiments, the processor 41 may be configured to execute the database statement query method in any other appropriate manner (e.g., by means of firmware).

[0132] Various implementations 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 chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0133] Computer programs for implementing 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, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0134] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

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

[0136] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0137] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.

[0138] In one embodiment, the embodiment of the present invention further includes a computer program product, the computer program product includes a computer program, and the computer program implements the database statement query method of any embodiment of the present invention when executed by a processor.

[0139] In the process of implementation, the computer program product can be written in one or more programming languages ​​or a combination thereof to perform the computer program code of the present invention, including object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computer (for example, using an Internet service provider to connect through the Internet).

[0140] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and this document does not limit this.

[0141] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A database statement query method, characterized in that: include: Optimize the obtained original query statement to obtain the target query statement; In response to a target query statement including a top keyword, determining grouping information of the target query statement, wherein the grouping information includes a top value and a grouping attribute corresponding to the top keyword; The grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement; The target query statement is executed based on the top value and the grouping attribute to obtain a query result, and the grouping information is used to guide the grouping of the innermost complete grouping statement in the target query statement.

2. The method according to claim 1, characterized in that The determining the grouping information of the target query statement includes: Determine a top value corresponding to the top keyword; Traversing the outermost grouping items of the target query statement to determine the innermost query item corresponding to the single grouping condition in the grouping items; Determine the grouping attribute and the leading grouping item according to the innermost query item; The top value, the grouping attribute and the leading grouping item are used as the grouping information of the target query statement.

3. The method according to claim 2, characterized in that The determining of the grouping attribute and the leading grouping item according to the innermost query item includes: If the innermost query item meets the preset column condition, the innermost query item is marked with a first marking symbol and used as a leading grouping item; Otherwise, marking the innermost query item with a second marking symbol; Counting the number of the first marking symbols in the innermost query items to obtain a first item number; Determining the total number of items according to the number of the second marking symbols and the first number of items; The first number of items and the total number of items are used as grouping attributes of the innermost query item.

4. The method according to claim 1, characterized in that The executing the target query statement based on the top value and the grouping attribute to obtain the query result includes: When the first number of items in the grouping attribute is not zero, determining a sorting method of the target table data in the target query statement, all inner grouping items in the innermost complete grouping statement, and the target table data; Determining a target grouping item and other target grouping items from each of the inner grouping items according to the first number of items and the total number of items in the grouping attribute; If the sorting method is ordered sorting, filtering and processing the target table data by group according to the first number of items, each of the target grouping items, the top value and the other target grouping items to determine grouping data; Otherwise, performing hash grouping on the target table data according to the first number of items, each of the inner grouping items, each of the target grouping items and the top value to determine the grouping data; The grouped data is further executed based on other layer statements in the target query statement to obtain a query result.

5. The method according to claim 4, characterized in that The step of grouping the target table data according to the first number of items, each of the target grouping items, the top value and other target grouping items to determine the grouping data includes: Grouping the current batch of first data in the target table data according to each of the target grouping items to obtain current first intermediate data, and determining the current first grouping number; Processing the current first intermediate data according to the set function in the innermost complete grouping statement to obtain current first grouped data; If the current first grouping number reaches the top value, determine the grouping data according to each historical first grouping data, the current first grouping data and other grouping data corresponding to other target grouping items; Otherwise, the current first packet data is used as the historical first packet data, and the process returns to the step of determining the current first packet data.

6. The method according to claim 4, characterized in that The step of performing hash grouping on the target table data according to the first number of items, each of the inner grouping items, each of the target grouping items and the top value to determine the grouping data includes: Performing hash grouping on the current batch of second data in the target table data according to each of the target grouping items to obtain a first hash table, and determining the current number of second groups; Perform hash grouping on the current batch of second data according to each of the inner grouping items to determine a second hash table; If the current second packet number reaches the top value, filtering the to-be-processed packet data matching the existing packet data in the first hash table from the second hash table; Otherwise, return to the first hash table determination step; The to-be-processed grouped data are processed according to the set function in the innermost complete grouping statement to determine the grouped data.

7. A database statement query device, characterized in that: include: A statement determination module is used to optimize the obtained original query statement to obtain a target query statement; An information determination module, configured to determine grouping information of a target query statement in response to a target query statement containing a top keyword, wherein the grouping information includes a top value and a grouping attribute corresponding to the top keyword; the grouping attribute is used to guide the grouping of the innermost complete grouping statement in the target query statement; A result determination module is used to execute the target query statement based on the top value and the grouping attribute to obtain a query result, and the grouping information is used to guide the grouping of the innermost complete grouping statement in the target query statement.

8. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the database statement query method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the database statement query method according to any one of claims 1 to 6 when executed.

10. A computer program product, characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the computer program implements the database statement query method according to any one of claims 1 to 6.

Images