Server, system, method, and program for providing automated service for interactive natural language-based sales management data analysis using query plan validation and feedback-based regeneration structure

Abstract

According to an embodiment of the present invention, a sales management data analysis automation service providing server is provided. The server comprises: a query analysis unit that provides a natural language query received from a user terminal and a plurality of pre-stored analysis intent definition information as inputs to a large-scale language model, selects an analysis intent corresponding to the natural language query among the plurality of analysis intent definitions, and extracts time limit information and category limit information from the natural language query; a table column determination unit that determines a necessary table and a necessary column to be used for processing the natural language query by providing table metadata and column metadata extracted from a database schema as inputs to a large-scale language model based on the analysis intent, the time limit information, the category limit information, and necessary element information corresponding to the analysis intent; and an SQL plan generation unit that generates a structured SQL query plan including a table list, join information, filter conditions, grouping criteria, and aggregation indicators to be used in an SQL query by providing the analysis intent, the time limit information, the category limit information, the necessary table and the necessary column, and relationship information between the necessary tables as inputs to a large-scale language model. It includes an SQL generation unit that generates a final SQL query statement satisfying the SQL query plan by providing the above SQL query plan as input to a large-scale language model; and a result providing unit that provides the result obtained by executing the final SQL query statement in a database to a user terminal.

Description

Technology Field

[0001] The present invention relates to a server, system, method, and program for providing an interactive natural language-based sales management data analysis automation service using a query plan verification and feedback regeneration structure. Background Technology

[0002] Unless otherwise indicated in this specification, the contents described in this section are not prior art for the claims of this application, and are not to be recognized as prior art simply because they are included in this section.

[0003] Generally, corporate management data analysis systems have a structure in which large amounts of data stored in ERP, POS, WMS, accounting systems, etc., are aggregated into a relational database, and analysts or data engineers use SQL queries or BI tools to query necessary indicators. In these conventional technologies, in most cases, users must either write SQL statements directly to check specific indicators such as sales, inventory, or cost, or select only fixed query conditions provided in predefined dashboards or structured reports.

[0004] To complement this, so-called NL2SQL (Natural Language to SQL) technology that converts natural language queries into SQL, or a UI in the form of a query builder, have been proposed.

[0005] However, conventional NL2SQL technology often relies on direct mapping of statements and SQL for a single schema or keyword-level rule-based transformations, which fails to adequately reflect domain-specific analysis intentions (e.g., cost ratio analysis, inventory risk analysis, etc.) and has limitations in terms of accuracy for complex queries involving multiple tables or ambiguous natural language expressions.

[0006] In addition, existing BI tools and query builders require users to directly select tables, join relationships, and aggregate functions. Consequently, general users (business personnel) lacking an understanding of data schemas found it difficult to perform desired analyses, and there was an inefficiency in having to request query writing from data managers whenever a new analysis requirement arose. Furthermore, even for the same analysis intent, tasks such as setting time limits, category limits, and calculation metrics had to be performed manually every time, which limited the automation of repetitive tasks or the utilization of RPA.

[0007] Against this backdrop, there is a demand for technology that enables even non-experts to easily perform complex data analysis by understanding users' analysis requests expressed in natural language to structurally extract analysis intent, timeframes, and categories, automatically determining necessary tables and columns in conjunction with database schema metadata, and reliably generating SQL query plans and final queries using LLM.

[0008] The matters described in the background technology above are intended to aid in understanding the background of the invention and may include matters that are not disclosed prior art. Prior art literature

[0009] Published Patent Application No. 10-2025-0001990 (2025.01.07.) Registered Patent Application No. 10-2737842 (2024.11.29.) Registered Patent Application No. 10-2498403 (2023.02.07.) Published Patent Application No. 10-2025-0051758 (2025.04.17.) The problem to be solved

[0010] One embodiment of the present invention aims to solve the problems that when processing a user's analysis request expressed in natural language using existing NL2SQL technology or a simple query builder method, (i) the analysis intent and necessary metrics for each domain are not properly reflected, (ii) the user must directly select table / join / aggregate functions in a complex schema involving multiple tables, and (iii) it is difficult to automate the repeated execution of identical / similar analyses.

[0011] More specifically, the present invention aims to provide a system that enables even non-experts to easily perform complex data analysis and efficiently automate iterative analysis by structurally deriving analysis intent, time limits, category limits, and necessary elements from natural language queries, automatically determining necessary tables / columns by combining them with schema metadata and relational information, and generating a stable SQL query plan and final query using a large-scale language model.

[0012] However, the technical problem that this embodiment aims to solve is not limited to the technical problem described above, and other technical problems may exist. means of solving the problem

[0013] According to an embodiment of the present invention for solving the above-mentioned problem, a sales management data analysis automation service providing server is provided.

[0014] The server comprises: a query analysis unit that provides a natural language query received from a user terminal and a plurality of pre-stored analysis intent definition information as input to a large-scale language model, selects an analysis intent corresponding to the natural language query among the plurality of analysis intent definitions, and extracts time limit information and category limit information from the natural language query; a table and column determination unit that determines necessary tables and necessary columns to be used to process the natural language query by providing table metadata and column metadata extracted from a database schema as input to a large-scale language model based on the analysis intent, the time limit information, the category limit information, and necessary element information corresponding to the analysis intent; an SQL plan generation unit that generates a structured SQL query plan including a list of tables to be used in an SQL query, join information, filter conditions, grouping criteria, and aggregation indicators by providing the SQL query plan as input to a large-scale language model; and an SQL generation unit that generates a final SQL query statement satisfying the SQL query plan by providing the SQL query plan as input to a large-scale language model. and includes a result providing unit that provides the result obtained by executing the above final SQL query statement in the database to a user terminal.

