Large model Text2SQL data construction and detection method, equipment and medium

Abstract

The invention provides a data construction and detection method and device of a large model Text2SQL and a medium, and relates to the technical field of large model Text2SQL. The method comprises the steps that a target SQL statement corresponding to a target problem text is obtained; determining the type of the target SQL statement; the target SQL statement is detected, and a query result corresponding to the target SQL statement is detected; detecting a query result corresponding to the target SQL statement; determining whether the target SQL statement construction is correct or not according to a detection result; according to the method, the problem that accuracy and efficiency are difficult to consider at the same time due to the fact that a detection strategy is single in the prior art is effectively solved, the applicability of the large model Text2SQL in the civil aviation field is remarkably improved, the accuracy and response speed of business query are ensured, and the high requirement of civil aviation business for data processing is better met.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of large model Text2SQL, in particular to a large model Text2SQL data construction and detection method, device and medium. BACKGROUND

[0002] In the field of civil aviation, in business scenarios such as flight dynamic query, ticket information retrieval, and baggage regulation query, it is often necessary to convert user natural language questions into SQL statements to obtain answers from the database. This process (Text2SQL) relies on large language models. However, the existing detection methods for SQL statements generated by large models have obvious shortcomings: on the one hand, the differences in problem text intentions are not considered - some problems only contain a single clear intention (such as "the departure time of a certain flight"), and the corresponding generated SQL statement structure is simple, while some problems contain multiple intentions or ambiguous intentions (such as "compare the on-time rate of a certain route this week with last week and analyze the reasons"), and the corresponding generated SQL statement often involves multiple table associations, complex subqueries, etc.; on the other hand, existing detection schemes mostly use a unified standard, either performing comprehensive but redundant detection on all SQL statements (including statement syntax, logic, and query result verification), resulting in low detection efficiency for complex statements, or simplifying the detection process, making it easy for simple statements to produce incorrect results due to syntax or logic errors, and it is difficult to balance the accuracy and efficiency of detection, and it is difficult to meet the stringent requirements of the civil aviation field for data query immediacy and accuracy. SUMMARY

[0003] To solve the above technical problems, the technical solution adopted by the present application is as follows: According to the first aspect of the present application, a large model Text2SQL data construction and detection method is provided, which comprises the following steps: S100, inputting a target problem text into a pre-set large language model to obtain a target SQL statement corresponding to the target problem text; S200, determining the type of the target SQL statement according to the number of intentions of the target problem text, the confidence of each intention, and the information of the target SQL statement; S300, if the type of the target SQL statement is a pre-set simple SQL statement type, detecting the target SQL statement itself and the query result corresponding to the target SQL statement; S400, if the type of the target SQL statement is a pre-set complex SQL statement type, detecting the query result corresponding to the target SQL statement; S500, if the type of the target SQL statement is a preset simple SQL statement type, and both the target SQL statement itself and the query result corresponding to the target SQL statement pass the detection, it is determined that the data structure of the target SQL statement is correct; otherwise, it is determined that the data structure of the target SQL statement is incorrect. S600, if the type of the target SQL statement is a preset complex SQL statement type, and the query result corresponding to the target SQL statement passes the detection, it is determined that the data structure of the target SQL statement is correct; otherwise, it is determined that the data structure of the target SQL statement is incorrect.

[0004] According to another aspect of the present application, a non-transitory computer readable storage medium is also provided, which stores at least one instruction or at least one program, and the at least one instruction or at least one program is loaded and executed by a processor to implement the above-mentioned data construction and detection method of the large model Text2SQL.

[0005] According to another aspect of the present application, an electronic device is also provided, which comprises a processor and the above-mentioned non-transitory computer readable storage medium.

[0006] The present application has at least the following beneficial effects: The data construction and detection method of the large model Text2SQL divides the problem text according to the number of intents, the intent confidence and the generated SQL statement information, and then implements double verification of "self-detection + query result detection" for simple SQL statements, and focuses on query result detection for complex SQL statements, thereby realizing a differentiated detection strategy. This scheme not only avoids query deviation caused by errors in the statement itself through comprehensive detection of simple SQL statements, but also improves the overall processing efficiency by simplifying the detection link of complex SQL statements, effectively solving the problem that the accuracy and efficiency are difficult to balance due to the single detection strategy in the prior art, significantly improving the applicability of the large model Text2SQL in the civil aviation field, ensuring the accuracy and response speed of business queries, and better meeting the high requirements of civil aviation business on data processing. BRIEF DESCRIPTION OF DRAWINGS

[0007] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0008] Figure 1 The flowchart of the data construction and detection method of the large model Text2SQL provided by the embodiments of the present application. DETAILED DESCRIPTION

[0009] The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by a person skilled in the art without creative work belong to the protection scope of the present application.

[0010] It should be noted that, based on the present disclosure, a person skilled in the art should understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, a device can be implemented and / or a method can be practiced using any number of the aspects set forth herein. In addition, this device and / or this method can be implemented using other structures and / or functionality in addition to or instead of one or more of the aspects set forth herein.

[0011] The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by a person skilled in the art without creative work belong to the protection scope of the present application. Figure 1 A flowchart of a data construction and detection method of a large model Text2SQL is shown in FIG. 1.

[0012] The data construction and detection method of the large model Text2SQL can include the following steps: S100, input a target question text into a preset large language model to obtain a target SQL statement corresponding to the target question text.

[0013] Input a natural language question (target question text) proposed by a user in a civil aviation scenario into a pre-trained large language model (such as a GPT model fine-tuned for the civil aviation field, LLaMA, etc.). The model generates a corresponding SQL statement (target SQL statement) according to the question semantics.

