KR20220111020A - Method and apparatus for generating auto sql sentence using computer data request basend on text in bigdata integrated management environmnet - Google Patents

Abstract

A method for an SQL phrase generation apparatus to generate an SQL phrase to search for computer data, includes the following steps of: receiving a computer data search request through an integrated management environment; generating a natural language conditional clause based on extraction information written in a Korean text and an extraction search condition through the computer data inquiry request; receiving one target database selected from among a plurality of databases, and connecting the selected target database; deriving a mapping table satisfying the natural language conditional clause by parsing the natural language conditional clause with the target database; and generating an SQL phrase based on the derived mapping table. Therefore, the present invention is capable of generating a SQL phrase based on a derived mapping table satisfying a natural language conditional clause.

Description

Method and device for automatic SQL statement generation using text-based computational data request form in big data integrated management environment

The present invention relates to a method for generating an SQL statement for inquiring computational data on an image.

The SQL language refers to a language that enables communication between a database and a computer language for data inquiry, storage, and deletion. The SQL language can connect to most databases (eg, Oracle, DB2, SQLServer, MySQL, etc.) through the standard SQL language. A recheck may be performed.

Among these SQL languages, in the case of PL/SQL (Procedural Language for SQL), which has elements of a programming language, elements of programming related to variables such as for??Loop, If??else, Case??then and Int, char, varchar, etc. may include.

In relation to the SQL language, Korean Patent Registration No. 10-1119179 discloses a system for applying a structured query language for accessing a database to a standardized format.

In general, in order to communicate with the database through the SQL language, most of the attributes of entities are written in English when modeling and designing the database. will create Through this, SQL language can be generated based on documents written in English for computerized data inquiry.

However, in the case of documents written in Korean, it was difficult to understand the documents, so it was difficult to generate the SQL language.

To provide a method to receive a computerized data inquiry request form through an integrated management environment, and to generate natural language conditional clauses based on extracted information and extracted inquiry conditions written in Korean text through the computerized data inquiry request form.

A target database of any one of a plurality of databases is selected, connected to the selected target database, a natural language conditional clause is parsed into the target database to derive a mapping table that satisfies the natural language conditional clause, and an SQL statement is generated based on the derived mapping table. We want to provide a way to

However, the technical problems to be achieved by the present embodiment are not limited to the technical problems described above, and other technical problems may exist.

As a means for achieving the above-described technical problem, an embodiment of the present invention includes the steps of receiving a computerized data inquiry request through an integrated management environment, extraction information and extraction inquiry conditions written in Korean text through the computerized data inquiry request form receiving input; generating a natural language conditional clause based on the input extraction information and extraction query condition, receiving a selection of a target database from among a plurality of databases, and connecting to the selected target database, parsing the natural language conditional clause into the target database and deriving a mapping table that satisfies the natural language conditional clause and generating an SQL syntax based on the derived mapping table may be provided.

The above-described problem solving means are merely exemplary, and should not be construed as limiting the present invention. In addition to the exemplary embodiments described above, there may be additional embodiments described in the drawings and detailed description.

According to any one of the above-described problem solving means of the present invention, a computerized data inquiry request is input through the integrated management environment, and a natural language conditional clause is generated based on the extraction information and extraction query conditions written in Korean text through the computerized data inquiry request form can provide a way to

By selecting one target database from among a plurality of databases and connecting to the selected target database, the natural language conditional clause is parsed into the target database to derive a mapping table that satisfies the natural language conditional clause, and SQL statements are generated based on the derived mapping table can provide a way to

1 is an exemplary diagram illustrating a system for generating SQL statements according to an embodiment of the present invention.
2 is a flowchart of a method of generating an SQL statement for inquiring computerized data according to an embodiment of the present invention.
3 is an exemplary view for explaining a process of receiving a computerized data inquiry request form according to an embodiment of the present invention.
4 is an exemplary diagram for explaining a process of deriving a mapping table according to an embodiment of the present invention.
5A to 5I are exemplary diagrams illustrating an integrated management environment according to an embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings so that those skilled in the art can easily carry out the present invention. However, the present invention may be embodied in several different forms and is not limited to the embodiments described herein. And in order to clearly explain the present invention in the drawings, parts irrelevant to the description are omitted, and similar reference numerals are attached to similar parts throughout the specification.

