A query expression processing method, device and equipment and storage medium

Abstract

The application discloses a query expression processing method and device, equipment and a storage medium. The method comprises the following steps: obtaining a query item of a virtual view expression in a SQL statement; if the query item of the virtual view expression comprises a deduplication keyword and a related subquery item, and the query item of the virtual view expression comprises all related columns in the related subquery item, then the subquery item referenced by the upper layer is removed from the virtual view expression, and a first expression is obtained according to the remaining query item after the removal; deduplication processing is performed based on the first expression, and a deduplication result is obtained; and column mapping calculation is performed according to the deduplication result and the complete virtual view expression before the removal, so that the number of times of executing the related subquery can be reduced, and the time consumed for executing the SQL statement can be reduced.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of database technology, and in particular to a query expression processing method, apparatus, device and storage medium. Background Technology

[0002] Structured Query Language (SQL) is a database manipulation language used to access, query, update, and manage relational database systems.

[0003] The query term is the expression used for querying in the SQL statement. If the query term includes deduplication keywords and correlated subqueries, the correlated subqueries must be executed first, followed by the deduplication operation. Since the implementation of correlated subqueries is complex, this process consumes a significant amount of time.

[0004] For example, the following SQL statement 1:

[0005] SELECT DISTINCT T1.C1, (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1;

[0006] Here, (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1) is a subquery. This subquery is a correlated subquery, meaning it involves a table that is not part of the current FROM item (in this example, T1, which is a FROM item from the parent level). C1 in the query comes from T1.

[0007] The typical implementation process of this query statement is to retrieve all data from table T1, then perform column mapping calculations, that is, calculate the corresponding C1 and query expression values ​​for each row of data to obtain two columns of data, and finally calculate DISTINCT for these two columns of data in all rows to remove duplicates and output the final result.

[0008] When executing SQL statement 1 in the above manner, the related subquery needs to be executed for each row T1.C1 value. The implementation of the related subquery in SQL statement 1 is relatively simple, but in reality it may be more complex, which would lead to more time consumption for each row calculation. Summary of the Invention

[0009] This invention provides a query expression processing method, apparatus, device, and storage medium that can reduce the time consumed by executing SQL statements by reducing the number of times related subqueries are executed.

[0010] According to one aspect of the present invention, a query expression processing method is provided, comprising:

[0011] Retrieve the query terms of the virtual view expression in the SQL statement, wherein the query terms include at least one subquery term;

[0012] If the query terms of the virtual view expression include deduplication keywords and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper level are removed from the virtual view expression, and the first expression is obtained based on the remaining query terms after removal.

[0013] Based on the first expression, deduplication is performed to obtain the deduplication result;

[0014] Column mapping is calculated based on the deduplication results and the complete virtual view expression before removal.

[0015] According to another aspect of the present invention, a query expression processing apparatus is provided, the query expression processing apparatus comprising:

[0016] The query item acquisition module is used to acquire query items of virtual view expressions in SQL statements, wherein the query items include at least one sub-query item;

[0017] The remove module is used to remove the subquery referenced by the upper layer from the virtual view expression if the query items of the virtual view expression include deduplication keywords and related subquery items, and the query items of the virtual view expression include all related columns in the related subquery items. The first expression is obtained based on the remaining query items after the removal.

[0018] The deduplication module is used to perform deduplication processing based on the first expression to obtain the deduplication result;

[0019] The column mapping calculation module is used to perform column mapping calculations based on the deduplication results and the complete virtual view expression before removal.

[0020] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0021] At least one processor; and

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

[0023] 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 query expression processing method according to any embodiment of the present invention.

[0024] 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 query expression processing method according to any embodiment of the present invention.

[0025] This invention, in its embodiments, obtains the query terms of a virtual view expression in an SQL statement. If the query terms of the virtual view expression include deduplication keywords and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper layer are removed from the virtual view expression. A first expression is obtained based on the remaining query terms after removal. Deduplication is performed based on the first expression to obtain the deduplication result. Column mapping calculation is performed based on the deduplication result and the complete virtual view expression before removal. This reduces the number of times related subqueries are executed, thereby reducing the time consumed by executing the SQL statement.