[0015] Additionally, the plurality of analysis intent definition information includes, for each analysis intent, an analysis intent identifier, an analysis intent name, a list of necessary elements, a flag indicating whether a time limit is typically required, and a flag indicating whether a category limit is typically required. The query analysis unit, by referring to the flag for the selected analysis intent, generates an additional query sentence using a large-scale language model if time limit information or category limit information is not extracted from the natural language query, and re-extracts the missing time limit information or category limit information from the user's response.

[0016] In addition, the table metadata extracted from the schema of the database includes a table name, a table description, and a list of included columns in text form, and the column metadata includes a table name, a column name, a column description, a data type, and related indicator information in text form; the table column determination unit selects candidate tables and candidate columns based on the similarity between the query vector generated by embedding text combining the analysis intent, the required elements, the category limitation information, and the natural language query, and the embedding vectors for the table metadata and column metadata, and provides the candidate tables and candidate columns and their score information as input to a large-scale language model to select the final required tables and columns.

[0017] In addition, the table column determination unit calculates the similarity between a query vector generated by embedding text including the analysis intent, the necessary elements, the category limitation information, and the natural language query, and a table metadata vector generated by embedding each table metadata text, and the similarity is greater than or equal to a preset standard. Select tables as candidate tables, and calculate column scores for each column matching each of the above candidate tables using the following mathematical formula, and , in the above mathematical formula, is a query vector, is the column metadata vector matching each column, is the set of necessary element vectors, sim is the cosine similarity, represents a weight, and columns whose column scores are greater than or equal to a preset standard are selected as candidate columns.

[0018] Additionally, the server further includes a query profile containing an analysis intent identifier, time limit information, category limit information, and necessary element information derived from the query analysis unit, and a script generation unit that stores the final SQL query statement generated and executed by the SQL generation unit in correspondence with the query profile by mapping them to each other. If a final SQL query statement mapped to the query profile exists, the query analysis unit and the SQL generation unit reuse the mapped final SQL query statement or modify and use the SQL query statement in part according to changes in conditions based on the natural language query. Effects of the invention

[0019] According to one embodiment of the present invention, even if a user does not understand database structure or SQL syntax, they can receive an SQL query and its analysis results in which the table / column / join / aggregation logic suitable for the analysis intention is automatically determined, simply by inputting a query in natural language.

[0020] In addition, by accumulating matching information between analysis intent, period, and category limits and actual SQL queries, the reuse of identical or similar queries and RPA-level automated execution become possible, thereby improving the response speed and consistency of data analysis tasks and significantly reducing the burden on data managers.

[0021] Furthermore, by utilizing schema metadata and an LLM-based decision structure, it is possible to flexibly respond to various analysis scenarios with minimal configuration changes, even when new tables or metrics are added.

[0022] The effects of the present invention are not limited to those mentioned above, and other unmentioned effects will be clearly understood by a person skilled in the art from the description below. Brief explanation of the drawing

[0023] FIG. 1 is a schematic diagram of a system providing an automated sales management data analysis service according to one embodiment of the present invention. FIG. 2 is a block diagram exemplarily showing a functional module of a service providing server according to FIG. 1. Figure 3 is a conceptual diagram illustrating the process of a service provider server according to Figure 2 performing an automated sales management data analysis process. Figure 4 is a flowchart illustrating the process of a service provider server according to Figure 2 performing an automated sales management data analysis process. Figure 5 is a flowchart illustrating the specific process of step S100 of Figure 4. FIG. 6 is a diagram illustrating, exemplarily, the analysis intent of step S110 of FIG. 5. Figure 7 is a flowchart illustrating the specific process of step S200 of Figure 4. FIG. 8 is a diagram illustrating, exemplarily, the table metadata and column metadata of step S210 of FIG. 5. Figure 9 is a flowchart illustrating the specific process of step S300 of Figure 4. Figure 10 is a diagram illustrating an exemplary query plan of step S310. FIG. 11 is a diagram illustrating the hardware configuration of a service provider server according to FIG. 1 as an example. Specific details for implementing the invention

[0024] The present invention is susceptible to various modifications and may have various embodiments; specific embodiments are illustrated in the drawings and described in detail in the detailed description. However, this is not intended to limit the invention to specific embodiments, and it should be understood that the invention includes all modifications, equivalents, and substitutions that fall within the spirit and scope of the invention. Similar reference numerals have been used for similar components in the description of each drawing.

[0025] Terms such as first, second, A, B, etc., may be used to describe various components, but said components should not be limited by said terms. These terms are used solely for the purpose of distinguishing one component from another. For example, without departing from the scope of the present invention, the first component may be named the second component, and similarly, the second component may be named the first component. The term "and / or" includes a combination of a plurality of related described items or any of a plurality of related described items.

