為文本到SQL應用程序創建一個主動式RAG

from datetime import date, timedelta

def transform_query(user_query):
    # 處理開放式時間參考
    today = date.today()
    transformations = {
        "as of today": f"up to {today}",
        "till now": f"up to {today}",
        "recent": "last 7 days",
        "last week": f"from {today - timedelta(days=7)} to {today}",
    }
    
    for key, value in transformations.items():
        user_query = user_query.replace(key, value)

    # 映射常見縮寫
    abbreviations = {
        "MTD": "Month to Date",
        "YTD": "Year to Date",
    }
    for abbr, full_form in abbreviations.items():
        user_query = user_query.replace(abbr, full_form)

    return user_query

query_transform_tool = Tool(
    name="Query Transformer",
    func=transform_query,
    description="Refines user queries for clarity and specificity, handles abbreviations and open-ended terms."
)

from langchain.chat_models import ChatOpenAI

llm = ChatOpenAI(model="gpt-4")

predefined_examples = {
    "Show sales grouped by region": "SELECT region, SUM(sales) FROM sales_data GROUP BY region;",
    "Show total sales by product": "SELECT product_name, SUM(sales) FROM sales_data GROUP BY product_name;",
}

def find_similar_question(user_query):
    prompt = "Find the most similar question type for the following user query:\n"
    prompt += f"User Query: {user_query}\n\nOptions:\n"
    for example in predefined_examples.keys():
        prompt += f"- {example}\n"
    prompt += "\nRespond with the closest match."

    response = llm.call_as_function(prompt)
    most_similar = response['content']
    return predefined_examples.get(most_similar, "")

few_shot_tool = Tool(
    name="Few-Shot Prompting",
    func=find_similar_question,
    description="Finds the most similar question type using an additional LLM call and retrieves the corresponding example SQL."
)

from rank_bm25 import BM25Okapi

def hybrid_search(query):
    # 基於關鍵字的映射
    keyword_to_table = {
        "sales": "sales_data",
        "product": "products",
    }
    keyword_results = [table for keyword, table in keyword_to_table.items() if keyword in query.lower()]

    # BM25 搜尋
    bm25 = BM25Okapi(["sales_data", "products", "orders", "financials"])
    bm25_results = bm25.get_top_n(query.split(), bm25.corpus, n=5)

    # 語義搜尋
    semantic_results = vector_store.similarity_search(query, k=5)

    # 倒數排名融合
    def reciprocal_rank_fusion(results):
        rank_map = {}
        for rank, table in enumerate(results):
            rank_map[table] = rank_map.get(table, 0) + 1 / (1 + rank)
        return sorted(rank_map, key=rank_map.get, reverse=True)

    combined_results = reciprocal_rank_fusion(bm25_results + semantic_results)

    return list(set(keyword_results + combined_results))

hybrid_search_tool = Tool(
    name="Hybrid Search",
    func=hybrid_search,
    description="Combines keyword mapping, BM25, and semantic search with RRF for table retrieval."
)

from transformers import pipeline

reranker = pipeline("text-classification", model="cross-encoder/ms-marco-TinyBERT-L-2")

def re_rank_context(query, results):
    scores = [(doc, reranker(query + " " + doc)[0]['score']) for doc in results]
    return [doc for doc, score in sorted(scores, key=lambda x: x[1], reverse=True)]

re_rank_tool = Tool(
    name="Re-Ranker",
    func=re_rank_context,
    description="Re-ranks the retrieved tables based on relevance to the query."
)