A data construction and detection method, device and medium of a large model Text2SQL

Abstract

The application provides a large model Text2SQL data construction and detection method, device and medium, relates to the large model Text2SQL technical field, and the method comprises the following steps: obtaining a target SQL statement corresponding to a target question text; determining the type of the target SQL statement; detecting the target SQL statement itself and the query result corresponding to the target SQL statement; detecting the query result corresponding to the target SQL statement; determining whether the target SQL statement construction is correct according to the detection result; the application effectively solves the problem that the accuracy and efficiency are difficult to be considered in the prior art due to the single detection strategy, significantly improves the applicability of the large model Text2SQL in the civil aviation field, ensures the accuracy and response speed of business query, and better meets the high requirements of civil aviation business on data processing.

Description

Technical Field

[0001] This invention relates to the field of large-scale Text2SQL technology, and in particular to a method, device and medium for constructing and detecting large-scale Text2SQL data. Background Technology

[0002] In the civil aviation sector, business scenarios such as flight status inquiries, ticket information retrieval, and baggage regulations often require converting users' natural language questions into SQL statements to retrieve answers from the database. This process (Text2SQL) largely relies on large language models. However, existing methods for detecting SQL statements generated by large models have significant shortcomings: Firstly, they fail to consider the differences in the intent of the question text—some questions contain only a single, explicit intent (such as "the departure time of a certain flight"), resulting in simple SQL statement structures, while others contain multiple or ambiguous intents (such as "comparing the on-time rate of a certain route this week with last week and analyzing the reasons"), often resulting in SQL statements involving multi-table joins and complex subqueries. Secondly, existing detection solutions often adopt a uniform standard, either performing comprehensive but redundant checks on all SQL statements (including verification of the statement's syntax, logic, and query results), leading to low efficiency in detecting complex statements, or simplifying the detection process, making it easy for simple statements to produce erroneous results due to undetected grammatical or logical errors. It is difficult to balance the accuracy and efficiency of detection, and cannot meet the stringent requirements of the civil aviation sector for the immediacy and accuracy of data queries. Summary of the Invention

[0003] To address the aforementioned technical problems, the technical solution adopted by this invention is as follows:

[0004] According to a first aspect of this application, a method for constructing and detecting large-scale Text2SQL data is provided, the method comprising the following steps:

[0005] S100: Input the target question text into the preset large language model to obtain the target SQL statement corresponding to the target question text;

[0006] 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;

[0007] 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.

[0008] 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;

[0009] 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.

[0010] 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.

[0011] According to another aspect of this application, a non-transitory computer-readable storage medium is also provided, wherein at least one instruction or at least one program is stored in the storage medium, and the at least one instruction or at least one program is loaded and executed by a processor to implement the above-described large-model Text2SQL data construction and detection method.

[0012] According to another aspect of this application, an electronic device is also provided, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0013] The present invention has at least the following beneficial effects:

[0014] The large-scale Text2SQL data construction and detection method of this invention first classifies the question text according to its intent quantity, intent confidence, and generated SQL statement information. Then, it implements dual verification of "self-detection + query result detection" for simple SQL statements, while focusing on query result detection 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, and improves 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. Attached Figure Description

[0015] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0016] Figure 1The flowchart illustrates the data construction and detection method for the large-scale Text2SQL model provided in this embodiment of the invention. Detailed Implementation

[0017] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0018] It should be noted that, based on this disclosure, those skilled in the art will 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, any number of aspects set forth herein can be used to implement the device and / or practice the method. Furthermore, this device and / or practice the method can be implemented using other structures and / or functionalities besides one or more of the aspects set forth herein.

[0019] The following will refer to Figure 1 The flowchart shown illustrates a method for constructing and detecting large-scale Text2SQL data, introducing such a method.

[0020] The data construction and testing method for this large-scale Text2SQL model can include the following steps:

[0021] S100: Input the target question text into the preset large language model to obtain the target SQL statement corresponding to the target question text.