[0026] 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

[0027] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0028] Figure 1 This is a flowchart of a query expression processing method according to an embodiment of the present invention;

[0029] Figure 2 This is a schematic diagram of the structure of a query expression processing device according to an embodiment of the present invention;

[0030] Figure 3 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0031] 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.

[0032] 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 a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0033] It is understood that before using the technical solutions disclosed in the various embodiments of this disclosure, users should be informed of the types, scope of use, and usage scenarios of the personal information involved in this disclosure in an appropriate manner in accordance with relevant laws and regulations, and user authorization should be obtained.

[0034] Example 1

[0035] Figure 1 This is a flowchart illustrating a query expression processing method provided in an embodiment of the present invention. This embodiment is applicable to query expression processing. The method can be executed by the query expression processing device in this embodiment, which can be implemented in software and / or hardware, such as... Figure 1 As shown, the method specifically includes the following steps:

[0036] S110, retrieve the query items of the virtual view expression in the SQL statement.

[0037] The virtual view expression is a query expression in the FROM item. For example, it can be a query expression in the FROM item that is not a user-created entity view but has a similar view implementation.

[0038] In a specific example, if the SQL statement is `SELECT C1FROM(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1))`, then the virtual view expression of the SQL statement is `(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1))`. The query term in the virtual view expression is: `DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)`.

[0039] S120, if the query terms of the virtual view expression include deduplication keywords and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper level are removed from the virtual view expression, and the first expression is obtained based on the remaining query terms after removal.

[0040] The query item includes at least one sub-query item.

[0041] The deduplication keyword is DISTINCT. If the query terms of the virtual view expression include DISTINCT, then the query terms of the virtual view expression are determined to include the deduplication keyword.

[0042] Among them, the related subquery is a subquery that involves a table that does not belong to the FROM item of this layer.

[0043] Among them, the relevant columns are those that do not belong to the FROM items of this layer.

[0044] It should be noted that if a subquery item is referenced by a parent query, it needs to be output; if a subquery item is not referenced by a parent query, it does not need to be output.

[0045] Specifically, if the virtual view expression includes SELECT, then the query terms of the virtual view expression are obtained. If the query terms of the virtual view expression include DISTINCT, then the subquery terms of the virtual view expression are traversed. If a subquery term is a related subquery term, then it is determined whether the query terms of the virtual view expression include all related columns in the related subquery term. If the query terms of the virtual view expression include all related columns in the related subquery term, then the subquery term referenced by the upper level is removed from the virtual view expression, and the first expression is obtained based on the remaining query terms after the removal.

[0046] Specifically, if the query terms of the virtual view expression include deduplication keywords and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper level will be marked with a first tag, and the virtual view expression will be transformed into the corresponding DISTINCT plan node. When setting the deduplication keyword of DISTINCT according to the subquery terms, the following processing is performed: traverse the subquery terms, remove and collect the subquery terms carrying the first tag from the virtual view expression, use the removed virtual view expression as the deduplication keyword of the DISTINCT plan node, and generate a new column mapping plan node above the DISTINCT plan node, and set the mapping column to all columns before removal.

[0047] For example, the original virtual view expression flow could be as follows (executed sequentially from bottom to top):

[0048] DISTINCT (Deduplication function, removes duplicates based on column values ​​CC1 and CC2)

[0049] PROJECT(column mapping calculation, calculates the values ​​of columns C1 and the query expression, assuming the output after calculation is columns CC1 and CC2).

[0050] TABLE_SCAN(Scans data from table T1, outputs column C1)

[0051] The optimized virtual view expression flow is as follows (execution order is from bottom to top):

[0052] PROJECT(column mapping calculation, calculate CC1, query expression (related columns become CC1), assuming output is CCC1, CCC2)

[0053] DISTINCT (Deduplication function, removes duplicates based on the values ​​in column CC1)