Throughout the specification, when a part is "connected" with another part, this includes not only the case of being "directly connected" but also the case of being "electrically connected" with another element interposed therebetween. . Also, when a part "includes" a certain component, it means that other components may be further included, rather than excluding other components, unless otherwise stated, and one or more other features However, it is to be understood that the existence or addition of numbers, steps, operations, components, parts, or combinations thereof is not precluded in advance.

In this specification, a "part" includes a unit realized by hardware, a unit realized by software, and a unit realized using both. In addition, one unit may be implemented using two or more hardware, and two or more units may be implemented by one hardware.

Some of the operations or functions described as being performed by the terminal or device in this specification may be instead performed by a server connected to the terminal or device. Similarly, some of the operations or functions described as being performed by the server may also be performed in a terminal or device connected to the corresponding server.

Hereinafter, an embodiment of the present invention will be described in detail with reference to the accompanying drawings.

1 is an exemplary diagram illustrating a system for generating SQL statements according to an embodiment of the present invention. Referring to FIG. 1 , the SQL syntax generating system may include an SQL syntax generating apparatus 100 and a database 110 .

The SQL syntax generating apparatus 100 may generate a Structured Query Language (SQL) language to access the database 110 and communicate with the database in a computer language in order to inquire, store, and delete data. By using the SQL language, it is possible to input, delete, change, store, etc. data in the database 110 .

The database 110 may store data collected by a specific institution. In this case, the database 110 may provide the corresponding computational data to the SQL syntax generating device 100 when receiving the computational data inquiry request from the SQL syntax generating device 100 .

For example, when a specific institution is the Health Insurance Review and Assessment Service, the database 110 may store specification details, medical institution treatment details, medical history knowledge details, specific details, patient injury and illness details, prescription issuance details, and the like. For example, in the case of specification details, the database 110 may store the billing statements collected monthly from hospitals, clinics, dentists, oriental medicine, etc. for examination of health insurance. In this case, in the specification, the unit of claim and payment may be defined at the examinee level, and the database 110 may extract statistics and data by searching for the examinee unit.

The database 110 stores information of various entities (eg, Table, View, Index, etc.) such as a table name (Table name) and a column name (Column name) in order to register meta information during modeling and design. By storing the input in a dictionary of (110), a model can be designed. Through this process, it is possible to grasp the entity and its attribute information.

In general, the attributes of most entities are written in English. Developers, DBAs, and administrators all create English dictionaries to generate the best English name of the shot.

However, in the present application, even if a document in a Korean format is used, SQL can be automatically generated by grasping the meaning of Hangul.

2 is a flowchart of a method of generating an SQL statement for inquiring computerized data according to an embodiment of the present invention.

In step S210, the SQL syntax generating apparatus 100 may receive a computerized data inquiry request through the integrated management environment.

In step S220, the SQL syntax generating apparatus 100 may receive the Korean text-based extraction information and extraction query conditions through the computerized data inquiry request form.

In step S230, the SQL syntax generating apparatus 100 may generate a natural language conditional clause based on the extraction information and the extraction query condition. The process of generating a natural language conditional clause by receiving a computerized data inquiry request form will be described in detail with reference to FIG. 3 .

3 is an exemplary diagram for explaining a process of receiving a computerized data inquiry request form according to an embodiment of the present invention. Referring to FIG. 3 , the SQL syntax generating apparatus 100 may receive an input of the computerized data inquiry request 300 through the integrated management environment from the user. Alternatively, the SQL syntax generating apparatus 100 may receive the computerized data request form 300 written in Korean text such as HWP format and DOC format through the import menu of the integrated management environment from the user.

The computerized data inquiry request 300 may be prepared based on part/chapter/section/subsection/article/paragraph/subject/subject. In addition, the computerized data inquiry request form is in the order of the index 1. Business contents → A. Task details → (1) Detailed examples → (A) It can be written in the order of task definition. Here, the task definition may be prepared based on the detailed table of contents, the standard font designated by the data inquiry requesting institution, and the document creation order.

