针对复杂、高频的查询场景提供示例 SQLexamples [ { 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: 参与了促销活动的订单PromotionKey1 表示无促销 } ] }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, -- 促销代理键关联 DimPromotion1无促销 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, -- 发货日期冗余字段 ... );