[0026] When it is stated that one component is "connected" or "connected" to another component, it should be understood that while it may be directly connected or connected to that other component, there may also be other components in between. On the other hand, when it is stated that one component is "directly connected" or "directly connected" to another component, it should be understood that there are no other components in between.

[0027] The terms used in this application are used merely to describe specific embodiments and are not intended to limit the invention. The singular expression includes the plural expression unless the context clearly indicates otherwise. In this application, terms such as "comprising" or "having" are intended to specify the presence of the features, numbers, steps, actions, components, parts, or combinations thereof described in the specification, and should be understood as not precluding the existence or addition of one or more other features, numbers, steps, actions, components, parts, or combinations thereof.

[0028] The "similarity" used in this application may be a known similarity calculation method, such as cosine similarity.

[0029] Unless otherwise defined, all terms used herein, including technical or scientific terms, have the same meaning as generally understood by those skilled in the art to which the present invention pertains. Terms such as those defined in commonly used dictionaries should be interpreted as having a meaning consistent with their meaning in the context of the relevant technology, and should not be interpreted in an ideal or overly formal sense unless explicitly defined in this application.

[0030] Hereinafter, a preferred embodiment according to the present invention will be described in detail with reference to the attached drawings.

[0031] FIG. 1 is a schematic diagram of a system providing an automated sales management data analysis service according to one embodiment of the present invention.

[0032] Referring to FIG. 1, a sales management data analysis automation service provision system includes a service provision server (100) and at least one user terminal (200), and the service provision server (100) and at least one user terminal (200) are connected to each other by a network. However, since the system of FIG. 1 is merely an embodiment of the present invention, the present invention is not to be interpreted as being limited by FIG. 1.

[0033] At this time, each component of FIG. 1 is generally connected through a network. For example, as shown in FIG. 1, at least one user terminal (200) can be connected to a service provider server (100) through a network. And, the service provider server (100) can be connected to at least one user terminal (200) through a network.

[0034] Here, a network refers to a connection structure capable of exchanging information among individual nodes, such as multiple terminals and servers. Examples of such networks include Local Area Networks (LANs), Wide Area Networks (WANs), the World Wide Web (WWW), wired and wireless data networks, telephone networks, and wired and wireless television networks. Examples of wireless data communication networks include, but are not limited to, 3G, 4G, 5G, 3GPP (3rd Generation Partnership Project), 5GPP (5th Generation Partnership Project), LTE (Long Term Evolution), WIMAX (World Interoperability for Microwave Access), Wi-Fi, Internet, LAN (Local Area Network), Wireless LAN (Wireless Local Area Network), WAN (Wide Area Network), PAN (Personal Area Network), RF (Radio Frequency), Bluetooth network, NFC (Near-Field Communication) network, satellite broadcasting network, analog broadcasting network, DMB (Digital Multimedia Broadcasting) network, etc.

[0035] In the following, the term "at least one" is defined as a term including both singular and plural forms, and it will be obvious that even if the term "at least one" does not exist, each component may exist in a singular or plural form and may mean singular or plural. Furthermore, whether each component is provided in a singular or plural form may be changed according to the embodiments.

[0036] At least one user terminal (200) can be connected to a platform or webpage that communicates with a service provider server (100) and provides an automated sales management data analysis service.

[0037] Additionally, the user terminal (200) can be connected to a server that provides a sales management data analysis automation service web page, app page, program, or application.

[0038] Here, at least one user terminal (200) may be implemented as a computer capable of connecting to a remote server or terminal via a network. Additionally, various shooting terminals for capturing an object desired by the user may be included as components. Here, the computer may include, for example, a navigation system, a laptop equipped with a web browser, a desktop, a laptop, etc. At this time, at least one user terminal (200) may be implemented as a terminal capable of connecting to a remote server or terminal via a network. At least one user terminal (200) may include all kinds of handheld-based wireless communication devices, such as navigation, PCS (Personal Communication System), GSM (Global System for Mobile communications), PDC (Personal Digital Cellular), PHS (Personal Handyphone System), PDA (Personal Digital Assistant), IMT (International Mobile Telecommunication)-2000, CDMA (Code Division Multiple Access)-2000, W-CDMA (W-Code Division Multiple Access), Wibro (Wireless Broadband Internet) terminal, smartphone, smartpad, tablet PC, etc.

[0039] The service providing server (100) may be a server that provides a sales management data analysis automation service web page, app page, program or application.

[0040] Here, the service providing server (100) can be implemented as a server capable of connecting to a remote server or terminal via a network.

[0041] Meanwhile, the service provider server (100) can build infrastructure using cloud services and configure a frontend and a backend on top of it.

[0042] FIG. 2 is a block diagram illustrating an exemplary functional module of a service providing server (100) according to FIG. 1. FIG. 3 is a conceptual diagram illustrating the process of a service providing server according to FIG. 2 performing an automated sales management data analysis process. FIG. 4 is a flowchart illustrating the process of a service providing server according to FIG. 2 performing an automated sales management data analysis process.

[0043] As shown in FIG. 2, the service providing server (100) includes a query analysis unit (101), an SQL generation unit (102), a result derivation unit (103), and a script generation unit (104), and automatically converts a natural language query into a database query according to steps S100 to S500 shown in FIG. 3 and 4, and provides the result to the user.