[0054] PROJECT(Column mapping calculation, calculates the value in column C1, assuming the output will be column CC1 after calculation)

[0055] TABLE_SCAN(Scans data from table T1, outputs column C1)

[0056] Specifically, if the query terms of the virtual view expression include deduplication keywords and related subqueries, and the query terms of the virtual view expression include all related columns in the related subqueries, then deduplication is performed first, followed by column mapping calculation.

[0057] Optionally, retrieve query items for the virtual view expression in the SQL statement, including:

[0058] Retrieve the FROM field from the SQL statement;

[0059] If the FROM term of the SQL statement is a virtual view expression, then the query terms of the virtual view expression are retrieved.

[0060] Specifically, if the FROM term of the SQL statement is a virtual view expression, the way to obtain the query terms of the virtual view expression can be as follows: if the FROM term of the SQL statement includes a query keyword, then determine that the FROM term of the SQL statement is a virtual view expression, and obtain the query terms of the virtual view expression.

[0061] Optional, also includes:

[0062] Delete subquery items in the SQL statement that are not referenced by the parent query.

[0063] In a specific example, the SQL statement is SELECT C1FROM(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)), where the FROM term of the SQL statement is (SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)), and (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1) is a query term of (SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)). Since the output item D2 of (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1) is not in the output item C1 of SELECT C1FROM(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)), we can directly remove (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1) and simplify it to SELECT C1FROM(SELECT DISTINCT T1.C1FROM T1). The execution time of SELECT C1FROM(SELECT DISTINCT T1.C1FROM T1) is much shorter than the execution time of SELECT C1FROM(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)).

[0064] By removing subquery items that are not referenced by the upper level from the SQL statement, the query expression of the SQL statement can be simplified, which can reduce the time consumed by executing the SQL statement.

[0065] Optionally, column mapping calculations are performed based on the deduplication results and the virtual view expression, including:

[0066] Determine the mapping columns based on the virtual view expression;

[0067] Perform column mapping calculations based on the deduplication results and the mapping columns.

[0068] Specifically, the way to determine the mapping columns based on the virtual view expression is to determine all columns in the virtual view expression as mapping columns.

[0069] Optionally, delete subquery items in the SQL statement that are not referenced by the parent query, including:

[0070] Delete subquery items in the SQL statement that are not referenced by the upper layer and do not contain undefined functions.

[0071] The uncertainty function can be the function SYSDATE() that returns the current date of the system, or other uncertainty functions. This embodiment of the invention does not impose any restrictions on this.

[0072] Specifically, it checks whether there is an uncertain function in the subquery item that is not referenced by the upper layer in the SQL statement. If there is an uncertain function in the subquery item that is not referenced by the upper layer in the SQL statement, the subquery item with the uncertain function is kept unchanged. If there is no uncertain function in the subquery item that is not referenced by the upper layer in the SQL statement, the subquery item that is not referenced by the upper layer in the SQL statement and has no uncertain function is deleted.

[0073] Optional, also includes:

[0074] Keep the subquery terms of the virtual view expression with uncertain functions unchanged.

[0075] Specifically, iterate through all subquery items in the virtual view. If a subquery item contains an uncertain function, skip it and do not optimize it.

[0076] Optional, also includes:

[0077] If the query terms of the virtual view expression include deduplication keywords and related subqueries, and at least one related column in the related subqueries is not included in the query terms, then the related subqueries remain unchanged.

[0078] Specifically, if the query terms of the virtual view expression include deduplication keywords and related subqueries, then all subqueries in the virtual view are traversed. If at least one related column in the current related subquery is not included in the query terms, then the current related subquery is skipped and the next subquery is processed.

[0079] In a specific example, the subquery item should output the following:

[0080] SQL statement 1:

