BigQuery Analyst Skill

🎯 核心使命

帮助用户安全、准确地分析 BigQuery 数据,确保:

1. 需求对齐: 先确认指标口径,再写SQL
2. 成本可控: 自动检查 Slot Time,超过阈值拒绝执行
3. 结果可信: AI自检合理性,输出业务解释

📊 数据环境

• 项目: srpproduct-dc37e
• 数据集: favie_rpt (报表层), favie_dw (数仓层)
• 表数量: 608张生产表 + 519个函数
• 业务域: 16个主要业务域
• 数据范围: 2025-01-01 至今

⚠️ 归档业务域

decofy 域（已归档）:

• 状态: 该应用已归档，基本不再使用
• 规则: 除非用户明确提及 "decofy"，否则默认不扫描该域下的表和文档
• 原因: 避免不必要的上下文加载，提高查询效率
• 位置: metadata/domains/decofy/ (66张表)

💡 如何触发: 当用户问题包含 "decofy" 关键词时，才加载该域的数据

⚠️ 启动连接验证（必须执行）

在处理任何查询请求前，必须先验证 BigQuery CLI 连接:

CRITICAL: 验证 BigQuery CLI 连接状态

1. 执行连接测试查询:
   bq query --use_legacy_sql=false --format=json "SELECT 1 as test"

2. 如果命令失败或返回认证错误:
   ❌ 立即停止，不要继续
   ❌ 使用 AskUserQuestion 工具弹出确认窗口
   ❌ 不要生成任何 SQL 查询

3. 如果连接成功:
   ✅ 继续正常流程

连接失败时的处理:

使用 AskUserQuestion 工具向用户确认:

{
  "questions": [{
    "question": "BigQuery CLI 连接失败。我们需要依赖 bq CLI 进行查询，您需要先完成认证登录。是否现在进行登录验证？",
    "header": "BQ认证",
    "options": [
      {
        "label": "立即登录验证",
        "description": "运行 gcloud auth application-default login 完成认证"
      },
      {
        "label": "稍后处理",
        "description": "暂时跳过，稍后再进行认证"
      }
    ],
    "multiSelect": false
  }]
}

如果用户选择"立即登录验证":

1. 提示用户在终端运行:
   gcloud auth application-default login
   gcloud auth login
   gcloud config set project srpproduct-dc37e

2. 等待用户完成认证

3. 重新执行连接测试查询验证是否成功

4. 如果仍然失败，提示用户检查:
   - GCP 项目 ID 是否正确
   - 是否有 BigQuery 访问权限
   - 网络连接是否正常

注意: 此验证不可跳过，没有 BigQuery CLI 连接无法执行任何查询。

---

📚 知识库加载策略 (三层渐进式)

Layer 1: 核心规则 (必须加载 ~10KB)

在处理任何查询前,必须先读取:

1. READ core/DATA_INFRASTRUCTURE.md - 数据集位置、表与函数关系、命名规范
2. READ core/CRITICAL_RULES.md - 10条必遵守的查询规则
3. READ core/LAYER_SELECTION.md - 快速决策用哪个数据层
4. SCAN metadata/index/DOMAIN_INDEX.md - 了解有哪些业务域

为什么必须:

• 了解数据存储位置和命名规范,避免引用错误的数据集
• 避免90%的常见错误(缺少dt过滤、user_group重复计数等)

---

Layer 2: 业务域加载 (按需加载 ~50KB/域)

触发规则: 识别用户问题涉及的业务域

用户提问 → 关键词匹配 → 加载对应域

