Civil aviation data quality dynamic analysis method and system based on Text2SQL large model

Abstract

The invention discloses a civil aviation data quality dynamic analysis method and system based on a Text2SQL large model, and belongs to the technical field of data fusion. The method comprises the following steps: receiving civil aviation data quality analysis natural language query; time, places, index entities and user intentions are extracted through field adaptive word segmentation, entity recognition and intention classification; performing term disambiguation, index compilation and rule mapping on various entities to generate a structured instruction; a two-stage Text2SQL engine is adopted to complete list screening and SQL generation, and an executable SQL is output in combination with a multi-model voting arbitration mechanism; and after executing the SQL, triggering a root cause analysis engine to position a data quality problem, and automatically matching a chart type according to the intention to generate a quality evaluation report. According to the scheme, the analysis response time is compressed from the hour level to the second level, the self-service analysis coverage rate and the complex query accuracy of business personnel are greatly improved, and the problems of term ambiguity, high query threshold, difficulty in root cause positioning and the like in the civil aviation field are solved.

Description

Technical Field

[0001] This application belongs to the field of data fusion technology, and specifically relates to a method, system, and related electronic equipment for dynamic analysis of civil aviation data quality based on a Text2SQL large model. Background Technology

[0002] I. Current Status and Pain Points of Civil Aviation Data Quality Analysis Civil aviation data quality analysis is a core component in ensuring flight operation efficiency and ticketing process compliance. Typical scenarios include monitoring and analyzing key indicators such as PNR (Passenger Reservation Record) cancellation rate, the proportion of unlinked ticket numbers for ET (Electronic Ticket) issuance, and the failure rate of BKO (Baggage Handling) integration with PNR. Currently, this field faces the following significant pain points: 1. Manual SQL queries are inefficient. Current analytics rely on technical staff manually writing complex SQL statements to extract multi-dimensional data and calculate metrics. These queries typically involve joins between 3-5 business tables (such as ics_pnr, ticket_number, airline_codes, etc.) and include nested conditions (such as time range filtering and status code matching) and custom metric logic (such as join success rate = number of successful joins / total number × 100%). A single query takes an average of 25-40 minutes, and due to the strong scenario-dependent nature of the query logic, code reusability is less than 15%. For example, analyzing the "root cause of the PNR-BKO join failure on the Beijing-Shanghai route in Q1 2025" requires cross-table joins and nested subqueries to statistically analyze the distribution of failure types. Manual coding of these queries can easily lead to omissions in join conditions and errors in aggregation logic, further reducing the reliability of the analysis.

[0003] 2. High barrier to entry for non-technical personnel Business personnel (such as flight operations specialists) often lack database operation skills and cannot directly access raw data for independent queries, requiring them to relay requests through the technical team. This model results in a response cycle that generally exceeds 48 hours, especially during abnormal flight events (such as large-scale check-in failures), where the inability to generate diagnostic reports in real time severely restricts the efficiency of emergency decision-making.

