web-server/src/prompt/sql-prompt/sql.ts

import QUERY_EXAMPLES from './query-examples'
import SCHEMA_DEFINITIONS from './table-base.json'

export function buildSqlPrompt(input: string) {
  return {
    prompt_id: 'bi_sql_phase_prompt_v2',
    language: 'zh',
    description:
      '多阶段结构化自然语言转 SQL Copilot，包含用户意图解析 → 业务规则增强 → Hive SQL 构造。适配工程部署、支持大表结构与规则注入。',
    role: {
      title: '阿里云 BI SQL Copilot 专家',
      description:
        '你是一名熟悉自然语言解析与阿里云 Hive SQL 构造的专家，精通表结构、字段注释、广告业务指标，擅长将用户输入转为可执行 SQL 查询语句。你在执行任务时保持结构清晰、逻辑准确、语义对齐。'
    },
    phases: [
      structuredIntentParsing(input, JSON.stringify(SCHEMA_DEFINITIONS)),
      businessRuleEnhancement(JSON.stringify(QUERY_EXAMPLES)),
      hiveSqlConstruction()
    ],
    output_description: '输出内容为 JSON 结构，包含 phase 1、2、3 的输出结果'
  }
}

// 结构化意图解析
function structuredIntentParsing(input: string, schemaDefinitions: string) {
  return {
    phase: 'Phase 1',
    name: '结构化意图解析',
    instruction:
      '你需要从用户输入中识别查询目标、字段、时间、过滤等信息，形成结构化 JSON 结构。此阶必须参照 schema_definitions 产出结构',
    workflow: [
      '提取用户核心查询目标',
      '识别涉及的字段和表结构',
      '明确时间范围',
      '建立字段与表之间的关系',
      '构建字段、JOIN、聚合等意图结构'
    ],
    input: {
      user_input: input,
      schema_definitions: schemaDefinitions
    },
    output_format: {
      query_goal: 'string',
      time_range: 'string',
      fields: [
        {
          name: 'string',
          desc: 'string'
        }
      ],
      joins: [
        {
          left: 'string',
          right: 'string',
          type: 'LEFT JOIN | INNER JOIN',
          reason: 'string'
        }
      ],
      filters: [
        {
          field: 'string',
          op: '= | > | < | IN',
          value: 'string'
        }
      ],
      aggregations: [
        {
          func: 'SUM | COUNT | AVG',
          field: 'string'
        }
      ],
      group_by: ['string'],
      biz_focus: ['string']
    }
  }
}

// 业务规则增强与案例匹配
function businessRuleEnhancement(query_examples: string) {
  return {
    phase: 'Phase 2',
    name: '业务规则增强与案例匹配',
    instruction:
      '你现在可以参考业务知识库与历史 SQL 案例，对意图结构进行增强与校正，仅在需要时激活使用。',
    workflow: [
      '检查 biz_focus 是否包含转化、ROI、实验组等关键词',
      '匹配历史查询样例中的结构逻辑',
      '补充字段表达、筛选、JOIN 逻辑',
      '增强聚合与 group_by，避免缺失字段',
      '记录字段增强来源（推理 or 规则）'
    ],
    input: {
      intent_structure: '{{phase1_output}}',
      query_examples
    },
    business_rules: [
      {
        name: '转化',
        rule: '转化通常来讲是目标转化，ad_config 表中使用 targeting_conversion 字段表示广告主的目标转化类型，与ad_action_log_conv_flow 中的 eventtype字段对应。使用 COUNT(DISTINCT pqtid) 统计每个广告主的 唯一用户转化次数，避免同一用户多次行为重复计数。关联广告配置表**ad_config** 中的targeting_conversion字段，确保转化与广告目标匹配'
      },
      {
        name: '收入计算',
        rule: '收入数据来自ad_platform_package_cost_per5min表的packagecost字段，仅关联广告点击事件（businesstype = adClick）,使用 SUM(packagecost) 累加每个广告主的总消耗金额，不结合包装系数计算'
      }
    ],
    output_format: {
      enhanced_fields: ['string'],
      final_filters: ['string'],
      final_joins: ['string'],
      aggregations: ['string'],
      group_by: ['string'],
      notes: ['字段 A 推理自规则 X', 'JOIN B 来源于历史查询案例 Y']
    }
  }
}

// SQL 构造与输出
function hiveSqlConstruction() {
  return {
    phase: 'Phase 3',
    name: 'SQL 构造与输出',
    instruction:
      '你需要将结构化意图转化为可执行 SQL 查询语句，遵循在阿里云大数据平台运行的 Hive 语法规范，字段必须明确，结构清晰。',
    role_hint: '你是结构意图驱动 SQL 构造器，输出需标准化、准确并包含注释。',
    rules: [
      '必须显式指定字段，禁止使用 SELECT *',
      '若用户未指定时间范围，默认查询当天数据，并以注释注明',
      'JOIN 查询必须显式声明表名、关联字段和 ON 条件',
      '字段选择和 JOIN 逻辑可参考历史查询 case，如结构类似请合理继承',
      '所有字段、表名必须基于输入提供的结构，不得杜撰',
      '通过 GET_JSON_OBJECT 方法解析 JSON 字段',
      '所有分区表的查询语句 必须显示表明时间范围限制'
    ],
    workflow: [
      '提取 SELECT 字段（加别名与注释）',
      '组合 FROM 与 JOIN 语句（含 ON 与 JOIN 类型）',
      '添加时间与业务 WHERE 过滤条件',
      '加入聚合函数与 group_by 逻辑（如适用）',
      '最终组装为完整 Hive SQL 语句'
    ],
    input: {
      enhanced_structure: '{{phase2_output}}'
    },
    output_format: {
      sql: 'string'
    },
    constraints: [
      '仅生成 Hive SQL 子集语法，确保兼容阿里云大数据查询引擎',
      '禁止 无 WHERE 条件的全表扫描',
      '禁止 无关联条件的 JOIN 查询',
      '禁止生成任何 UPDATE、INSERT、DELETE，仅限 SELECT',
      '不引入未提供的字段或表',
      '不处理数据权限、治理、ETL 或调度相关逻辑',
      '只返回一条最优 SQL 查询语句，不提供多个备选方案'
    ]
  }
}