CN106250390B

CN106250390B - Method and device for automatically generating SQL step by step

Info

Publication number: CN106250390B
Application number: CN201610559533.2A
Authority: CN
Inventors: 郝建明; 张炼; 马平清; 王巍; 韩智东; 廉宜果
Original assignee: China Unionpay Co Ltd
Current assignee: China Unionpay Co Ltd
Priority date: 2016-07-15
Filing date: 2016-07-15
Publication date: 2019-12-13
Anticipated expiration: 2036-07-15
Also published as: CN106250390A

Abstract

the invention provides a method and a device for automatically generating an SQL (structured query language) step by step, wherein the method comprises the following steps: analyzing the indexes in the index analysis system to obtain basic elements required to be included by configuring a standard index; splitting the SQL script corresponding to the index, wherein the split result corresponds to the basic elements one by one; converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff; and carrying out configuration operation on the indexes on the page. According to the technical scheme, a tool for skipping developers and directly planning the service indexes is provided, and a link of communication between the service personnel and the technical personnel in the development process is omitted, so that the development cost is reduced, and the requirement accuracy is improved.

Description

Method and device for automatically generating SQL step by step

Technical Field

The invention relates to the technical field of structured query language processing, in particular to a method and a device for automatically generating an SQL (structured query language) step by step.

background

With the development of the informatization technology, the dependence degree of people on an automatic information system in daily work is higher and higher, data in a production operation flow is recorded while the automatic information system is used, the data are effectively organized and analyzed, a decision-making basis can be provided for a leader, and the production operation quality can be improved. In the case of a large amount of data, the organization and analysis of the data are generally automatically completed by an index analysis system.

Indexes in a traditional index analysis system are configured in advance by developers in a development stage according to business requirements. In the later period, no matter the indexes needing to be added, modified and deleted on the service need to be developed by service personnel, and then the development is realized by the development personnel.

Such an approach has the following disadvantages:

1. Long development period and lack of flexibility;

2. there may be a bias in the communication of business people with developers.

disclosure of Invention

In order to enable service personnel to newly add and modify an SQL script in an index analysis system without technical personnel, the invention provides a method and a device for automatically generating an SQL language step by step

In order to achieve the above object, the present invention provides a method for automatically generating SQL language step by step, which comprises:

analyzing the indexes in the index analysis system to obtain basic elements required to be included by configuring a standard index;

splitting the SQL script corresponding to the index, wherein the split result corresponds to the basic elements one by one;

Converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff;

and carrying out configuration operation on the indexes on the page.

optionally, in an embodiment of the present invention, the basic elements include: data sources, data source relationships, screening conditions, analysis dimensions, and statistical types.

optionally, in an embodiment of the present invention, the page in the form readable by the service staff includes: selecting a table, setting table association relation, screening data, setting dimension and detail and calculating results.

In order to achieve the above object, the present invention further provides a device for automatically generating SQL in steps, which includes:

The basic element acquisition unit is used for analyzing the indexes in the index analysis system to obtain basic elements required to be included by configuring one standard index;

the splitting unit is used for splitting the SQL script corresponding to the index, and the split results correspond to the basic elements one by one;

The conversion unit is used for converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff;

and the configuration unit is used for performing configuration operation on the indexes on the page.

Optionally, in an embodiment of the present invention, the basic elements acquired by the basic element acquiring unit include: data sources, data source relationships, screening conditions, analysis dimensions, and statistical types.

Optionally, in an embodiment of the present invention, the page in the form readable by the service staff acquired by the conversion unit includes: selecting a table, setting table association relation, screening data, setting dimension and detail and calculating results.

Compared with the traditional method, the technical scheme has the advantages that the effects and advantages are shown in the following aspects:

1. The new addition, modification and deletion of indexes by service personnel are realized, so that the service personnel can complete the work which needs technical personnel to complete originally.

2. the development cost is saved.

3. the risk that business personnel and developers are not communicated smoothly and therefore the requirement understanding is wrong is shielded.

Drawings

in order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

FIG. 1 is a flowchart of a method for step-by-step automatic SQL generation according to the present invention;

FIG. 2 is a schematic diagram of a pick list page obtained by the present invention;

FIG. 3 is a diagram illustrating table association obtained by the present invention;

FIG. 4 is a schematic diagram of data screening obtained by the present invention;

FIG. 5 is a schematic diagram of the dimension and detail setup obtained by the present invention;

FIG. 6 is a diagram illustrating the calculation results obtained by the present invention;

fig. 7 is a block diagram of an apparatus for automatically generating SQL step by step according to the present invention.

Detailed Description