关键词映射:
- "产品"/"SKU"/"质量"/"品牌" → metadata/domains/product_quality/README.md
- "广告"/"投放"/"ROI"/"成本" → metadata/domains/advertising/README.md
- "gem"/"头像"/"avatar" → metadata/domains/gem/README.md
- "活跃"/"留存"/"DAU"/"MAU" → metadata/domains/user_behavior/README.md
- "feed"/"内容"/"推荐" → metadata/domains/feed/README.md
- "搜索"/"search" → metadata/domains/search/README.md
- "媒体"/"图片"/"视频"/"image" → metadata/domains/media/README.md
- "试穿"/"tryon"/"生成" → metadata/domains/tryon/README.md
- "积分"/"会员"/"points" → metadata/domains/points_membership/README.md
- "增长"/"归因"/"AppsFlyer" → metadata/domains/growth/README.md
- "系统"/"配置"/"映射" → metadata/domains/system/README.md
- "用户画像"/"账号"/"profile" → metadata/domains/user_profile/README.md
- "聊天"/"chat"/"对话" → metadata/domains/chat/README.md
- "爬虫"/"crawl"/"抓取" → metadata/domains/crawl/README.md
- "标签"/"分类规则"/"数据质量"/"数据增强" → metadata/domains/data_enrichment/README.md

⚠️ 排除规则:
- "decofy" → 仅当用户明确提及"decofy"时才加载 metadata/domains/decofy/ (已归档应用)

域README包含:

• ✅ 业务概览和核心流程
• ✅ 关键指标定义
• ✅ 常见查询场景 (5-10个示例)
• ✅ 注意事项和已知问题

---

Layer 3: 表文档加载 (精确加载 ~5KB/表)

触发时机: 确定具体要查询的表后

Step 1: 读取 metadata/domains/{domain}/TABLES.md
        → 使用决策树选择正确的表
        → 例: 需要明细数据 → DWD层, 需要聚合指标 → RPT层

Step 2: 读取 metadata/domains/{domain}/tables/{table_name}.md
        → 获取完整字段定义
        → 了解数据范围和更新频率
        → 查看查询示例

Step 3: (可选) 读取 metadata/domains/{domain}/functions/{function_name}.md
        → 仅当需要理解指标计算逻辑时才加载

绝不做: 一次性加载所有表文档 (会消耗几十万tokens)

---

🔄 标准查询工作流

Step 1: 需求理解与口径确认

用户提问示例:

"查询最近7天积分消耗量最高的功能"

AI 分析流程:

1. 识别业务域: "积分消耗" → points_membership域
2. 加载域概览: READ metadata/domains/points_membership/README.md
3. 提取关键指标:
   - "消耗量" = consume_points_points_amt (积分数) 还是 consume_points_user_cnt (用户数)?
   - "功能" = consume_type字段
   - "最近7天" = dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

4. 生成确认清单并等待用户回复

⚠️ 关键要求：必须等待用户确认

CRITICAL: 指标口径确认是强制步骤

1. 输出确认清单后，**必须停止执行**
2. **等待用户明确回复**，不要自行假设或继续
3. **只有在收到用户确认后**，才能进入 Step 2
4. 如果用户没有明确回复，**不要生成任何 SQL**

这是为了确保：
- 指标理解对齐，避免查询错误数据
- 用户清楚知道将要查询什么
- 结果符合用户真实需求

输出给用户:

📊 **指标口径确认** (请回复后我再继续):

1. **"消耗量"** 的定义:
   - A) 消耗积分总数 (consume_points_points_amt)
   - B) 消耗用户数 (consume_points_user_cnt)
   - C) 消耗次数 (consume_points_task_cnt)

   您的选择: _____

2. **时间范围**:
   - 数据截止到昨天 (2026-01-29)
   - 时区: UTC
   - 确认? 是/否

3. **人群筛选**:
   - A) 全部用户 (user_group='all')
   - B) 特定平台 (如Android)

   您的选择: _____

⚠️ **请您回复确认后，我再为您生成 SQL 查询。**

注意：在用户回复确认前，不要执行任何后续步骤。

---

Step 2: 表选择与字段确认

前置条件: 已收到用户的口径确认

用户确认示例: "A, 全部用户"

AI 执行:

1. READ metadata/domains/points_membership/TABLES.md
   → 决策: 需要明细数据(DWD) 还是 聚合指标(RPT)?
   → 用户要分析"各功能消耗量" → 需要consume_type维度
   → 选择: dwd_consume_point (明细表)

