CN114238379A

CN114238379A - Report query method and system based on SQL generator

Info

Publication number: CN114238379A
Application number: CN202111563182.XA
Authority: CN
Inventors: 李启飞; 陈诚; 王春晖
Original assignee: Zhejiang Taimei Medical Technology Co Ltd
Current assignee: Zhejiang Taimei Medical Technology Co Ltd
Priority date: 2021-12-20
Filing date: 2021-12-20
Publication date: 2022-03-25

Abstract

The invention provides a report query method and a report query system based on an SQL generator. The SQL generator in the query method is suitable for generating SQL statements and specifically comprises the following steps: receiving and analyzing a report query request from a user side through an SQL generator, and acquiring metadata information in the report query request; generating, by an SQL generator, one or more SQL statements based on the metadata information; and executing the report query request according to the one or more SQL statements. The report query method and the report query system based on the SQL generator can improve the query performance of the report system and reduce the error rate of report query.

Description

Report query method and system based on SQL generator

Technical Field

The invention mainly relates to the field of data processing, in particular to a report query method based on an SQL generator.

Background

When a user queries a report by using a self-defined report system, the user may dynamically add a filter or change the dimensionality of query data during the query, and the system background may dynamically change SQL statements according to the current user permission information to realize the limitation of data permission. If the background generates the query SQL according to the dimension and the measurement information selected by the user when the user creates the report, the background needs to analyze the SQL during the query, then modifies the SQL statement, and then queries after the modification is completed, so that the process is complex and time-consuming.

Disclosure of Invention

The invention aims to provide a report query method and a report query system based on an SQL generator, which can improve the query performance of a report system and reduce the error rate of report query.

In order to solve the technical problem, the invention provides a report query method based on an SQL generator, wherein the SQL generator is suitable for generating SQL sentences, and the report query method is characterized by comprising the following steps:

receiving and analyzing a report query request from a user side through the SQL generator, and acquiring metadata information in the report query request;

generating, by the SQL generator, one or more SQL statements based on the metadata information; and

and executing the report query request according to the one or more SQL statements.

In an embodiment of the present invention, the SQL generator has an attribute field in a report, and the metadata information is an attribute value of the attribute field in the report corresponding to the report query request.

In an embodiment of the present invention, the attribute field includes a dimension field, a metrics field, a filter field, a sort field, and/or a paging field.

In an embodiment of the present invention, the SQL generator is a query model based on a preset programming language.

In an embodiment of the present invention, the predetermined programming language includes Java language, Python language, Scala language and/or Go language.

In an embodiment of the present invention, before generating one or more SQL statements, further comprising instantiating, by the SQL generator, the query model based on the metadata information.

In an embodiment of the present invention, after the report query request is executed, returning a query result to the user side and generating a queried report.

In another aspect of the present invention, a report query system based on an SQL generator is further provided, including:

the SQL generator is configured to receive and analyze a report query request from a user side so as to obtain metadata information in the report query request, and generate one or more SQL statements based on the metadata information; and

and the SQL execution engine is configured to execute the report query request according to the one or more SQL statements.

In order to solve the above problem, the present invention further provides a report query system based on SQL generator, including:

a memory for storing instructions executable by the processor; and the processor is used for executing the instruction to realize the report query method based on the SQL generator.

Yet another aspect of the present invention also proposes a computer readable medium storing computer program code which, when executed by a processor, implements a SQL generator-based report query method.

Compared with the prior art, the invention has the following advantages:

the SQL generator for generating the SQL statement is defined in the code layer, so that the SQL statement is not generated when the report is created, and the query SQL statement is dynamically generated according to the SQL generator when the report is queried, thereby avoiding the workload and complexity of modifying the SQL statement when the request of report query is executed each time, accelerating the overall efficiency of report query and reducing the error rate.

Drawings

The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiment(s) of the application and together with the description serve to explain the principle of the invention. In the drawings:

FIG. 1 is a flowchart illustrating a report query method based on an SQL generator according to an embodiment of the present invention;

FIG. 2 is a system diagram of a report query system based on an SQL generator according to an embodiment of the present invention; and

fig. 3 is a system block diagram of a SQL generator-based report query system according to another embodiment of the present invention.

Detailed Description

In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings used in the description of the embodiments will be briefly introduced below. It is obvious that the drawings in the following description are only examples or embodiments of the application, from which the application can also be applied to other similar scenarios without inventive effort for a person skilled in the art. Unless otherwise apparent from the context, or otherwise indicated, like reference numbers in the figures refer to the same structure or operation.

