155 lines
6.8 KiB
SQL
155 lines
6.8 KiB
SQL
CREATE TABLE metric_def (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
|
||
metric_code VARCHAR(64) NOT NULL, -- 内部编码：order_cnt_delivery
|
||
metric_name VARCHAR(128) NOT NULL, -- 中文名：外送订单数
|
||
metric_aliases JSON NULL, -- 别名列表
|
||
|
||
biz_domain VARCHAR(64) NOT NULL, -- 通过table tag获取，支持人工配置
|
||
biz_desc TEXT NULL, -- 业务口径描述
|
||
|
||
chat_turn_id BIGINT NULL, -- 来自哪轮会话
|
||
|
||
tech_desc TEXT NULL, -- 技术口径描述
|
||
formula_expr TEXT NULL, -- 公式描述："sum(pay_amount)"
|
||
base_sql MEDIUMTEXT NOT NULL, -- 标准计算SQL（逻辑SQL/snippet）
|
||
|
||
time_grain VARCHAR(32) NOT NULL, -- DAY/HOUR/WEEK/MONTH
|
||
dim_binding JSON NOT NULL, -- 维度绑定，如 ["dt","store_id","channel"]
|
||
|
||
update_strategy VARCHAR(32) NOT NULL, -- FULL/INCR/REALTIME
|
||
schedule_id BIGINT NULL, -- 调度ID
|
||
schedule_type INT NULL, -- 调度类型，默认调度cron
|
||
|
||
version INT NOT NULL DEFAULT 1,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
|
||
sql_hash VARCHAR(64) NULL, -- base_sql hash 用于版本比较
|
||
created_by BIGINT NULL,
|
||
updated_by BIGINT NULL,
|
||
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
UNIQUE KEY uk_metric_code (metric_code),
|
||
KEY idx_domain_active (biz_domain, is_active),
|
||
KEY idx_update_strategy (update_strategy),
|
||
KEY idx_name (metric_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
|
||
CREATE TABLE metric_schedule (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
metric_id BIGINT NOT NULL, -- 关联 metric_def.id
|
||
|
||
cron_expr VARCHAR(64) NOT NULL, -- 调度表达式
|
||
enabled TINYINT(1) NOT NULL DEFAULT 1, -- 是否启用
|
||
priority INT NOT NULL DEFAULT 10, -- 优先级
|
||
|
||
backfill_allowed TINYINT(1) NOT NULL DEFAULT 1, -- 是否允许补数
|
||
max_runtime_sec INT NULL, -- 最大运行时长（秒）
|
||
retry_times INT NOT NULL DEFAULT 0, -- 失败重试次数
|
||
|
||
owner_team VARCHAR(64) NULL,
|
||
owner_user_id BIGINT NULL,
|
||
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
KEY idx_metric_enabled (metric_id, enabled),
|
||
KEY idx_owner (owner_team, owner_user_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
|
||
CREATE TABLE metric_job_run (
|
||
id BIGINT AUTO_INCREMENT,
|
||
|
||
metric_id BIGINT NOT NULL, -- metric_def.id
|
||
schedule_id BIGINT NULL, -- metric_schedule.id，手动触发则可为空
|
||
source_turn_id BIGINT NULL, -- 若本次运行由某次问答触发，关联 qa_turn.id
|
||
|
||
data_time_from DATETIME NULL, -- 指标统计时间窗口起
|
||
data_time_to DATETIME NULL, -- 指标统计时间窗口止
|
||
|
||
metric_version INT NOT NULL, -- 执行时使用的指标版本
|
||
base_sql_snapshot MEDIUMTEXT NOT NULL, -- 本次执行使用的SQL快照
|
||
|
||
status VARCHAR(32) NOT NULL, -- RUNNING/SUCCESS/FAILED/SKIPPED
|
||
error_msg TEXT NULL,
|
||
|
||
affected_rows BIGINT NULL, -- 写入行数
|
||
runtime_ms BIGINT NULL, -- 执行耗时
|
||
|
||
triggered_by VARCHAR(32) NOT NULL, -- SCHEDULER/MANUAL/API/QA_TURN
|
||
triggered_at DATETIME NOT NULL,
|
||
started_at DATETIME NULL,
|
||
finished_at DATETIME NULL,
|
||
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
-- 主键改为联合主键，必须包含 created_at
|
||
PRIMARY KEY (id, created_at),
|
||
KEY idx_metric_time (metric_id, data_time_from, data_time_to),
|
||
KEY idx_status_time (status, triggered_at),
|
||
KEY idx_schedule (schedule_id),
|
||
KEY idx_source_turn (source_turn_id)
|
||
)
|
||
ENGINE=InnoDB
|
||
DEFAULT CHARSET=utf8mb4
|
||
PARTITION BY RANGE COLUMNS(created_at) (
|
||
-- 历史数据分区（根据实际需求调整）
|
||
PARTITION p202511 VALUES LESS THAN ('2025-12-01'),
|
||
PARTITION p202512 VALUES LESS THAN ('2026-01-01'),
|
||
-- 2026年按月分区
|
||
PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
|
||
PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
|
||
PARTITION p202603 VALUES LESS THAN ('2026-04-01'),
|
||
PARTITION p202604 VALUES LESS THAN ('2026-05-01'),
|
||
PARTITION p202605 VALUES LESS THAN ('2026-06-01'),
|
||
PARTITION p202606 VALUES LESS THAN ('2026-07-01'),
|
||
-- ... 可以预建几个月 ...
|
||
|
||
-- 兜底分区，存放未来的数据，防止插入报错
|
||
PARTITION p_future VALUES LESS THAN (MAXVALUE)
|
||
);
|
||
|
||
|
||
CREATE TABLE metric_result (
|
||
id BIGINT AUTO_INCREMENT,
|
||
|
||
metric_id BIGINT NOT NULL, -- metric_def.id
|
||
metric_version INT NOT NULL, -- metric_def.version
|
||
stat_time DATETIME NOT NULL, -- 按 time_grain 对齐后的时间
|
||
|
||
extra_dims JSON NULL, -- 其他维度，JSON 存
|
||
|
||
metric_value DECIMAL(32,8) NOT NULL, -- 指标结果值
|
||
|
||
load_time DATETIME NOT NULL, -- 入库时间
|
||
data_version BIGINT NULL, -- 版本或 job_run id
|
||
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
-- 主键改为联合主键，必须包含 created_at
|
||
PRIMARY KEY (id, created_at),
|
||
KEY idx_metric_time (metric_id, stat_time),
|
||
KEY idx_load_time (load_time)
|
||
)
|
||
ENGINE=InnoDB
|
||
DEFAULT CHARSET=utf8mb4
|
||
PARTITION BY RANGE COLUMNS(created_at) (
|
||
-- 历史数据分区（根据实际需求调整）
|
||
PARTITION p202511 VALUES LESS THAN ('2025-12-01'),
|
||
PARTITION p202512 VALUES LESS THAN ('2026-01-01'),
|
||
-- 2026年按月分区
|
||
PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
|
||
PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
|
||
PARTITION p202603 VALUES LESS THAN ('2026-04-01'),
|
||
PARTITION p202604 VALUES LESS THAN ('2026-05-01'),
|
||
PARTITION p202605 VALUES LESS THAN ('2026-06-01'),
|
||
PARTITION p202606 VALUES LESS THAN ('2026-07-01'),
|
||
-- ... 可以预建几个月 ...
|
||
|
||
-- 兜底分区，存放未来的数据，防止插入报错
|
||
PARTITION p_future VALUES LESS THAN (MAXVALUE)
|
||
); |