[0022] The user inputs the natural language question (target question text) raised in the civil aviation scenario into a pre-trained large language model (such as the GPT model or LLaMA model fine-tuned for the civil aviation field). The model generates the corresponding SQL statement (target SQL statement) based on the question semantics.

[0023] For example, if a user's question is "to query the departure time of flight CA1835 on October 1, 2024", after inputting the question into 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'.

[0024] Furthermore, 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.

[0025] 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:

[0026] Recognition and matching of fuzzy time words:

[0027] 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:

[0028] 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.

[0029] 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.

[0030] Generate SQL statements that include transformation functions:

[0031] 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.

[0032] Example 1:

[0033] Target question text: "How many times was flight CA1835 delayed last year?", target SQL statement generated by the large language model:

[0034] SELECT COUNT(*) AS delay_count

[0035] FROM flight_delays

[0036] WHERE flight_no = 'CA1835'

[0037] AND delay_date BETWEEN TO_TIME_RANGE('last year'); -- Embedded conversion function with the parameter "last year".

[0038] Example 2:

[0039] 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:

[0040] SELECT SUM(flight_count) AS total_flights

[0041] FROM route_stats

[0042] WHERE departure_city='Beijing'

[0043] AND arrival_city='Shanghai'

[0044] AND stat_date BETWEEN TO_TIME_RANGE('During the Spring Festival of 2024'); -- Embedded conversion function with the parameter "During the Spring Festival of 2024".

[0045] The pre-defined large language model can be trained in the following way:

[0046] Domain-specific fine-tuning: adapting to specific scenarios (such as civil aviation)

[0047] Data preparation: Collect professional text and SQL examples from the target field (e.g., civil aviation), focusing on:

[0048] Vague time terms in the field include: "Spring Festival travel rush", "flight season" (a civil aviation term, such as the summer and autumn flight season of 2024, which is from March 31 to October 26), and "holidays".

[0049] Example of SQL query in the field: For example, the SQL for "querying the number of delays of CA1835 during the 2024 Spring Festival travel rush" must include custom conversion functions such as TO_TIME_RANGE('2024 Spring Festival travel rush').

[0050] Training method: Continue training the model with domain data (freezing some low-level parameters and only fine-tuning the upper layers) to familiarize the model with the domain's time expression habits and SQL conventions. For example, the model will learn that "flight season" in civil aviation is a fixed six-month cycle, rather than the "season" in everyday language.

[0051] Task fine-tuning: Focus on "Natural Language → SQL with transformation functions".

[0052] Data Construction: Construct a "question-SQL" pairing dataset, where the questions contain fuzzy time terms, and the SQL statements contain corresponding transformation functions. For example:

[0053] The SQL statement corresponding to "the total number of flights from Beijing to Shanghai last year" is: SELECT SUM(num) FROM flights WHERE dep='Beijing' AND arr='Shanghai' AND date BETWEEN TO_TIME_RANGE('last year').

[0054] Training objective: To enable the model to learn the mapping relationship between "fuzzy time terms → transformation function parameters". For example, through a large number of samples, the model learns that when "last year" appears in the question, the SQL must include TO_TIME_RANGE('last year'), and the function must return "the start and end dates of the previous calendar year".

[0055] The automatic generation of SQL statements containing fuzzy time transformation functions by large language models is a key link connecting natural language queries with precise database retrieval, significantly improving the practicality and reliability of Text2SQL in the civil aviation field.

[0056] 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.

[0057] Furthermore, step S200 includes the following steps:

[0058] S210, parse each key field corresponding to the target SQL statement to obtain the key field list T corresponding to the target SQL statement.

[0059] The target SQL statement generated by the large language model is parsed in a structured manner, and all key fields (i.e., the core elements that determine the SQL query logic and complexity) are extracted and formed into a list T.

[0060] In the civil aviation sector, "key fields" include, but are not limited to:

[0061] Basic query elements: table name (e.g., flights, flight_delays), query fields (e.g., departure_time, punctuality_rate);

[0062] Logical operators: JOIN (multi-table join), WHERE (filtering condition), GROUP BY (grouping), SUBQUERY (subquery).