[0044] First, in step S100, the query analysis unit (101) analyzes a natural language query entered by a user and extracts time limit information, category limit information, and analysis intent corresponding to the query. At this time, the query analysis unit (101) selects the analysis intent that best matches the user's query from among a plurality of predefined analysis intents, and determines whether the necessary time / category limit is included in the query according to the selected analysis intent.

[0045] Next, in step S200, based on the time limit, category limit, and analysis intent extracted in step S100, necessary tables and necessary columns to be referenced to perform the analysis are determined. In one embodiment, the service providing server (100) can derive necessary tables and necessary columns by using schema metadata and a vector matching technique to extract candidate tables from a plurality of tables and automatically selecting columns required for analysis from each candidate table.

[0046] In step S300, the SQL generation unit (102) generates an SQL query statement executable in the database based on the time limit, category limit, analysis intent, necessary tables and necessary columns, and relationship information between tables determined in steps S100 and S200. In one embodiment, the SQL generation unit (102) may perform a two-step procedure of generating an intermediate query plan, verifying it, and generating a final SQL statement.

[0047] In step S400, the result derivation unit (103) executes the SQL query statement generated in step S300 on one or more databases and receives the execution result. The result derivation unit (103) processes the received result into a form such as a table, graph, or indicator summary and provides it to the user terminal (200), and, in some cases, can generate and output natural language insight sentences such as "average sales volume over 2 weeks" or "day of maximum sales occurrence".

[0048] Finally, in step S500, the time limit, category limit, and analysis intent derived as described above are mapped to the SQL query actually used and stored. Subsequently, if the same or similar query is entered, the query analysis unit (101) can refer to the stored mapping information to skip steps S200 and S300, directly reuse the matching SQL query, or partially modify the query according to the user's modification request if necessary, thereby providing analysis results more quickly.

[0049] As described above, the overall flow of steps S100 to S500 is schematically explained through FIGS. 1 to 4, and below, the detailed operation of each step and each component is explained in more detail with reference to additional drawings.

[0050] FIG. 5 is a flowchart illustrating the specific process of step S100 of FIG. 4. FIG. 6 is a diagram exemplarily illustrating the analysis intent of step S110 of FIG. 5.

[0051] As illustrated in Fig. 5, step S100 is a step of interpreting a query entered by a user in natural language and deriving analysis intent, time limit, category limit, necessary elements, and derived indicators in a structured form to be used in the subsequent SQL generation step.

[0052] To this end, step S100 consists of multiple sub-steps of selecting the analysis intent (S110), extracting time and category limits (S120), verifying missing elements and additional querying (S130), and determining necessary elements and derived indicators (S140).

[0053] First, the query analysis unit (101) stores a predefined analysis intent definition information (intent catalog) in the database as exemplified in FIG. 6.

[0054] In one embodiment, each analysis intent definition may include an identifier for distinguishing analysis intents, an analysis intent name, necessary elements for analysis intents, whether a time limit is required, whether a category limit is required, and a derived indicator calculated using the necessary elements.

[0055] In one embodiment, the information on the intent of the analysis may include the following information.

[0056] intent_id: Identifier to distinguish analysis intents (e.g., "COST_MARGIN_ANALYSIS")

[0057] name_ko: Korean name of the analysis intent (e.g., "Cost rate / margin analysis")

[0058] needed_elements: List of elements required to calculate the analysis intent (e.g., ["sales_amount", "cost_amount"])

[0059] default_period_required: A flag indicating whether a period limit is typically required.

[0060] default_category_required : A flag indicating whether category restriction is typically required.

[0061] computed_metrics: Names and formulas of derived metrics calculated using necessary factors (e.g., cost_rate = cost_amount / sales_amount, margin_rate = (sales_amount - cost_amount) / sales_amount, etc.)

[0062] The query analysis unit (101) provides the user query sentence as input to the large-scale language model (LLM) along with the multiple analysis intent definition information.

[0063] In this case, the LLM may be given a constraint (prompt) stating, "Do not create a new analysis intent, but select one or more of the most suitable analysis intents from the given list and return their identifiers and confidence levels (or scores)."

[0064] LLM compares the meaning of the user query with the description of each analysis intent definition, selects the most matching analysis intent, and outputs its intent_id.

[0065] The query analysis unit (101) determines the analysis intent of the highest score (or higher than a predetermined threshold) among the output results of the LLM as the selected analysis intent.

[0066] In one embodiment, when a user inputs a query such as "Tell me the cost rate and excess inventory for this summer season," the query analysis unit (101) can select intent_id = "COST_MARGIN_ANALYSIS" by referring to the "cost rate / margin analysis" analysis intent definition similar to FIG. 6.

[0067] In addition, the query analysis unit (101) takes the selected analysis intent and user query as input,

[0068] Extracts time-limited and category-limited information expressed in natural language.

[0069] In one embodiment, the query analysis unit (101) uses an LLM or time series / entity recognition model to recognize time expressions such as "last 2 weeks," "this summer," and "first quarter of 2024," converts them into structured data in the form of a relative period (e.g., 14 days, summer season) or an absolute period (e.g., 2024-06-01~2024-08-31), recognizes product group / category notations such as "blouse," "summer season product," and "top category," and maps them to identifiers used in the internal system, such as category codes and attribute values.