[0081] SELECT DISTINCT T1.C1, (SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1;

[0082] Since the calculated value of the related subquery item is also determined for a given value of T1.C1 in each row, deduplication of a single C1 query item can also guarantee the deduplication effect of two query items. Therefore, for the query expression in SQL statement 1, instead of calculating the value of the related subquery item for all T1.C1 data, we can first deduplicate the T1.C1 column to reduce the amount of data in the intermediate result set. Then, we can calculate the related subquery item for each row in the C1 column of the deduplicated result set. In this way, the number of times the related subquery item is executed (or the number of rows) is reduced, thereby improving the overall execution efficiency of the SQL statement.

[0083] Cases where subquery items do not need to be output:

[0084] SQL statement 2:

[0085] The SQL statement `SELECT C1FROM(SELECT DISTINCT T1.C1,(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)FROM T1)` uses a virtual view expression. This expression includes a deduplication keyword and related subqueries. Since the output item `D2` of `(SELECT T2.D2FROM T2WHERE T2.D1=T1.C1)` is not in the output item `C1` of `SQL statement 2`, this useless subquery item can be removed, simplifying the SQL statement to `SELECT C1FROM(SELECT DISTINCT T1.C1FROM T1)`. The simplified SQL statement has better performance.

[0086] In another specific example, the query expression processing flow includes the following steps:

[0087] Step 1: When analyzing the FROM field of an SQL statement, if the FROM field is a virtual view expression composed of a SELECT statement, then probe it; otherwise, exit optimization.

[0088] Step 2: Determine whether the query terms of the virtual view expression contain the DISTINCT keyword. If yes, continue to Step 3; otherwise, exit optimization.

[0089] Step 3: Traverse all subquery items in the virtual view expression. If a subquery item is a related subquery item, perform the following judgment and processing:

[0090] If a subquery contains an uncertain function (such as the function SYSDATE() that returns the current system date), then skip the current subquery and process the next subquery; otherwise, continue.

[0091] Collect the relevant columns used in the related subquery. Relevant columns are those that do not belong to the current FROM item, such as T1.C1. Check if the relevant columns exist in the virtual view expression. If not, skip the current subquery and process the next subquery; otherwise, continue.

[0092] Determine if the subquery is referenced by its parent column, i.e., used in the statement. In this example, the parent column is SELECT C1 in statement 2. If it is referenced, set its flag FLAG1 to B (the default is A), indicating that the columns after DISTINCT can be deduplicated first before the query expression is calculated, and then the next query item can be processed. Otherwise, if it is not referenced, delete the query item and continue processing the next query item.

[0093] When converting the virtual view expression into the corresponding DISTINCT plan node and setting the deduplication keyword for DISTINCT based on the subquery item, the following processing is performed:

[0094] Iterate through the subquery items, remove and collect the items where FLAG1=B from the virtual view expression, use the removed virtual view expression as the deduplication key for the DISTINCT plan node, and generate a new column mapping plan node above the DISTINCT plan node, with the mapping column set to all columns before removal.

[0095] The technical solution of this embodiment obtains the query items of the virtual view expression in the SQL statement; if the query items of the virtual view expression include deduplication keywords and related subquery items, and the query items of the virtual view expression include all related columns in the related subquery items, then the query items that do not contain related subquery items are deduplicated first, and then the values ​​of the related subquery items and other query items are calculated, thereby reducing the time consumed by executing the SQL statement.

[0096] Example 2

[0097] Figure 2 This is a schematic diagram of a query expression processing device provided in an embodiment of the present invention. This embodiment is applicable to query expression processing. The device can be implemented using software and / or hardware, and can be integrated into any device that provides query expression processing functionality, such as… Figure 2 As shown, the query expression processing device specifically includes: a query item acquisition module 210, an removal module 220, a deduplication module 230, and a column mapping calculation module 240.

[0098] The query item acquisition module is used to acquire query items of virtual view expressions in SQL statements, wherein the query item includes at least one sub-query item;

[0099] The remove module is used to remove the subquery referenced by the upper layer from the virtual view expression if the query items of the virtual view expression include deduplication keywords and related subquery items, and the query items of the virtual view expression include all related columns in the related subquery items. The first expression is obtained based on the remaining query items after the removal.

[0100] The deduplication module is used to perform deduplication processing based on the first expression to obtain the deduplication result;

[0101] The column mapping calculation module is used to perform column mapping calculations based on the deduplication results and the complete virtual view expression before removal.

[0102] The above-described products can perform the methods provided in any embodiment of the present invention, and have the corresponding functional modules and beneficial effects for performing the methods.

[0103] Example 3

[0104] Figure 3 A schematic diagram of an electronic device 10 that can be used to implement embodiments 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 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.

[0105] like Figure 3 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or 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 program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 may also store various programs and data required for the operation of the electronic 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.

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

[0107] 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, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as: query expression processing methods:

[0108] Retrieve the query terms of the virtual view expression in the SQL statement, wherein the query terms include at least one subquery term;

[0109] If the query terms of the virtual view expression include deduplication keywords and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper level are removed from the virtual view expression, and the first expression is obtained based on the remaining query terms after removal.

[0110] Based on the first expression, deduplication is performed to obtain the deduplication result;

[0111] Column mapping is calculated based on the deduplication results and the complete virtual view expression before removal.

[0112] In some embodiments, the query expression processing 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 mounted on electronic 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 query expression processing method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the query expression processing method by any other suitable means (e.g., by means of firmware).

[0113] 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.

[0114] 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.

[0115] 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.

[0116] To provide interaction with a user, the systems and techniques described herein can 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 pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic 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 tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0117] 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.