[0063] Functions: Fuzzy time conversion functions (such as TO_TIME_RANGE()), window functions (such as RANK()), and aggregation functions (such as AVG()).

[0064] Special markers: Relationship keys for multi-table joins (e.g., flight_no is used to join the flights and flight_delays tables).

[0065] By extracting key fields through structured parsing, the "complexity characteristics" of SQL statements are transformed into a quantifiable list of elements. This avoids subjective judgments about SQL complexity (such as "this SQL feels complex") and provides an objective and repeatable basis for subsequent type classification. Simultaneously, the parsing process focuses on key elements unique to the civil aviation field (such as fuzzy time conversion functions and flight table join keys), ensuring that the extracted fields are highly relevant to the business scenario and improving the targeting of subsequent judgments.

[0066] 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.

[0067] Preset specified fields: These refer to key fields corresponding to "fixed complex query methods". In the civil aviation field, these include: multi-table join operators (JOIN), subqueries (SUBQUERY), window functions (RANK() / ROW_NUMBER()), multiple occurrences of fuzzy time conversion functions (TO_TIME_RANGE() ≥ 2 times), cross-database table references, etc. (The appearance of these fields usually means that the SQL logic is more complex).

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

[0069] Judgment rule: If the ratio is greater than or equal to the first preset weight λ (λ is an empirical value, such as 0.7, which can be adjusted according to the civil aviation business scenario), then the target SQL is determined to be a "complex SQL statement type".

[0070] By using the quantitative rule of "specifying the proportion of fields", an objective classification of SQL types is achieved:

[0071] Avoid subjective bias: Instead of relying on human judgment of "whether it is complex", the decision is made automatically through data ratios to ensure that the judgment standards are consistent in different scenarios.

[0072] Adapting to the characteristics of civil aviation scenarios: Specified fields focus on high-frequency and complex query elements in the civil aviation field (such as multi-table flight data and multiple fuzzy time comparisons), making the partitioning results more in line with actual business needs.

[0073] Flexible and adjustable: By adjusting the λ value (such as increasing λ for scenarios with extremely high security requirements and relaxing the criteria for judging complex SQL), it can adapt to different business definitions of "complex" and enhance the versatility of the solution.

[0074] In summary, S210 and S220 provide a scientific and practical method for SQL type classification through the logic of "parsing-quantification," which is a key prerequisite for subsequent differential detection (dual detection for simple SQL and result-oriented detection for complex SQL).

[0075] Furthermore, after step S220, the method further includes the following steps:

[0076] 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.

[0077] When "the specified field percentage NUM1 / NUM < λ" in S220 (i.e., from the perspective of SQL key fields, it does not yet meet the characteristics of complex types), it is necessary to further analyze the semantic intent of the target question text through a preset intent recognition model and output a list A containing all identified intents. Here, "intent" refers to the query goal that the user wants to achieve through the question, and n is the number of intents (n≥1).

[0078] In the civil aviation industry, common intents include: querying the departure time of a single flight, comparing the on-time rates of two flights, calculating the average monthly flight volume of a route, and analyzing the causes of delays. However, some target question texts may correspond to multiple intents, or the intent may be unclear, resulting in SQL statements that appear simple but actually contain multiple query methods. Such SQL statements cannot be directly judged as simple or complex from their structure alone.

[0079] When the complexity characteristics of key SQL fields are not obvious (NUM1 / NUM < λ), intent analysis is used to supplement the judgment criteria, avoiding reliance solely on SQL structure while ignoring the semantic complexity of the user's original question (e.g., seemingly simple SQL 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 intent is highly relevant to the business scenario, and providing semantic support for subsequent type classification.

[0080] The pre-defined intent recognition model can be trained in the following way:

[0081] Collect texts of real user questions from the civil aviation industry, covering common scenarios:

[0082] Basic queries: such as "departure time of CA1835 flight" or "what flights are available from Beijing to Shanghai".