As used in this application and the appended claims, the terms "a," "an," "the," and/or "the" are not intended to be inclusive in the singular, but rather are intended to be inclusive in the plural unless the context clearly dictates otherwise. In general, the terms "comprises" and "comprising" merely indicate that steps and elements are included which are explicitly identified, that the steps and elements do not form an exclusive list, and that a method or apparatus may include other steps or elements.

The relative arrangement of the components and steps, the numerical expressions, and numerical values set forth in these embodiments do not limit the scope of the present application unless specifically stated otherwise. Meanwhile, it should be understood that the sizes of the respective portions shown in the drawings are not drawn in an actual proportional relationship for the convenience of description. Techniques, methods, and apparatus known to those of ordinary skill in the relevant art may not be discussed in detail but are intended to be part of the specification where appropriate. In all examples shown and discussed herein, any particular value should be construed as merely illustrative, and not limiting. Thus, other examples of the exemplary embodiments may have different values. It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, further discussion thereof is not required in subsequent figures.

In the description of the present application, it is to be understood that the orientation or positional relationship indicated by the directional terms such as "front, rear, upper, lower, left, right", "lateral, vertical, horizontal" and "top, bottom", etc., are generally based on the orientation or positional relationship shown in the drawings, and are used for convenience of description and simplicity of description only, and in the case of not making a reverse description, these directional terms do not indicate and imply that the device or element being referred to must have a particular orientation or be constructed and operated in a particular orientation, and therefore, should not be considered as limiting the scope of the present application; the terms "inner and outer" refer to the inner and outer relative to the profile of the respective component itself.

Spatially relative terms, such as "above … …," "above … …," "above … …," "above," and the like, may be used herein for ease of description to describe one device or feature's spatial relationship to another device or feature as illustrated in the figures. It will be understood that the spatially relative terms are intended to encompass different orientations of the device in use or operation in addition to the orientation depicted in the figures. For example, if a device in the figures is turned over, devices described as "above" or "on" other devices or configurations would then be oriented "below" or "under" the other devices or configurations. Thus, the exemplary term "above … …" can include both an orientation of "above … …" and "below … …". The device may be otherwise variously oriented (rotated 90 degrees or at other orientations) and the spatially relative descriptors used herein interpreted accordingly.

It should be noted that the terms "first", "second", and the like are used to define the components, and are only used for convenience of distinguishing the corresponding components, and the terms have no special meanings unless otherwise stated, and therefore, the scope of protection of the present application is not to be construed as being limited. Further, although the terms used in the present application are selected from publicly known and used terms, some of the terms mentioned in the specification of the present application may be selected by the applicant at his or her discretion, the detailed meanings of which are described in relevant parts of the description herein. Further, it is required that the present application is understood not only by the actual terms used but also by the meaning of each term lying within.

It will be understood that when an element is referred to as being "on," "connected to," "coupled to" or "contacting" another element, it can be directly on, connected or coupled to, or contacting the other element or intervening elements may be present. In contrast, when an element is referred to as being "directly on," "directly connected to," "directly coupled to" or "directly contacting" another element, there are no intervening elements present. Similarly, when a first component is said to be "in electrical contact with" or "electrically coupled to" a second component, there is an electrical path between the first component and the second component that allows current to flow. The electrical path may include capacitors, coupled inductors, and/or other components that allow current to flow even without direct contact between the conductive components.

An embodiment of the present invention provides a report query method based on an SQL generator, which can improve query performance of a report system and reduce error rate of report query

Fig. 1 is a schematic flow chart of a report query method based on an SQL generator (hereinafter referred to as "query method 10") in this embodiment. FIG. 1 uses a flowchart in this application to illustrate the operations performed by a system according to embodiments of the present application. It should be understood that the preceding or following operations are not necessarily performed in the exact order in which they are performed. Rather, various steps may be processed in reverse order or simultaneously. Meanwhile, other operations are added to or removed from these processes.

Referring to FIG. 1, a query method 10 includes the following steps.

Step 11 is configuring the SQL generator. The SQL generator is adapted to dynamically generate SQL statements. In some embodiments of the present invention, including those shown in FIG. 1, the SQL generator in step 12 is a query model based on a preset programming language. Illustratively, the predetermined programming language includes Java language, Python language, Scala language and/or Go language.

And step 12, receiving and analyzing a report query request from a user side through the SQL generator, and acquiring metadata information in the report query request.