[0070] When step S120 is completed, the query analysis unit (101) has a structured query analysis result including the selected analysis intent (intent_id) and the recognized time-limited information and category-limited information from the query.

[0071] Additionally, at step S130, the query analysis unit (101) verifies whether the currently extracted period limit and category limit information is missing by referring to the period limit requirement and category limit requirement (default_period_required and default_category_required) included in the selected analysis intent definition.

[0072] In one embodiment, if default_period_required = true but the period restriction is not extracted in step S120, it is determined that the query typically requires period information but the user has not specified it.

[0073] In one embodiment, even if default_category_required = true and category constraints are not extracted, it is similarly determined that category information is missing.

[0074] If an omission is determined, the query analysis unit (101) generates an additional query sentence using LLM.

[0075] In one embodiment, if the period is omitted, an additional query sentence such as “Which period would you like to view the cost rate based on? (e.g., last month, this year, this summer, etc.)" can be generated and provided to the user terminal (200).

[0076] In one embodiment, when a category is missing, an additional query sentence such as "Would you like to view only specific product groups (e.g., blouses, dresses) or all products?" can be generated and provided to the user terminal (200).

[0077] Additionally, when a user response is received through the user terminal (200), the query analysis unit (101) can input the response back into the LLM to re-extract the time limit or category limit and supplement the missing information.

[0078] Therefore, as a result of step S130, additional queries and reanalysis may be repeated until all period / category limitation information typically required for the selected analysis intent is satisfied.

[0079] In addition, at step S140, the query analysis unit (101) determines the basic indicators and derived indicators to be calculated for the corresponding query by referring to the needed_elements (list of necessary elements) and computed_metrics (definition of derived indicators) corresponding to the selected intent_id from the analysis intent definition information exemplified in FIG. 6.

[0080] In one embodiment, when the selected analysis intent is "cost rate / margin analysis," the required elements are defined as sales_amount (sales amount) and cost_amount (cost amount), and the derived indicators are defined as cost_rate = cost_amount / sales_amount and margin_rate = (sales_amount - cost_amount) / sales_amount. Therefore, the query analysis unit (101) determines these required elements and derived indicators as a "set of output items required for analysis" and transmits them to the SQL generation unit (102) along with the time limit and category limit determined in steps S120 and S130.

[0081] Accordingly, when step S140 is completed, the user's natural language query is transformed into a structured analysis result including (i) analysis intent, (ii) time limit, (iii) category limit, (iv) required elements, and (v) derived indicators, which is utilized in subsequent steps (after S200) to determine required tables and columns and to generate SQL queries.

[0082] FIG. 7 is a flowchart illustrating the specific process of step S200 of FIG. 4. FIG. 8 is a diagram illustrating the table metadata and column metadata of step S210 of FIG. 5 as an example.

[0083] First, the service providing server (100) obtains schema information including table names, column names, data types, constraints, Korean descriptions, etc. from one or more databases to be used for analysis.

[0084] The table column determination unit (102) normalizes the acquired schema information and generates and stores table metadata and column metadata as shown in FIG. 8 (S210).

[0085] In one embodiment, table metadata may include TABLE (table name), KOREAN_NAME (Korean name), DESCRIPTION (description of data stored by the table), COLUMNS (list of columns and the name, type, and brief description of each column), etc. For example, for the product_sales table, a description such as "a table that stores the sales date, category, quantity, and sales amount of each product" may be included.

[0086] In one embodiment, the column metadata includes COLUMN (table name and column name), KOREAN_NAME (Korean name), DESCRIPTION (meaning represented by the column), DATA_TYPE (data type such as INT, DATE, NUMERIC, etc.), and RELATED_METRICS (metrics or concepts related to the column).

[0087] It may include, etc. For example, for product_sales.quantity, information such as "sales quantity of the product on a specific date", RELATED_METRICS: sales_volume, and stock_turnover may be stored.

[0088] In this way, schema information is normalized into text-based metadata, which can then be utilized for vectorization and similarity calculation as described below.

[0089] In addition, the table column determination unit (102) generates a query embedding for the corresponding query using the analysis intent, necessary elements, category limitation, and user natural language query derived in step S100 (S220).

[0090] In one embodiment, the server combines the identifier and description of the analysis intent, a list of required elements, category qualifications (e.g., blouse, top category, etc.), and the original user query into a single text, and can convert this text into a fixed-length vector using a vector embedding model (e.g., a language model-based embedding model).

[0091] The query vector generated in this way is subsequently used to calculate similarity with the table metadata vector and column metadata vector.

[0092] Additionally, the table column determination unit (102) generates a table metadata vector by embedding the text of the metadata (table description, column list summary, etc.) for each table metadata.

[0093] The table column determination unit (102) calculates the similarity between the query vector and each table metadata vector and determines the tables whose similarity is greater than or equal to a preset threshold as candidate tables. The similarity can be measured using cosine similarity, and a method of selecting the top K tables is also possible.

[0094] In one embodiment, regarding the analysis intent of “aggregating sales volume by category” and the query “tell me the sales volume of blouses for the last 2 weeks,” the product_sales table metadata has high similarity, and accordingly, product_sales can be selected as a candidate table.

[0095] Additionally, the table column determination unit (102) constructs a set of column metadata by querying column metadata for columns belonging to each candidate table determined in step S230.