[0083] Queries with fuzzy time terms: such as "on-time performance of MU5102 during last year's Spring Festival" or "recent flight delays".

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

[0085] Data sources may include civil aviation customer service logs, APP user query records, and simulated standardized questions, with a total recommended amount of no less than 100,000 records (ensuring coverage of various intents).

[0086] Based on the needs of civil aviation operations, define structured intent tags (which must be aligned with subsequent SQL type judgment logic), for example:

[0087] Single intent tags: [Query basic flight information] (departure time, aircraft type, etc.), [Query delay data] (number of delays, delay rate, etc.), [Statistics on flight volume], etc.

[0088] Multi-intent tags: [Query basic flight information + query delay data] (e.g., query both time and delay), [Compare multiple flight data] (e.g., compare the on-time rates of two flights), etc.

[0089] Fuzzy time-related intents: Intents containing fuzzy time terms need to be specifically labeled, such as [Query last year's flight data], [Query flight information during the Spring Festival] (to facilitate targeted learning by the model).

[0090] Data annotation:

[0091] The collected question texts are manually annotated by experts or annotators in the civil aviation field. Each text corresponds to one or more intent tags (for multiple intents, all sub-intents must be clearly defined), and the consistency of the annotation is recorded (if multiple people annotate the same text, the consistency rate must be ≥90%). For example: Question "Departure time and number of delays of CA1835 last year" → annotated as [Query basic flight information (time) + Query delay data (number)]; Question "On-time rate of flights from Beijing to Shanghai during the Spring Festival" → annotated as [Query on-time rate + fuzzy time (Spring Festival)].

[0092] Prioritize pre-trained language models (such as BERT, RoBERTa, ERNIE, etc.). These models learn language rules through massive amounts of general text and are suitable for handling ambiguous expressions in natural language (such as complex questions containing "last year" or "Spring Festival"). For example, choose a "BERT model fine-tuned for the civil aviation field" (e.g., pre-trained on civil aviation encyclopedias, manuals, etc., based on a general BERT model, and then fine-tuned for intent recognition) to improve the understanding of domain terminology. Then, use existing model training methods to obtain the pre-defined intent recognition model.

[0093] S240, Obtain the intent confidence list η = (η1, η2, ..., η) corresponding to A. i , ..., η n ); where η i The intention of the problem is A i The confidence level.

[0094] By quantifying the explicitness of intent through confidence, we avoid judging solely based on the "number of intents" (e.g., even with two intents, the semantic complexity differs greatly between high and low confidence levels). Confidence provides a quantifiable standard for subsequent judgments on the "explicitness of intent," making type classification more precise.

[0095] 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.

[0096] Calculate the maximum value η in the intention confidence list η. max (i.e., the credibility of the most explicit intention), if η max If the value is greater than or equal to the first preset confidence threshold μ1 (μ1 is usually set to 0.9-0.95, representing "high confidence"), then the target SQL is determined to be a "simple SQL statement type".

[0097] Logic: η max A value ≥μ1 indicates the existence of a dominant and clear core intent. Even if there are other secondary intents, the overall semantic complexity is low, and the corresponding SQL logic is usually relatively simple.

[0098] By quickly identifying simple scenarios through "high-confidence dominant intent," the system avoids over-analyzing semantically clear questions (such as queries with a single, clear intent), thus improving the efficiency of type classification. At the same time, the μ1 setting can adapt to the "clarity" requirements of the civil aviation industry (such as when passengers query information for a single flight, their intent is usually highly clear).

[0099] S260, if η max If <μ1, then obtain the number of intermediate confidence levels NUM2 in η; where intermediate confidence level is the confidence level that is greater than the second preset confidence threshold μ2 and less than μ1.

[0100] If η max If μ1 < η (i.e., there is no dominant intent with high confidence, and the overall intent is unclear), then count the number of "intermediate confidence" values ​​NUM2 in list η. Here, "intermediate confidence" refers to values ​​that satisfy μ2 < η. i η <μ1 i (μ2 is usually set to 0.6-0.9, which means "medium confidence"), that is, the intention has a certain degree of credibility but is not clear.