2. READ metadata/domains/points_membership/tables/dwd_consume_point.md
   → 确认字段:
     - dt: DATE类型,分区字段 ✅
     - consume_type: STRING, 消耗类型 ✅
     - consume_points: INTEGER, 消耗积分数 ✅
     - consume_status: STRING, 状态过滤 ✅
   → 确认规则:
     - 必须过滤 consume_status='consumed'
     - dt用DATE函数,不能用字符串

3. READ core/CRITICAL_RULES.md (复习规则)
   → 规则1: 必须dt过滤 ✅
   → 规则3: dt是DATE类型 ✅
   → 规则6: 除法用NULLIF ✅

---

Step 3: SQL 生成

⚠️ 关键规则：禁止使用中文字段别名

❌ 错误示例:
SELECT SUM(consume_points) AS 总积分  -- 会导致 PreHook 成本检查失败

✅ 正确示例:
SELECT SUM(consume_points) AS total_points  -- 必须使用英文别名

应用规则生成SQL:

-- ✅ 遵循所有核心规则的标准SQL
SELECT
  consume_type,
  SUM(consume_points) as total_points,  -- ✅ 使用英文别名
  COUNT(DISTINCT user_id) as unique_users,
  ROUND(SUM(consume_points) * 100.0 / NULLIF(SUM(SUM(consume_points)) OVER(), 0), 2) as percentage
FROM `srpproduct-dc37e.favie_dw.dwd_favie_gensmo_membership_consume_point_inc_1d`
WHERE
  -- 规则1: 必须dt过滤
  dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  -- 规则: 只统计已消耗
  AND consume_status = 'consumed'
GROUP BY consume_type
ORDER BY total_points DESC
LIMIT 10

成本预估 (通过PreHook):

# Dry-run检查
bq query --dry_run --use_legacy_sql=false "..."
# 输出: Will process 500 MB
# 估算: 500MB ≈ 0.014 slot-hours ✅ 远低于20小时阈值

---

Step 4: 结果校验与解释

执行查询后,AI自检:

1. 数据范围检查:
   - 返回行数是否合理? (TOP10不应返回100行)
   - 日期范围正确? (7天内,不应有去年数据)

2. 数值合理性:
   - total_points是否为正数? ✅
   - percentage总和是否≈100%? ✅
   - unique_users是否合理? (1千-1万,不应是100万)

3. 空值检查:
   - consume_type是否有NULL?
   - 若有,说明数据质量问题,需提示

4. 异常检测:
   - 某个功能占比>80%? 需特别说明
   - 某天数据突降/突增? 需标注异常

5. 数据溯源说明 (新增):
   - 准备向用户解释:表选择原因、字段计算逻辑
   - 格式: 不需要完整SQL,用简洁的业务语言描述

输出给用户:

## 📊 查询结果

### 数据概览
- 时间范围: 2026-01-23 至 2026-01-29 (7天)
- 总消耗积分: 25,732分
- 去重用户数: 1,723人

### 功能消耗排行 TOP 5

1. **情侣装试穿 (try_on_couple)** - 49.1%
   - 消耗积分: 12,640分
   - 用户数: 892人
   - 💡 解读: 绝对核心功能,占比接近一半

2. **视频试穿 (try_on_video)** - 22.7%
   - 消耗积分: 5,853分
   - 用户数: 421人

3. **头像精修 (avatar_refine)** - 11.1%
   - 消耗积分: 2,851分
   - 用户数: 298人

...

### ⚠️ 重要发现
- 🔍 情侣装试穿功能远超其他功能,建议优化质量和体验
- 📉 1月28-29日消耗量下降56%,需立即排查原因

### 📋 数据来源与计算逻辑
**数据表**: `favie_dw.dwd_favie_gensmo_membership_consume_point_inc_1d` (DWD层明细表)
- **选择原因**: 需要按功能类型(consume_type)分析，RPT层无此维度，需使用明细表
- **数据范围**: dt >= 2026-01-23 (最近7天)，仅统计 consume_status='consumed'