[0096] In one embodiment, when the candidate table is product_sales, column metadata for date, product_id, category, quantity, sales_amount, cost_amount, etc., exemplified in FIG. 8 is selected.

[0097] At this stage, column importance evaluation is not yet performed; instead, it serves to prepare the columns associated with the candidate tables as the set for similarity analysis in the next stage.

[0098] Additionally, the table column determination unit (102) calculates the similarity between the query vector and the required element vector and each column metadata vector to calculate the score of the column candidate (S250).

[0099] First, a column metadata vector is generated by embedding the text of each column metadata (column name, description, data type, related metrics, etc.).

[0100] In addition, it calculates the similarity between the query vector and the column metadata vector, and the similarity between the column metadata vector and the vector of necessary elements, which is obtained by representing each necessary element (e.g., sales_amount, cost_amount, stock_qty, etc.) as text and embedding it.

[0101] The table column determination unit (102) can calculate a score for column candidates using these similarities in the form of a weighted sum, for example, as follows.

[0102]

[0103] In the above mathematical formula, is a query vector, is a column metadata vector, is the set of necessary element vectors, sim is the cosine similarity, means weight.

[0104] Additionally, the table column determination unit (102) can apply a correction value to the score using the data type of the column and keywords in the description. For example, if there is a time limit, the column candidate score can be adjusted by applying a weight to the DATE / DATEIME type column, and if there is a category limit, the column candidate score can be adjusted by applying a weight to the text type column that includes keywords such as "category, product group" in the description.

[0105] Additionally, the table column determination unit (102) determines columns whose scores are greater than or equal to a preset standard as candidate columns based on the column candidate scores calculated in step S250 (S260).

[0106] In one embodiment, for the "sales volume aggregation" analysis intent, product_sales.quantity is selected as a candidate column because it has a high score, and for the "cost ratio analysis" analysis intent, product_sales.sales_amount and product_sales.cost_amount can be selected as candidate columns.

[0107] In this case, if there are columns with overlapping roles within the same table (e.g., various types of date columns), additionally consider data types, column descriptions, and related metrics.

[0108] You can adjust the priority.

[0109] When the process is completed up to step S260 in this manner, a list of columns suitable for use in the analysis is derived as a candidate group for each candidate table.

[0110] Finally, the table column determination unit (102) provides the candidate table metadata, candidate column metadata, and score information calculated for each candidate as input to a large-scale language model (LLM) to finally determine the necessary tables and columns to be used for generating SQL queries.

[0111] In one embodiment, the table column determination unit (102) provides the LLM with structured data as a prompt, including a list of candidate tables and metadata for each table (table description, summary of included columns, etc.), a list of candidate columns belonging to each table and column metadata (column description, data type, related indicators, etc.), a score per table / column calculated based on similarity with a query vector and a required element vector, and information on the analysis intent, required elements, time limit, and category limit determined in step S100, and may give the LLM a constraint to "select the minimum set of tables required to satisfy the analysis intent, and the columns to be used in each table and their roles."

[0112] Based on the above input, LLM can determine the set of necessary tables required for analysis by considering the meaning, score, and whether each table among the candidate tables satisfies the requirements.

[0113] Additionally, for each required table, a column representing the time axis (e.g., date), a column to be used for category filters (e.g., category), and a column to be used for calculating analysis metrics (e.g., quantity, sales_amount, cost_amount, etc.) are selected from candidate columns and designated as required columns, and role information such as "time column," "category column," "aggregation target column," or "filter-only column" can be assigned to each selected required column.

[0114] The table column determination unit (102) receives and stores the set of necessary tables, the list of necessary columns, and role information output by the LLM, and when generating the SQL query in the subsequent step (S300), each column can be used as a hint on how to use the SELECT clause, WHERE clause, GROUP BY clause, HAVING clause, etc. according to the role information.

[0115] Accordingly, the S200 step according to the present invention is characterized by providing natural language-based analysis intent, necessary elements, category-limiting information, database schema metadata, and vector-based scores as inputs to the LLM, thereby enabling the LLM to automatically select necessary tables and necessary columns suitable for analysis through a human-like judgment process.

[0116] FIG. 9 is a flowchart illustrating the specific process of step S300 of FIG. 4. FIG. 10 is a diagram illustrating the query plan of step S310 as an example.

[0117] As illustrated in FIG. 9, in step S310, the SQL generation unit (102) of the service providing server (100) provides the time limit information, category limit information, analysis intent and required elements determined in step S100, the set of required tables and the set of required columns determined in step S200, metadata for each required table and column (see FIG. 8), and relational metadata indicating the relationship between the required tables (such as JSON normalized from ORM relationships) as input to the LLM, and generates an SQL query plan based on this information.

[0118] At this time, the SQL query plan is expressed in a structured format such as JSON, as exemplified in Fig. 10.

[0119] In one embodiment, the following items may be included.

[0120] intent_id: Identifier of the analysis intent corresponding to the query

[0121] tables : List of tables to be used in the query

[0122] joins: Join relationships between tables (relationship ID, join type (INNER, LEFT, etc.), from / to tables and aliases, join condition, etc.)

[0123] filters : Filter conditions including time limits and category limits (e.g., product_sales.date >= CURRENT_DATE - INTERVAL '14 day', product_sales.category = 'Blouse', etc.)