[0101] For example, if a user's question is "What is the recent status of flights CA1835 and MU5102?", the intent recognition model outputs: A = (A1: query the recent departure time of CA1835, A2: query the recent on-time rate of MU5102, A3: query the reasons for recent delays of both flights), η = (η1 = 0.65, η2 = 0.62, η3 = 0.75). If μ1 = 0.9 and μ2 = 0.6, then the confidence scores of all three intents satisfy 0.6 < η. i <0.9, therefore NUM2=3.

[0102] By filtering for "intermediate confidence" intents, we focus on those intents that are "somewhat semantically directed but not clear." These intents are often the key to increased problem complexity (e.g., when user expressions are vague, the model struggles to accurately identify the core needs). Statistical NUM2 provides a quantitative basis for subsequent judgments on "whether the intent is scattered and complex."

[0103] 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.

[0104] Calculate the proportion of intermediate confidence level NUM2 / n. If this proportion is greater than or equal to the second preset weight τ (τ is usually set to 0.5-0.7, indicating that "most intentions are intermediate confidence level"), then the target SQL is determined to be "complex SQL statement type"; otherwise, the type is determined manually.

[0105] Logic: NUM2 / n≥τ indicates that most intentions are vague and scattered, and the semantics of the user's question are complex (such as unclear expression, implicit multi-dimensional requirements). The corresponding SQL usually needs to handle complex logic (such as multi-intention fusion, ambiguity resolution), so it is determined to be a complex type; if the proportion is insufficient, the intention complexity is between simple and complex, and manual intervention is required to ensure accuracy.

[0106] For the scenario of "most intentions are vague and scattered", it is automatically determined as a complex type, adapting to the complexity characteristics of multi-dimensional queries in the civil aviation field (such as "flight situation" may include multiple vague intentions such as time, aircraft type, on-time rate, etc.); for boundary cases (insufficient proportion of intermediate confidence), manual judgment is introduced to avoid the limitations of automated rules and balance efficiency and accuracy; the adjustability of τ (such as high requirements for intention clarity in the civil aviation customer service scenario, τ can be increased) enables the solution to adapt to the accuracy requirements of different business scenarios.

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

[0108] Furthermore, the detection of the target SQL statement itself includes the following steps:

[0109] S310, perform lexical and syntactic analysis on the target SQL statement through a SQL parser, detect whether there are keyword errors, clause order errors, and symbol format errors, and if there are errors, output and terminate.

[0110] Perform lexical analysis (split the statement into the smallest units such as keywords, identifiers, operators, etc.) and syntactic analysis (verify whether the combination of units conforms to SQL syntax rules) on the target SQL statement through a SQL parser (such as ANTLR, SQLFluff, etc.), and focus on detecting three types of errors:

[0111] Keyword error: misspelling of keywords (such as SELCT instead of SELECT, JION instead of JOIN).

[0112] 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).

[0113] Symbol format error: unclosed parentheses / quotation marks (such as WHERE name='张三' missing the right quotation mark), operator error (such as using == instead of = to represent equality), extra commas (such as SELECT name, age, FROM student), etc.

[0114] As the first step in SQL's self-checking process, syntax verification directly eliminates invalid statements caused by basic errors such as spelling and formatting, avoiding wasted resources in subsequent checks. In the civil aviation industry, correct syntax is the foundation for reliable SQL execution (for example, if a flight query cannot be executed due to incorrect keywords, it will directly affect the retrieval of passenger information). This step can quickly intercept obvious errors and improve detection efficiency.

[0115] 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.

[0116] Extract table names (such as flights, flight_delays) and field names (such as departure_time, flight_no) referenced in the target SQL statement, compare them with database metadata (pre-stored table structure, field list, data type, etc.), and detect three types of problems:

[0117] Invalid table / field: The referenced table or field does not exist in the database (e.g., the table name is mistakenly written as flight instead of flight, or the field name departure should be departure_time).