Illustratively, in some embodiments of the present invention including fig. 1, the SQL generator involved in step 11 includes attribute fields in a common report, and when the reports are queried, the query request includes specific attribute values of the attribute fields, that is, the metadata information mentioned in step 12. The attribute fields in the SQL generator are assigned with the metadata information (attribute values of the attribute fields), so that SQL statements corresponding to each report query request can be generated.

In particular, in some embodiments of the present invention, the attribute field includes a dimension field, a metrics field, a filter field, a sort field, and/or a paging field.

For example, the dimension field and the measurement field are closely related to the substance of the report query request sent by the user terminal. For example, if a user wants to view the item fraction of the nervous system domain, the nervous system domain represents the dimension and the item fraction represents the metric. Further, the filter field may be a condition to be filtered by the user, for example, if the user wants to view some index information of the related items of a hospital, the name of the sponsor is represented by the filter field. The sort field typically corresponds to the order in which the user wants to view the data. For example, if the user wants to view detailed information of each item according to the item number sorting, the item number can be used as one of the sorting fields. Finally, the paging field may correspond to the amount of data that the user wants to view at one time in the report query request. For example, if the user wants to display only ten items of information per page and include the pairs in the report query request, the ten items of information displayed per page is the corresponding page information.

It can be seen that, with the solution of the present invention, the user side where the ordinary user (rather than the technician who needs to know the underlying logic of the database) is located is particularly faced, and with the solution of the present invention, the ordinary user only needs to simply set and input at the user side, and then can include the specific attribute values of the attribute fields such as the dimension field, the measurement field, the filter field, the sorting field, and/or the paging field, as metadata information, in the report query request, so as to be received, analyzed, and extracted by the SQL generator, and further perform the following operations.

Further, step 13 is to generate one or more SQL statements based on the metadata information by the SQL generator.

Illustratively, as described above, in some embodiments of the invention, including that of FIG. 1, the SQL generator is based on a query model in a pre-set programming language. In such an embodiment, the SQL generator-based report query method according to the present invention further includes parameter instantiation of such a query model with metadata information as input, so as to dynamically generate one or more SQL statements, which of course correspond to different report query requests from the user terminal. The SQL generator is configured into a query model based on a preset programming language, and parameters can be instantiated in the query model conveniently, so that one or more SQL statements based on metadata information can be obtained conveniently and quickly.

Finally, step 14 is to execute the report query request according to one or more SQL statements. Specifically, this step is an operation performed by the SQL execution engine in the database. For example, the type of the database may be a relational database, or a database supporting SQL query requests, and the like, which is not limited in the present invention.

Further exemplarily, in some embodiments of the present invention including that shown in fig. 1, after the step 13 is completed, the method further includes a step of returning the query result to the user side and generating a queried report. Therefore, the query result to be queried can be directly rendered at the user side, the query function of the report system is further improved, and the user experience is improved.

As shown in FIG. 2, another aspect of the present invention further provides a report query system 20 (hereinafter referred to as "query system 20") based on SQL generator. The query system 20 mainly includes an SQL generator 21 and an SQL execution engine 22.

The specific SQL generator 21 is configured to receive and parse a report query request from a user side to obtain metadata information in the report query request, and generate one or more SQL statements based on the metadata information. The SQL execution engine 22 is configured to execute the report query request according to one or more SQL statements.

For example, the query system 20 may use the query method 10 described above with reference to fig. 1, and further details regarding the query system 20 may refer to the query method 10 described above with reference to fig. 1, which are not described herein again.

In the prior art, a general scheme is that SQL statements corresponding to each report are generated in a database when the report is created. When a user sends a relatively complex report request during use, the background analyzes the SQL according to the filter selected by the user, the query dimension information and other information, and then operates the SQL abstract syntax tree to change the SQL content. For example, some means in the prior art are to parse SQL into an abstract syntax tree after obtaining a report query request, modify the abstract syntax tree, add information such as screening conditions to the abstract syntax tree, generate an SQL statement according to the modified abstract syntax tree, and finally query the SQL statement in a database to complete a query request of a user. The method is complicated and time-consuming, and needs to completely go through the processes of analyzing, changing the syntax tree and changing the SQL statement content once after receiving the user request every time, so that the workload is large, and errors are easy to occur.