The SQL syntax generating apparatus 100 may receive an input of the purpose of the document from the user through the computerized data inquiry request 300 . The purpose of the document is, for example, "to establish computerized data to understand the general status of medical services for patients with dementia related to [dementia preliminary evaluation]", so that the main points of the related work can be received concisely from the user.

The computerized data inquiry request 300 may include extraction information. Here, the extraction information may be automatically generated as a detailed description and an appendix. Extraction information is, for example, '1. Bill information, 2. Specification information (medical, dental, oriental medicine, pharmacy, etc.), 3. Injury information, 4. Medical treatment history information, 5. Outpatient prescription information, 6. Other information', etc. may be included.

The computerized data inquiry request 300 may include an extract inquiry condition. The extraction query condition may include detailed item information related to the extraction information. The extract query condition is, for example, '1. Invoice information (eg, year of treatment, year of examination, medical care symbol, from-to of billing date), 2. Statement information (injured person = 'AA100', injured person in('xx', 'xx2', 'xx3') ), age > 19 years old, gender classification, etc.), 3. Medical history ('148601ATB', '385201ATB', '190001ALQ), 4. Injury ('F00', 'F01', ??)', etc. can do.

The computerized data inquiry request 300 may be prepared in the form of a table of extraction information and extraction inquiry conditions. For example, extraction information and extraction query conditions may be generated as shown in Table 1 below.

The SQL syntax generating apparatus 100 may generate the natural language conditional clause 310 by further considering the additional extracted query condition written in relation to the extracted query condition through the computerized data query request 300 . Here, the additional extraction inquiry condition may include a condition of information of the details through a subdivided operation in the extraction inquiry condition. Additional extraction conditions are, for example, 1. Data construction criteria 1) Outpatient specification with the treatment start date of '18.10.3~12.31.' among the specifications for dementia, 2. Specification, 3. Dementia disease (based on total disease, excluding excluded disease), 4. Outpatient statement within +90 days, 5. Others, etc. may be included.

The SQL syntax generating apparatus 100 may generate the natural language conditional clause 310 by further considering the extraction exclusion condition written in Korean text through the computerized data inquiry request 300 .

As such, it is possible to reduce the dependence of the request writer through the computerized data inquiry request 300 and to allow the computerized data inquiry to be computerized. In addition, by pre-writing the expressions frequently appearing in the plurality of computerized data inquiry request 300, it is possible to provide the advantage of allowing reuse. In this case, it may be required to manage the empirically applied syntax and to precede the standardization work for the same object expressed differently in multiple documents. In addition, by centralizing the management and storage of computerized data inquiry forms, it is possible to provide linkage of data with other departments of the institution.

Returning again to FIG. 2 , in step S240 , the SQL syntax generating apparatus 100 may select one target database from among a plurality of databases and connect to the selected target database.

In step S250 , the SQL syntax generating apparatus 100 may parse the natural language conditional clause into the target database 110 to derive a mapping table that satisfies the natural language conditional clause. Here, the SQL syntax generating apparatus 100 may derive a mapping table from the target database based on the entity mapped with the extraction information and the attribute information mapped with the extraction query condition.

In step S260 , the SQL syntax generating apparatus 100 may generate an SQL syntax based on the derived mapping table. For example, the SQL statement generating apparatus 100 may generate an SQL statement based on a SELECT clause, a FROM clause, a WHERE clause, AND, OR, JOIN CONDITION, and the like.

Here, the SQL syntax generating device 100 constructs a main query based on attribute information corresponding to the extracted query condition from the mapping table, and constructs a sub-query within the main query based on the attribute information corresponding to the additional extracted query condition. You can create SQL statements. For example, the SQL syntax generating apparatus 100 may generate SQL based on a subquery of an In, or, form clause. A process of generating an SQL statement based on the mapping table will be described in detail with reference to FIG. 4 .

4 is an exemplary diagram for explaining a process of deriving a mapping table according to an embodiment of the present invention. 3 and 4 , the SQL syntax generating apparatus 100 may derive the mapping table 400 between the natural language conditional clause and the database information by reading the table information of the database 110 .