[0124] group_by : List of columns used as grouping criteria

[0125] metrics : Expressions to be used for aggregation and their aliases (e.g., SUM(product_sales.quantity) AS total_qty)

[0126] order_by : Sort by

[0127] That is, in step S310, the LLM creates a human-readable structured plan for "which tables to join and how to filter, aggregate, and sort based on which columns," but does not yet generate the actual SQL string. Thus, the SQL generation unit (102) can clearly understand the logical structure of the query.

[0128] Additionally, the SQL generation unit (102) verifies whether the SQL query plan generated in step S310 matches the actual schema and constraints (S320).

[0129] Specifically, the SQL generation unit (102) can check the following items.

[0130] Whether the table names included in tables actually exist in the database schema

[0131] Whether the from / to table pairs and join conditions listed in joins match the relationships defined in the relationship metadata

[0132] Whether the column names used in filters, group_by, metrics, and order_by are included in the list of required columns confirmed in step S200 and the schema of the corresponding table

[0133] Whether the combination of GROUP BY and aggregate expressions is syntactically valid in SQL (e.g., whether all non-aggregated columns included in the SELECT are included in group_by, etc.)

[0134] If, as a result of verification, an error is found in the query plan, the SQL generation unit (102) may provide feedback to the LLM including the reason for the error (e.g., non-existent column name, incorrect join target, etc.) to regenerate a modified SQL query plan. Through this iteration, steps S310 and S320 may be repeated until a valid SQL query plan matching the schema is finally obtained.

[0135] In addition, the SQL generation unit (102) provides the SQL query plan that has passed verification, the necessary table metadata, and the necessary column metadata back to the LLM, and generates a final SQL query statement that faithfully reflects the plan (S330).

[0136] In one embodiment, the following restrictions may be specified in the LLM.

[0137] The table and column names that can be used to write queries shall be limited to those included in the query plan and metadata.

[0138] The JOIN clause should use only conditions defined in the relationship metadata and the joins entry of the query plan.

[0139] The composition of the GROUP BY, WHERE, and ORDER BY clauses should be based on the group_by, filters, and order_by items of the query plan.

[0140] The output will generate only a single SELECT statement without explanation.

[0141] The generated SQL query is executed in the actual database by the result derivation unit (103) in a subsequent step (S400), and the result is provided to the user terminal (200) in the form of a table, graph, natural language insight, etc.

[0142] As such, the S300 step according to the present invention adopts a two-step (plan-actual query) structure in which an intermediate SQL query plan is first generated using LLM, the plan is verified by comparing it with schema and relational metadata, and a final SQL query statement is generated using LLM again based on the verified plan. This effectively reduces syntax errors and schema mismatch errors compared to a method of simply generating SQL all at once, and enables the generation of accurate SQL queries corresponding to natural language queries.

[0143] In step S400, the result derivation unit (103) executes the SQL query generated in step S300 on one or more databases to receive a raw result set, and calculates key indicators that correspond to the analysis intent based on the results. For example, derived indicators such as average, maximum, minimum, sum, cost ratio / margin ratio / inventory turnover ratio are calculated from the daily sales volume results, and data structures for visualization such as tables, graphs, and heatmaps are automatically generated using role information for time columns / category columns / aggregation indicator columns. If necessary, the result derivation unit (103) provides the calculated key indicators as input to the LLM to generate natural language insight sentences such as "The average sales volume over two weeks is X, and the day with the highest sales volume is X month Y day." and provides them to the user terminal (200) along with the table and graph, thereby allowing the user to intuitively check the analysis results for the natural language query.

[0144] In addition, the script generation unit (104) stores the time limit, category limit, and analysis intent derived as described above, and the SQL query statement actually executed in step S300 by mapping them to each other. Specifically, the query analysis results (analysis intent identifier, period / category condition, required element, etc.) are structured into a single query profile, and the metadata of the SQL query statement and execution result corresponding to the profile is stored in conjunction. This allows the matched SQL query statement to be reused immediately when the same or similar query is input, or allows a new query statement to be quickly generated by modifying only some conditions according to the user's additional request. By doing so, the system has the effect of improving overall query processing efficiency by omitting or shortening the LLM-based table / column selection and query plan generation process for repetitive analysis requests.

[0145] FIG. 11 is a diagram illustrating the hardware configuration of a service provider server according to FIG. 1 as an example.

[0146] Referring to FIG. 11, a service providing server (100) may include at least one processor (110) and a memory that stores instructions that instruct the at least one processor (110) to perform at least one operation.

[0147] The above at least one operation may include components (101 to 104) of the aforementioned service providing server (100) or other functions or methods of operation.

[0148] Here, at least one processor (110) may mean a central processing unit (CPU), a graphics processing unit (GPU), or a dedicated processor on which methods according to embodiments of the present invention are performed. Each of the memory (120) and the storage device (160) may be composed of at least one of a volatile storage medium and a non-volatile storage medium.

[0149] For example, the memory (120) may be one of read-only memory (ROM) and random access memory (RAM), and the storage device (160) may be flash memory, hard disk drive (HDD), solid-state drive (SSD), or various memory cards (e.g., micro SD card).

[0150] Additionally, the server (100) may include a transceiver (130) that performs communication via a wireless network. Additionally, the server (100) may further include an input interface device (140), an output interface device (150), a storage device (160), etc. Each component included in the server (100) may be connected by a bus (170) to communicate with each other.