**核心指标计算**:
- **消耗积分** = SUM(consume_points) 按功能类型聚合
- **用户数** = COUNT(DISTINCT user_id) 去重统计
- **占比** = 各功能消耗积分 / 全部功能消耗积分总和

### 数据说明
- ✅ 所有数据已过滤consume_status='consumed'
- ✅ 时区: UTC
- ⚠️ DWD层明细表,数据更新延迟约8小时

---

📂 知识库文件索引

必读文件 (启动加载)

• core/DATA_INFRASTRUCTURE.md - 数据基础设施说明 (数据集位置、表与函数关系、命名规范)
• core/CRITICAL_RULES.md - 10条核心规则
• core/LAYER_SELECTION.md - 层级选择指南
• metadata/index/DOMAIN_INDEX.md - 业务域索引

业务域文件 (按需加载)

• metadata/domains/product_quality/ - 产品质量域 (124表)
• metadata/domains/advertising/ - 广告投放域 (76表)
• metadata/domains/gem/ - Gem应用域 (66表)
• metadata/domains/decofy/ - Decofy应用域 (66表) ⚠️ 已归档，仅在明确提及时加载
• metadata/domains/user_behavior/ - 用户行为域 (56表)
• metadata/domains/feed/ - 内容流域 (52表)
• metadata/domains/data_enrichment/ - 数据增强域 (3表) ✨ 新增
• metadata/domains/search/ - 搜索推荐域 (39表)
• metadata/domains/other/ - 其他域 (28表)
• metadata/domains/media/ - 媒体资源域 (24表)
• metadata/domains/tryon/ - 试穿生成域 (23表)
• metadata/domains/points_membership/ - 积分会员域 (10表)
• metadata/domains/growth/ - 增长归因域 (10表)
• metadata/domains/system/ - 系统配置域 (10表)
• metadata/domains/crawl/ - 爬虫任务域 (8表)
• metadata/domains/user_profile/ - 用户画像域 (8表)
• metadata/domains/chat/ - 聊天对话域 (6表)
• metadata/domains/content/ - 内容域 (2表)

工作流文件 (参考)

• workflows/requirement_clarification.md - 需求澄清模板
• workflows/sql_generation.md - SQL生成规范
• workflows/result_validation.md - 结果校验清单

示例文件 (学习)

• examples/simple_aggregation.md - 简单聚合示例
• examples/multi_table_join.md - 多表关联示例
• examples/trend_analysis.md - 趋势分析示例

---

💡 最佳实践

✅ DO (推荐做法)

1. 渐进式查询: 先探索性查询LIMIT 100 → 确认无误后去掉LIMIT
2. 优先RPT层: 现成指标,性能最优
3. 明确时间范围: 总是指定dt过滤,避免全表扫描
4. 确认人群: 聚合查询必须过滤user_group,避免重复计数

❌ DON'T (避免做法)

1. **不要SELECT ***: 明确列出需要的字段
2. 不要跨层JOIN: 同一查询不要混用ODS/DWD/RPT层
3. 不要猜测字段: 不确定时,先READ表文档确认
4. 不要忽略规则: 10条核心规则必须严格遵守

---

🔍 调试提示

查询失败时检查清单:

• 是否包含dt分区过滤? → 规则1
• 聚合查询是否过滤user_group='all'? → 规则2
• dt是否用DATE类型? → 规则3
• 字段名是否正确(有无重复词)? → 规则4
• 表名是否完整(project.dataset.table)? → 基础检查

性能问题时:

• 检查是否扫描了过多分区 (建议≤30天)
• 检查是否有不必要的JOIN
• 考虑是否可用更高层级的聚合表

---

📞 获取帮助

遇到问题:

1. 检查 core/CRITICAL_RULES.md 是否遗漏
2. 查看 metadata/domains/{domain}/README.md 的已知问题
3. 参考 examples/ 目录的相似示例

反馈渠道:

• 数据团队:
• Issue系统: [内部GitLab]

---

最后更新: 2026-02-05
维护者: Data Team gutingyi
基于: 602张表 + 517个函数的完整元数据扫描