[0014] For example, the user question is "query the departure time of CA1835 flight on October 1, 2024", and after inputting the large language model, the model generates the target SQL statement: SELECT departure_time FROM flights WHERE flight_no='CA1835' AND flight_date='2024-10-01'.

[0015] Further, if the target question text contains a preset fuzzy time word, a fuzzy time word conversion function corresponding to the fuzzy time word exists in the target SQL statement corresponding to the target question text; the fuzzy time conversion function is used to convert the fuzzy time word into a corresponding time range.

[0016] In this embodiment, when the target question text contains preset fuzzy time words such as "last year", "Spring Festival", and "recently", the large language model will automatically complete the fuzzy time word recognition and conversion function embedding during the process of converting natural language into SQL statements (Text2SQL). The specific logic is as follows: Recognition and matching of fuzzy time words: The large language model first identifies fuzzy time words from the target question text using pre-trained knowledge of the civil aviation domain (or a fuzzy time lexicon injected during fine-tuning). For example: When a user asked, "How do I find the delay record for flight CA1835 last year?", the model identified "last year" as a vague time term.

[0017] When a user asked about the load factor of flight MU5102 during the 2024 Spring Festival, the model identified "Spring Festival" as a vague time term.

[0018] Generate SQL statements that include transformation functions: When generating SQL statements, the model does not directly include fuzzy time terms in the conditions (because the database cannot parse words like "last year"). Instead, it automatically embeds a pre-defined fuzzy time conversion function (such as TO_TIME_RANGE()) and passes the identified fuzzy time terms as parameters. This function dynamically converts the fuzzy time terms into a specific time range that the database can recognize during SQL execution.

[0019] Example 1: Target question text: "How many times was flight CA1835 delayed last year?", target SQL statement generated by the large language model: SELECT COUNT(*) AS delay_count FROM flight_delays WHERE flight_no = 'CA1835' AND delay_date BETWEEN TO_TIME_RANGE('last year'); -- Embedded conversion function with the parameter "last year".

[0020] Example 2: Target question text: "What is the total number of flights from Beijing to Shanghai during the 2024 Spring Festival?", and the target SQL statement generated by the large language model: SELECT SUM(flight_count) AS total_flights FROM route_stats WHERE departure_city='Beijing' AND arrival_city='Shanghai' AND stat_date BETWEEN TO_TIME_RANGE('2024 Spring Festival period');--Embed conversion function, parameter is "2024 Spring Festival period".

[0021] The preset large language model can be trained in the following way: Domain fine-tuning: adapt to specific scenarios (such as civil aviation) Data preparation: Collect professional texts and SQL cases in the target domain (such as civil aviation), focusing on: Fuzzy time words within the domain: "Spring Festival", "season" (civil aviation terminology, such as the summer and autumn season in 2024 is from March 31 to October 26), "holiday" and others.

[0022] SQL query examples within the domain: such as "Query the number of delays of CA1835 during the 2024 Spring Festival period", the corresponding SQL needs to include custom conversion functions such as TO_TIME_RANGE('2024 Spring Festival').

[0023] Training method: Continue training the model with domain data (freeze part of the underlying parameters, only fine-tune the upper layer), so that the model is familiar with the time expression habits and SQL specifications within the domain. For example, the model will learn that "season" in civil aviation is a fixed six-month cycle, not the "season" in daily language.

[0024] Task fine-tuning: Specialize in "natural language → SQL with conversion functions".

[0025] Data construction: Build "question-SQL" paired data sets, where the question contains fuzzy time words and the SQL contains corresponding conversion functions. For example: "The total number of flights from Beijing to Shanghai last year" corresponds to the SQL statement: SELECT SUM(num) FROM flights WHERE dep='Beijing' AND arr='Shanghai' AND date BETWEEN TO_TIME_RANGE('last year').

[0026] Training goal: Let the model learn the mapping relationship between "fuzzy time words" and "conversion function parameters". For example, the model learns through a large number of samples that when "last year" appears in the question, TO_TIME_RANGE('last year') must be included in the SQL, and this function needs to return the "start and end date of the last natural year".

[0027] The large language model automatically generates SQL statements containing fuzzy time conversion functions, which is a key link between natural language queries and precise database retrieval, significantly improving the practicality and reliability of Text2SQL in the civil aviation field.

[0028] In S200, the type of the target SQL statement is determined according to the number of intents corresponding to the target problem text, the confidence of each intent, and the information of the target SQL statement.

[0029] Further, step S200 includes the following steps: In S210, each key field corresponding to the target SQL statement is parsed to obtain a key field list T corresponding to the target SQL statement.

[0030] The target SQL statement generated by the large language model is structurally parsed to extract all key fields (i.e., core elements that determine the logic and complexity of SQL queries) and form a list T.

[0031] In the civil aviation field, "key fields" include but are not limited to: Basic query elements: table name (e.g., flights, flight_delays), query field (e.g., departure_time, punctuality_rate); Logical operators: JOIN (multi-table association), WHERE (filtering condition), GROUP BY (grouping), SUBQUERY (subquery).

[0032] Functions: fuzzy time conversion function (e.g., TO_TIME_RANGE()), window function (e.g., RANK()), aggregation function (e.g., AVG()).

[0033] Special markers: association key for multi-table association (e.g., flight_no for associating flights and flight_delays tables).