[0151] Examples of the server (100) may be a communicable desktop computer, laptop computer, notebook, smartphone, tablet PC, mobile phone, smart watch, smart glass, e-book reader, PMP (portable multimedia player), portable game console, navigation device, digital camera, DMB (digital multimedia broadcasting) player, digital audio recorder, digital audio player, digital video recorder, digital video player, PDA (Personal Digital Assistant), etc.

[0153] The methods according to the present invention may be implemented in the form of program instructions that can be executed through various computer means and recorded on a computer-readable medium. The computer-readable medium may include program instructions, data files, data structures, etc., either alone or in combination. The program instructions recorded on the computer-readable medium may be those specifically designed and configured for the present invention, or they may be those known and available to those skilled in the art of computer software.

[0154] Examples of computer-readable media may include hardware devices specifically configured to store and execute program instructions, such as ROM, RAM, and flash memory. Examples of program instructions may include machine code, such as that generated by a compiler, as well as high-level language code that can be executed by a computer using an interpreter, etc. The aforementioned hardware devices may be configured to operate as at least one software module to perform the operation of the present invention, and vice versa.

[0155] In addition, the above-described method or device may be implemented by combining all or part of its configuration or function, or by implementing it separately.

[0156] Although the present invention has been described above with reference to preferred embodiments, those skilled in the art will understand that various modifications and changes can be made to the invention without departing from the spirit and scope of the invention as described in the following claims.

[0157] Although embodiments of the present invention have been described above with reference to the attached drawings, those skilled in the art will understand that various modifications and changes can be made to the present invention without departing from the technical spirit and scope of the invention as set forth in the claims below. Therefore, the embodiments described above should be understood as illustrative in all respects and not restrictive.

Claims

Claim 1 A server providing an automated sales management data analysis service comprises: a query analysis unit that provides a natural language query received from a user terminal and a plurality of pre-stored analysis intent definition information as input to a large-scale language model, selects an analysis intent corresponding to the natural language query among the plurality of analysis intent definitions, and extracts time limit information and category limit information from the natural language query; a table and column determination unit that determines necessary tables and columns to be used to process the natural language query by providing table metadata and column metadata extracted from a database schema as input to a large-scale language model based on the analysis intent, the time limit information, the category limit information, and necessary element information corresponding to the analysis intent; an SQL plan generation unit that generates a structured SQL query plan including a list of tables to be used in an SQL query, join information, filter conditions, grouping criteria, and aggregation indicators by providing the SQL query plan as input to a large-scale language model; and an SQL generation unit that generates a final SQL query statement satisfying the SQL query plan by providing the SQL query plan as input to a large-scale language model.The system includes a result providing unit that provides results obtained by executing the final SQL query statement in a database to a user terminal, wherein the plurality of analysis intent definition information includes, for each analysis intent, an analysis intent identifier, an analysis intent name, a list of necessary elements, a flag indicating whether a time limit is typically required, and a flag indicating whether a category limit is typically required, wherein the query analysis unit, by referring to the flag for the selected analysis intent, generates an additional query sentence using a large-scale language model if time limit information or category limit information is not extracted from the natural language query, and re-extracts the missing time limit information or category limit information from the user's response, wherein the table metadata extracted from the schema of the database includes a table name, a table description, and a list of included columns in text form, and the column metadata includes a table name, column name, column description, data type, and related indicator information in text form, wherein the table column determination unit determines candidate tables and candidate columns based on the similarity between the query vector generated by embedding text combining the analysis intent, the necessary elements, the category limit information, and the natural language query, and the embedding vector for the table metadata and column metadata. Selects, and provides the candidate table and candidate column and their score information as input to a large-scale language model to select the final required table and required column; the table column determination unit calculates the similarity between a query vector generated by embedding text including the analysis intent, the required element, the category limitation information, and the natural language query, and a table metadata vector generated by embedding each table metadata text, and the similarity is greater than or equal to a preset standard; Select tables as candidate tables, and calculate column scores for each column matching each of the above candidate tables using the following mathematical formula, and In the above mathematical formula, is a query vector, is the column metadata vector matching each column, is the set of necessary element vectors, sim is the cosine similarity, ... refers to a weight, and a column whose score is greater than or equal to a preset standard is selected as the candidate column. The server further includes a script generation unit that maps and stores a query profile containing an analysis intent identifier, time limit information, category limit information, and necessary element information derived from the query analysis unit, and the final SQL query statement generated and executed by the SQL generation unit in correspondence with the query profile. If a final SQL query statement mapped to the query profile exists, the query analysis unit and the SQL generation unit reuse the mapped final SQL query statement or use the SQL query statement after partially modifying it according to changes in conditions based on the natural language query. The SQL plan generation unit verifies whether the table name included in the SQL query plan exists in the database schema, whether the join information matches the relationship defined in the relationship information, whether the column name used in the filter condition, grouping criterion, and aggregation indicator is included in the list of necessary columns, and whether the combination of the grouping criterion and the aggregation expression is grammatically correct. If an error is found, it provides feedback including the reason for the error to a large-scale language model to regenerate a modified SQL query plan, and a valid A server characterized by repeating the above generation and verification until an SQL query plan is obtained.

Images