[0118] Ambiguous field references: When querying multiple tables, the table aliases are not used to distinguish fields with the same name (e.g., both the flights and flight_status tables have a status field, but the SQL directly writes status without specifying f.status or fs.status).

[0119] This step ensures that the tables and fields referenced by the SQL statement are consistent with the actual database structure, avoiding query failures due to "incorrect table / field lookups" (e.g., in a civil aviation database, the `flights` table stores basic flight information, and the `flight_delays` table stores delay data; confusing the two tables will lead to completely incorrect results). Metadata validation is crucial to ensuring that the SQL "points to the correct data," laying the foundation for subsequent logical validation.

[0120] 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 any logical contradictions are found, the statement will output the results and terminate.

[0121] Verify the logical validity of SQL statements, focusing on detecting three types of logical contradictions:

[0122] Conditional expression data type matching: Whether the data types on both sides of the comparison operator are compatible (e.g., comparing the numeric field delay_minute with the string '30', or comparing the date field flight_date with the numeric 20240101).

[0123] GROUP BY and SELECT list consistency: When using GROUP BY, all non-aggregated fields in the SELECT list must be included in GROUP BY (e.g., when using GROUP BY flight_no, the SELECT list cannot contain the non-aggregated departure_time).

[0124] Join field compatibility: When joining multiple tables, the data types of the joined fields must be compatible (e.g., if flights.id is an integer, flight_delay.flight_id cannot be a string).

[0125] This step eliminates SQL statements that are syntactically correct but logically flawed (e.g., in civil aviation scenarios, comparing delay times using strings can lead to the incorrect result "'5'>'30'"). Logical validation ensures that the SQL's calculation logic conforms to business rules (e.g., GROUP BY must be grouped by business dimension), which is a core step in guaranteeing that the query results are logically sound.

[0126] S340: If all steps pass, the target SQL statement detection is deemed successful; otherwise, the target SQL statement detection is deemed unsuccessful.

[0127] If S310 (syntax), S320 (metadata), and S330 (logic) all pass, the target SQL statement itself is considered to have passed the test; if any step fails, the test is considered to have failed.

[0128] A multi-dimensional validation "unanimous pass" mechanism ensures that simple SQL statements are error-free, providing a prerequisite for the reliability of subsequent query results. For simple queries in the civil aviation field (such as single flight time queries), passing the self-check means that the statement is "executable and logically correct," significantly reducing the risk of erroneous results due to problems with the SQL itself.

[0129] S400: If the target SQL statement is of a preset complex SQL statement type, then the query results corresponding to the target SQL statement will be checked.

[0130] Furthermore, the detection of the query results corresponding to the target SQL statement includes the following steps:

[0131] S410, retrieve the key fields from the query results corresponding to the target SQL statement to obtain the list of key fields C.

[0132] After executing the target SQL statement, extract all key fields (i.e., column names in the result set) from the query results 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 results.

[0133] In the civil aviation sector, common key fields for query results include: flight_no (flight number), departure_time (departure time), punctuality_rate (punctuality rate), and delay_minute (delay minutes).

[0134] For example, if the target SQL statement is "query the departure time and on-time rate of flight CA1835 during the 2024 Spring Festival", and the fields returned after execution are flight_no, departure_time, and punctuality_rate, then the key field list C = [flight_no, departure_time, punctuality_rate].

[0135] S420, Obtain the list of standard key fields D corresponding to the standard answer to the target question text.

[0136] Obtain the list of key fields D (column names included in the standard answer) corresponding to the standard answer (i.e., the correct result confirmed by human or authoritative sources) of the target question text.

[0137] 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.

[0138] By comparing fields, we ensure that the query results contain the core information needed for the user's question (fields in D), avoiding irrelevant answers (such as a user asking about on-time performance, but the result only returning the departure time). In the civil aviation field, this step can quickly intercept invalid results with missing information (such as a passenger querying for the reason for the delay, but the result only returning the flight time), ensuring the relevance of the results.

[0139] 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.

