大模型微调-损失收敛&动态调整 Epochs（THS）

import pandas as pd

import re

import matplotlib.pyplot as plt

# 读取数据

df = pd.read_parquet('text2sql-dataset/data/train-00000-of-00001.parquet', engine='pyarrow')

# 定义检测函数

def detect_sql_features(sql_query):

    sql_query = sql_query.lower()

    # 聚合统计：SUM, AVG, COUNT, MIN, MAX

    aggregation = bool(re.search(r'\b(sum|avg|count|min|max)\b', sql_query))

    # 多表关联：通过识别 FROM 后面出现多个表名

    multi_table = False

    from_match = re.search(r'from\s+([a-zA-Z0-9_,\s]+)', sql_query)

    if from_match:

        tables_str = from_match.group(1)

        tables = re.split(r'[,\s]+', tables_str.strip())

        tables = [t for t in tables if t and not t.startswith('on') and not t.isdigit()]

        if len(set(tables)) >= 2:

            multi_table = True

    # 子查询：SELECT 在括号内或 WITH

    subquery = bool(re.search(r'from\s+$$select|with', sql_query, re.IGNORECASE))

    # 时间筛选：DATE、MONTH、YEAR、BETWEEN 等关键词

    time_filter = bool(re.search(r'\b(date|month|year|between|where.*\d{4}-\d{2}-\d{2})\b', sql_query))

    # 唯一值提取：DISTINCT 或 UNIQUE

    distinct = bool(re.search(r'\b(distinct|unique)\b', sql_query))

    return {

        'aggregation': aggregation,

        'multi_table': multi_table,

        'subquery': subquery,

        'time_filter': time_filter,

        'distinct': distinct

    }

# 初始化计数器

task_counter = {'aggregation': 0, 'multi_table': 0, 'subquery': 0, 'time_filter': 0, 'distinct': 0}

# 遍历每一行的 SQL 查询并更新计数器

for index, row in df.iterrows():

    sql_query = row['answer']  # 使用 answer 字段中的 SQL 查询

    features = detect_sql_features(sql_query)

    for key in task_counter:

        if features[key]:

            task_counter[key] += 1

# 可视化

task_names = ['Aggregation', 'Multi-table', 'Subquery', 'Time Filter', 'Distinct']

counts = [

    task_counter['aggregation'],

    task_counter['multi_table'],

    task_counter['subquery'],

    task_counter['time_filter'],

    task_counter['distinct']

]

plt.figure(figsize=(10, 6))

bars = plt.bar(task_names, counts, color='skyblue')

plt.title('Frequency of SQL Task Types in Answer Data', fontsize=16)

plt.xlabel('Task Type', fontsize=14)

plt.ylabel('Frequency', fontsize=14)

plt.grid(axis='y', linestyle='--', alpha=0.7)

# 显示数值标签

for bar in bars:

    yval = bar.get_height()

    plt.text(bar.get_x() + bar.get_width() / 2, yval + 0.5, int(yval), ha='center', va='bottom')

plt.tight_layout()

plt.show()

"Based on the following table structure:

{context}

Please convert the following natural language question into an SQL query:

{prompt}

"

import pandas as pd

df = pd.read_parquet('text2sql-dataset/data/train-00000-of-00001.parquet', engine='pyarrow')

# 定义拼接模板

def build_prompt_template(prompt, context):

    return f"""Based on the following table structure:

{context}

Please convert the following natural language question into an SQL query:

{prompt}

"""

# 拼接 Prompt 字段

df['Prompt'] = df.apply(lambda row: build_prompt_template(row['prompt'], row['context']), axis=1)

# 准备导出的 DataFrame

export_df = df[['Prompt', 'answer']].rename(columns={'answer': 'Completion'})

# 导出到 Excel

output_path = 'text2sql-dataset/data/train_text2sql_prompt_with_context_en.xlsx'

export_df.to_excel(output_path, index=False)

print(f"Data has been successfully exported to {output_path}")