针对复杂、高频的查询场景，提供示例 SQL：
examples = [ { "question": "查询每个产品类别的销售数量", "sql": """ SELECT dc.EnglishProductCategoryName, COUNT(*) AS SalesCount FROM FactInternetSales fis INNER JOIN DimProduct dp ON fis.ProductKey = dp.ProductKey INNER JOIN DimProductSubcategory dps ON dp.ProductSubcategoryKey = dps.ProductSubcategoryKey INNER JOIN DimProductCategory dc ON dps.ProductCategoryKey = dc.ProductCategoryKey GROUP BY dc.EnglishProductCategoryName ORDER BY SalesCount DESC; """ }, { "question": "查找最近30天未下单的客户", "sql": """ SELECT dc.FirstName, dc.LastName, dc.EmailAddress FROM DimCustomer dc WHERE dc.CustomerKey NOT IN ( SELECT DISTINCT CustomerKey FROM FactInternetSales WHERE OrderDate >= DATEADD(DAY, -30, GETDATE()) ); """ }, { "question": "查询每个地区的销售额和订单数", "sql": """ SELECT dst.SalesTerritoryRegion, dst.SalesTerritoryCountry, COUNT(DISTINCT fis.SalesOrderNumber) AS OrderCount, SUM(fis.SalesAmount) AS TotalSales FROM FactInternetSales fis INNER JOIN DimSalesTerritory dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey GROUP BY dst.SalesTerritoryRegion, dst.SalesTerritoryCountry ORDER BY TotalSales DESC; """ }, { "question": "查询年收入前10的客户及其购买总额", "sql": """ SELECT TOP 10 dc.FirstName, dc.LastName, dc.YearlyIncome, SUM(fis.SalesAmount) AS TotalPurchases FROM DimCustomer dc INNER JOIN FactInternetSales fis ON dc.CustomerKey = fis.CustomerKey GROUP BY dc.CustomerKey, dc.FirstName, dc.LastName, dc.YearlyIncome ORDER BY dc.YearlyIncome DESC; """ } ]示例 SQL 的价值：
- 帮助模型"举一反三"，将示例应用到类似场景
- 规范 SQL 风格和最佳实践
- 提升复杂查询的生成质量
2.3 自定义提示词规则
定义全局适用的业务规则：
全局规则: 1. 所有金额字段统一使用 money 类型，注意精度问题 2. 时间范围查询优先使用 DateKey 关联 DimDate 表 3. 产品相关查询需要考虑产品层次结构: Product → Subcategory → Category 4. 客户相关查询注意 GeographyKey 关联地理位置信息 5. 销售数据有两个来源: FactInternetSales (互联网销售) 和 FactResellerSales (经销商销售) 6. 日期字段有三种: OrderDate (下单日期), DueDate (到期日期), ShipDate (发货日期) 7. 金额计算注意货币类型: CurrencyKey 关联 DimCurrency 8. 促销活动: PromotionKey 关联 DimPromotion三、业务上下文配置：让模型"懂业务"
3.1 术语配置：业务语言 → 技术语言
业务人员说的"大客户"，数据库里可能是YearlyIncome > 100000。术语配置是关键桥梁：
{ "terminology": [ { "business_term": "大客户", "technical_mapping": "YearlyIncome > 100000", "description": "年收入超过10万美元的客户" }, { "business_term": "互联网销售", "technical_mapping": "FROM FactInternetSales", "description": "通过网站直接销售给客户的订单" }, { "business_term": "经销商销售", "technical_mapping": "FROM FactResellerSales", "description": "通过经销商渠道的销售" }, { "business_term": "北美地区", "technical_mapping": "SalesTerritoryGroup = 'North America'", "description": "北美销售区域（美国、加拿大）" }, { "business_term": "欧洲地区", "technical_mapping": "SalesTerritoryGroup = 'Europe'", "description": "欧洲销售区域" }, { "business_term": "太平洋地区", "technical_mapping": "SalesTerritoryGroup = 'Pacific'", "description": "太平洋销售区域（澳大利亚等）" }, { "business_term": "自行车类产品", "technical_mapping": "EnglishProductCategoryName = 'Bikes'", "description": "自行车类别产品" }, { "business_term": "配件类产品", "technical_mapping": "EnglishProductCategoryName = 'Accessories'", "description": "配件类别产品" }, { "business_term": "服装类产品", "technical_mapping": "EnglishProductCategoryName = 'Clothing'", "description": "服装类别产品" }, { "business_term": "促销订单", "technical_mapping": "PromotionKey <> 1", "description": "参与了促销活动的订单（PromotionKey=1 表示无促销）" } ] }3.2 字段描述增强
清晰的字段描述比字段名更重要：
示例：FactInternetSales 表字段描述
-- ❌ 不好的做法（仅有字段名） CREATE TABLE FactInternetSales( ProductKey int, OrderDateKey int, CustomerKey int, SalesAmount money, ... ); -- ✅ 好的做法（包含详细描述） -- 销售事实表：记录互联网销售订单明细 CREATE TABLE FactInternetSales( ProductKey int, -- 产品代理键，关联 DimProduct OrderDateKey int, -- 订单日期键，关联 DimDate（订单创建日期） DueDateKey int, -- 到期日期键，关联 DimDate（应付日期） ShipDateKey int, -- 发货日期键，关联 DimDate（实际发货日期） CustomerKey int, -- 客户代理键，关联 DimCustomer PromotionKey int, -- 促销代理键，关联 DimPromotion（1=无促销） CurrencyKey int, -- 货币代理键，关联 DimCurrency SalesTerritoryKey int, -- 销售区域代理键，关联 DimSalesTerritory SalesOrderNumber nvarchar(20), -- 销售订单号（业务主键） SalesOrderLineNumber tinyint, -- 订单行号（与 SalesOrderNumber 组合为主键） RevisionNumber tinyint, -- 订单修订版本号 OrderQuantity smallint, -- 订购数量 UnitPrice money, -- 单价 ExtendedAmount money, -- 扩展金额 = UnitPrice × OrderQuantity UnitPriceDiscountPct float, -- 单价折扣百分比 DiscountAmount float, -- 折扣金额 ProductStandardCost money, -- 产品标准成本 TotalProductCost money, -- 产品总成本 SalesAmount money, -- 销售金额（实际收入） TaxAmt money, -- 税额 Freight money, -- 运费 CarrierTrackingNumber nvarchar(25), -- 物流跟踪号 CustomerPONumber nvarchar(25), -- 客户采购订单号 OrderDate datetime, -- 订单日期（冗余字段，便于查询） DueDate datetime, -- 到期日期（冗余字段） ShipDate datetime, -- 发货日期（冗余字段） ... );