The technical solution in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. It is to be understood that the described embodiments are merely exemplary of the invention, and not restrictive of the full scope of the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

the working principle of the technical scheme is as follows: indexes in the index analysis system correspond to a series of SQL scripts at the background of the system, the SQL scripts are split, SQL sentences, business tables, fields and the like are converted, and a page in a form readable by business personnel is obtained. The page provides a tool for business personnel to skip developers and directly plan business indexes, and saves the link of communication between the business personnel and technical personnel in the development process, thereby reducing the development cost and improving the requirement accuracy.

based on the working principle, the invention provides a method for automatically generating SQL step by step. As shown in fig. 1, includes:

Step 101): analyzing the indexes in the index analysis system to obtain basic elements required to be included by configuring a standard index;

In this step, a plurality of types of indexes are analyzed to obtain basic elements required to be included in the index configuring one standard. Wherein the obtained basic elements include: data sources, data source relationships, screening conditions, analysis dimensions, and statistical types. Please refer to Table 1 below.

TABLE 1

step 102): splitting the SQL script corresponding to the index, wherein the split result corresponds to the basic elements one by one;

each index corresponds to a series of sql scripts in the background of the index analysis system, the sql scripts comprise deletion, insertion and query of data, and split objects are sql query statements. The sql query statement for one index is such that:

select count(id)

from table1t1

left join table2t2

on t1.id＝t2.id

where t1.department＝’A’

and t2.time>＝’2016-01-01 00:00:00’

and t2.time<’2016-02-01 00:00:00’

group by t2.sort,t1.name；

splitting the sql query statement in the sql script in a one-to-one correspondence manner with the basic elements, as shown in table 2.

TABLE 2

Step 103): converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff;

step 101 obtains basic elements of the index, and step 102 splits the sql statement of the index in a one-to-one correspondence manner with the basic elements, so that the index can be planned step by step, but the index is not available for direct use by service personnel. Step 103, converting the split result and the index in background data (such as a service table, a field and the like) of the index analysis system to obtain a page in a form readable by a service person. As shown in fig. 2, 3, 4, 5, and 6.

Step 104): and carrying out configuration operation on the indexes on the page.

As can be seen from fig. 2 to fig. 6, the page in a form readable by the service person is a tool for providing the service person with a direct plan of the service index by skipping the developer. In the later period, no matter the indexes needing to be added, modified and deleted on the service are realized by directly skipping developers by service personnel according to the development requirements.

it should be noted that while the operations of the method of the present invention are depicted in the drawings in a particular order, this does not require or imply that the operations must be performed in this particular order, or that all of the illustrated operations must be performed, to achieve desirable results. Additionally or alternatively, certain steps may be omitted, multiple steps combined into one step execution, and/or one step broken down into multiple step executions.

having described the method of the exemplary embodiment of the present invention, the apparatus of the exemplary embodiment of the present invention is next described with reference to fig. 7.

Fig. 7 is a block diagram of an apparatus for automatically generating SQL step by step according to the present invention. The method comprises the following steps:

a basic element obtaining unit 701, configured to analyze an index in an index analysis system, and obtain a basic element that needs to be included in an index configured with a standard;

A splitting unit 702, configured to split the SQL script corresponding to the index, where the split result corresponds to the basic element one to one;

a conversion unit 703, configured to convert the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff;

A configuration unit 704, configured to perform a configuration operation on the index on the page.

furthermore, although several units of the apparatus are mentioned in the above detailed description, such division is not mandatory only. Indeed, the features and functions of two or more of the units described above may be embodied in one unit, according to embodiments of the invention. Also, the features and functions of one unit described above may be further divided into embodiments by a plurality of units.

finally, it should be noted that: the above description is only for the purpose of illustrating the present invention and is not meant to limit the technical solutions described in the present invention; although the present invention has been described in detail in the specification, those skilled in the art can make modifications and equivalents of the present invention, and all technical solutions and modifications thereof without departing from the spirit and scope of the present invention should be covered by the claims of the present invention.

Claims

1. a method for distributing and automatically generating SQL is characterized by comprising the following steps:

Wherein the basic elements include: data sources, data source relationships, screening conditions, analysis dimensions and statistical types;

converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff; the business person readable form page includes: selecting a table, setting table association relation, screening data, setting dimension and detail and calculating results;

And carrying out configuration operation on the indexes on the page.

2. An apparatus for distributing and automatically generating SQL, comprising:

The conversion unit is used for converting the split result and the background data of the index in the index analysis system into a page in a form readable by a service staff; the business person readable form page includes: selecting a table, setting table association relation, screening data, setting dimension and detail and calculating results;