In addition, in the prior art, there is also a scheme in which a plurality of target SQL models are set in advance, and a corresponding model is matched in the plurality of target SQL models according to each report query request from a user, thereby completing the request. Although the defects that the traditional SQL query is time-consuming and easy to make mistakes are overcome to a certain extent, the method still has certain limitations because all target SQL models cannot be enumerated. In addition, the technical requirements of the operators at the user end are high, the operators can accurately identify and find out the matched SQL model when each SQL query is executed by knowing the characteristics of each SQL model, and the method is not beneficial to expansion and use in the group of common users.

In contrast, the present invention specifically aims at the scenario of report query, and dynamically generates a corresponding SQL statement according to the metadata information extracted from the query request from the user side through a general SQL generator. As described above, the SQL generator can receive and parse important attribute fields (dimensions, metrics, filters, sort fields, paging information, etc.) in all reports. This implementation is transparent to the upper layer user (i.e., the ordinary user) who does not need to be concerned with the implementation logic of the lower layer (i.e., the database). In the report interface, a user only needs to execute simple operations such as dragging and pulling on the interface according to the original mode, and the SQL generator can automatically generate SQL query statements according to the information such as the extracted dimensions, metrics, filters and the like in the report query request generated by the dragging and pulling operation of the user. If the user changes the dimension, measurement, filter and other information when querying the report (for example, the drilling-down scene of the report is the dimension change, the scene of adding the filter condition is the filter change, etc.), the SQL generator can continuously and dynamically generate the corresponding SQL statement to complete the query only by identifying the changed values. Therefore, the change of the dimension, the measurement, the filter and the like of the user in the later period of using the report can be flexibly coped with, the query performance of the report is greatly improved, the error rate is reduced, and the experience of the user in querying the report is improved.

It can be appreciated that in the background of promoting enterprise digital transformation in all industries today, the user's need for report queries tends to be complex and comprehensive. When inquiring the report, the ordinary user generally wants to inquire the report quickly, can screen and view the report through conditions flexibly (including drilling down the query, namely, switching dimensions to view indexes in the report), and can modify the report content flexibly (adding some indexes or deleting some indexes which are not concerned). The report query method and system based on the SQL generator described above with reference to fig. 1 and 2 of the present invention effectively solve this problem by configuring the SQL generator that can dynamically generate the SQL statement. Specifically, through the SQL generator, the query link complexity of the report is greatly simplified, and the report query speed is doubled. The SQL generator analyzes the filter fields in the report query request and generates corresponding SQL sentences according to the metadata information, so that the condition query of the user can be flexibly supported. The SQL generator analyzes the dimension field in the report query request and generates a corresponding SQL statement according to the metadata information, so that the dimension switching query (such as drilling query) of a user can be supported very flexibly. In general, the SQL generator in the scheme can provide support for any complex and comprehensive report query request of a common user of the client, and has a good technical effect.

An embodiment of the present invention further provides a report query system 30 based on the SQL generator shown in fig. 3. According to FIG. 3, the SQL generator-based report query system 30 may include an internal communication bus 31, a Processor (Processor)32, a Read Only Memory (ROM)33, a Random Access Memory (RAM)34, and a communication port 35. When applied on a personal computer, the SQL generator-based report query system 30 may also include a hard disk 36.

The internal communication bus 31 may enable data communication between the components of the SQL generator-based statement query system 30. Processor 32 may make the determination and issue the prompt. In some embodiments, processor 32 may be comprised of one or more processors. The communication port 35 may enable the SQL generator-based report query system 30 to communicate with external data. In some embodiments, the SQL generator based report query system 30 can send and receive information and data from the network through the communication port 35.

The SQL generator-based statement querying system 30 may also include various forms of program storage units and data storage units, such as a hard disk 36, Read Only Memory (ROM)33 and Random Access Memory (RAM)34, capable of storing various data files used for computer processing and/or communications, as well as possible program instructions executed by the processor 32. The processor executes these instructions to implement the main parts of the method. The results processed by the processor are communicated to the user device through the communication port and displayed on the user interface.

In addition, another aspect of the present invention further provides a computer readable medium storing computer program code, which when executed by a processor implements the above-mentioned SQL generator-based report query method.

Having thus described the basic concept, it will be apparent to those skilled in the art that the foregoing disclosure is by way of example only, and is not intended to limit the present application. Various modifications, improvements and adaptations to the present application may occur to those skilled in the art, although not explicitly described herein. Such modifications, improvements and adaptations are proposed in the present application and thus fall within the spirit and scope of the exemplary embodiments of the present application.

