SQLBot/backend/templates/sql_examples/StarRocks.yaml

template:
  quot_rule: |
    <rule>
      必须对数据库名、表名、字段名、别名外层加反引号（`）。
      <note>
        1. 点号（.）不能包含在反引号内，必须写成 `database`.`table`
        2. 当标识符为关键字、含特殊字符或需保留大小写时必须加反引号
        3. StarRocks 标识符默认不区分大小写，但建议统一使用反引号
      </note>
    </rule>

  limit_rule: |
    <rule>
      当需要限制行数时，必须使用标准的LIMIT语法
      <note>
        1. 标准写法：LIMIT [count]
        2. 分页写法：LIMIT [count] OFFSET [start]
        3. 支持 MySQL 兼容写法：LIMIT [offset], [count]
      </note>
    </rule>

  other_rule: |
    <rule>必须为每个表生成别名（不加AS）</rule>
    {multi_table_condition}
    <rule>禁止使用星号(*)，必须明确字段名</rule>
    <rule>中文/特殊字符字段需保留原名并添加英文别名</rule>
    <rule>函数字段必须加别名</rule>
    <rule>百分比显示为：CONCAT(ROUND(x*100,2),'%')</rule>
    <rule>避免与StarRocks关键字冲突（如`rank`/`partition`/`values`）</rule>
    <rule>分区查询需使用PARTITION语句明确指定</rule>
    <rule>优先使用Colocate Group特性优化JOIN性能</rule>

  basic_example: |
    <basic-examples>
      <intro>
        📌 以下示例严格遵循 StarRocks 规范（兼容 MySQL 协议）
        ⚠️ 注意：示例中的表名、字段名均为演示虚构，实际使用时需替换为用户提供的真实标识符。
        🔍 重点观察：
          1. 反引号包裹所有数据库对象
          2. 分区/分桶等MPP优化特性
          3. 关键字冲突规避（如`rank`）
      </intro>
      <example>
        <input>查询 test.orders 表的前100条订单（含中文字段）</input>
        <output-bad>
          SELECT * FROM test.orders LIMIT 100  -- 错误：使用星号
          SELECT `订单ID`, `金额` FROM `test`.`orders` `t1`  -- 错误：缺少英文别名
        </output-bad>
        <output-good>
          SELECT 
            `t1`.`订单ID` AS `order_id`,
            `t1`.`金额` AS `amount_usd`,
            CONCAT(ROUND(`t1`.`折扣率` * 100, 2), '%') AS `discount_percent`
          FROM `test`.`orders` `t1`
          PARTITION (`p2023`)  -- StarRocks分区裁剪
          LIMIT 100
        </output-good>
      </example>
      <example>
        <input>统计用户表 dw.users（含关键字rank）的活跃占比</input>
        <output-bad>
          SELECT rank, status FROM dw.users  -- 错误：未处理关键字
          SELECT `rank`, active_ratio FROM `dw`.`users`  -- 错误：百分比未格式化
        </output-bad>
        <output-good>
          SELECT 
            `u`.`rank` AS `user_rank`,
            CONCAT(ROUND(`u`.`active_ratio` * 100, 2), '%') AS `active_percent`
          FROM `dw`.`users` `u`
          WHERE `u`.`status` = 1
          AND `u`.`dt` = '2024-01-01'  -- 分区键过滤
          LIMIT 1000
        </output-good>
      </example>

      <example>
        <input>统计订单表 test.orders 中2024年每个客户的消费总金额，返回金额最高的前100名客户</input>
        <output-bad>
          SELECT `t`.`customer_id` AS `customer_id`, SUM(`t`.`amount`) AS `total_amount`
          FROM (SELECT `customer_id`, `amount` FROM `test`.`orders` WHERE `create_time` >= '2024-01-01' LIMIT 100) `t`
          GROUP BY `t`.`customer_id`
          -- 严重错误：LIMIT加在了内层子查询中，聚合只基于被截断的100行数据计算，结果错误！
        </output-bad>
        <output-good>
          SELECT
            `o`.`customer_id` AS `customer_id`,
            SUM(`o`.`amount`) AS `total_amount`
          FROM `test`.`orders` `o`
          WHERE `o`.`create_time` >= '2024-01-01'
          GROUP BY `o`.`customer_id`
          ORDER BY `total_amount` DESC
          LIMIT 100
          -- 正确：聚合基于完整数据计算，LIMIT只限制最终返回结果的行数，且只出现在最外层查询
        </output-good>
      </example>
    </basic-examples>

  example_engine: StarRocks 3.0
  example_answer_1: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `continent` AS `continent_name`, `year` AS `year`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` ORDER BY `country`, `year`","tables":["sample_country_gdp"],"chart-type":"line"}
  example_answer_1_with_limit: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `continent` AS `continent_name`, `year` AS `year`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` ORDER BY `country`, `year` LIMIT 1000","tables":["sample_country_gdp"],"chart-type":"line"}
  example_answer_2: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` WHERE `year` = '2024' ORDER BY `gdp` DESC","tables":["sample_country_gdp"],"chart-type":"pie"}
  example_answer_2_with_limit: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` WHERE `year` = '2024' ORDER BY `gdp` DESC LIMIT 1000","tables":["sample_country_gdp"],"chart-type":"pie"}
  example_answer_3: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` WHERE `year` = '2025' AND `country` = '中国'","tables":["sample_country_gdp"],"chart-type":"table"}
  example_answer_3_with_limit: |
    {"success":true,"sql":"SELECT `country` AS `country_name`, `gdp` AS `gdp_usd` FROM `Sample_Database`.`sample_country_gdp` WHERE `year` = '2025' AND `country` = '中国' LIMIT 1000","tables":["sample_country_gdp"],"chart-type":"table"}