[0004] 3. Insufficient ability to analyze root causes Traditional analysis methods can only output basic statistical results (such as "BKO integration failure rate = 12.7%)", lacking the ability to deeply analyze and correlate data. They cannot automatically correlate contextual information (such as "ticket number prefix errors account for 68% of failure reasons"), nor can they generate trend insights (such as a 30% surge in failure rate for a specific airline in ticketing delay scenarios). Subsequent manual investigation of data lineage to pinpoint the root cause is required, adding more than 30% to operational costs.

[0005] II. Limitations of Existing Text2SQL Technology Text2SQL technology aims to convert natural language queries into structured SQL statements, which theoretically lowers the barrier to data analysis. However, it still has significant limitations in civil aviation data quality analysis scenarios. 1. Domain terminology mapping bias The general Text2SQL model lacks a deep understanding of civil aviation terminology, leading to frequent semantic parsing errors. On the one hand, there are issues with ambiguous terminology, such as misinterpreting the civil aviation-specific term "Goshow" (advance boarding without check-in) as the general semantic term "real-time display," generating incorrect filtering conditions (such as WHERE status='SHOW'). On the other hand, there are significant issues with missing business logic. For example, when querying "ticket numbers for ETs not associated with ICS PNRs," the model ignores the industry rule that both the ics_pnr and crspnr fields must be validated simultaneously, resulting in the omission of critical data.

[0006] 2. Insufficient ability to generate complex queries For high-frequency multi-table join queries (such as the PNR-ET-BKO three-table join) in civil aviation data quality analysis, the existing model generates an accuracy of less than 58% (based on Spider benchmark test results). The core bottlenecks include: ① Incorrect join path inference, failing to correctly identify the join path between pnr.icspnr and et.icspnr, and incorrectly using the ticket_number field forcibly joining; ② Missing nested processing logic, failing to automatically generate window functions such as RANK() OVER(PARTITION BY airline) for queries requiring sorting and statistics, such as "Top 3 airlines with the highest failure rates," resulting in results that do not meet business requirements.

[0007] 3. Weak support from dynamic indicators Existing models struggle to adapt to the dynamic calculation requirements of civil aviation-specific quality indicators. On one hand, there is a significant lack of formulas. For example, the civil aviation-defined "integration success rate" needs to be calculated using (SUM(success_flag) / COUNT(*))×100, while the general model directly outputs AVG(success_rate), causing the calculation results to deviate from the true value. On the other hand, there is a lack of timeliness verification mechanisms, failing to consider the impact of dynamic factors such as ET ticketing delays (e.g., >24 hours) on the association failure rate, thus failing to support time-sensitive analysis scenarios.

[0008] Furthermore, although intelligent query and analysis methods based on Text2SQL technology have been developed successively, such as the patent application "A Text2SQL Intelligent Query and Question Answering Method and System Based on LLM and Vector Model" (application number: CN202411657542.6, Turing Artificial Intelligence Research Institute (Nanjing) Co., Ltd.), which discloses a Text2SQL intelligent query and question answering method and system based on LLM and vector model, aiming to construct an interactive joint model with LLM as the core and vector model as the auxiliary, and realize an intelligent Text2SQL query and question answering chain through multi-dimensional data representation, task decoupling, multi-task synchronous fine-tuning of the model, and link construction, including the collaboration of multiple stages such as SQL generation and execution, and answer generation, thereby converting the user's query requirements expressed in natural language into professional MySQL query statements. However, most of these technologies adopt a task-decoupled horizontal architecture (e.g., time extraction → SQL generation → answer generation are processed independently in stages), rely on multiple rounds of LLM fine-tuning and vector retrieval concatenation, have redundant processes, require multiple manual verifications, and lack diagnostic capabilities. Therefore, the maintenance cost is high, and frequent manual intervention will introduce more human errors. Summary of the Invention

[0009] To overcome the aforementioned shortcomings in existing technologies, this application proposes a novel method and system for dynamic analysis of civil aviation data quality based on a Text2SQL large-scale model. This solution constructs a vertical domain four-layer closed-loop architecture (natural language → semantic enhancement → SQL generation → visualization), utilizing a civil aviation-specific semantic layer (terminology library + indicator compiler) to achieve end-to-end compilation, requiring no manual intervention throughout the process.

[0010] To achieve the above objectives, this application provides the following technical solution: The first aspect of this application provides a method for dynamic analysis of civil aviation data quality based on a large Text2SQL model, the method comprising: S1. Receive the natural language query statement for civil aviation data quality analysis input by the user; S2. Perform domain-adaptive word segmentation, entity recognition, and intent classification on the natural language query statement to extract time entities, location entities, indicator entities, and user intent types; S3. Input the extracted three types of entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. S4. Based on the structured query instructions, a two-stage Text2SQL generation engine is used to filter table columns and generate SQL statements, and the final executable SQL is output through a multi-model voting arbitration mechanism. S5. Execute the SQL to obtain the analysis result data, trigger the root cause analysis engine to locate data quality problems, and automatically match the visualization chart type according to the user intent type to generate a quality assessment report.

[0011] Optionally, in the method of this application, the domain-adaptive word segmentation adopts a word segmentation framework based on dictionaries and statistics, preloads a terminology database of the civil aviation domain, and retains the terminology integrity of PNR, ET, BKO, Goshow, ICS PNR business abbreviations and professional expressions by dynamically adjusting word frequency weights and forced segmentation rules, combined with bidirectional maximum matching algorithm and hidden Markov model.

[0012] Optionally, in the method of this application, the entity recognition uses a fine-tuned BERT model for sequence labeling, wherein time entities are parsed by combining regular rules with CRF layers to parse absolute and relative time; location entities are combined with address tree hierarchical mapping to output IATA standardized codes; and indicator entities are enhanced with quality-related word weights through a domain attention mechanism and classified into predefined indicator types such as success rate and failure distribution.

[0013] Optionally, in the method of this application, the intent classification constructs a lightweight TextCNN model, with the input being the BERT encoded vector of the segmented sequence, and the output being three types of labels: statistics, diagnosis, and visualization, which correspond to basic numerical aggregation, root cause localization, and chart generation requests, respectively.

[0014] Optionally, in the method of this application, the term disambiguation adopts a multimodal vector fusion algorithm: term vector = α × general word vector + β × business feature vector, where α = 0.6 and β = 0.4. The best match is retrieved in the term library of FAISS index by cosine similarity, and knowledge graph reasoning based on HowNet semantic primitives is initiated for terms with confidence scores below the threshold.

[0015] Optionally, in the method of this application, the indicator compilation establishes a domain-specific language compiler to convert the natural language indicator description into SQL logic: parsing the indicator keyword mapping relationship, matching the pre-set formula template library, and injecting dynamic parameters, wherein the formula template library contains mapping rules such as success rate → (SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) / COUNT(*))100, ticket delay → AVG(et_delay_hours), and TOP reason → GROUP BY failure_reason ORDERBY COUNT() DESC LIMIT 3.

[0016] Optionally, in the method of this application, the two-stage Text2SQL generation engine first uses a LoRA-tuned BERT classifier to filter the table columns of the query statement and the vectorized representation of the database schema, and outputs the top 3 field combinations with the highest probability; in the second stage, the field combinations are injected into a pre-set SQL skeleton template, and the CodeLlama-13B, SQLCoder, and InternLM large models are called in parallel to generate candidate SQL, and the final executable SQL is selected by voting based on the consistency of the database execution results.

[0017] Optionally, in the method of this application, the root cause analysis engine performs statistical analysis on the field null value rate and enumeration value distribution of the query results, identifies illegal enumeration value anomalies based on the LOF local outlier factor algorithm, outputs data quality problem diagnosis conclusions in conjunction with the preset rule base, and the attribution output includes field-level problems such as ticket number prefix errors and airline coding rule violations.

[0018] Optionally, in the method of this application, the step of automatically matching the visualization chart type according to the user intent type to generate a quality assessment report includes: generating a time series line chart containing the keyword "trend", generating a pie chart or bar chart containing the keyword "distribution"; automatically adding quality red line threshold markers, airline logo watermarks, and highlighting anomalies in red; and performing result set desensitization and RBAC permission filtering security control.

[0019] A second aspect of this application provides a dynamic analysis system for civil aviation data quality based on a Text2SQL large model, the system comprising: The information receiving module is used to receive natural language query statements for civil aviation data quality analysis input by users; The natural language interaction module is used to perform domain-adaptive word segmentation, entity recognition, and intent classification on natural language query statements, and extract time entities, location entities, indicator entities, and user intent types. The civil aviation semantic enhancement module is used to input the extracted entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. The Text2SQL generation module is used to filter table columns and generate SQL statements based on structured query commands using a two-stage Text2SQL generation engine, and outputs the final executable SQL through a multi-model voting arbitration mechanism. The quality assessment visualization module is used to execute SQL to obtain analysis results data, trigger the root cause analysis engine to locate data quality issues, and automatically match the visualization chart type according to the user's intent type to generate a quality assessment report.

[0020] The system implements the steps of the aforementioned dynamic analysis method for civil aviation data quality based on the Text2SQL large model during runtime.

[0021] A third aspect of this application provides an electronic device, including: a memory and a processor; Memory: Used to store computer programs; Processor: Used to execute the computer program to implement the steps of the aforementioned method for dynamic analysis of civil aviation data quality based on the Text2SQL large model.

[0022] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned method for dynamic analysis of civil aviation data quality based on a large Text2SQL model.

[0023] In summary, this application proposes a method and system for dynamic analysis of civil aviation data quality based on a large Text2SQL model. Compared with existing technologies, this solution has the following characteristics and advantages: (1) Improved analysis efficiency: Through natural language direct translation of SQL technology, the response time of civil aviation data quality analysis has been significantly reduced from the hourly response (average 32 minutes / time) of traditional manual SQL writing to the second-level response (<5 minutes), with an efficiency improvement of over 80%; business personnel can directly input natural language to obtain analysis results instantly, completely eliminating the technical transfer link and shortening the demand response cycle from 48 hours to real-time feedback.

[0024] (2) Significantly reduced barrier to entry: Pioneering natural language direct translation of SQL technology, through a civil aviation terminology disambiguation mechanism (e.g., accurately identifying "Goshow" as a check-out / boarding status rather than a real-time display) and an automatic quality indicator compilation function (e.g., automatically converting "success rate" into an aggregation logic of (SUM(success) / COUNT(*))*100), allows non-technical personnel to complete complex queries without mastering SQL syntax. Actual testing showed that self-service analysis coverage for business personnel reached over 95%, effectively freeing up technical resources.

[0025] (3) Enhanced domain adaptability and accuracy: Strong domain adaptability: It comes pre-built with 15 types of civil aviation-specific templates (covering a multi-table JOIN path library) and a library of more than 20 quality indicator formulas, which improves the accuracy of complex queries to 92% (based on Spider benchmark testing), which is 34 percentage points higher than the general Text2SQL model; Root cause deep localization: Supports root cause deep localization based on pure data layer features (such as illegal ticket number prefix detection), and can output diagnostic suggestions with confidence of >85%, reducing the cost of manual secondary investigation by 30%.

[0026] (4) Full-process automated closed loop: realize end-to-end automation of "natural language input → SQL generation → execution → visualization report", dynamically match chart types (such as "trend" automatically mapped to line chart, "distribution" mapped to pie chart), automatically embed civil aviation quality red line alarm (such as 80% compliance threshold), and support result drill-down and interactive exploration analysis to form a complete data quality analysis closed loop.

[0027] The table below shows the quantitative test results of various performance indicators of this solution:

[0028] Other features and advantages of this application will be set forth in the following description and will be apparent in part from the description or may be learned by practicing the application. The objectives and other advantages of this application may be realized and obtained by means of the techniques pointed out in the description, claims and drawings. Attached Figure Description

[0029] To more clearly illustrate the technical solutions of this application, 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 this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0030] Figure 1 This is a flowchart illustrating the overall design architecture and implementation process of the proposed solution.

[0031] Figure 2 This is a flowchart illustrating the overall implementation process of the method described in this application.

[0032] Figure 3 This is a flowchart of the natural language interaction layer operation in the scheme of this application.

[0033] Figure 4 This is a flowchart of the operation of the civil aviation semantic enhancement layer in this application.

[0034] Figure 5 This is a flowchart of the Text2SQL generation layer operation in this application.

[0035] Figure 6 This is a structural diagram of the Civil Aviation Data Quality Dynamic Analysis System based on the Text2SQL large model proposed in this application.

[0036] Figure 7 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0037] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0038] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0039] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0040] Example: A method for dynamic analysis of civil aviation data quality based on a large Text2SQL model; Figure 1 The diagram shows the overall design architecture and implementation process of the dynamic analysis method for civil aviation data quality based on the Text2SQL large model in this application, including: user input → entity recognition → intent classification → execution of: statistics - success rate calculation; diagnosis - root cause analysis of failure; prediction - trend visualization.

[0041] I. System Overall Architecture This system employs a four-layer adaptive architecture to achieve end-to-end conversion between natural language and quality analysis SQL. Specifically, it includes: (1) Natural language interaction layer: Entity recognition and intent classification are achieved based on fine-tuning the BERT model.

[0042] (2) Civil aviation semantic enhancement layer: includes domain knowledge base and term disambiguation module.

[0043] (3) Text2SQL generation layer: a two-stage generation engine combined with dynamic modules.

[0044] (4) Quality assessment visualization layer: The execution engine and the automatic drawing module work together.

[0045] The following is a detailed explanation, layer by layer: 1. Natural Language Interaction Layer Core function: Parse natural language queries into machine-processable structured instructions. Implementation logic: (1) Domain-Adaptive Word Segmentation: A word segmentation framework based on dictionaries and statistics is adopted, and a civil aviation domain terminology database is preloaded (such as business abbreviations like PNR / ET / BKO, professional expressions like Goshow / ICS PNR, etc.). By dynamically adjusting word frequency weights and mandatory segmentation rules, it is ensured that business entities are not incorrectly segmented (e.g., “PNR cancellation rate” is not split into “PN” and “R cancellation rate”). The word segmentation process integrates bidirectional maximum matching algorithm and Hidden Markov Model (HMM) to prioritize the preservation of domain terminology integrity.

[0046] (2) Entity recognition: The fine-tuned BERT model (pre-trained weights + civil aviation operation and maintenance log fine-tuning) was used for sequence labeling to identify the following three types of core entities: a) Time Entities: Regular rules are combined with the CRF layer to parse absolute time (e.g., "Q1 2025") and relative time (e.g., "the last three months").

[0047] b) Location entity: Combine the address tree hierarchical mapping (province → city → airport → terminal) to output standardized codes (e.g., “Pudong T2” → “PVG-T2”).

[0048] c) Indicator Entities: The weight of quality-related terms ("rate", "TOP", "trend") is enhanced through a domain attention mechanism and classified into predefined indicator types such as success rate and failure distribution.

[0049] (3) Intent classification: Construct a lightweight TextCNN model, with the input being the BERT encoded vector of the segmented sequence, and outputting the following three types of labels: Statistics: Basic numerical aggregation (such as counting, summation); Diagnosis: Root cause localization (e.g., distribution of failure causes); Visualization: Chart generation requests (such as trend charts, pie charts).

[0050] 2. Civil Aviation Semantic Enhancement Layer Core functionality: Eliminate ambiguity in domain terminology and compile business logic into executable instructions.

[0051] Implementation logic: (1) Term disambiguation (based on the civil aviation business-specific lexicon standard): Design a multimodal vector fusion algorithm: Term vector = α * general word vector (Tencent word embedding) + β * business feature vector (manual rule encoding), where α = 0.6 (general semantic weight) and β = 0.4 (business rule weight). The best match is retrieved in the FAISS-indexed term lexicon through cosine similarity. For terms with confidence scores below the threshold, knowledge graph reasoning (based on the HowNet semantic primitive system) is initiated.

[0052] (2) Indicator Compilation: Establish a Domain-Specific Language (DSL) compiler to convert natural language indicator descriptions (such as "TOP3 Reasons") into SQL logic: parse indicator keywords ("TOP" → "LIMIT", "Distribution" → "GROUP BY"); match pre-set formula templates (such as "Success Rate" → "(SUM(success) / COUNT(*))*100"); inject dynamic parameters (such as the N value of TOP N). (3) Rule Engine: Stores the following three types of rules: Field mapping rules: Natural language → Database field (e.g., “ticket number” → “ticket_no”); Association path rules: Table join logic (e.g., PNR → ET associated via pnr_id); Value range constraint rules: the range of field values ​​(e.g., airport codes must conform to IATA standards).

[0053] 3. Text2SQL generation layer Core function: Generate high-precision SQL with correct syntax and semantic matching.

[0054] Implementation logic: Two-stage generation engine: (1) Filtering table columns: Input: BERT encoding of the user query + vectorized representation of the database schema.

[0055] Model: LoRA-tuned BERT classifier (rank=64).

[0056] Output: The top 3 most probable field combinations (e.g., [pnr.status, pnr.date]).

[0057] (2) SQL generation: Dynamic template population: Injects field combinations into a pre-defined SQL skeleton (including SELECT / WHERE / GROUP BY placeholders).

[0058] Parallel generation of multiple models: calling CodeLlama-13B, SQLCoder, and InternLM to generate candidate SQL.

[0059] Voting arbitration mechanism: The consistency of database execution results is used as the arbitration basis (all models must return the same result set).

[0060] Dynamic optimization mechanism: Syntax validation: AST (Abstract Syntax Tree) parsing detects nesting errors; Semantic validation: Compare with historical similar query results; if the deviation exceeds the threshold, an alarm is triggered.

[0061] 4. Quality assessment visualization layer: Core functionality: Execute SQL and generate automated analysis reports.

[0062] Implementation logic: (1) Root Cause Analysis Engine: Feature extraction: Perform statistical analysis on the query results (such as field null value rate, enumeration value distribution).

[0063] Anomaly detection: Identify data quality issues (such as illegal enumeration values) based on the LOF (Local Outlier) algorithm.

[0064] Attribution output: Corresponds to the pre-set rule base output diagnostic conclusions (such as "Unregistered ticket number prefix detected: XY").

[0065] (2) Visualized automatic mapping: a) Intention-driven decision-making: Include the keyword "trend" → Generate a time series line chart (Plotly).

[0066] Include the keyword "distribution" → Generate pie charts / bar charts (Matplotlib).

[0067] b) Customized enhancements for civil aviation: Automatically add quality red lines (such as the 80% compliance threshold).

[0068] Embed the airline logo watermark (semi-transparent background).

[0069] Abnormal points are highlighted in red (such as success rates below the threshold).

[0070] c) Security Controls: Result set desensitization: Regular expression replacement of sensitive fields (e.g., ID number → \1****\2).

[0071] Permission filtering: The RBAC model masks unauthorized fields based on roles.

[0072] Figure 2 The diagram shows the overall implementation flow of the method described in this application, which includes the following steps: S1. Receive the natural language query statement for civil aviation data quality analysis input by the user; S2. Perform domain-adaptive word segmentation, entity recognition, and intent classification on the natural language query statement to extract time entities, location entities, indicator entities, and user intent types; S3. Input the extracted three types of entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. S4. Based on the structured query instructions, a two-stage Text2SQL generation engine is used to filter table columns and generate SQL statements, and the final executable SQL is output through a multi-model voting arbitration mechanism. S5. Execute the SQL to obtain the analysis result data, trigger the root cause analysis engine to locate data quality problems, and automatically match the visualization chart type according to the user intent type to generate a quality assessment report.

[0073] To better understand the technical solution of this application, the following embodiments are provided for further explanation.

[0074] 1. Natural Language Interaction Layer: Precise Interpretation of Business Intent The core objective of this layer is to transform the natural language of business personnel into structured instructions. First, sentences are segmented using a civil aviation-specific word segmenter, and industry terminology (such as PNR / ET) is injected to ensure the integrity of the basic semantics. Then, a fine-tuned BERT model is used to identify key entities (time / location / metrics). Finally, a lightweight classification model is used to determine the user's intent (statistics / diagnosis / visualization).

[0075] The operation process is as follows Figure 3 As shown, it includes: user input → domain segmentation → entity recognition → intent classification → output structured instructions.

[0076] Technical Implementation: Domain-Specific Word Segmentation # Step 1: Domain-Adaptive Word Segmentation (Solving Mixed Chinese and English Terms) import jieba jieba.add_word('PNR', freq=1000) # Force "PNR" to be treated as a separate word tokens = jieba.lcut("Check Beijing Capital International Airport PNR cancellation rate") # Output: ['Query', 'Beijing Capital Airport', 'PNR', 'Cancellation Rate'] # Step 2: Entity Recognition (Fine-tuning BERT) entities = bert_ner.predict(tokens) # Output entity type dictionary: {'Capital Airport': 'LOC', 'PNR': 'DATA_TYPE', 'Cancellation Rate': 'METRIC'} # Step 3: Intent Classification (TextCNN model) intent = model.predict("Analyze the distribution of failure reasons") # Output: 'Diagnosis' Example illustration: When the business personnel input "Check the ET ticket issuance delay at Guangzhou Baiyun Airport": Word segmentation result: ['Check', 'Guangzhou Baiyun Airport', 'ET', 'Ticket Issuance', 'Delay'] Entity recognition: {'Guangzhou Baiyun Airport': 'LOC', 'ET': 'DATA_TYPE', 'Ticket Issuance Delay': 'METRIC'} Intent classification: Statistics → Trigger the indicator calculation process 2. Civil Aviation Semantic Enhancement Layer: Indicator and Rule Compilation This layer is responsible for converting business language into machine-executable logic. It compiles colloquial descriptions (such as "Success Rate") into SQL calculation expressions through a pre-set indicator formula library; at the same time, it constructs an address tree mapping table to convert natural language locations (such as "Pudong Airport T2") into database field conditions (airport='PVG' AND terminal='T2').

[0077] The operation process is as Figure 4 shown, including: Entity recognition result → Indicator compiler and term disambiguation → Generate the final SQL.

[0078] Technical implementation: # Indicator compiler (business language → SQL formula) def compile_metric(phrase): metric_map = { "Success Rate": "(SUM(CASE WHEN status='Success' THEN 1 ELSE 0 END) / COUNT(*))*100", "Ticket Issuance Delay": "AVG(et_delay_hours)", "TOP Reason": "GROUP BY failure_reason ORDER BY COUNT(*) DESC LIMIT 3" } return metric_map.get(phrase, "UNKNOWN_METRIC") # Location Mapper (Natural Language → Database Value) Terminology Disambiguation location_map = { "Beijing Capital International Airport": "airport='PEK'", Guangzhou Baiyun Airport: "airport='CAN'", "Pudong Airport T2": "airport='PVG' AND terminal='T2'"

[0079] } … Example explanation: Input the business language "Statistics on BKO success rate at Pudong Airport Terminal 2": Indicator compilation: "Success Rate" → (SUM(success) / COUNT(*))*100 Location mapping: "Pudong Airport T2" → airport='PVG' AND terminal='T2' Output condition snippet: WHERE airport='PVG' AND terminal='T2' 3. Text2SQL Generation Layer: Dynamic SQL Construction This layer employs a two-stage generation engine to address complex query problems. The first stage uses a lightweight BERT model to filter the table field combinations with the highest probabilities; the second stage fills in the SQL skeleton based on a pre-set template and selects the optimal SQL through a multi-model voting mechanism (CodeLlama / SQLCoder / InternLM) to ensure syntactic correctness and semantic matching.

[0080] The operation process is as follows Figure 5 As shown, it includes: table column filtering → lightweight BERT → SQL generation → multi-model voting.

[0081] Technical Implementation: # Phase 1: Table Column Filtering (Lightweight BERT) columns = bert_columns.predict("Check PNR cancellation rate", db_schema) # Output the top 3 fields: ['pnr.status', 'pnr.date', 'pnr.airline'] # Phase 2: SQL Generation (Multi-model Voting) template = "SELECT {{date}}, {{metric}} FROM {{table}} WHERE{{location}}" filled_sql = template.replace("{{metric}}", compile_metric("cancellation rate")) # Three models generate candidate SQL in parallel candidates = [model.generate(filled_sql) for model in [codellama,sqlcoder, internlm]] final_sql = vote_by_execution_consistency(candidates) # Voting for consistency of execution results SQL generation example: / * Finally, SQL is generated * / SELECT DATE_TRUNC('month', date) AS month, (SUM(CASE WHEN status='Cancel' THEN 1 ELSE 0 END)*100.0 / COUNT(*)) AS Cancellation Rate FROM pnr_orders WHERE airport='PVG' AND terminal='T2' -- Location conditions are automatically injected GROUP BY month 4. Quality Assessment Visualization Layer: Automated Report Generation This layer implements end-to-end transformation of data results into business reports. After executing the generated SQL to obtain structured data, it automatically matches the chart type based on intent keywords (such as "trend"); at the same time, it initiates root cause analysis of the data layer to locate field-level quality issues (such as illegal ticket number prefixes).

[0082] Technical Implementation: # Visualizing Automatic Mapping (Natural Language → Chart Code) def auto_chart(intent, df): if "trend" is in the intent: return px.line(df, x='date', y='rate', title='Success Rate Trend') # Plotly line chart elif "distribution" in intent: return plt.pie(df['count'], labels=df['reason'])# Matplotlib pie chart # Data-level root cause analysis (pure field-level issues) def analyze_failure(df): if 'ticket number prefix error' in df['reason'].values: invalid = df[df.reason=='ticket_prefix error']['ticket_prefix'].unique() The return value f states that an invalid prefix was detected: {invalid} (violation of airline coding rules). Output report example:

[0083] Root cause diagnosis: Ticket number prefix error detected.

[0084] Figure 6 The diagram shows the structural composition of the Civil Aviation Data Quality Dynamic Analysis System based on the Text2SQL large model provided in this application embodiment. The system includes: The information receiving module is used to receive natural language query statements for civil aviation data quality analysis input by users; The natural language interaction module is used to perform domain-adaptive word segmentation, entity recognition, and intent classification on natural language query statements, and extract time entities, location entities, indicator entities, and user intent types. The civil aviation semantic enhancement module is used to input the extracted entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. The Text2SQL generation module is used to filter table columns and generate SQL statements based on structured query commands using a two-stage Text2SQL generation engine, and outputs the final executable SQL through a multi-model voting arbitration mechanism. The quality assessment visualization module is used to execute SQL to obtain analysis results data, trigger the root cause analysis engine to locate data quality issues, and automatically match the visualization chart type according to the user's intent type to generate a quality assessment report.

[0085] The above system implements the steps of the dynamic analysis method for civil aviation data quality based on the Text2SQL large model disclosed in this application during runtime.

[0086] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. Each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0087] like Figure 7 As shown in the illustration, an embodiment of this application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned dynamic analysis method for civil aviation data quality based on a Text2SQL large model.

[0088] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0089] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types: Object-oriented programming languages, such as Java, Smalltalk, C++, etc. Conventional procedural programming languages, such as "C" or similar programming languages.

[0090] The execution methods of program code include, but are not limited to: It runs entirely on the user's computer; Part of it executes on the user's computer, and part of it executes on a remote computer; Execute as a standalone software package; It is executed entirely on a remote computer or server.

[0091] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0092] Furthermore, this application also discloses a computer-readable storage medium, which, when the instructions in the computer-readable storage medium are executed by the processor of an electronic device, enables the electronic device to perform the various steps of the dynamic analysis method for civil aviation data quality based on the Text2SQL large model disclosed in this application.

[0093] In the context of this application, computer-readable storage media can be tangible media, and more specific examples include portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, and portable compact disc read-only memory (CD). ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0094] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application include a computer program product comprising a computer program carried on a non-transitory computer-readable medium, the computer program containing program code for executing the dynamic analysis method for civil aviation data quality based on the Text2SQL large model disclosed in this application. When the computer program is executed by a processing system, it performs the functions defined in the methods of the embodiments of this application.

[0095] While the foregoing discussion includes several specific implementation details, these should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of disclosure in this application is not limited to technical solutions formed by specific combinations of the above-described technical features, but should also cover other technical solutions formed by arbitrary combinations of the above-described technical features or their equivalents without departing from the above-described concept.

[0096] Those skilled in the art should also understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for dynamic analysis of civil aviation data quality based on a Text2SQL large model, characterized in that, The method includes: S1. Receive the natural language query statement for civil aviation data quality analysis input by the user; S2. Perform domain-adaptive word segmentation, entity recognition, and intent classification on the natural language query statement to extract time entities, location entities, indicator entities, and user intent types; S3. Input the extracted three types of entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. S4. Based on the structured query instructions, a two-stage Text2SQL generation engine is used to filter table columns and generate SQL statements, and the final executable SQL is output through a multi-model voting arbitration mechanism. S5. Execute the SQL to obtain the analysis result data, trigger the root cause analysis engine to locate data quality problems, and automatically match the visualization chart type according to the user intent type to generate a quality assessment report.

2. The method according to claim 1, characterized in that, The domain-adaptive word segmentation adopts a word segmentation framework based on dictionaries and statistics, preloads a terminology database of the civil aviation field, and retains the integrity of terminology such as PNR, ET, BKO, Goshow, ICS PNR business abbreviations and professional expressions by dynamically adjusting word frequency weights and forced segmentation rules, combined with bidirectional maximum matching algorithm and hidden Markov model.

3. The method according to claim 1, characterized in that, The entity recognition uses a fine-tuned BERT model for sequence labeling. Time entities are parsed using regularization rules and a CRF layer to parse absolute and relative time. Location entities are output with IATA-standardized codes using address tree hierarchical mapping. Index entities are enhanced with quality-related word weights through a domain attention mechanism and classified into predefined index types such as success rate and failure distribution.

4. The method according to claim 1, characterized in that, The intent classification constructs a lightweight TextCNN model, which takes BERT encoded vectors of the segmented sequence as input and outputs three types of labels: statistics, diagnosis, and visualization, corresponding to basic numerical aggregation, root cause localization, and chart generation requests, respectively.

5. The method according to claim 1, characterized in that, The term disambiguation adopts a multimodal vector fusion algorithm: term vector = α × general word vector + β × business feature vector, where α = 0.6 and β = 0.

4. The best match is retrieved in the term database indexed by FAISS through cosine similarity. For terms with confidence scores below the threshold, knowledge graph reasoning based on the HowNet semantic primitive system is initiated.

6. The method according to claim 1, characterized in that, The aforementioned indicator compilation establishes a domain-specific language compiler to convert natural language indicator descriptions into SQL logic: parsing the indicator keyword mapping relationship, matching a pre-set formula template library, and injecting dynamic parameters. The formula template library contains mapping rules such as success rate → (SUM(CASE WHEN status='Success' THEN 1 ELSE0 END) / COUNT(*))100, ticket issuance delay → AVG(et_delay_hours), and TOP reason → GROUP BY failure_reason ORDER BY COUNT() DESC LIMIT 3.

7. The method according to claim 1, characterized in that, The two-stage Text2SQL generation engine first uses a LoRA-tuned BERT classifier to filter table columns of the query statement and the vectorized representation of the database schema, and outputs the top 3 field combinations with the highest probability. In the second stage, the field combinations are injected into a pre-set SQL skeleton template, and the CodeLlama-13B, SQLCoder, and InternLM large models are called in parallel to generate candidate SQL. The final executable SQL is selected by voting based on the consistency of the database execution results.

8. The method according to claim 1, characterized in that, The root cause analysis engine performs statistical analysis on the field null value rate and enumeration value distribution of the query results, identifies illegal enumeration value anomalies based on the LOF local outlier factor algorithm, and outputs data quality problem diagnosis conclusions in conjunction with the preset rule base. The attribution output includes field-level problems such as ticket number prefix errors and airline coding rule violations.

9. The method according to claim 1, characterized in that, The automatic matching of visualization chart types based on user intent type to generate quality assessment reports includes: generating time-series line charts with the keyword "trend", generating pie charts or bar charts with the keyword "distribution"; automatically adding quality red line threshold markers, airline logo watermarks, and highlighting anomalies in red; and performing result set desensitization and RBAC permission filtering security control.

10. A dynamic analysis system for civil aviation data quality based on a Text2SQL large model, characterized in that, The system implements the steps of the dynamic analysis method for civil aviation data quality based on the Text2SQL large model as described in any one of claims 1-9 during operation, and the system includes: The information receiving module is used to receive natural language query statements for civil aviation data quality analysis input by users; The natural language interaction module is used to perform domain-adaptive word segmentation, entity recognition, and intent classification on natural language query statements, and extract time entities, location entities, indicator entities, and user intent types. The civil aviation semantic enhancement module is used to input the extracted entities into the civil aviation semantic enhancement layer, and generate structured query instructions through term disambiguation, indicator compilation and rule engine mapping. The Text2SQL generation module is used to filter table columns and generate SQL statements based on structured query commands using a two-stage Text2SQL generation engine, and outputs the final executable SQL through a multi-model voting arbitration mechanism. The quality assessment visualization module is used to execute SQL to obtain analysis results data, trigger the root cause analysis engine to locate data quality issues, and automatically match the visualization chart type according to the user's intent type to generate a quality assessment report.

Images