[0140] If S430 passes, obtain the number of rows in the query result NUM3 (the number of records in the result set) and compare it with the number of rows in the standard answer to the target question text (denoted as NUM_std). At the same time, compare the content of the query result with the content of the standard answer line by line (such as specific values, dates, text, etc.): If NUM3 = NUM_std, and the content of each line is completely consistent (such as "CA1835's on-time rate" being "92%" in both the result and the standard answer), then continue to determine whether the target SQL statement contains an ORDER BY field; if the number of rows is different (such as the standard answer having 3 records and the result having 2), or if there is any inconsistency in the content of any line (such as the on-time rate being "90%" in the result and "92%" in the standard answer), then the query result detection is deemed to have failed.

[0141] Verifying the accuracy of query results involves both "quantity" and "content," ensuring that the results not only include the required fields but also accurately match actual data (such as the on-time rate and departure time of civil aviation flights, which must be consistent with the actual records). This step is the core verification of the "correctness" of the query results, directly related to the reliability of information in civil aviation operations (such as dispatchers relying on accurate delay data to make decisions).

[0142] 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.

[0143] If the target SQL statement contains an ORDER BY field (i.e., specifies the sorting rules for the results, such as ORDER BY departure_time ASC in ascending order of departure time), then the row order of the query results needs to be compared with the row order of the standard answer. If the order is consistent (e.g., both are sorted by date from earliest to latest), then the query result is deemed to have passed the test. If the order is inconsistent (e.g., the SQL specifies ascending order by time, but the results are sorted in descending order), then the test is deemed to have failed. If the target SQL statement does not contain an ORDER BY field (the result order does not affect the semantics), then there is no need to verify the order, and the test is deemed to have passed directly (provided that S430 has passed).

[0144] For SQL statements that include sorting requirements, ensure that the order of the results matches the expected order (e.g., sorting by time, on-time rate, etc.) to avoid misunderstandings caused by sorting errors (e.g., in civil aviation scheduling, if the results of "sorting by departure time" are in the wrong order, it may affect flight arrangements). This step improves the reliability of the query results in terms of "logical integrity," and is especially suitable for scenarios that require sequential analysis (e.g., trend statistics).

[0145] Steps S410 to S440 constitute a four-layer verification system for the query results: "field integrity - row count matching - content accuracy - sorting consistency," ensuring result quality layer by layer.

[0146] Ensure information relevance: By comparing fields, ensure that the results contain the core information needed for the user's question, and avoid "answering the wrong question".

[0147] Ensure data accuracy: By comparing the number of rows and the content, verify the precise match between the results and the standard answers to meet the high requirements of data authenticity in the civil aviation field (such as the fact that key information such as flight time and on-time rate must not be wrong).

[0148] Ensuring logical integrity: Through sorting verification, ensure that results with sorting requirements conform to the logical order, avoiding decision-making biases caused by incorrect order.

[0149] This process is applicable to both simple SQL statements (which require self-testing) and complex SQL statements (as a core testing step), providing the ultimate guarantee for the reliability of the entire Text2SQL solution, and is particularly in line with the core requirements of "accurate information and rigorous logic" in civil aviation business.

[0150] 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.

[0151] 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.

[0152] 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.

[0153] 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.

[0154] 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.

[0155] 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.

[0156] 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.

[0157] 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.

[0158] Overall synergistic effect:

[0159] Layered validation balances rigor and flexibility:

[0160] 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.

[0161] Improve the efficiency and accuracy of data construction:

[0162] 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.

[0163] Adapt to business needs in different scenarios:

[0164] 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).

[0165] 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.

[0166] 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.

[0167] 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.

[0168] 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.

[0169] 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.

[0170] 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.

[0171] 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).

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

[0173] 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.

[0174] 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).

[0175] 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.

[0176] 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).

[0177] 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.

[0178] 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.

[0179] 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.

[0180] 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.

[0181] 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.

[0182] 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. 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; 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, ..., η) 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.

2. 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.

3. 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.

4. 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 term conversion function is used to convert the fuzzy time terms into the corresponding time range.

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

6. 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-5.

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

Images