[0034] By structurally parsing and extracting key fields, the "complexity characteristics" of SQL statements are converted into a quantifiable element list, avoiding subjective judgments of SQL complexity (e.g., "I feel this SQL is complex"), and providing objective and repeatable analysis basis for subsequent type classification. At the same time, the parsing process focuses on key elements specific to the civil aviation field (such as fuzzy time conversion functions and flight table association keys), ensuring that the extracted fields are highly relevant to the business scenario and improving the relevance of subsequent judgments.

[0035] S220, if NUM1 / NUM≥λ, determining that the type of the target SQL statement is a complex SQL statement type; wherein NUM1 is the number of preset specified fields contained in T, NUM is the number of key fields in T, and λ is a first preset weight; the specified field corresponds to a fixed query mode.

[0036] Preset specified field: refers to a key field corresponding to a "fixed complex query mode", which in the civil aviation field includes: multi-table association operator (JOIN), subquery (SUBQUERY), window function (RANK() / ROW_NUMBER()), fuzzy time conversion function (TO_TIME_RANGE()) appearing multiple times (≥2 times), cross-database table reference, etc. (The appearance of these fields usually means that the SQL logic is more complex).

[0037] Calculate the proportion: NUM1 is the number of "preset specified fields" in the list T, and NUM is the total number of all key fields in T, and the ratio of NUM1 / NUM is calculated.

[0038] Judgment rule: if the ratio is≥the first preset weight λ (λ is an empirical value, such as 0.7, which can be adjusted according to the civil aviation business scenario), it is determined that the target SQL is a "complex SQL statement type".

[0039] Through the quantitative rule of "specified field proportion", the objective division of SQL type is realized: Avoid subjective bias: without relying on manual judgment "whether complex" or not, but through data proportion automatic decision, to ensure that the judgment standard is consistent in different scenarios.

[0040] Adapt to the characteristics of the civil aviation scene: the specified field focuses on high-frequency complex query elements in the civil aviation field (such as multi-table association flight data, multiple fuzzy time comparison), so that the division result is more in line with the actual business needs.

[0041] Flexible and adjustable: by adjusting the value of λ (such as increasing λ for scenarios with extremely high safety requirements, and relaxing the judgment standard of complex SQL), it can adapt to different business definitions of "complexity" and enhance the universality of the scheme.

[0042] In summary, S210 and S220 provide a scientific and implementable method for SQL type division through "analysis-quantification" logic, which is a key prerequisite for subsequent differentiated detection (simple SQL double detection, complex SQL result detection).

[0043] Further, after step S220, the method further comprises the following steps: S230, if NUM1 / NUM<λ, inputting the target problem text into a preset intent recognition model to obtain an intent list A=(A1, A2, …, Ai A n ), i = 1, 2, …, n; where A i is the i-th intent corresponding to the target problem text, and n is the number of intents corresponding to the target problem text.

[0044] When the "specified field proportion NUM1 / NUM < λ" in S220 (i.e., from the perspective of the SQL key field, it does not meet the complex type characteristics), the semantic intent of the target problem text needs to be further analyzed by the pre-set intent recognition model, and a list A containing all recognized intents is output. Where "intent" refers to the query goal that the user wants to achieve through the question, and n is the number of intents (n≥1).

[0045] In the civil aviation field, common intents include: querying the departure time of a single flight, comparing the punctuality of two flights, counting the monthly average number of flights on a certain route, and analyzing the delay reasons. However, some target problem texts may correspond to multiple intents, or the intent expression is not obvious, resulting in a seemingly simple SQL statement that actually contains multiple query methods. Such SQL statements cannot be directly judged as simple or complex SQL statements from the structure alone.

[0046] When the complexity of the SQL key field is not obvious (NUM1 / NUM < λ), additional judgment basis is supplemented through intent analysis to avoid relying solely on the SQL structure and ignoring the semantic complexity of the user's original question (such as a seemingly simple SQL that may correspond to multiple hidden intents). The intent recognition model focuses on typical query targets in the civil aviation field (such as flight information, delay statistics, etc.), ensuring that the extracted intents are highly relevant to the business scenario and providing semantic support for subsequent type classification.

[0047] The pre-set intent recognition model can be trained in the following way: Collect real user question texts in the civil aviation field, covering common scenarios: Basic queries: such as "CA1835 flight departure time", "What flights are there from Beijing to Shanghai".

[0048] Queries with fuzzy time words: such as "MU5102 punctuality during last Spring Festival", "Recent flight delay situation".

[0049] Multi-intent queries: such as "Query the CA1835 aircraft type and the number of delays last year", "Compare the number of flights from Beijing to Guangzhou this week and last week".

[0050] Data sources can include civil aviation customer service logs, APP user query records, and simulated standard questions, with a total of at least 100,000 (to ensure coverage of various intents).

[0051] In combination with the business needs of civil aviation, define structured intent labels (need to be aligned with subsequent SQL type judgment logic), for example: Single intent label: [Query flight basic information] (departure time, aircraft type, etc.), [Query delay data] (delay times, delay rate, etc.), [Statistical flight volume], etc.

[0052] Multi-intent label: [Query flight basic information + query delay data] (such as asking for time and delay), [Compare multiple flight data] (such as comparing the punctuality of two flights), etc.

[0053] Fuzzy time-related intent: Special attention should be paid to intents containing fuzzy time words, such as [Query last year's flight data], [Query flight information during the Spring Festival] (to facilitate targeted learning by the model).

[0054] Data labeling: Civil aviation experts or annotators manually annotate the collected question texts, each text corresponds to one or more intent labels (multi-intent needs to clearly indicate all sub-intents), and records the consistency of labeling (such as the consistency rate of multiple people labeling the same text needs to be ≥90%). For example: the question "Last year's departure time and delay times of CA1835" is labeled as [Query flight basic information (time) + Query delay data (times)]; the question "Punctuality rate of flights from Beijing to Shanghai during the Spring Festival" is labeled as [Query punctuality rate + Fuzzy time (Spring Festival)].

[0055] Prefer to use pre-trained language models (such as BERT, RoBERTa, ERNIE, etc.), which have learned language rules through massive general texts and are suitable for handling fuzzy expressions in natural language (such as complex questions containing "last year" and "Spring Festival"). For example: choose "BERT model fine-tuned in civil aviation field" (such as first pre-training with civil aviation encyclopedias, manuals, etc. based on general BERT, then fine-tune for intent recognition), to improve the understanding of domain terminology. Then, use existing model training methods to get the preset intent recognition model.

[0056] S240, get the intent confidence list η = (η1, η2, …, η i , …, η n ) corresponding to A; where η i is the confidence of the question's intent being A i .

[0057] Quantify the clarity of intent through confidence, avoiding relying solely on "number of intents" for judgment (such as high and low confidence have very different semantic complexity even if there are 2 intents). Confidence provides a quantifiable standard for subsequent "intent clarity" judgment, making type classification more accurate.

[0058] S250, if the maximum confidence η in η max ≥ μ1, determine the type of the target SQL statement as a simple SQL statement type; μ1 is a first pre-set confidence threshold.

[0059] Calculate the maximum value η in the list of intent confidences η max (i.e., the confidence of the most explicit intent), if η max ≥ the first pre-set confidence threshold μ1 (μ1 is usually set to 0.9-0.95, indicating "high confidence"), determine that the target SQL is of the "simple SQL statement type".

[0060] Logic: η max ≥ μ1 indicates that there is a dominant and explicit core intent, even if there are other secondary intents, the overall semantic complexity is low, and the corresponding SQL logic is usually simple.

[0061] By "high confidence dominant intent", simple scenarios are quickly identified, avoiding over-analysis of semantically explicit problems (such as single explicit intent queries), improving the efficiency of type classification. At the same time, the setting of μ1 can adapt to the requirements of the civil aviation field for "explicitness" (such as when a passenger queries single flight information, the intent is usually highly explicit).

[0062] S260, if η max < μ1, obtain the number NUM2 of intermediate confidences in η; wherein the intermediate confidence is a confidence greater than a second pre-set confidence threshold μ2 and less than μ1.

[0063] If η max < μ1 (i.e., there is no high-confidence dominant intent, and the intent as a whole is not explicit), count the number NUM2 of "intermediate confidences" in the list η. The "intermediate confidence" refers to η i < μ1 i (μ2 is usually set to 0.6-0.9, indicating "moderate confidence"), i.e., the intent has a certain confidence but is not explicit.

[0064] For example, the user question is "CA1835 and MU5102 flight recent situation", the intent recognition model outputs: A = (A1: query CA1835 recent departure time, A2: query MU5102 recent punctuality rate, A3: query two flights recent delay reason), η = (η1 = 0.65, η2 = 0.62, η3 = 0.75). If μ1 = 0.9, μ2 = 0.6, then the confidences of the three intents all satisfy 0.6 < η i < 0.9, so NUM2 = 3.

[0065] By screening the "intermediate confidence" intent, focus on those "with some semantic direction but not clear" intent, which often leads to the key of the problem complexity (such as when the user's expression is ambiguous, the model is difficult to accurately identify the core demand). Statistics NUM2 provides a quantitative basis for subsequent judgment of "whether the intent is scattered and complex".

[0066] S270, if NUM2 / n≥τ, determine the type of the target SQL statement as a complex SQL statement type; wherein τ is a second preset weight; otherwise, determine the type of the target SQL statement by manual judgment.

[0067] Calculate the proportion NUM2 / n of intermediate confidence, if the proportion ≥ second preset weight τ (τ is usually set to 0.5-0.7, indicating "most intents are intermediate confidence"), then determine that the target SQL is "complex SQL statement type"; otherwise, determine the type by manual judgment.

[0068] Logic: NUM2 / n≥τ indicates that most intents are ambiguous and scattered, and the user's problem semantics is complex (such as unclear expression, implied multi-dimensional demand), and the corresponding SQL usually needs to handle complex logic (such as multi-intent fusion, ambiguity resolution), so it is determined as a complex type; if the proportion is insufficient, the intent complexity is between simple and complex, and manual intervention is required to ensure accuracy.

[0069] For the scene of "most intents are ambiguous and scattered", automatically determine as a complex type, adapt to the complexity characteristics of multi-dimensional queries in the civil aviation field (such as "flight conditions" may contain time, aircraft type, punctuality rate, etc. Multiple ambiguous intents); For the boundary condition (intermediate confidence proportion is insufficient), introduce manual judgment to avoid the limitations of automated rules, balance efficiency and accuracy; The adjustability of τ (such as the civil aviation customer service scene requires high intent clarity, which can increase τ) makes the scheme adapt to the accuracy requirements of different business scenarios.

[0070] S300, if the type of the target SQL statement is a preset simple SQL statement type, then detect the target SQL statement itself and the query result corresponding to the target SQL statement.

[0071] Further, the detection of the target SQL statement itself includes the following steps: S310, perform lexical and syntactic analysis on the target SQL statement by a SQL parser, detect whether there are keyword errors, clause order errors, and symbol format errors, and output and terminate if there are errors.

[0072] Through the SQL parser (such as ANTLR, SQLFluff, etc.), the target SQL statement is lexically analyzed (splitting the statement into keywords, identifiers, operators, etc. The smallest unit) and syntax analysis (verify whether the unit combination conforms to the SQL syntax rules), focusing on detecting three types of errors: Keyword error: keyword spelling error (such as SELCT instead of SELECT, JION instead of JOIN).

[0073] Clause order error: SQL clauses violate the fixed order (such as WHERE before FROM, GROUP BY after WHERE, etc. The correct order is SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY).

[0074] Symbol format error: parentheses / quotation marks are not closed (such as WHERE name='Zhang San' missing the right quotation mark), operator error (such as using == instead of = to represent equality), extra comma (such as SELECT name, age, FROM student), etc.

[0075] As the first step of SQL detection, syntax checking directly excludes invalid statements caused by low-level errors such as spelling and format, avoiding waste of subsequent detection resources. For the civil aviation field, syntax correctness is the basis for reliable SQL execution (such as flight query statements cannot be executed due to keyword errors, which will directly affect passenger information acquisition), this step can quickly intercept obvious errors and improve detection efficiency.

[0076] S320, compare the table name and field name referenced in the target SQL statement with the database metadata, detect invalid tables, fields, and field reference ambiguity, and output and terminate if there are problems.

[0077] Extract the table name (such as flights, flight_delays) and field name (such as departure_time, flight_no) referenced in the target SQL statement, and compare it with the database metadata (pre-stored table structure, field list, data type, etc. Information) to detect three types of problems: Invalid table / field: The referenced table or field does not exist in the database (such as mistakenly writing the table name flights as flight, or the field name departure should be departure_time).

[0078] Field reference ambiguity: In multi-table queries, the same name field is not distinguished by table alias (such as the flights and flight_status tables both have a status field, and the SQL directly writes status without specifying f.status or fs.status).

[0079] Through this step, it is ensured that the tables and fields referenced by the SQL statement are consistent with the actual structure of the database, avoiding query failure due to "checking wrong table / field" (for example, in the civil aviation database, the flights table stores basic information of flights, and the flight_delays table stores delay data. If the two tables are confused, the result will be completely wrong). Metadata verification is the key to ensuring that the SQL "points to the correct data" and lays the foundation for subsequent logical verification.

[0080] S330, verify the condition expression data type matching, GROUP BY clause and SELECT list consistency, and join field compatibility in the target SQL statement. If there is a logical contradiction, output and terminate.

[0081] Verify the logical reasonableness of the SQL statement, focusing on detecting three types of logical contradictions: Condition expression data type matching: compare whether the data types on both sides of the comparison operator are compatible (for example, the numeric field delay_minute is compared with the string '30', or the date field flight_date is compared with the value 20240101).

[0082] GROUP BY and SELECT list consistency: when using GROUP BY, non-aggregated fields in the SELECT list must all be included in the GROUP BY (for example, when GROUP BY flight_no, departure_time cannot appear in SELECT without aggregation).

[0083] Join field compatibility: when joining multiple tables, the data types of the associated fields must be compatible (for example, flights.id is an integer type, and flight_delay.flight_id cannot be a string type).

[0084] Through this step, "correct syntax but logical error" SQL statements are excluded (for example, in the civil aviation scenario, comparing delay time with a string will result in an error result of "'5'>'30'"). Logical verification ensures that the calculation logic of the SQL is consistent with the business rules (for example, GROUP BY needs to be grouped by business dimensions), which is the core step to ensure that the query result is "logically reasonable".

[0085] S340, if all steps pass, determine that the target SQL statement passes the detection; otherwise, determine that the target SQL statement does not pass the detection.

[0086] If S310 (syntax), S320 (metadata), and S330 (logic) all pass, then determine that the target SQL statement itself passes the detection; if any step fails, then determine that the detection does not pass.

[0087] The "full vote through" mechanism of multi-dimensional verification ensures that the simple SQL statement itself is error-free, providing a premise for the reliability of subsequent query results. For simple queries in the civil aviation field (such as single flight time queries), self-detection passing means that the statement is "executable and logically correct", significantly reducing the risk of incorrect results due to SQL itself.

[0088] S400, if the type of the target SQL statement is a preset complex SQL statement type, detecting the query result corresponding to the target SQL statement.

[0089] Further, the detecting the query result corresponding to the target SQL statement comprises the following steps: S410, obtaining key fields in the query result corresponding to the target SQL statement to obtain a query key field list C.

[0090] After executing the target SQL statement, extract all key fields (column names in the result set) in the query result to form list C. These fields are the query dimensions specified by the SELECT clause of the SQL statement, directly reflecting the information type of the result.

[0091] In the civil aviation field, common query result key fields include: flight_no (flight number), departure_time (departure time), punctuality_rate (punctuality rate), delay_minute (delay minutes), etc.

[0092] For example: the target SQL statement is "query the departure time and punctuality rate of CA1835 flight during the 2024 Spring Festival", and the returned result fields are flight_no, departure_time, and punctuality_rate. The key field list C = [flight_no, departure_time, punctuality_rate].

[0093] S420, obtaining a standard key field list D corresponding to the standard answer of the target question text.

[0094] Obtain the key field list D (column names contained in the standard answer) corresponding to the standard answer (i.e. the correct result confirmed by artificial or authoritative sources) of the target question text.

[0095] S430, if D is the same as C or D is a subset of C, obtain the number of rows NUM3 of the query result corresponding to the target SQL statement, and enter S440; otherwise, determine that the detection of the query result corresponding to the target SQL statement does not pass.

[0096] By field comparison, the query result is ensured to contain the core information required by the user's question (fields in D), avoiding "answering the wrong question" (e.g. the user asks about the punctuality rate, and the result only returns the departure time). For the civil aviation field, this step can quickly intercept invalid results with missing information (e.g. a passenger queries the delay reason, and the result only has the flight time), ensuring the relevance of the result.

[0097] In S440, if NUM3 is the same as the number of rows corresponding to the standard answer of the target question text, and the content of each row in the query result corresponding to the target SQL statement is consistent with the content of each row of the standard answer of the target question text, it is determined whether the target SQL statement has an ordeby field; otherwise, it is determined that the query result corresponding to the target SQL statement fails the detection.

[0098] If S430 passes, the number of rows NUM3 of the query result (the number of records in the result set) is obtained and compared with the number of rows (denoted as NUM_std) of the standard answer of the target question text. At the same time, the content of the query result is compared with the content of the standard answer row by row (such as specific numerical values, dates, texts, etc.): if NUM3 = NUM_std, and the content of each row is completely consistent (such as "the punctuality rate of CA1835" is "92%" in both the result and the standard answer), it is determined whether the target SQL statement contains an ORDER BY field; if the number of rows is different (such as the standard answer has 3 records, and the result has 2 records), or there is any row content inconsistency (such as the punctuality rate in the result is "90%", and the standard answer is "92%"), it is determined that the query result fails the detection.

[0099] The accuracy of the query result is verified from "quantity" and "content", ensuring that the result not only contains the required fields, but also accurately matches the actual data (such as the punctuality rate of civil aviation flights, departure time, etc. Key information must be consistent with the real records). This step is the core verification of the "correctness" of the query result, and is directly related to the reliability of information in civil aviation business (such as dispatch personnel relying on accurate delay data to make decisions).

[0100] In S450, if the target SQL statement has an ordeby field, and the order of the rows corresponding to the query result corresponding to the target SQL statement is the same as the order of the rows of the standard answer of the target question text, it is determined that the query result corresponding to the target SQL statement passes the detection; otherwise, it is determined that the query result corresponding to the target SQL statement fails the detection.

[0101] If the target SQL statement contains an ORDER BY field (i.e., specifies the sorting rule of the result, such as ORDER BY departure_time ASC for ascending order of departure time), the row order of the query result needs to be compared with that of the standard answer; if the orders are consistent (e.g., both are sorted by date from early to late), the query result is determined to pass the detection; if the orders are inconsistent (e.g., the SQL specifies ascending order by time, but the result is arranged in descending order), the detection is determined to fail; if the target SQL statement does not contain an ORDER BY field (the order of the result does not affect the semantics), the order does not need to be verified, and the detection is determined to pass (provided that S430 has passed).

[0102] For SQL statements containing sorting requirements, the order of the result is ensured to meet expectations (e.g., sorting by time, on-time rate, etc.), avoiding misinterpretation of information due to sorting errors (e.g., in civil aviation scheduling, if the result of "sorting by departure time" is reversed, it may affect flight scheduling). This step improves the reliability of the query result in terms of "logical integrity," especially for scenarios that require sequential analysis (e.g., trend statistics).

[0103] Steps S410 to S440 constitute a four-layer verification system for the "field integrity-row number matching-content accuracy-sorting consistency" of the query result, ensuring result quality layer by layer: Ensure information relevance: through field comparison, ensure that the result contains the core information required by the user's question, avoiding "answering the wrong question."

[0104] Ensure data accuracy: through row number and content comparison, verify the accurate matching of the result and the standard answer, adapting to the high requirements of the civil aviation field for data authenticity (e.g., flight time, on-time rate, etc., which are key information that cannot be inaccurate).

[0105] Consider logical integrity: through sorting verification, ensure that the result containing sorting requirements meets the logical order, avoiding decision bias due to order errors.

[0106] This process is suitable for both simple SQL statements (which need to be combined with their own detection) and complex SQL statements (as a core detection link), providing final protection for the reliability of the entire Text2SQL solution, especially meeting the core needs of "accurate information and logical rigor" in civil aviation business.

[0107] S500, if the type of the target SQL statement is a preset simple SQL statement type, and both the target SQL statement and the query result corresponding to the target SQL statement pass the detection, it is determined that the data structure of the target SQL statement is correct; otherwise, it is determined that the data structure of the target SQL statement is incorrect.

[0108] Simple SQL queries are typically logically clear (such as single-table queries and basic conditional filtering), and require higher accuracy in syntax and results. By simultaneously validating both the "validity of the SQL statement itself" and the "correctness of the query results," invalid data caused by syntax errors (such as misspelled keywords) or logical oversights (such as incorrect condition judgments) can be avoided, ensuring the rigor of data construction in simple scenarios.

[0109] For example, if a simple SQL query contains a misspelled field name (failing its own check) or the number of rows in the query result does not match the standard answer (failing the result check), it will be judged as an error to prevent erroneous data from flowing into subsequent processes.

[0110] To mitigate the risk of errors in simple scenarios, which are often more basic (such as daily report statistics) applications, errors in simple SQL queries can have a wide-ranging impact. A dual-checking mechanism can minimize low-level errors, improve the reliability of data construction, and reduce the cost of manual secondary verification.

[0111] S600: If the target SQL statement is of the preset complex SQL statement type and the query result corresponding to the target SQL statement passes the test, then the data construction of the target SQL statement is determined to be correct; otherwise, the data construction of the target SQL statement is determined to be incorrect.

[0112] Complex SQL queries often involve multi-table joins, nested subqueries, aggregate functions, and other intricate logic, and their syntax may be more cumbersome. However, the core objective is to obtain correct query results. This approach allows for the use of "correct query results" as the primary criterion even when the statement itself may have non-fatal issues (such as incorrect clause order that doesn't affect the result). This avoids rejecting valid results due to excessive focus on syntax, providing flexibility to adapt to complex scenarios.

[0113] For example, if the order of the GROUP BY clause in a complex SQL statement is not exactly the same as that in the SELECT list, but the result is correct after execution (possibly due to database compatibility mechanisms), then result checking can determine that it is correct and avoid misjudgment.

[0114] The core value of complex SQL lies in its ability to produce results that meet requirements, rather than the absolute standardization of its syntax. By prioritizing result validation, invalid rejections due to syntactic details (such as non-standard formats) can be reduced, improving the efficiency of complex data construction while ensuring the validity of the final data.

[0115] Overall synergistic effect: Layered validation balances rigor and flexibility: Strictly validate the syntax and results of simple SQL queries, and focus on the correctness of results for complex SQL queries. Implement a layered strategy of "emphasizing standardization in simple scenarios and emphasizing results in complex scenarios" to ensure the reliability of basic data while avoiding efficiency losses in complex scenarios.

[0116] Improve the efficiency and accuracy of data construction: Reduce unnecessary validation thresholds (such as non-fatal syntax issues in complex SQL) while ensuring the correctness of results. Optimize the validation process and reduce the cost of manual intervention while guaranteeing data quality.

[0117] Adapt to business needs in different scenarios: Whether it's a simple query or a complex analysis, the core objective is "final data validity." Differentiated validation rules ensure that the constructed results of different types of SQL can meet the requirements of the corresponding business scenarios (such as the standardization of simple scenarios and the validity of results in complex scenarios).

[0118] In this embodiment, the question text is first categorized based on its intent quantity, intent confidence, and generated SQL statement information. Then, a dual verification process of "self-detection + query result detection" is implemented for simple SQL statements, while query result detection is emphasized for complex SQL statements, thus achieving a differentiated detection strategy. This approach avoids query bias caused by errors in the statements themselves through comprehensive detection of simple SQL statements, while improving overall processing efficiency by simplifying the detection process for complex SQL statements. It effectively solves the problem of difficulty in balancing accuracy and efficiency caused by a single detection strategy in existing technologies, significantly improving the applicability of the large-scale Text2SQL model in the civil aviation field, ensuring the accuracy and response speed of business queries, and better meeting the high data processing requirements of civil aviation operations.

[0119] Furthermore, although the steps of the method in this disclosure are described in a specific order in the accompanying drawings, this does not require or imply that the steps must be performed in that specific order, or that all the steps shown must be performed to achieve the desired result. Additional or alternative steps may be omitted, multiple steps may be combined into one step, and / or a step may be broken down into multiple steps.

[0120] Embodiments of the present invention also provide a non-transitory computer-readable storage medium that can be disposed in an electronic device to store at least one instruction or at least one program related to implementing a method in the method embodiments, wherein the at least one instruction or the at least one program is loaded and executed by the processor to implement the method provided in the above embodiments.

[0121] The program product may employ any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0122] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A readable signal medium may also be any readable medium other than a readable storage medium, capable of sending, propagating, or transmitting programs for use by or in conjunction with an instruction execution system, apparatus, or device.

[0123] The program code contained on the readable medium may be transmitted using any suitable medium, including but not limited to wireless, wired, optical fiber, RF, etc., or any suitable combination thereof.

[0124] Program code for performing the operations of this application can be written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Java and C++, and conventional procedural programming languages ​​such as C or similar languages. The program code can execute entirely on the user's computing device, partially on the user's device, as a standalone software package, partially on the user's computing device and partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0125] Embodiments of the present invention also provide an electronic device, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0126] The electronic device is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments in this application.

[0127] Electronic devices are manifested in the form of general-purpose computing devices. Components of an electronic device may include, but are not limited to: at least one processor, at least one memory, and a bus connecting different system components (including memory and processor).

[0128] The memory stores program code that can be executed by the processor, causing the processor to perform the steps in the various embodiments described in this specification.

[0129] The memory may include readable media in the form of volatile memory, such as random access memory (RAM) and / or cache memory, and may further include read-only memory (ROM).

[0130] The memory may also include programs / utilities having a set (at least one) of program modules, including but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of these examples may include an implementation of a network environment.

[0131] A bus can represent one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor, or a local bus that uses any of the various bus structures.

[0132] Electronic devices can also communicate with one or more external devices (e.g., keyboards, pointing devices, Bluetooth devices, etc.), one or more devices that enable user interaction with the electronic device, and / or any device that enables the electronic device to communicate with one or more other computing devices (e.g., routers, modems, etc.). This communication can be achieved through input / output (I / O) interfaces. Furthermore, electronic devices can communicate with one or more networks (e.g., local area networks (LANs), wide area networks (WANs), and / or public networks, such as the Internet) via network adapters. The network adapter communicates with other modules of the electronic device via a bus. It should be understood that other hardware and / or software modules can be used in conjunction with the electronic device, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0133] From the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of this disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) or on a network, including several instructions to cause a computing device (such as a personal computer, server, terminal device, or network device, etc.) to execute the methods according to the embodiments of this disclosure.

[0134] Embodiments of the present invention also provide a computer program product including program code, which, when the program product is run on an electronic device, causes the electronic device to perform the steps of the methods described above in various exemplary embodiments of the present invention.

[0135] While specific embodiments of the invention have been described in detail by way of examples, those skilled in the art should understand that the examples are for illustrative purposes only and are not intended to limit the scope of the invention. Those skilled in the art should also understand that various modifications can be made to the embodiments without departing from the scope and spirit of the invention.

Claims

1. A method for constructing and detecting large-scale Text2SQL data, characterized in that, The method includes the following steps: S100: Input the target question text into the preset large language model to obtain the target SQL statement corresponding to the target question text; S200, determine the type of the target SQL statement based on the number of intents corresponding to the target question text, the confidence level of each intent, and the information of the target SQL statement; S300, If the target SQL statement is of the preset simple SQL statement type, then the target SQL statement itself and the corresponding query results are checked. S400, If the target SQL statement is of a preset complex SQL statement type, then the query results corresponding to the target SQL statement are checked; S500: If the target SQL statement is of the preset simple SQL statement type, and both the target SQL statement itself and the corresponding query result pass the test, then the data construction of the target SQL statement is determined to be correct; otherwise, the data construction of the target SQL statement is determined to be incorrect. S600: If the target SQL statement is of the preset complex SQL statement type and the query result corresponding to the target SQL statement passes the test, then the data construction of the target SQL statement is determined to be correct; otherwise, the data construction of the target SQL statement is determined to be incorrect.

2. The method for constructing and detecting large-scale Text2SQL data according to claim 1, characterized in that, Step S200 includes the following steps: S210, parse each key field corresponding to the target SQL statement to obtain the list of key fields T corresponding to the target SQL statement; S220, if NUM1 / NUM≥λ, then the target SQL statement is determined to be a complex SQL statement type; where NUM1 is the number of preset specified fields contained in T, NUM is the number of key fields in T, and λ is the first preset weight; the specified fields correspond to fixed query methods.

3. The method for constructing and detecting large-scale Text2SQL data according to claim 2, characterized in that, Following step S220, the method further includes the following steps: S230, if NUM1 / NUM < λ, then input the target question text into the preset intent recognition model to obtain the intent list A = (A1, A2, ..., A...) corresponding to the target question text. i A n ), i=1, 2,...,n; among them, A i Let be the i-th intent corresponding to the target question text, and n be the number of intents corresponding to the target question text. S240, Obtain the intent confidence list η = (η1, η2, ..., η3) corresponding to A. i , ..., η n ); where η i The intention of the problem is A i Confidence level; S250, if the maximum confidence level η in η is max If μ1 is greater than or equal to 1, then the target SQL statement is determined to be a simple SQL statement; μ1 is the first preset confidence threshold. S260, if η max If <μ1, then obtain the number of intermediate confidence levels NUM2 in η; where, the intermediate confidence level is the confidence level that is greater than the second preset confidence threshold μ2 and less than μ1; S270, if NUM2 / n≥τ, then the type of the target SQL statement is determined to be a complex SQL statement type; where τ is the second preset weight; otherwise, the type of the target SQL statement is determined manually.

4. The method for constructing and detecting large-scale Text2SQL data according to claim 1, characterized in that, The detection of the target SQL statement itself includes the following steps: S310 performs lexical and syntactic analysis on the target SQL statement through the SQL parser, and checks for keyword errors, clause order errors, and symbol format errors. If errors are found, the parser outputs the results and terminates the process. S320 compares the table names and field names referenced in the target SQL statement with the database metadata to detect invalid tables, fields, and ambiguous field references. If any problems are found, the process is output and terminated. S330 verifies the data type matching of the conditional expressions in the target SQL statement, the consistency between the GROUP BY clause and the SELECT list, and the compatibility of the join fields. If there is a logical contradiction, output the result and terminate. S340: If all steps pass, the target SQL statement detection is deemed successful; otherwise, the target SQL statement detection is deemed unsuccessful.

5. The method for constructing and detecting large-scale Text2SQL data according to claim 1, characterized in that, The detection of the query results corresponding to the target SQL statement includes the following steps: S410, retrieve the key fields from the query results corresponding to the target SQL statement to obtain the list of key query fields C; S420, Obtain the list of standard key fields D corresponding to the standard answer to the target question text; S430: If D is the same as C or D is a subset of C, then obtain the number of rows NUM3 of the query result corresponding to the target SQL statement and proceed to S440; otherwise, determine that the query result corresponding to the target SQL statement fails the test. S440, if NUM3 has the same number of rows as the standard answer to the target question text and the content of each row in the query result corresponding to the target SQL statement is consistent with the content of each row in the standard answer to the target question text, then determine whether the target SQL statement has an ORDEBY field; otherwise, determine that the query result corresponding to the target SQL statement fails the test. S450. If the target SQL statement has an ORDEBIY field, and the row order of the query result corresponding to the target SQL statement is the same as the row order of the standard answer to the target question text, then the query result corresponding to the target SQL statement is determined to pass the test; otherwise, the query result corresponding to the target SQL statement is determined to fail the test.

6. The method for constructing and detecting large-scale Text2SQL data according to claim 1, characterized in that, If the target question text contains preset fuzzy time terms, then the target SQL statement corresponding to the target question text contains a fuzzy time term conversion function; the fuzzy time conversion function is used to convert the fuzzy time terms into the corresponding time range.

7. The method for constructing and detecting large-scale Text2SQL data according to claim 2, characterized in that, The value of λ ranges from [0.9, 0.95].

8. A non-transitory computer-readable storage medium, wherein the storage medium stores at least one instruction or at least one program segment, characterized in that, The at least one instruction or the at least one program segment is loaded and executed by the processor to implement the large model Text2SQL data construction and detection method as described in any one of claims 1-7.

9. An electronic device, characterized in that, Includes a processor and the non-transitory computer-readable storage medium as described in claim 8.

Images