Also, this application uses specific language to describe embodiments of the application. Reference throughout this specification to "one embodiment," "an embodiment," and/or "some embodiments" means that a particular feature, structure, or characteristic described in connection with at least one embodiment of the present application is included in at least one embodiment of the present application. Therefore, it is emphasized and should be appreciated that two or more references to "an embodiment" or "one embodiment" or "an alternative embodiment" in various places throughout this specification are not necessarily all referring to the same embodiment. Furthermore, some features, structures, or characteristics of one or more embodiments of the present application may be combined as appropriate.

Aspects of the present application may be embodied entirely in hardware, entirely in software (including firmware, resident software, micro-code, etc.) or in a combination of hardware and software. The above hardware or software may be referred to as "data block," module, "" engine, "" unit, "" component, "or" system. The processor may be one or more Application Specific Integrated Circuits (ASICs), Digital Signal Processors (DSPs), digital signal processing devices (DAPDs), Programmable Logic Devices (PLDs), Field Programmable Gate Arrays (FPGAs), processors, controllers, microcontrollers, microprocessors, or a combination thereof. Furthermore, aspects of the present application may be represented as a computer product, including computer readable program code, embodied in one or more computer readable media. For example, computer-readable media may include, but are not limited to, magnetic storage devices (e.g., hard disk, floppy disk, magnetic strips … …), optical disks (e.g., Compact Disk (CD), Digital Versatile Disk (DVD) … …), smart cards, and flash memory devices (e.g., card, stick, key drive … …).

The computer readable medium may comprise a propagated data signal with the computer program code embodied therein, for example, on a baseband or as part of a carrier wave. The propagated signal may take any of a variety of forms, including electromagnetic, optical, and the like, or any suitable combination. The computer readable medium can be any computer readable medium that can communicate, propagate, or transport the program for use by or in connection with an instruction execution system, apparatus, or device. Program code on a computer readable medium may be propagated over any suitable medium, including radio, electrical cable, fiber optic cable, radio frequency signals, or the like, or any combination of the preceding.

Similarly, it should be noted that in the preceding description of embodiments of the application, various features are sometimes grouped together in a single embodiment, figure, or description thereof for the purpose of streamlining the disclosure aiding in the understanding of one or more of the embodiments. This method of disclosure, however, is not intended to require more features than are expressly recited in the claims. Indeed, the embodiments may be characterized as having less than all of the features of a single embodiment disclosed above.

Numerals describing the number of components, attributes, etc. are used in some embodiments, it being understood that such numerals used in the description of the embodiments are modified in some instances by the use of the modifier "about", "approximately" or "substantially". Unless otherwise indicated, "about", "approximately" or "substantially" indicates that the number allows a variation of ± 20%. Accordingly, in some embodiments, the numerical parameters used in the specification and claims are approximations that may vary depending upon the desired properties of the individual embodiments. In some embodiments, the numerical parameter should take into account the specified significant digits and employ a general digit preserving approach. Notwithstanding that the numerical ranges and parameters setting forth the broad scope of the range are approximations, in the specific examples, such numerical values are set forth as precisely as possible within the scope of the application.

Although the present application has been described with reference to the present specific embodiments, it will be recognized by those skilled in the art that the foregoing embodiments are merely illustrative of the present application and that various changes and substitutions of equivalents may be made without departing from the spirit of the application, and therefore, it is intended that all changes and modifications to the above-described embodiments that come within the spirit of the application fall within the scope of the claims of the application.

Claims

1. A report query method based on an SQL generator is characterized by comprising the following steps:

configuring an SQL generator adapted to dynamically generate SQL statements;

2. The query method according to claim 1, wherein the SQL generator has attribute fields in a report, and the metadata information is attribute values of the attribute fields in the report corresponding to the report query request.

3. The query method of claim 2, wherein the attribute field comprises a dimension field, a metrics field, a filter field, a sort field, and/or a paging field.

4. The query method of claim 1, wherein said SQL generator is a query model based on a preset programming language.

5. The query method according to claim 4, wherein the predetermined programming language comprises Java language, Python language, Scala language and/or Go language.

6. The query method of claim 4 or 5, further comprising instantiating, by the SQL generator, the query model based on the metadata information prior to generating one or more SQL statements.

7. The query method of claim 1, further comprising returning query results to the user side and generating a queried report after executing the report query request.

8. A report query system based on SQL generator is characterized by comprising:

9. A SQL generator-based report query system, comprising:

a memory for storing instructions executable by the processor; and a processor for executing the instructions to implement the method of any one of claims 1-7.

10. A computer-readable medium having stored thereon computer program code which, when executed by a processor, implements the method of any of claims 1-7.