CN113434130A - SQL automatic generation method and device - Google Patents
SQL automatic generation method and device Download PDFInfo
- Publication number
- CN113434130A CN113434130A CN202110713821.XA CN202110713821A CN113434130A CN 113434130 A CN113434130 A CN 113434130A CN 202110713821 A CN202110713821 A CN 202110713821A CN 113434130 A CN113434130 A CN 113434130A
- Authority
- CN
- China
- Prior art keywords
- canvas area
- operation instruction
- user
- functional
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/34—Graphical or visual programming
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a method and a device for automatically generating SQL, which relate to the field of data processing and comprise the following steps: the method comprises the steps of displaying a first interface, generating a first operation instruction representing that a user selects and moves original data from a non-canvas area of the first interface to the canvas area of the first interface and a second operation instruction representing that the user selects and moves a function component from the non-canvas area to the canvas area, responding to the operation of the user on the first operation instruction and the second operation instruction, calling an automatic generation program according to the selected original data and the selected function component in the canvas area, and automatically generating SQL statement information. The method provided by the application can achieve the purposes of reducing the use threshold of the user on SQL, reducing the learning cost of the user and improving the data acquisition timeliness and the utilization rate.
Description
Technical Field
The application belongs to the field of data processing, and particularly relates to an SQL automatic generation method and device.
Background
The SQL language is a necessary skill for using a database, and can realize functions of building a database, building a table, performing addition, deletion, modification and check on data and the like, performing data analysis and the like. However, when the database is used, the SQL language needs to be written by IT professionals who know the database, which has a high threshold, and thus, the learning and use cost of non-IT professional users is increased.
Therefore, when a non-IT professional user wants to query data or analyze data by using a database, in the prior art, the non-IT professional user can only inform an IT professional such as development of the requirement, and the IT professional writes an SQL language according to the requirement and then sends an operation result to the non-IT professional user.
In the above process, there may be problems that IT professionals do not understand the requirements in place, the communication time between both parties is long, the delivery time of the operation result is long, and the like, so that the requirements of non-IT professional users on data cannot be met quickly.
Disclosure of Invention
The embodiment of the application provides an automatic SQL generation method, which solves the problem that a non-IT professional user cannot process data according to needs by using an SQL language, and can achieve the purposes of reducing the use threshold of the user, reducing the learning cost of the user and improving the data acquisition timeliness and the utilization rate.
In order to achieve the purpose, the technical scheme is as follows:
in a first aspect, a method for automatically generating SQL is provided, where the method includes: displaying a first interface, and generating a first operation instruction representing that a user selects and moves original data from a non-canvas area of the first interface to a canvas area of the first interface and a second operation instruction representing that the user selects and moves a function component from the non-canvas area to the canvas area; wherein the non-canvas area is used to display the raw data and the functional components;
and responding to the operation of the user on the first operation instruction and the second operation instruction, and calling an automatic generation program according to the selected original data and the selected functional components in the canvas area to automatically generate SQL statement information.
In the method for automatically generating the SQL provided by the first aspect, the original data and the functional components are visually presented to the user, so that the user can arbitrarily select and combine the original data and the functional components according to the requirements, and then, the SQL statement information meeting the requirements of the user can be generated by automatically generating a program. In the method, a user does not need to know SQL grammar specifications and specific use methods of various databases, and can analyze data and acquire required data through simple operation, so that the use threshold of the user is greatly reduced.
In a possible implementation manner of the first aspect, when the original data includes at least one database of Oracle, MySQL, PostgreSQL, SQL Server, Hadoop, hive, and each of the databases includes a plurality of data tables; the generating of the first operation instruction representing that a user selects and moves original data from a non-canvas area of the first interface to a canvas area of the first interface comprises: generating a first operation instruction representing that the user selects and moves one or more databases from the non-canvas area to the canvas area; alternatively, a first operation instruction is generated to represent that the user selects and moves one or more data tables from the non-canvas area to the canvas area. In the implementation mode, all users perform visual operation, the operation is simple and convenient, and deep knowledge of the database is not needed.
In a possible implementation manner of the first aspect, when the functional component includes at least one of data input, data output, field selection, adding of a constant, type conversion, date operation, text processing, field merging, data calculation, numerical value replacement, data filtering, mathematical function, and grouping aggregation; the generating second operation instructions representing the user selecting and moving a function component from the non-canvas area into the canvas area comprises: generating a second operation instruction representing that the user selects and moves one or more functional components from the non-canvas area to the canvas area.
In a possible implementation manner of the first aspect, when the functional component includes a plurality of functional units, and the functional unit includes a plurality of sub-functional units, each sub-functional unit corresponds to a different functional function for different databases; the generating a second operation instruction representing that the user selects and moves one or more of the function single components from the non-canvas area to the canvas area comprises: generating a second operation instruction representing that the user selects and moves one or more functional units from the non-canvas area to the canvas area; or generating a second operation instruction which represents that the user selects and moves one or more sub-function units from the non-canvas area to the canvas area.
In the implementation process, a user does not need to know how the where condition is written, how the time type is converted from the character type, how the computation function is used for statistical analysis and other relatively professional SQL usage, and the user sets processing steps such as filtering and query conditions only through very simple visual operations such as dragging and the like, so that the implementation process is simple to use and easy to understand.
In a possible implementation manner of the first aspect, when the functional component is a type conversion, the type conversion includes at least one of the functional units of text conversion, integer conversion, decimal conversion, date conversion, and time conversion, and the date conversion includes a string date conversion as the sub-functional unit.
In a possible implementation manner of the first aspect, when the selected sub-functional unit is a string date, and the selected database is Oracle, a function corresponding to the string date is to _ date; and when the selected database is MySQL, the function corresponding to the character string conversion date is date _ format.
In this implementation manner, the specific usage of the function may be obtained according to the field type, the data table to which the field belongs, the database described in the data table, the type of the database, the functional component, and other information, and then, the SQL language information may be generated by assembly. The method has no limitation on the types of the databases, can realize the full coverage of the databases, is a universal method, and has wide application range. In addition, a user does not need to learn SQL language specifications, the learning cost is low, a front-line business worker can analyze and explore data through simple visual operation, the data acquisition timeliness and the data utilization rate are improved, and the data bring value promotion to business.
In a possible implementation manner of the first aspect, the method further includes: generating a third operation instruction which represents that the user sorts the sequence of all or part of the selected functional components in the drawing area; and responding to the operation of the user on the first operation instruction, the second operation instruction and the third operation instruction, calling the automatic generation program according to all or part of the original data selected in the canvas area and the selected and sequenced functional components, and automatically generating SQL statement information.
In the implementation mode, a user can adjust the selected content according to needs, and when a plurality of functional components, a plurality of functional units or a plurality of sub-functional units are selected, sequential adjustment can be performed according to needs, implementation rules are set, and the method is simple and convenient.
In a second aspect, an apparatus for automatically generating SQL is provided, which includes:
the display unit is used for displaying a first interface; wherein the first interface comprises a canvas area and a non-canvas area; the non-canvas area is used for displaying original data and functional components;
a generating unit, configured to generate a first operation instruction representing that a user selects and moves the original data from a non-canvas area of the first interface to a canvas area of the first interface, and a second operation instruction representing that the user selects and moves the functional component from the non-canvas area to the canvas area;
the generating unit is further configured to respond to the operation of the user on the first operation instruction and the second operation instruction, and invoke an automatic generating program according to the selected original data and the selected functional component in the canvas area, so as to automatically generate SQL statement information.
In a third aspect, an electronic device is provided, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and when the processor executes the computer program, the processor implements the SQL automatic generation method according to the first aspect or any possible implementation manner of the first aspect.
In a fourth aspect, a computer-readable storage medium is provided, where a computer program or instructions are stored, and when the computer program or instructions are read and executed by a computer, the computer is caused to execute the SQL automatic generation method according to the first aspect or any possible implementation manner of the first aspect.
The advantageous effects of the second aspect, the third aspect and the fourth aspect can refer to the advantageous effects of the first aspect, and are not described herein again.
Drawings
FIG. 1 is a schematic diagram of an example of a scenario suitable for use in embodiments of the present application;
FIG. 2 is a schematic view of a first interface provided by an embodiment of the present application;
fig. 3 is a schematic flowchart of an SQL automatic generation method according to an embodiment of the present application;
fig. 4 is a schematic flowchart of another SQL automatic generation method provided in the embodiment of the present application;
fig. 5 is a schematic structural diagram of an SQL automatic generation apparatus according to an embodiment of the present application.
Reference numerals:
10-an electronic device; 11-a display screen; 110 — a first interface; 111-canvas area; 112-non-canvas area; 120-raw data; 130-a functional component; 20-server.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
In the description of the embodiments of the present application, "/" data means or, unless otherwise stated, for example, a/B may data means a or B; "and/or" herein is merely an association describing an associated object, and the data representation may have three relationships, e.g., a and/or B, and may be represented by: a exists alone, A and B exist simultaneously, and B exists alone.
In the following, the terms "first", "second" are used for descriptive purposes only and are not to be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of the present embodiment, "a plurality" means two or more unless otherwise specified.
The word "comprising" or "comprises", and the like, means that the element or item preceding the word covers the element or item listed after the word and its equivalents, but does not exclude other elements or items.
Because the kinds of databases are various, each database has respective syntax specification, so that the written SQL language needs to meet the syntax specification of the corresponding database, if the SQL language which does not meet the specification is written, an error is reported, functions of querying and extracting data cannot be realized, and the like.
Because each corresponding syntax specification of each database has a certain difference, when a plurality of databases need to be used in a cross mode, the written SQL language needs to correspondingly meet the syntax specifications of different databases according to the use condition, so that the databases can be used by professional IT personnel who are skilled in mastering the SQL language specifications of each database, and other non-IT professional personnel have no capability to use the databases.
Therefore, in the prior art, when a non-IT professional user wants to use a database to query data or perform data analysis, the non-IT professional user only needs to inform an IT professional of the requirement, and the IT professional writes SQL language according to the requirement and then sends an operation result to the non-IT professional user.
In the process, the problems that the IT professional does not understand the requirements in place, the two parties communicate with each other for a long time, the running result delivery time is long and the like exist, so that the requirements of non-IT professional users on data cannot be met quickly.
In view of this, the embodiment of the present application provides an automatic SQL generation method, which visually presents original data such as a database and a function that can implement different functions to a user, so that the user can arbitrarily select and combine according to needs, and then, SQL statement information that meets the needs of the user can be generated by automatically generating a program. In the method, the realization mode of generating the SQL statement information meeting the user requirements is simple, the writing of IT professionals is not needed, and the use threshold of the user is reduced.
The following describes in detail an SQL automatic generation method provided in an embodiment of the present application with reference to the drawings.
Fig. 1 is a schematic diagram of an example of a scenario applicable to the embodiment of the present application. As shown in fig. 1, in the system, an electronic device 10 and a server 20 are included, which are connected. Wherein, a display screen 11 is configured on the electronic device 10.
For example, in an embodiment of the present application, an electronic device may include: the mobile terminal includes a smart phone, a smart television, a large screen device, a tablet computer, a Personal Digital Assistant (PDA), a computer handheld communication device, a notebook computer, an ultra-mobile personal computer (UMPC), a handheld computer, a netbook, and the like.
In the example shown in FIG. 1, as shown in FIG. 2, a first interface 110 is displayed on the display screen 11 on the electronic device 10, the first interface 110 including a canvas area 111 and a non-canvas area 112. The non-canvas area 112 is used to display raw data and functional components. It should be appreciated that when the user is not operating, the canvas area is blank and does not display any content.
When operated by the user, the electronic device 10 generates a first operation instruction representing the user selecting and moving the original data from the non-canvas area 112 of the first interface 110 to the canvas area 111 of the first interface 110 and a second operation instruction representing the user selecting and moving the functional component from the non-canvas area 112 to the canvas area 111.
That is, the first interface may acquire 1 or more first operation instructions and 1 or more second operation instructions. Wherein the 1 or more first operation instructions represent: the user selects and moves all or part of the raw data from the non-canvas area into the canvas area. The 1 or more second operation instructions represent: the user selects and moves all or a portion of the functional components from the non-canvas area into the canvas area.
It should be appreciated that moving the selected content from the non-canvas area to the canvas area by the user is equivalent to a simple operation of dragging, pulling, dragging, etc. the selected content on the first interface 110. The position, direction and sequence of the specific movement can be selected according to the needs, and the embodiment of the present application does not limit this.
It should be understood that when the display screen is not a touch screen, the user can input the first operation instruction and the second operation instruction by means of a mouse, voice or the like; when the display screen is a touch screen, a user can directly click, drag, pull, drag and the like on the display screen through fingers.
Based on this, the electronic device 10 calls the automatic generation program to automatically generate the SQL statement information according to the original data selected in the canvas area and the selected functional component in response to the operation of the user for the first operation instruction and the second operation instruction.
It should be understood that, in response to 1 or more first operation instructions and 1 or more second operation instructions, according to the selected raw data and the selected functional component in the canvas area, the automatic generation program is invoked, and the SQL statement information is automatically generated, and may be executed by the electronic device, the server, the electronic device and the server, which are not limited in any way by the embodiments of the present application.
In one possible application scenario, a user wants to perform data analysis (e.g., analyzing loan and loan data of a bank) on a notebook computer, the user uses the notebook computer to perform operations, the server responds to the operations of the user, calls an automatic generation program, automatically generates SQL statement information, and then provides the operation result to the user through the notebook computer for use. In the process, the SQL automatic generation method provided by the application can be used.
Fig. 3 is a schematic flow chart of an SQL automatic generation method provided by the present application in the scenario shown in fig. 1. In the flow shown in fig. 3, the display screen displays the first interface 110, and the user performs an operation on the display screen 11 of the electronic device.
As shown in fig. 3, an embodiment of the present application provides an SQL automatic generation method, where the method 100 includes the following S110 to S120.
S110, displaying the first interface, and generating a first operation instruction for representing that a user selects and moves original data from the non-canvas area of the first interface to the canvas area of the first interface and a second operation instruction for representing that the user selects and moves a function component from the non-canvas area to the canvas area.
The first interface comprises a canvas area and a canvas area, and the non-canvas area is used for displaying original data and functional components. The first operation instruction represents: the user selects and moves all or part of the raw data from the non-canvas area into the canvas. That is, the whole of the original data is selected and moved, or a part of the original data is selected and moved as necessary.
The second operation instruction represents: the user selects and moves all or a portion of the functional components from the non-canvas area into the canvas area. That is, all of the functional components are selected and moved, or several of the functional components are selected and moved as necessary.
The specific selection and movement manner can be set and changed according to needs, and the embodiment of the present application does not limit this. For example, the raw data and the functional components can be selected and moved from the non-canvas area to the canvas area respectively by performing visual operations such as dragging, pulling, and dragging.
And S120, responding to the operation of the user on the first operation instruction and the second operation instruction, calling an automatic generation program according to the original data selected in the canvas area and the selected functional component, and automatically generating SQL statement information.
It should be understood that the automatic generation program may be set and changed as needed, and the embodiment of the present application does not set any limitation to this.
It should be understood that the generated SQL language information includes a plurality of SQL statements for implementing corresponding functions of the selected functional components on the selected raw data.
After the SQL statement information is generated, the user can perform query operations in the database as needed to obtain data needed by the user, or perform data analysis processing on the data in the database.
The embodiment of the application provides an SQL automatic generation method, which is characterized in that original data and functional components are visually presented to a user, so that the user can randomly select and combine the original data and the functional components according to requirements, and then SQL statement information meeting the requirements of the user can be generated through an automatic generation program. In the method, a user does not need to know SQL grammar specifications and specific use methods of various databases, and can analyze data and acquire required data through simple operation, so that the use threshold of the user is greatly reduced.
Optionally, as a possible implementation manner, when the original data includes at least one database of Oracle, MySQL, PostgreSQL, SQL Server, Hadoop, and hive, and each database includes a plurality of data tables, in the above S110, generating a first operation instruction indicating that the user selects and moves the original data from the non-canvas area of the first interface to the canvas area of the first interface includes:
generating a first operation instruction representing that a user selects and moves one or more databases from the non-canvas area to the canvas area;
alternatively, a first operational instruction is generated that indicates that the user selected and moved one or more data tables from the non-canvas region to the canvas region.
That is, each first operation instruction may represent: the user selects and moves from the non-canvas area to one or more databases or selects one or more data tables into the canvas area.
It should be understood that the number and content of the databases and the data tables may be set and changed as needed, and the embodiment of the present application does not set any limitation.
It should be understood that the various databases and the multiple data tables may be respectively presented in the non-canvas area in a manner of text, link, list, etc., or the original data may be distributed in the non-canvas area for the option control, the multiple databases are presented as multiple sub-options of the option control of the original data, and the multiple data tables may be presented as multiple sub-options of the corresponding databases.
Accordingly, the user may drag all or a portion of the original data in the non-canvas area into the canvas area, or the user may select all or a portion of the data in the original data option control located in the non-canvas area and drag the selected option into the canvas area, or the user may drag the original data option control located in the non-canvas area into the canvas area and then select the database or the data table from the sub-options.
When a user needs to use multiple databases, the user may perform multiple dragging, that is, input multiple first operation instructions (for example, drag multiple databases from a non-canvas area to a canvas area, drag different databases each time), and then use the multiple databases selected in the canvas area as information for data processing. In the implementation process, all users carry out visual operation, the operation is simple and convenient, and deep knowledge of the database is not needed.
Optionally, when the functional component includes at least one of data input, data output, field selection, constant addition, type conversion, date operation, text processing, field merging, data calculation, numerical value replacement, data filtering, mathematical function, and grouping aggregation, in S110, generating a second operation instruction representing that the user selects and moves the functional component from the non-canvas area to the canvas area includes:
a second operation instruction is generated that represents the user selecting and moving one or more functional components from the non-canvas area into the canvas area.
That is, each second operation instruction may represent: the user selects and moves one or more functional components from the non-canvas area into the canvas area.
According to the selection of the user, the association of the original data selected by the first operation instruction and the functional component selected by the second operation instruction can be established. For example, an arrow is also displayed in the non-layout area, and the user can drag the arrow from the non-layout area to the selected original data and the selected functional component as required to represent the association between the selected original data and the selected functional component.
When a plurality of first operation instructions are generated, if a user selects the same database a plurality of times, the user may associate different functional components indicated by second operation instructions with the same database a plurality of times.
When a plurality of first operation commands are generated, if a user selects a plurality of different databases, the functional components indicated by the same second operation command may be associated with the plurality of different databases, or the functional components indicated by different second operation commands may be associated with the plurality of different databases, or the functions of the functional components indicated by different second operation commands may be performed in a cross manner by associating the plurality of databases. The embodiments of the present application do not set any limit to this.
On the basis, different functional components can be associated with different fields aiming at the fields in each data table, so that data meeting different conditions can be acquired.
Optionally, as a possible implementation manner, when the functional component includes a plurality of functional units, and the functional unit includes a plurality of sub-functional units, each sub-functional unit corresponds to a different functional function for different databases. At this time, in S110, generating a second operation instruction indicating that the user selects and moves one or more function components from the non-canvas area to the canvas area includes:
generating a second operation instruction representing that the user selects and moves one or more functional units from the non-canvas area to the canvas area;
alternatively, a second operation instruction representing that the user selects and moves one or more sub-function units from the non-canvas area to the canvas area is generated.
That is, each second operation instruction may represent: the user selects and moves one or more functional units, or one or more sub-functional units, from the non-canvas area into the canvas area.
It should be understood that a sub-functional unit is used for implementing a functional process on data information of a database and the like, but when the databases are different, even if the same sub-functional unit is arranged, the sub-functional unit can be implemented by using different functional functions according to the databases so as to adapt to the SQL syntax specification of the databases.
It should be understood that the functional components, the functional units, the number of sub-functional units, and the implemented functions may be arranged and modified as required, and the embodiments of the present application do not limit this.
It should be understood that the plurality of functional components, the plurality of functional units, and the plurality of sub-functional units may be respectively presented in the non-canvas area in a manner of text, link, list, or the like, or the functional components may be distributed in the non-canvas area for the option control, the plurality of functional units are presented as a plurality of sub-options of this option space of the functional component, and the plurality of sub-functional units are presented as a plurality of sub-options of the corresponding functional unit.
Correspondingly, the user can drag all or part of the functional components in the non-canvas area into the canvas area, or the user can select all or part of the functional units or sub-functional units in the functional component option control positioned in the non-canvas area and drag the selected options into the canvas area, or the user can drag the functional component option control positioned in the non-canvas area into the canvas area and then select the functional units or sub-functional units from the sub-options.
When the user needs to use a plurality of functional components, the user may drag the functional components for a plurality of times, that is, input a second operation instruction for a plurality of times (for example, drag a plurality of functional components from the non-canvas area to the canvas area, drag a different functional component each time), and then use the plurality of functional components selected in the canvas area as a step of processing the database. In the implementation process, a user does not need to know how the where condition is written, how the time type is converted from the character type, how the computation function is used for statistical analysis and other relatively professional SQL usage, and the user sets processing steps such as filtering and query conditions only through very simple visual operations such as dragging and the like, so that the implementation process is simple to use and easy to understand.
Optionally, when the functional component is a type conversion, the type conversion includes at least one functional unit of text conversion, integer conversion, decimal conversion, date conversion, and time conversion, and the date conversion includes a character string date conversion as a sub-functional unit. Of course, the transition date may also include other sub-functional units, which is not limited in any way by the embodiment of the present application.
Here, the background may add a type restriction to data that can implement type conversion, and not support conversion that does not satisfy conversion requirements and does not meet conversion specifications. The specific type of the limitation condition can be set and changed according to the need, and the embodiment of the present application does not set any limitation.
Optionally, in the case that the selected sub-function unit is a string date, when the selected database is Oracle, the function corresponding to the string date is to _ date; when the selected database is MySQL, the function corresponding to the string-to-date is date _ format.
The text conversion example comprises two sub-functional units of digit-text conversion and date-text conversion. Under the condition that the selected sub-functional units are digital-to-text, when the selected databases are different, the SQL grammar specifications of the digital-to-text corresponding to the different databases correspond to different functional functions. And under the condition that the selected sub-functional units are date-to-text, when the selected databases are different, the SQL grammar specifications of the different databases corresponding to the date-to-text correspond to different functional functions. The specific function can be set and changed as required, and the embodiment of the present application does not set any limitation to this.
It should be appreciated that a list of functional components may be maintained in a configuration library on the server corresponding to the various visual functional components on the first interface. Such as data input, data output, field selection, adding constants, type conversion, date manipulation, text processing, field merging, data computation, numerical substitution, data filtering, mathematical functions, grouping aggregation, and the like.
Then, the configuration library is also configured with an associated functional unit table and a sub-functional unit table, which correspond to various visual functional units and sub-functional units on the first interface. For example, the function component is type conversion, and the corresponding function unit table includes sub-function units of text conversion, integer conversion, decimal conversion, date conversion, time conversion, and the like.
The sub-function unit table in the configuration library is used for storing the names of various function functions so as to call the function corresponding to the database to realize the specific use of various operations required by the user. For example, the function corresponding to the string-to-date includes to _ date and date _ format, etc.
The configuration library may be set and changed as needed, and the embodiment of the present application does not limit this. The configuration library is clear in classification and easy to adjust and maintain.
Here, the configuration library may further configure a switch for each of the functional components, the functional units, the sub-functional units, and the functional functions, and open the switch as needed for the user to use. When a certain function has a problem, the switch can be closed, so that a user can not use the switch, and flexible control is realized.
In addition, the function called in the configuration library supports the user to perform custom setting according to the requirement, and can be directly called when the user wants to use, so that the efficiency is improved.
Based on this, when the first interface acquires the first operation instruction and the second operation instruction performed by the user, the automatic generation program may automatically match a function meeting the corresponding SQL language specification according to the database, the data table, or the field selected by the user and the selected functional component, the functional unit, or the sub-functional unit, and call the function, and then generate the SQL statement information.
In the implementation process, the specific usage of the function can be obtained according to the field type, the data table to which the field belongs, the database described in the data table, the type of the database, the functional component and other information, and then, the SQL language information is generated by assembly. The method has no limitation on the types of the databases, can realize the full coverage of the databases, is a universal method, and has wide application range. In addition, a user does not need to learn SQL language specifications, the learning cost is low, a front-line business worker can analyze and explore data through simple visual operation, the data acquisition timeliness and the data utilization rate are improved, and the data bring value promotion to business.
Fig. 4 provides a schematic flow chart of another SQL automatic generation method, as shown in fig. 4, after S110, the method may further include:
and S111, generating a third operation instruction which represents that the user sorts the sequence of all or part of the selected functional components in the canvas area.
That is, the third operation instruction may represent: the user orders the order of all or a portion of the selected functional components in the canvas area.
At this time, S120 can be expressed as: and responding to the operation of the user on the first operation instruction, the second operation instruction and the third operation instruction, calling an automatic generation program according to all or part of the original data selected in the canvas area and the selected and sequenced functional components, and automatically generating the SQL statement information.
In the implementation mode, a user can adjust the selected content according to needs, and when a plurality of functional components, a plurality of functional units or a plurality of sub-functional units are selected, sequential adjustment can be performed according to needs, implementation rules are set, and the method is simple and convenient.
It should be understood that the above description is only for the purpose of helping those skilled in the art better understand the embodiments of the present application, and is not intended to limit the scope of the embodiments of the present application. It will be apparent to those skilled in the art from the foregoing examples that various equivalent modifications or changes may be made, for example, some steps in the above-described methods may not be necessary, or some steps may be newly added, etc. Or a combination of any two or more of the above embodiments. Such modifications, variations, or combinations are also within the scope of the embodiments of the present application.
It should also be understood that the manner, the case, the category, and the division of the embodiments are only for convenience of description and should not be construed as a particular limitation, and features in various manners, the category, the case, and the embodiments may be combined without contradiction.
It should also be understood that the various numerical references referred to in the examples of the present application are merely for ease of description and distinction and are not intended to limit the scope of the examples of the present application. The sequence numbers of the above processes do not mean the execution sequence, and the execution sequence of each process should be determined by its function and inherent logic, and should not be limited in any way to the implementation process of the embodiments of the present application.
It should also be understood that the foregoing descriptions of the embodiments of the present application focus on highlighting differences between the various embodiments, and that the same or similar elements that are not mentioned may be referred to one another and, for brevity, are not repeated herein.
An embodiment of the present application further provides an apparatus for automatically generating SQL, as shown in fig. 5, including:
a display unit 510 for displaying a first interface; wherein the first interface comprises a canvas area and a non-canvas area; the non-canvas area is used to display raw data and a plurality of functional components.
The generating unit 520 is configured to generate a first operation instruction indicating that the user selects and moves the original data from the non-canvas area of the first interface to the canvas area of the first interface and a second operation instruction indicating that the user selects and moves the function component from the non-canvas area to the canvas area.
The generating unit 520 is further configured to respond to the operation of the user on the first operation instruction and the second operation instruction, and invoke an automatic generating program according to the original data selected in the canvas area and the selected functional component, so as to automatically generate SQL statement information.
The beneficial effects of the SQL automatic generation device provided by the embodiment of the application are the same as the beneficial effects corresponding to the SQL automatic generation method, and are not described herein again.
Optionally, when the raw data comprises at least one database of Oracle, MySQL, PostgreSQL, SQL Server, Hadoop, hive, each database comprising a plurality of data tables;
a generating unit 520, further configured to generate a first operation instruction representing that a user selects and moves one or more databases from the non-canvas area to the canvas area; alternatively, a first operational instruction is generated that indicates that the user selected and moved one or more data tables from the non-canvas region to the canvas region.
That is, each first operation instruction may represent: the user selects and moves one or more databases, or one or more data tables, from the non-canvas area into the canvas area.
Optionally, when the functional components include at least one of data input, data output, field selection, adding constants, type conversion, date manipulation, text processing, field merging, data calculation, numerical value replacement, data filtering, mathematical functions, grouping aggregation,
and the generating unit 520 is further used for generating a second operation instruction which represents that the user selects and moves one or more functional components from the non-canvas area to the canvas area.
That is, each second operation instruction may further represent: the user selects and moves one or more functional components from the non-canvas area into the canvas area.
Optionally, when the functional assembly comprises a plurality of functional units, the functional units comprise a plurality of sub-functional units; aiming at different databases, each sub-functional unit corresponds to different functional functions;
the generating unit 520 is further used for generating a second operation instruction which represents that the user selects and moves one or more functional units from the non-canvas area to the canvas area;
alternatively, a second operation instruction representing that the user selects and moves one or more sub-function units from the non-canvas area to the canvas area is generated.
That is, each second operation instruction may further represent: the user selects and moves one or more functional units, or one or more sub-functional units, from the non-canvas area into the canvas area.
Optionally, when the functional component is a type conversion, the type conversion includes at least one functional unit of text conversion, integer conversion, decimal conversion, date conversion, and time conversion, and the date conversion includes a character string date conversion as a sub functional unit.
Optionally, in the case that the selected sub-function unit is a string date, when the selected database is Oracle, the function corresponding to the string date is to _ date; when the selected database is MySQL, the function corresponding to the string-to-date is date _ format.
Optionally, the generating unit 520 is further configured to generate a third operation instruction indicating that the user sorts the order of all or part of the selected functional components in the canvas area.
That is, the third operation instruction may represent: the user orders the order of all or a portion of the selected functional components in the canvas area.
At this time, the generating unit 520 is further configured to, in response to the user's operation on the first operation instruction, the second operation instruction, and the third operation instruction, invoke the automatic generating program according to all or part of the original data selected in the canvas area and the selected and sorted functional components, and automatically generate the SQL statement information.
The embodiment of the present application further provides an electronic device, which includes a memory, a processor, and a computer program that is stored in the memory and can be run on the processor, and when the processor executes the computer program, the SQL automatic generation method described above is implemented.
The beneficial effects of the electronic device provided by the embodiment of the application are the same as the corresponding beneficial effects of the automatic SQL generation method, and are not repeated herein.
The embodiment of the present application further provides a computer-readable storage medium, where a computer program or an instruction is stored in the computer-readable storage medium, and when the computer program or the instruction is read and executed by a computer, the computer is enabled to execute the above SQL automatic generation method.
The beneficial effects of the computer-readable storage medium provided by the embodiment of the application are the same as the beneficial effects corresponding to the SQL automatic generation method, and are not described herein again.
The above-mentioned embodiments are only used for illustrating the technical solutions of the present application, and not for limiting the same; although the present application has been described in detail with reference to the foregoing embodiments, it should be understood by those of ordinary skill in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some technical features may be equivalently replaced; such modifications and substitutions do not cause the essential features of the corresponding technical solutions to depart from the spirit scope of the technical solutions of the embodiments of the present application, and are intended to be included within the scope of the present application.
Claims (10)
1. An SQL automatic generation method is characterized by comprising the following steps:
displaying a first interface, and generating a first operation instruction representing that a user selects and moves original data from a non-canvas area of the first interface to a canvas area of the first interface and a second operation instruction representing that the user selects and moves a function component from the non-canvas area to the canvas area; wherein the non-canvas area is used to display the raw data and the functional components;
and responding to the operation of the user on the first operation instruction and the second operation instruction, and calling an automatic generation program according to the selected original data and the selected functional components in the canvas area to automatically generate SQL statement information.
2. The method of claim 1, wherein when the raw data comprises at least one database of Oracle, MySQL, PostgreSQL, SQL Server, Hadoop, hive, each of the databases comprising a plurality of data tables;
the generating of the first operation instruction representing that a user selects and moves original data from a non-canvas area of the first interface to a canvas area of the first interface comprises:
generating a first operation instruction representing that the user selects and moves one or more databases from the non-canvas area to the canvas area;
alternatively, a first operation instruction is generated to represent that the user selects and moves one or more data tables from the non-canvas area to the canvas area.
3. The method of claim 1 or 2, wherein when the functional component comprises at least one of data input, data output, field selection, adding constants, type conversion, date manipulation, text processing, field merging, data calculation, numerical replacement, data filtering, mathematical functions, grouping aggregation;
the generating second operation instructions representing the user selecting and moving a function component from the non-canvas area into the canvas area comprises:
generating a second operation instruction representing that the user selects and moves one or more functional components from the non-canvas area to the canvas area.
4. The method of claim 3, wherein when the functional component comprises a plurality of functional units, the functional units comprising a plurality of sub-functional units, each sub-functional unit corresponds to a different functional function for different databases;
the generating a second operation instruction representing that the user selects and moves one or more of the function single components from the non-canvas area to the canvas area comprises:
generating a second operation instruction representing that the user selects and moves one or more functional units from the non-canvas area to the canvas area;
or generating a second operation instruction which represents that the user selects and moves one or more sub-function units from the non-canvas area to the canvas area.
5. The method of claim 4, wherein when the functional component is a type conversion, the type conversion includes at least one of text conversion, integer conversion, decimal conversion, date conversion, and time conversion, and the date conversion includes a string date conversion as the sub-functional unit.
6. The method according to claim 5, wherein in the case that the selected sub-functional unit is a string date, when the selected database is Oracle, the function corresponding to the string date is to _ date; and when the selected database is MySQL, the function corresponding to the character string conversion date is date _ format.
7. The method of claim 1 or 6, further comprising:
generating a third operation instruction which represents that the user sorts the sequence of all or part of the selected functional components in the drawing area;
and responding to the operation of the user on the first operation instruction, the second operation instruction and the third operation instruction, calling the automatic generation program according to all or part of the original data selected in the canvas area and the selected and sequenced functional components, and automatically generating SQL statement information.
8. An apparatus for automatically generating SQL, comprising:
the display unit is used for displaying a first interface; wherein the first interface comprises a canvas area and a non-canvas area; the non-canvas area is used for displaying original data and functional components;
a generating unit, configured to generate a first operation instruction representing that a user selects and moves the original data from a non-canvas area of the first interface to a canvas area of the first interface, and a second operation instruction representing that the user selects and moves the functional component from the non-canvas area to the canvas area;
the generating unit is further configured to respond to the operation of the user on the first operation instruction and the second operation instruction, and invoke an automatic generating program according to the selected original data and the selected functional component in the canvas area, so as to automatically generate SQL statement information.
9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the SQL automatic generation method according to any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, having stored therein a computer program or instructions, which, when read and executed by a computer, causes the computer to execute the SQL automatic generation method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110713821.XA CN113434130A (en) | 2021-06-25 | 2021-06-25 | SQL automatic generation method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110713821.XA CN113434130A (en) | 2021-06-25 | 2021-06-25 | SQL automatic generation method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113434130A true CN113434130A (en) | 2021-09-24 |
Family
ID=77754803
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110713821.XA Pending CN113434130A (en) | 2021-06-25 | 2021-06-25 | SQL automatic generation method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113434130A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116049294A (en) * | 2023-03-27 | 2023-05-02 | 北京大学深圳研究生院 | Excel operation method, device, equipment and storage medium |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090119640A1 (en) * | 2007-11-07 | 2009-05-07 | Microsoft Corporation | Graphical application for building distributed applications |
| CN105549982A (en) * | 2016-01-14 | 2016-05-04 | 国网山东省电力公司物资公司 | Automated development platform based on model configuration |
| US20170115968A1 (en) * | 2015-10-23 | 2017-04-27 | Oracle International Corporation | Application builder with automated data objects creation |
| CN106909610A (en) * | 2017-01-10 | 2017-06-30 | 中电科华云信息技术有限公司 | Visualization based on browser pulls the method and system of inquiry data |
| CN108446327A (en) * | 2018-02-12 | 2018-08-24 | 平安科技（深圳）有限公司 | Operation system dynamic creation method, device, computer equipment and storage medium |
| US20190171750A1 (en) * | 2017-12-04 | 2019-06-06 | Bank Of America Corporation | Sql server integration services (ssis) package analyzer |
| CN110427410A (en) * | 2019-07-29 | 2019-11-08 | 武汉宏途科技有限公司 | A kind of method and system for realizing data statistics based on form component |
| CN110764753A (en) * | 2019-09-18 | 2020-02-07 | 亚信创新技术(南京)有限公司 | Business logic code generation method, device, equipment and storage medium |
| CN111930862A (en) * | 2020-09-17 | 2020-11-13 | 国网浙江省电力有限公司 | SQL interactive analysis method and system based on big data platform |
-
2021
- 2021-06-25 CN CN202110713821.XA patent/CN113434130A/en active Pending
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090119640A1 (en) * | 2007-11-07 | 2009-05-07 | Microsoft Corporation | Graphical application for building distributed applications |
| US20170115968A1 (en) * | 2015-10-23 | 2017-04-27 | Oracle International Corporation | Application builder with automated data objects creation |
| CN105549982A (en) * | 2016-01-14 | 2016-05-04 | 国网山东省电力公司物资公司 | Automated development platform based on model configuration |
| CN106909610A (en) * | 2017-01-10 | 2017-06-30 | 中电科华云信息技术有限公司 | Visualization based on browser pulls the method and system of inquiry data |
| US20190171750A1 (en) * | 2017-12-04 | 2019-06-06 | Bank Of America Corporation | Sql server integration services (ssis) package analyzer |
| CN108446327A (en) * | 2018-02-12 | 2018-08-24 | 平安科技（深圳）有限公司 | Operation system dynamic creation method, device, computer equipment and storage medium |
| CN110427410A (en) * | 2019-07-29 | 2019-11-08 | 武汉宏途科技有限公司 | A kind of method and system for realizing data statistics based on form component |
| CN110764753A (en) * | 2019-09-18 | 2020-02-07 | 亚信创新技术(南京)有限公司 | Business logic code generation method, device, equipment and storage medium |
| CN111930862A (en) * | 2020-09-17 | 2020-11-13 | 国网浙江省电力有限公司 | SQL interactive analysis method and system based on big data platform |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116049294A (en) * | 2023-03-27 | 2023-05-02 | 北京大学深圳研究生院 | Excel operation method, device, equipment and storage medium |
| CN116049294B (en) * | 2023-03-27 | 2023-06-27 | 北京大学深圳研究生院 | Excel operation method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11048871B2 (en) | Analyzing natural language expressions in a data visualization user interface | |
| US10902045B2 (en) | Natural language interface for building data visualizations, including cascading edits to filter expressions | |
| CN110300966B (en) | Enhanced pivot table creation and interaction | |
| US9418178B2 (en) | Controlling a size of hierarchical visualizations through contextual search and partial rendering | |
| JP2005267647A (en) | Method for rendering table by using natural language command | |
| CN115061721A (en) | Report generation method and device, computer equipment and storage medium | |
| CN101776999A (en) | Platform for developing and implementing software system | |
| US20230134235A1 (en) | Applying a Visual Analytics Intent Language to Generate Data Visualizations | |
| CN113434130A (en) | SQL automatic generation method and device | |
| CN110688109A (en) | Model editing system and creation method thereof | |
| CN113268496A (en) | Client list screening method and device | |
| US20190236195A1 (en) | Actionable answer card in natural language query | |
| CN111026916B (en) | Text description conversion method and device, electronic equipment and storage medium | |
| WO2021168634A1 (en) | Graphical codeless programming method, computer device, and storage medium | |
| US11556878B2 (en) | Systems and methods for managing organizational structures | |
| EP3853714B1 (en) | Analyzing natural language expressions in a data visualization user interface | |
| CN110780970A (en) | Data screening method, device, equipment and computer readable storage medium | |
| CN112579664A (en) | Processing method and device for chart linkage | |
| US11567967B2 (en) | Applying a visual analytics intent language to infer ambiguous or underspecified intent | |
| CN115543428A (en) | Simulated data generation method and device based on strategy template | |
| CN115469849A (en) | Service processing system, method, electronic device and storage medium | |
| CN115203162A (en) | WYSIWYG graph data construction method | |
| CN111695036B (en) | Content recommendation method and device | |
| CN115687704A (en) | Information display method and device, electronic equipment and computer readable storage medium | |
| CN110688108A (en) | Model generation method and device and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |