基于Python的金融数据NL2SQL项目求助：分词后无法生成SQL查询

Hey there! Let's tackle this NL2SQL conversion challenge you're facing—turning processed tokens into valid SQL queries for your financial database makes total sense, especially with domain-specific data like net income and company names. Below are practical, actionable solutions tailored to your project stage:

Since you're working with a fixed financial dataset (known company names, predefined metrics like net income), a rule-based approach is perfect for fast, reliable results. Here's how to implement it:

Step-by-Step Breakdown #

1. Map Natural Language Terms to SQL Entities
   Create dictionaries to link your processed tokens to database fields and company names.
2. Extract Key Tokens
   Scan your cleaned tokens to identify the target financial metric and company.
3. Generate SQL via Predefined Templates
   Use conditional logic to plug extracted values into valid SQL structures.

Example Code #

def rule_based_nl2sql(cleaned_tokens):
    # Map Chinese financial terms to database columns
    field_mapping = {
        "净收入": "net_income",
        "营收": "revenue",
        "营业利润": "operating_profit"
    }
    # List of valid company names from your database
    valid_companies = ["苹果", "谷歌", "微软", "亚马逊"]
    
    target_field = None
    target_company = None
    
    # Extract key entities from tokens
    for token in cleaned_tokens:
        if token in field_mapping:
            target_field = field_mapping[token]
        if token in valid_companies:
            target_company = token
    
    # Generate SQL if both entities are found
    if target_field and target_company:
        return f"SELECT {target_field} FROM finance WHERE name = '{target_company}'"
    else:
        return "无法识别查询意图，请尝试明确公司名称或财务指标"

# Test with your sample tokens
tokens = ["苹果", "净收入", "多少"]
print(rule_based_nl2sql(tokens))
# Output: SELECT net_income FROM finance WHERE name = '苹果'

This approach is lightweight, easy to debug, and ideal for handling 80% of common queries in your financial domain.

If you need to support more complex questions later (e.g., "苹果2023年的净收入比2022年高多少？"), a model-based approach will scale better. Here's how to adapt pre-trained NLP models to your use case:

Step-by-Step Breakdown #

1. Build a Custom Financial NL2SQL Dataset
   Collect pairs of natural language questions and their corresponding SQL queries (e.g., 500-1000 pairs for solid results).
2. Fine-Tune a Pre-Trained Model
   Use frameworks like Hugging Face Transformers to adapt models like T5 or BERT to your dataset.

Example Code Snippet #

from transformers import T5Tokenizer, T5ForConditionalGeneration, Trainer, TrainingArguments
from datasets import Dataset

# Load base model and tokenizer
tokenizer = T5Tokenizer.from_pretrained("t5-small")
model = T5ForConditionalGeneration.from_pretrained("t5-small")

# Sample training data (replace with your custom dataset)
train_data = [
    {"input": "苹果的净收入是多少？", "output": "SELECT net_income FROM finance WHERE name = '苹果'"},
    {"input": "谷歌2023年的营收是多少？", "output": "SELECT revenue FROM finance WHERE name = '谷歌' AND year = 2023"}
]

# Preprocess data for training
def preprocess_data(examples):
    inputs = tokenizer(examples["input"], max_length=512, truncation=True)
    labels = tokenizer(examples["output"], max_length=512, truncation=True)
    inputs["labels"] = labels["input_ids"]
    return inputs

# Convert to Hugging Face Dataset format
dataset = Dataset.from_list(train_data)
tokenized_data = dataset.map(preprocess_data, batched=True)

# Set training parameters
training_args = TrainingArguments(
    output_dir="./financial_nl2sql_model",
    per_device_train_batch_size=2,
    num_train_epochs=10,
    logging_dir="./logs"
)

# Train the model
trainer = Trainer(
    model=model,
    args=training_args,
    train_dataset=tokenized_data
)
trainer.train()

# Generate SQL from a question
def generate_sql(question):
    input_ids = tokenizer(question, return_tensors="pt").input_ids
    outputs = model.generate(input_ids)
    return tokenizer.decode(outputs[0], skip_special_tokens=True)

# Test
print(generate_sql("苹果的净收入是多少？"))

For balanced performance, combine rule-based matching for simple queries with a fine-tuned model for complex edge cases. This ensures speed for common requests while handling unexpected questions gracefully.

Bonus Optimization Tips #

• Entity Linking: Add fuzzy matching (e.g., using fuzzywuzzy) to handle typos like "苹菓" → "苹果".
• SQL Validation: Use sqlparse to check generated SQL syntax before executing it against your database:

  import sqlparse
  
  def validate_sql(sql):
      try:
          parsed = sqlparse.parse(sql)
          return len(parsed) > 0
      except:
          return False
  

内容的提问来源于stack exchange，提问作者Lahari