ctc_poc2026/.opencode/AGENTS.md

AGENTS.md - 电信集团PG+HiveSQL项目指南

项目概述

本项目是以PostgreSQL + HiveSQL为主的数据计算项目，专注于SQL设计和开发工作，包括DDL定义、表结构设计、数据模型设计。本项目不涉及任何SQL执行、脚本运行或命令操作，仅进行SQL代码的设计与维护。

SQL编码规范

命名约定

• 模式名: 使用dmk作为主模式（data mart kit）
• 表名:
  • 使用td_前缀表示维度表（dimension table），如td_account_period、td_region
  • 使用tm_前缀表示事实表或中间表（fact/middle table）
  • 表名使用小写字母和下划线分隔（snake_case）
• 字段名: 使用小写字母和下划线分隔（snake_case），如region_code、updated_time、data_type
• 索引名: 使用idx_前缀，后跟表名和字段名，如idx_td_region_geom、idx_td_region_parent
• 约束名: 主键使用PRIMARY KEY关键字，外键使用fk_前缀

格式化规则

• 关键字使用大写：CREATE TABLE、SELECT、INSERT INTO、WHERE、AND等
• 表名和字段名使用小写
• 每个字段定义单独一行，逗号在行末
• 括号格式：左括号后换行，右括号独占一行

CREATE TABLE IF NOT EXISTS dmk.table_name (
    column1 type CONSTRAINT,
    column2 type DEFAULT value
);

• CREATE TABLE语句中的字段缩进使用4个空格
• COMMENT ON语句单独成行，保持对齐

注释规范

• 使用COMMENT ON语句为表和字段添加注释，注释内容用单引号包裹
• 表注释说明表的用途、关联API或业务场景
• 字段注释说明字段含义、数据格式、取值范围和用途
• 使用--进行代码内章节分隔注释

示例：

-- =========================================================
-- 1. 通用维度与配置表
-- =========================================================

CREATE TABLE IF NOT EXISTS dmk.td_region (
    region_code integer PRIMARY KEY,
    region_name varchar(64) NOT NULL
);

COMMENT ON TABLE dmk.td_region IS '行政区域维表，保留区域树、区域 WKT 和区域空间索引字段。';
COMMENT ON COLUMN dmk.td_region.region_code IS '区域编码，全国/省/市/区县统一主键';
COMMENT ON COLUMN dmk.td_region.region_name IS '区域名称';

数据类型选择

• 整数使用integer，避免使用int
• 变长字符串使用varchar(n)并指定长度，避免使用text（除非确实需要存储任意长度文本）
• 时间字段使用timestamp without time zone或timestamptz
• 布尔值使用boolean，默认值为true/false
• 枚举值使用varchar配合CHECK约束，如：CHECK (region_level IN ('nation', 'province', 'city', 'district'))
• 空间数据使用PostGIS类型：geometry(Point, 4326)、geometry(MultiPolygon, 4326)
• 数值类型使用numeric(precision, scale)，如numeric(10, 6)

约束与索引设计

• 主键在字段定义处使用PRIMARY KEY，或在表定义末尾统一指定
• 复合主键使用PRIMARY KEY (col1, col2)格式
• 所有业务字段明确指定NOT NULL约束（除非允许为空）
• 逻辑删除字段使用is_valid smallint NOT NULL DEFAULT 1（1=有效，0=无效）
• 时间字段默认使用DEFAULT now()
• 排序字段使用DEFAULT 0
• 创建索引时使用IF NOT EXISTS避免重复
• 空间索引使用USING gist(geom_column)语法
• 部分索引使用WHERE子句优化查询性能

示例：

CREATE INDEX IF NOT EXISTS idx_td_region_geom ON dmk.td_region USING gist(region_geom) WHERE region_geom IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_td_region_parent ON dmk.td_region(parent_region_code, region_level, sort_no);

表设计模式

• 维度表设计：包含编码、名称、层级、父级编码、排序号、有效性标志、更新时间
• 字典表设计：使用复合主键(dict_type, dict_code)，包含字典项名称、描述、排序号
• 账期表设计：区分数据来源类型，包含年月、是否当前账期等字段
• 空间表设计：包含WKT文本字段和生成的几何字段，支持空间索引

CHECK约束规范

• 枚举值约束使用CHECK (column IN ('value1', 'value2', ...))
• 范围约束使用CHECK (column >= 0 AND column <= 100)等
• CHECK约束紧跟在字段定义之后或表定义末尾

项目结构

.opencode/           # Agent配置和缓存目录（严禁在项目根目录创建任何文件）
docs/                # 文档目录（包含生成的表定义CSV文件）
*.sql                # SQL DDL/DML脚本文件
parse_ddl*.js        # DDL解析脚本（仅用于文档生成，不涉及执行）
parse_ddl.py         # DDL解析脚本（仅用于文档生成，不涉及执行）
fix_epsg.js          # EPSG坐标系处理工具（仅用于文档生成）

应用层表模型规范 (Application Layer Table Models)

1. 表分类标识说明

在查阅项目文档或进行表设计时，必须关注表名后缀的特殊标识：