For example, the SQL syntax generating apparatus 100 may generate the FROM and WHERE clauses of the SQL syntax by combining the natural language conditional clause generated from the computational data inquiry request and the derived mapping table 400 . In this case, when a plurality of mapping tables 400 are derived, a JOIN Condition may be automatically generated based on layout information of a specific organization.

The SQL statement generating apparatus 100 may generate a column list of the SELECT clause by using the values of the 'Sujinja Mart Column Details' 320 table of the computerized data inquiry request 300 .

Returning to FIG. 2 , the SQL syntax generating apparatus 100 may further include receiving correction information for the SQL syntax and editing the SQL syntax based on the input correction information.

The SQL syntax generating device 100 generates an SQL file including the generated SQL syntax, transmitting the generated SQL file to the target database, and computing data query result data derived based on the SQL file from the target database. It may further include the step of receiving.

Through this process, the SQL syntax generating apparatus 100 may extract the information of the specification from among the statistical information of various claim specifications of a specific institution (eg, Health Insurance Review and Assessment Service). In addition, by inputting Korean text in real time, the user can generate SQL in real time and directly check the result. In this case, user-defined SQL tuning can be easily performed because the SQL can be modified after it is created.

In addition, the present application can improve productivity in the analysis, design, and development stages by automatically extracting SQL for the output of the design stage of the database 110 by writing all the logic and specifications. Here, when the extraction information and extraction query conditions are not written in the table-based method, various queries can be automatically generated through the join relationship between the A Select clause and the B Select clause.

5A to 5I are exemplary diagrams illustrating an integrated management environment according to an embodiment of the present invention.

Referring to FIG. 5A , a screen configuration diagram of the integrated management environment 500 is shown. The integrated management environment 500 includes a menu toolbar 501 , a navigator 502 , an editing area 503 , and an overview screen 504 . can be displayed. In the explorer 502 , all files of the program are displayed in a tree form, and when a file is selected through the explorer 502 , the contents of the file are displayed through the editing area 503 , and the current editing is performed through the overview screen 504 . The outline information of the file in progress can be displayed.

Referring to FIG. 5B , the DB connection management screen 510 of the integrated management environment 500 is illustrated, and the user can set DB connection information through the DB connection management screen 510 . In this case, the connection to the database 110 may be enabled through a JDBC driver. The integrated management environment 500 may receive a JDBC driver and class selection from the user through the find button 511 of the DB connection management screen 510 . The integrated management environment 500 may provide an access address sample for each database through the sample button 512 , and may perform a connection test through the confirmation button 513 . All objects in the database 110 may be displayed on the screen through the overview screen 514 .

Referring to FIG. 5C , the interactive SQL syntax 520 may be input through the DB connection management screen 510 of the integrated management environment 500 . For example, it is possible to provide an environment in which a user can access a database through the interactive SQL statement 520 and freely input and execute various SQL statements. For example, the integrated management environment 500 receives the SQL syntax input from the user, and then ';' When a character is input and the Enter key is input, the corresponding SQL statement can be executed. Here, the integrated development environment 500 may receive a table name, a column name, etc. from the user by inputting a Ctrl+space key or by dragging a mouse on the overview screen 521 .

Referring to FIG. 5D , the integrated management environment 500 may edit the SQL file through the SQL syntax editor 530 . In this case, the integrated management environment 500 may provide various editing functions such as line numbers, syntax coloring, and find/replace through the SQL syntax editor 530 . Here, the overview screen 531 may display the structure of the SQL syntax in a tree format. In the integrated management environment 500 , the execution environment area 532 receives a selection of a database to execute SQL through a drag input, a selection input, and the like from a user, and receives an execution button 533 to execute an SQL file.

Referring to FIG. 5E , the integrated management environment 500 may edit the computerized data inquiry request form through the request editor 540 . The request editor 540 may generate an SQL statement by selecting or receiving a database 110 to execute SQL from a user through the execution environment area 541 and receiving an execution button 542 . In this case, the integrated management environment 500 may receive input by selecting a value from the database through the 'select and add' function.