[0118] 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.

[0119] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0120] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A query expression processing method, characterized in that, include: Retrieve the query terms of the virtual view expression in the SQL statement, wherein the query terms include at least one subquery term; If the query terms of the virtual view expression include a deduplication keyword and related subquery terms, and the query terms of the virtual view expression include all related columns in the related subquery terms, then the subquery terms referenced by the upper level are removed from the virtual view expression, and the first expression is obtained based on the remaining query terms after removal; wherein, the deduplication keyword is DISTINCT; Based on the first expression, deduplication is performed to obtain the deduplication result; Column mapping is calculated based on the deduplication results and the complete virtual view expression before removal.

2. The method according to claim 1, characterized in that, Retrieve query items from the virtual view expression in the SQL statement, including: Retrieve the FROM field from the SQL statement; If the FROM term of the SQL statement is a virtual view expression, then the query terms of the virtual view expression are retrieved.

3. The method according to claim 1, characterized in that, Also includes: Delete subquery items in the SQL statement that are not referenced by the parent query.

4. The method according to claim 1, characterized in that, Column mapping calculations are performed based on the deduplication results and the virtual view expression, including: Determine the mapping columns based on the virtual view expression; Perform column mapping calculations based on the deduplication results and the mapping columns.

5. The method according to claim 3, characterized in that, Remove subquery items from the SQL statement that are not referenced by the parent query, including: Delete subquery items in the SQL statement that are not referenced by the upper layer and do not contain undefined functions.

6. The method according to claim 5, characterized in that, Also includes: Keep the subquery terms of the virtual view expression with uncertain functions unchanged.

7. The method according to claim 1, characterized in that, Also includes: If the query terms of the virtual view expression include deduplication keywords and related subqueries, and at least one related column in the related subqueries is not included in the query terms, then the related subqueries remain unchanged.

8. A query expression processing device, characterized in that, include: The query item acquisition module is used to acquire query items of virtual view expressions in SQL statements, wherein the query items include at least one sub-query item; The removal module is used to remove the subquery referenced by the upper layer from the virtual view expression if the query items of the virtual view expression include deduplication keywords and related subquery items, and the query items of the virtual view expression include all related columns in the related subquery items. The first expression is obtained based on the remaining query items after removal. The deduplication keyword is DISTINCT. The deduplication module is used to perform deduplication processing based on the first expression to obtain the deduplication result; The column mapping calculation module is used to perform column mapping calculations based on the deduplication results and the complete virtual view expression before removal.

9. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, 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 query expression processing method according to any one of claims 1-7.

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

Images