• # (依赖维表)：本项目计算过程中重点依赖的外部维度表（由第三方提供）。它们是计算的基础，智能体需重点关注其字段语义。
• * (目标计算表)：本项目需要通过 SQL 计算生成的最终目标表。这是智能体开发工作的核心产出。
• 辅助表：未带有 # 或 * 标识的表为辅助表，通常用于前端展示或配置，除非特别说明，否则不需要在计算逻辑中重点关注。

2. 文档维护与读写禁忌

• 文档权威性：docs/tables/ 目录下的 .md 文件是应用层设计的唯一权威定义。
• 修改限制：未经明确授权，严禁修改 docs/tables/ 目录下的任何 .md 文件（包括索引文件 index.md）。
• 数据访问限制：未经明确授权，严禁对 docs/tables/*_archive 目录下的 .csv 文件进行读写操作。
• 详细索引参考：完整的表清单及其业务功能说明请参考 docs/tables/index.md。

目标表设计原则 (Target Table Design)

1. 数据源选择与融合策略 (UNION 模式)

• 主从原则：除 tm_cell_grid_coverage_m 以 ODS MR 数据为主外，其余目标表原则上以 ODS OTT 数据为主数。
• 场景化引入 MR：仅在涉及重叠覆盖、过覆盖、MOD 干扰等 MR 专有指标时，才引入 MR 数据源。
• 双源 UNION 模式：
  • 室内外明细粒度 (indoor_flag IN (0, 1))：数据源锁定为 ODS MR，代表电信本网深度覆盖。
  • 全量聚合粒度 (indoor_flag = -1)：数据源锁定为 ODS OTT，代表全网大盘覆盖。
  • 集成方式：两类数据执行 UNION ALL 后存入目标表。严禁在同一行中混合两个数据源的原始指标。
• 维度缺省填充：若数据源缺失目标维度，必须使用默认值（如 indoor_flag = -1, freq = 'all'）。

2. 指标计算与聚合规范

• 加权平均原则：严禁对 avg_xxx 字段直接执行 AVG()。必须使用公式：SUM(total_xxx) / SUM(xxx_count)。
• 用户数去重：跨栅格聚合（如区域、楼宇）的用户数统计必须基于 device_id_list 去重。推荐使用近似计算函数（如 approx_count_distinct）。
• 字段过滤：涉及“市场份额”、“驻留比”、“高价值”、“VIP”等字样的字段直接置空（NULL）处理。

3. 专项表设计规则

• tm_cluster_area_m (融合聚类)：遵循“OTT 锚点聚类 + MR 空间回填”策略。基于 OTT 弱覆盖栅格确立簇边界，通过空间关联回填 MR 侧质差指标。最终表不含 indoor_flag 字段。
• tm_region_coverage_m：行政区域聚合时，必须确保 indoor_flag 的维度完整性（涵盖 0, 1, -1）。
• 楼宇分类判别：仅在 OTT 分支（indoor_flag = -1）下执行判定逻辑，严格遵循 specs/build_type_specs.md。

开发工作流规范

1. 计算逻辑 Skill 化

• 每个目标表的梳理结论必须沉淀为独立的 Skill 文档。
• 存储路径：target_table_skills/{table_name}.md。
• Skill 文档是智能体生成 SQL 的唯一基准。

2. 产物归档与结构

• 所有开发产物（SQL、Shell）必须按表归档。
• 存储路径：src/{table_name}/。
• 要求：SQL 与 Shell 脚本分离，且必须包含一个 README.md 说明执行顺序与依赖。

运行环境与持久化

• 双侧冗余：核心维表（如 td_grid, td_building_cell_m）需在 PG（支撑应用）和 Hive（支撑大规模聚合）两侧同步备份。
• 全量持久化：所有核心维表和目标计算表最终必须持久化存储于 PostgreSQL (PG) 中。
• 计算侧重：默认以 HiveSQL 侧计算为主，仅在涉及空间计算（如 tm_cluster_area_m）时使用 PostGIS (PG)。

重要注意事项

1. 本项目仅涉及SQL代码的设计与开发，不包含任何执行、测试或运行操作
2. 严禁在项目根目录创建缓存文件或临时文件，所有agent相关文件必须放在.opencode/目录
3. SQL文件包含PostGIS扩展，设计时需考虑CREATE EXTENSION IF NOT EXISTS postgis;
4. 表定义统一使用CREATE TABLE IF NOT EXISTS避免重复创建问题
5. 涉及空间数据的表需要正确设置SRID（通常为4326，对应EPSG:4326）
6. 字典表td_dict_item使用复合主键(dict_type, dict_code)设计
7. 所有表都应包含is_valid字段用于逻辑删除，updated_time字段记录更新时间
8. 表注释应说明该表支撑的API接口或业务功能
9. 关键业务语义引用：在进行数据建模时，必须参考 ods/基础信息语义统一.md。注意 ODS 原始名称到 dmk 规范名称的映射。
10. 设计指引引用：目标表设计必须遵循 specs/openspec.md 中的总体原则。