{
"chartParams": [ // 交互输入参数，SQL 里用 ${name} 引用
{"name":"dims","type":"dimension","allowMulti":true, // -> GROUP BY ${dims}
"fromTableRefs":[{"tableName":"cat.schema.table","columns":["region"]}]},
{"name":"filters","type":"filter","allowMulti":true, // -> WHERE ${filters}
"fromTableRefs":[{"tableName":"cat.schema.table","columns":["channel"]}]}
],
"outputColumns": [ // 每个 SELECT 输出列对应一项
{"name":"total_amount", // 必须与 SQL 的 AS 别名一致
"metricName":"区域销售额", // 必填，且在域内唯一
"type":"decimal","stdTypeName":"double", // type 必填，stdTypeName 可选
"alias":["销售额"], // 可选，展示别名
"description":"按区域汇总的销售总额"} // 可选
],
"relatedTables": ["cat.schema.table"], // SQL 涉及的所有表
"sql": "SELECT ${dims}, SUM(final_amount) AS total_amount FROM ... GROUP BY ${dims}"
}
字段说明
字段
作用
关键点
chartParams[].name
chartParams[].name
占位符名，SQL 里写
${name}
${name}
每个 SQL 占位符必须在此有对应项
chartParams[].type
chartParams[].type
dimension
dimension
（分组维度）/
filter
filter
（筛选条件）
dimension → GROUP BY；filter → WHERE
chartParams[].allowMulti
chartParams[].allowMulti
是否允许多选
维度多选可下钻，筛选多选可组合
chartParams[].fromTableRefs
chartParams[].fromTableRefs
该参数可选的表和列
用户交互时从这些列里选
outputColumns[].name
outputColumns[].name
输出列名
必须等于 SQL 里的
AS
AS
别名
outputColumns[].metricName
outputColumns[].metricName
指标名（页面显示）
必填，且在域内唯一
outputColumns[].type
outputColumns[].type
数据类型（
bigint
bigint
/
decimal
decimal
…）
必填
outputColumns[].alias
outputColumns[].alias
展示别名（数组）
可选
outputColumns[].description
outputColumns[].description
指标描述
可选
relatedTables
relatedTables
SQL 涉及的所有表
JOIN 时把维表也列进去
sql
sql
分析 SQL 模板
建议用
--sql
--sql
单独传，避免引号转义
用
--sql
--sql
分离 SQL
SQL 里常有单引号（
WHERE status='已完成'
WHERE status='已完成'
），塞进
--content
--content
JSON 要层层转义、极易出错。用独立的
--sql
--sql
参数传 SQL，CLI 会自动把它注入 content 的
sql
sql
字段。
--content
--content
与
--sql
--sql
至少提供其一。
⚠️ 注意：
--sql
--sql
里的
${dims}
${dims}
/
${filters}
${filters}
占位符存在 shell 引号陷阱——如果放在双引号
"..."
"..."
里且不转义，会被 shell（bash/zsh）当成变量展开成空串，CLI 收到的是
SELECT , ... WHERE GROUP BY
SELECT , ... WHERE GROUP BY
，报
CZLH-42000: Syntax error at or near ','
CZLH-42000: Syntax error at or near ','
，看似 SQL 语法错，实则占位符在到达 CLI 前就没了。两种正确写法：
# ✅ 推荐：单引号包裹，shell 完全不碰 $
--sql 'SELECT ${dims}, COUNT(*) AS c FROM t WHERE ${filters} GROUP BY ${dims}'
# ✅ 或：双引号内转义 $
--sql "SELECT \${dims}, COUNT(*) AS c FROM t WHERE \${filters} GROUP BY \${dims}"
五条必记规则
这五条是实操中最容易踩坑、也最影响成败的规则。
1. 每个
${name}
${name}
占位符必须在 chartParams 中有对应项
SQL 里写了
${dims}
${dims}
，chartParams 里就必须有一个
name:"dims"
name:"dims"
的项。否则占位符不会被替换，
$
$
裸留在 SQL 里，报错：
CZLH-42000: Syntax error at or near '$'
2.
outputColumns[].metricName
outputColumns[].metricName
必填，且在域内唯一
每个输出列都要有
metricName
metricName
（页面上的"指标名"）。缺失会导致页面显示"请输入指标名"、无法保存；在同一个域内重名会报错：
CZD-99999: 答案构建器输出指标名【销售总额】和域中的指标名重名
⚠️ 注意：通用名（销售额、订单数、满意度）极易在域内重复。给它们加业务上下文前缀区分，例如
区域销售额
区域销售额
/
行业销售额
行业销售额
/
分层销售额
分层销售额
。
3. 窗口函数 / ROLLUP 引用
${dims}
${dims}
列时，用子查询包裹
当
RANK() OVER (PARTITION BY ...)
RANK() OVER (PARTITION BY ...)
、
LAG(...) OVER (ORDER BY ...)
LAG(...) OVER (ORDER BY ...)
或
ROLLUP(${dims})
ROLLUP(${dims})
里的列正好是
${dims}
${dims}
展开出来的列时，占位符替换会与窗口/分组的列引用冲突，报语义错误。
规避方法：内层子查询用固定列名完成窗口/聚合计算，外层再
SELECT ${dims}
SELECT ${dims}
选取。
-- ❌ 直接在窗口里用 ${dims} 展开的列，冲突报错
SELECT ${dims}, RANK() OVER (PARTITION BY region ORDER BY SUM(final_amount) DESC) AS rk
FROM t GROUP BY ${dims}
-- ✅ 内层固定列名算好，外层只选取
SELECT ${dims}, amt, rank_in_region FROM (
SELECT region, channel, SUM(final_amount) AS amt,
RANK() OVER (PARTITION BY region ORDER BY SUM(final_amount) DESC) AS rank_in_region
FROM t GROUP BY region, channel
) x
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "销售员业绩(可筛选区域渠道产品)" \
--content '{"chartParams":[{"name":"filters","type":"filter","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_fact_sales","columns":["region","channel","product_type"]}]},{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_fact_sales","columns":["salesperson"]}]}],"outputColumns":[{"name":"订单数","metricName":"销售员订单数","type":"bigint","stdTypeName":"int"},{"name":"销售总额","metricName":"销售员销售总额","type":"decimal","stdTypeName":"double"},{"name":"平均客单价","metricName":"销售员客单价","type":"decimal","stdTypeName":"double"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_fact_sales"]}' \
--sql "SELECT \${dims}, COUNT(*) AS \`订单数\`, SUM(final_amount) AS \`销售总额\`, ROUND(AVG(final_amount),2) AS \`平均客单价\` FROM quick_start.ict_industry_demo.v_gpt_fact_sales WHERE \${filters} GROUP BY \${dims}"
其中
--sql
--sql
的 SQL 格式化后：
SELECT ${dims},
COUNT(*) AS `订单数`,
SUM(final_amount) AS `销售总额`,
ROUND(AVG(final_amount), 2) AS `平均客单价`
FROM quick_start.ict_industry_demo.v_gpt_fact_sales
WHERE ${filters}
GROUP BY ${dims}
要点：
filters
filters
列了三个可筛选列，SQL 用
WHERE ${filters}
WHERE ${filters}
，用户运行时勾选筛选值；输出列的 SQL
AS
AS
别名和
outputColumns[].name
outputColumns[].name
都用了中文，且
metricName
metricName
加了"销售员"前缀保证域内唯一。
场景三：跨表 JOIN 关联分析
业务问题：卖得多的产品，售后满意度和工单率如何？——销售表关联售后表，普通指标只能单表，做不到。
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "产品销售与售后质量关联" \
--content '{"chartParams":[{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_fact_sales","columns":["product_type"]}]}],"outputColumns":[{"name":"sales_amount","metricName":"产品销售额(关联售后)","type":"decimal","stdTypeName":"double"},{"name":"service_count","metricName":"产品售后工单数","type":"bigint","stdTypeName":"int"},{"name":"avg_satisfaction","metricName":"产品售后满意度","type":"decimal","stdTypeName":"double"},{"name":"service_per_order","metricName":"产品单均工单数","type":"decimal","stdTypeName":"double"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_fact_sales","quick_start.ict_industry_demo.v_gpt_fact_service"]}' \
--sql "SELECT \${dims}, SUM(s.final_amount) AS sales_amount, COUNT(DISTINCT sv.service_id) AS service_count, ROUND(AVG(sv.satisfaction_score),2) AS avg_satisfaction, ROUND(COUNT(DISTINCT sv.service_id)*1.0/COUNT(DISTINCT s.sale_id),3) AS service_per_order FROM quick_start.ict_industry_demo.v_gpt_fact_sales s LEFT JOIN quick_start.ict_industry_demo.v_gpt_fact_service sv ON s.order_no=sv.order_no GROUP BY \${dims}"
其中
--sql
--sql
的 SQL 格式化后：
SELECT ${dims},
SUM(s.final_amount) AS sales_amount,
COUNT(DISTINCT sv.service_id) AS service_count,
ROUND(AVG(sv.satisfaction_score), 2) AS avg_satisfaction,
ROUND(COUNT(DISTINCT sv.service_id) * 1.0
/ COUNT(DISTINCT s.sale_id), 3) AS service_per_order
FROM quick_start.ict_industry_demo.v_gpt_fact_sales s
LEFT JOIN quick_start.ict_industry_demo.v_gpt_fact_service sv
ON s.order_no = sv.order_no
GROUP BY ${dims}
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "产品销售排名与累计占比(帕累托)" \
--content '{"chartParams":[{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_fact_sales","columns":["product_type"]}]}],"outputColumns":[{"name":"total_amount","metricName":"产品销售额(帕累托)","type":"decimal","stdTypeName":"double"},{"name":"sales_rank","metricName":"产品销售排名","type":"bigint","stdTypeName":"int"},{"name":"cum_ratio","metricName":"产品累计占比","type":"decimal","stdTypeName":"double"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_fact_sales"]}' \
--sql "SELECT \${dims}, SUM(final_amount) AS total_amount, RANK() OVER (ORDER BY SUM(final_amount) DESC) AS sales_rank, ROUND(SUM(SUM(final_amount)) OVER (ORDER BY SUM(final_amount) DESC)*100.0/SUM(SUM(final_amount)) OVER (),2) AS cum_ratio FROM quick_start.ict_industry_demo.v_gpt_fact_sales GROUP BY \${dims}"
其中
--sql
--sql
的 SQL 格式化后：
SELECT ${dims},
SUM(final_amount) AS total_amount,
RANK() OVER (ORDER BY SUM(final_amount) DESC) AS sales_rank,
ROUND(SUM(SUM(final_amount)) OVER (ORDER BY SUM(final_amount) DESC)
* 100.0
/ SUM(SUM(final_amount)) OVER (), 2) AS cum_ratio
FROM quick_start.ict_industry_demo.v_gpt_fact_sales
GROUP BY ${dims}
要点：
RANK()
RANK()
排名、
SUM(...) OVER (ORDER BY ...)
SUM(...) OVER (ORDER BY ...)
算累计。这里
${dims}
${dims}
在最外层 SELECT，没进 PARTITION BY，所以不触发规则 3 的冲突。
场景五：时序环比——区域内渠道排名要用子查询包裹
业务问题：每个区域内，各销售渠道的销售额排名。这里
RANK() OVER (PARTITION BY region ...)
RANK() OVER (PARTITION BY region ...)
的
region
region
正是
${dims}
${dims}
展开列，直接写会冲突，必须按规则 3 用子查询包裹。
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "区域渠道效能矩阵" \
--content '{"chartParams":[{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_ads_monthly_sales_summary","columns":["region","channel"]}]}],"outputColumns":[{"name":"amt","metricName":"区域渠道销售额","type":"decimal","stdTypeName":"double"},{"name":"rank_in_region","metricName":"渠道区域内排名","type":"bigint","stdTypeName":"int"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_ads_monthly_sales_summary"]}' \
--sql "SELECT \${dims}, amt, rank_in_region FROM (SELECT region, channel, SUM(final_amount) AS amt, RANK() OVER (PARTITION BY region ORDER BY SUM(final_amount) DESC) AS rank_in_region FROM quick_start.ict_industry_demo.v_gpt_ads_monthly_sales_summary GROUP BY region, channel) t"
其中
--sql
--sql
的 SQL 格式化后（注意内外两层结构）：
SELECT ${dims}, amt, rank_in_region
FROM (
SELECT region,
channel,
SUM(final_amount) AS amt,
RANK() OVER (PARTITION BY region
ORDER BY SUM(final_amount) DESC) AS rank_in_region
FROM quick_start.ict_industry_demo.v_gpt_ads_monthly_sales_summary
GROUP BY region, channel
) t
要点：内层子查询用固定列名
region, channel
region, channel
完成 PARTITION BY 排名，外层
SELECT ${dims}
SELECT ${dims}
只做选取——这是窗口/时序类分析的通用范式。
场景六：多维小计（ROLLUP）与风险标记（条件聚合 + 子查询）
多维小计——分区域分产品的销售额 + 各级汇总一次出：
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "销售额多维小计报表" \
--content '{"chartParams":[{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_fact_sales","columns":["region","product_type"]}]}],"outputColumns":[{"name":"total_amount","metricName":"多维小计销售额","type":"decimal","stdTypeName":"double"},{"name":"order_cnt","metricName":"多维小计订单数","type":"bigint","stdTypeName":"int"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_fact_sales"]}' \
--sql "SELECT \${dims}, SUM(final_amount) AS total_amount, COUNT(*) AS order_cnt FROM quick_start.ict_industry_demo.v_gpt_fact_sales GROUP BY ROLLUP(\${dims})"
其中
--sql
--sql
的 SQL 格式化后：
SELECT ${dims},
SUM(final_amount) AS total_amount,
COUNT(*) AS order_cnt
FROM quick_start.ict_industry_demo.v_gpt_fact_sales
GROUP BY ROLLUP(${dims})
风险象限——标记"高销量但低满意度"的高风险产品，用
CASE WHEN
CASE WHEN
+ 相关子查询算全局阈值：
cz-cli analytics-agent answer-builder create \
--domain-id 43 --datasource-id 8448 \
--analysis-name "产品口碑风险预警象限" \
--content '{"chartParams":[{"name":"dims","type":"dimension","allowMulti":true,"fromTableRefs":[{"tableName":"quick_start.ict_industry_demo.v_gpt_dim_product","columns":["brand_vendor","category"]}]}],"outputColumns":[{"name":"sales_amt","metricName":"品牌销售额","type":"decimal","stdTypeName":"double"},{"name":"avg_sat","metricName":"品牌满意度","type":"decimal","stdTypeName":"double"},{"name":"complaint_rate","metricName":"品牌工单率","type":"decimal","stdTypeName":"double"},{"name":"risk_flag","metricName":"品牌口碑风险标记","type":"bigint","stdTypeName":"int"}],"relatedTables":["quick_start.ict_industry_demo.v_gpt_fact_sales","quick_start.ict_industry_demo.v_gpt_fact_service","quick_start.ict_industry_demo.v_gpt_dim_product"]}' \
--sql "SELECT \${dims}, SUM(s.final_amount) AS sales_amt, ROUND(AVG(sv.satisfaction_score),2) AS avg_sat, ROUND(COUNT(DISTINCT sv.service_id)*100.0/COUNT(DISTINCT s.sale_id),2) AS complaint_rate, CASE WHEN AVG(sv.satisfaction_score)<3 AND SUM(s.final_amount)>(SELECT AVG(final_amount) FROM quick_start.ict_industry_demo.v_gpt_fact_sales) THEN 1 ELSE 0 END AS risk_flag FROM quick_start.ict_industry_demo.v_gpt_fact_sales s JOIN quick_start.ict_industry_demo.v_gpt_dim_product p ON s.product_id=p.product_id LEFT JOIN quick_start.ict_industry_demo.v_gpt_fact_service sv ON s.order_no=sv.order_no GROUP BY \${dims}"
其中
--sql
--sql
的 SQL 格式化后：
SELECT ${dims},
SUM(s.final_amount) AS sales_amt,
ROUND(AVG(sv.satisfaction_score), 2) AS avg_sat,
ROUND(COUNT(DISTINCT sv.service_id) * 100.0
/ COUNT(DISTINCT s.sale_id), 2) AS complaint_rate,
CASE
WHEN AVG(sv.satisfaction_score) < 3
AND SUM(s.final_amount) > (SELECT AVG(final_amount)
FROM quick_start.ict_industry_demo.v_gpt_fact_sales)
THEN 1 ELSE 0
END AS risk_flag
FROM quick_start.ict_industry_demo.v_gpt_fact_sales s
JOIN quick_start.ict_industry_demo.v_gpt_dim_product p ON s.product_id = p.product_id
LEFT JOIN quick_start.ict_industry_demo.v_gpt_fact_service sv ON s.order_no = sv.order_no
GROUP BY ${dims}