The integrated management environment 500 may generate an SQL syntax based on the input extracted information 543 .

Referring to FIG. 5F , the integrated management environment 500 may generate SQL statements based on the input extraction query condition 544 .

Referring to FIG. 5G , the integrated management environment 500 may generate an SQL statement based on the summation criterion 545 . Here, the summation criterion 545 includes, for example, a function of 'summing up the treatment details when two or more cases with the same care symbol and examinee identification number occur within the same target period and become a condition for continuous hospitalization'. can

Referring to FIG. 5H , the integrated management environment 500 may display the detailed information 546 related to the extraction information 543 and the extraction inquiry condition 544 through the request editor 540 .

Referring to FIG. 5I , the integrated management environment 500 may display the automatically generated SQL syntax through the query screen 550 . Here, in the query screen 550, the content of the SQL syntax may not be changed.

The method of generating an SQL statement for inquiring computational data in the SQL statement generating apparatus described through FIGS. 1 to 5I is a computer program stored in a medium executed by a computer or a recording medium including instructions executable by the computer. It can also be implemented in a form. In addition, the method of generating an SQL statement for inquiring computational data in the SQL statement generating apparatus described with reference to FIGS. 1 to 5I may be implemented in the form of a computer program stored in a medium executed by a computer.

Computer-readable media can be any available media that can be accessed by a computer and includes both volatile and nonvolatile media, removable and non-removable media. Also, computer-readable media may include computer storage media. Computer storage media includes both volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data.

The above description of the present invention is for illustration, and those of ordinary skill in the art to which the present invention pertains can understand that it can be easily modified into other specific forms without changing the technical spirit or essential features of the present invention. will be. Therefore, it should be understood that the embodiments described above are illustrative in all respects and not restrictive. For example, each component described as a single type may be implemented in a distributed manner, and likewise components described as distributed may also be implemented in a combined form.

The scope of the present invention is indicated by the following claims rather than the above detailed description, and all changes or modifications derived from the meaning and scope of the claims and their equivalent concepts should be interpreted as being included in the scope of the present invention. do.

100: SQL syntax generator
110: text recognition device

Claims (8)

A method of generating an SQL statement for inquiring computational data in an SQL statement generating device, the method comprising:
receiving a computerized data inquiry request through an integrated management environment;
receiving input of Korean text-based extraction information and extraction inquiry conditions through the computerized data inquiry request form;
generating a natural language conditional clause based on the input extraction information and extraction query condition;
receiving a selection of a target database from among a plurality of databases, and connecting to the selected target database;
parsing the natural language conditional clause into the target database to derive a mapping table that satisfies the natural language conditional clause; and
Generating an SQL statement based on the derived mapping table
A method for generating SQL statements, including.
The method of claim 1,
The step of generating the natural language conditional clause,
generating the natural language conditional clause by further considering an extraction exclusion condition written in Korean text through the computerized data inquiry request form;
Which will further include, SQL syntax generation method.
The method of claim 1,
The step of deriving the mapping table comprises:
and deriving the mapping table from the target database based on an entity mapped with the extraction information and attribute information mapped with the extraction query condition.
4. The method of claim 3,
The step of generating the natural language conditional clause,
and generating the natural language conditional clause by further considering the additional extracted query condition written in relation to the extracted query condition through the computerized data query request form.
5. The method of claim 4,
The step of generating the SQL statement is:
Constructing a main query from the mapping table based on attribute information corresponding to the extraction query condition, and constructing a sub-query in the main query based on attribute information corresponding to the additional extraction query condition to generate the SQL syntax A method for generating SQL statements, comprising the steps of:
The method of claim 1,
receiving correction information for the SQL syntax; and
The method of generating an SQL statement, further comprising the step of editing the SQL statement based on the input correction information.
The method of claim 1,
The method of generating an SQL statement, wherein the computerized data inquiry request form is written in a table format in which the extraction information and the extraction query condition are written.
The method of claim 1,
generating an SQL file including the generated SQL syntax;
transmitting the generated SQL file to the target database; and
The method further comprising the step of receiving computational data inquiry result data derived based on the SQL file from the target database.

Images