mirror of
https://github.com/ThearchyHelios/AgentLab.git
synced 2026-11-18 20:31:59 +00:00
09949fc8ac
- 模型接入:支持跨 Anthropic / OpenAI 兼容网关显式关闭深度思考(thinking=off / effort=low),对不识别推理参数的网关自动剥离重发并做进程级记忆 - 快速直查:收敛直查图推理预算(max_steps=4),杜绝长思维链与多轮现场盲探 - 语义指标与预命中:Oracle / PostgreSQL 分组聚合补充 DESC NULLS LAST;基于关系图、逆表频共享键与低基数统计(1..20 distinct)实现零硬编码的通用关联维表发现、分类列抽样及全空分组折叠诊断 - 零延迟数字证据:支持纯数字文本编码(含前导零)无损回绑、多表同行实体键与列序号消歧,结合上下文事实白名单与冲突守卫消除误报并保留防编造拦截
2156 lines
94 KiB
Python
2156 lines
94 KiB
Python
"""独立语义指标库(SemanticMetric)校验、确定性 SQL 编译、试算与 AI 提取服务。
|
||
|
||
核心能力:
|
||
1. 即存即用指标卡片 CRUD:严格校验指标 key、物理表存在性、聚合表达式与过滤条件安全性(拒绝写语句、多语句、子查询与遮罩列引用)。
|
||
2. 确定性单条 SQL 编译器(compile_metric_sql):支持按时间区间(date_from / date_to)、按时间序列趋势(group_by_date)、
|
||
按预定义业务维度切片(group_by)及维度过滤(filters);当维度绑定了 CanonicalEntity 时,自动将业务别名确定性翻译为
|
||
目标库物理编码。
|
||
3. T+1 空结果自动探底:单日查询返回空或全 NULL 时,自动探测底表最新可用业务日期(latest_available_date),省去 Agent 多轮试探。
|
||
4. AI 辅助提取(suggest_metrics_from_source):结合数据目录(中文名、粒度、业务日期、有效过滤)与知识库文档(如《指标公式总表》),
|
||
自动生成通过表列与语法核验的候选指标卡片。
|
||
"""
|
||
from __future__ import annotations
|
||
|
||
import json
|
||
import logging
|
||
import re
|
||
import time
|
||
from collections.abc import Mapping, Sequence
|
||
from datetime import date, datetime, timedelta, timezone
|
||
from functools import lru_cache
|
||
from typing import Any
|
||
|
||
import sqlglot
|
||
from langchain_core.messages import HumanMessage, SystemMessage
|
||
from sqlglot import exp
|
||
from sqlalchemy import or_, select
|
||
from sqlalchemy.dialects import oracle as oracle_dialect
|
||
from sqlalchemy.ext.asyncio import AsyncSession
|
||
|
||
from app.engine.state import message_text
|
||
from app.data import catalog, catalog_assistant
|
||
from app.data.engine import QueryLimits, masked_columns, run_query
|
||
from app.data.entities import (
|
||
ACTIVE_ENTITY_STATUSES,
|
||
confirmed_bindings,
|
||
list_entities,
|
||
translate_entity_value,
|
||
)
|
||
from app.data.guard import SqlRejected, _ALWAYS_DENIED, _HIDDEN_WRITE, _SIDE_EFFECT_CALL, check as guard_check, first_verb
|
||
from app.data.names import name_key
|
||
from app.data.sqlcheck import dialect_of
|
||
from app.db.models import DataSource, Document
|
||
from app.db.models_metrics import SemanticMetric
|
||
|
||
logger = logging.getLogger(__name__)
|
||
|
||
METRIC_STATUSES = ("enabled", "draft", "disabled")
|
||
METRIC_SOURCES = ("human", "ai")
|
||
CALC_MODES = ("expression", "sql_template")
|
||
TIME_GRAINS = ("day", "month", "hour", "none")
|
||
ALLOWED_TEMPLATE_SLOTS = frozenset(
|
||
{
|
||
"select_dims",
|
||
"where",
|
||
"and_where",
|
||
"group_by",
|
||
"order_by",
|
||
"date_from",
|
||
"date_to",
|
||
"period_days",
|
||
"limit",
|
||
}
|
||
)
|
||
|
||
_KEY_RE = re.compile(r"^[a-z][a-z0-9_]{0,63}$")
|
||
_IDENT_SAFE_RE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$|^[\u4e00-\u9fffA-Za-z0-9_]+$")
|
||
_ORACLE_SIMPLE_IDENT_RE = re.compile(r"^[A-Za-z][A-Za-z0-9_$#]*$")
|
||
_ORACLE_PSEUDOCOLUMNS = frozenset({"rownum", "rowid", "sysdate", "systimestamp", "level"})
|
||
_ISO_DATE_RE = re.compile(r"^\d{4}-\d{2}-\d{2}$")
|
||
_FENCE_RE = re.compile(r"```(?:json)?\s*([\s\S]*?)```", re.IGNORECASE)
|
||
_SLOT_TOKEN_RE = re.compile(r"\{\{\s*([a-zA-Z0-9_]+)\s*\}\}")
|
||
_SELECT_DIMS_SLOT_RE = re.compile(r"\{\{\s*select_dims\s*\}\}\s*,?", re.IGNORECASE)
|
||
_WHERE_SLOT_RE = re.compile(r"(?P<kw>\bWHERE\s+)?\{\{\s*where\s*\}\}", re.IGNORECASE)
|
||
_AND_WHERE_SLOT_RE = re.compile(r"(?:\bAND\s+)?\{\{\s*and_where\s*\}\}", re.IGNORECASE)
|
||
_GROUP_BY_SLOT_RE = re.compile(r"(?:\bGROUP\s+BY\s+)?\{\{\s*group_by\s*\}\}", re.IGNORECASE)
|
||
_ORDER_BY_SLOT_RE = re.compile(r"(?:\bORDER\s+BY\s+)?\{\{\s*order_by\s*\}\}", re.IGNORECASE)
|
||
_DATE_FROM_SLOT_RE = re.compile(r"\{\{\s*date_from\s*\}\}", re.IGNORECASE)
|
||
_DATE_TO_SLOT_RE = re.compile(r"\{\{\s*date_to\s*\}\}", re.IGNORECASE)
|
||
_PERIOD_DAYS_SLOT_RE = re.compile(r"\{\{\s*period_days\s*\}\}", re.IGNORECASE)
|
||
_LIMIT_SLOT_RE = re.compile(r"\{\{\s*limit\s*\}\}", re.IGNORECASE)
|
||
_TRAILING_LIMIT_RE = re.compile(r"\s+\bLIMIT\s+(\d+)\s*$", re.IGNORECASE)
|
||
_HAS_ORACLE_ROW_LIMIT_RE = re.compile(
|
||
r"(?:\bFETCH\s+(?:FIRST|NEXT)\s+\d+\s+ROWS?\s+ONLY\s*$|\bROWNUM\s*<=?\s*\d+\s*$)",
|
||
re.IGNORECASE,
|
||
)
|
||
|
||
|
||
class MetricInvalid(ValueError):
|
||
"""指标定义或查询参数不合法。"""
|
||
|
||
def __init__(self, message: str, *, field: str = "") -> None:
|
||
super().__init__(message)
|
||
self.message = message
|
||
self.field = field
|
||
|
||
|
||
class MetricConflict(ValueError):
|
||
"""指标唯一键冲突或乐观锁版本冲突。"""
|
||
|
||
def __init__(self, message: str, *, field: str = "") -> None:
|
||
super().__init__(message)
|
||
self.message = message
|
||
self.field = field
|
||
|
||
|
||
class MetricNotFound(LookupError):
|
||
"""指标或关联数据源不存在。"""
|
||
|
||
|
||
def _iso(val: Any) -> str | None:
|
||
if val is None or not hasattr(val, "isoformat"):
|
||
return None
|
||
if getattr(val, "tzinfo", None) is None:
|
||
val = val.replace(tzinfo=timezone.utc)
|
||
return val.isoformat()
|
||
|
||
|
||
def normalize_metric_key(raw: Any, *, field: str = "key") -> str:
|
||
val = str(raw or "").strip().lower()
|
||
if not _KEY_RE.match(val):
|
||
raise MetricInvalid("指标标识(key)须以小写字母开头,仅含小写字母、数字和下划线(最长 64 字符)", field=field)
|
||
return val
|
||
|
||
|
||
def _clean_aliases(raw: Any, *, field: str = "aliases") -> list[str]:
|
||
if raw in (None, ""):
|
||
return []
|
||
if isinstance(raw, str):
|
||
items = [p.strip() for p in re.split(r"[,,、;\n]+", raw) if p.strip()]
|
||
elif isinstance(raw, list):
|
||
items = [str(p or "").strip() for p in raw if str(p or "").strip()]
|
||
else:
|
||
raise MetricInvalid(f"字段「{field}」需要是字符串列表", field=field)
|
||
seen: set[str] = set()
|
||
out: list[str] = []
|
||
for item in items:
|
||
low = item.lower()
|
||
if low not in seen:
|
||
seen.add(low)
|
||
out.append(item[:80])
|
||
return out[:24]
|
||
|
||
|
||
def _validate_column_identifier(
|
||
col: str,
|
||
*,
|
||
field: str,
|
||
masked_cols: set[str] | None = None,
|
||
allow_qualified: bool = False,
|
||
) -> str:
|
||
clean = str(col or "").strip()
|
||
if not clean:
|
||
raise MetricInvalid(f"字段「{field}」不能为空", field=field)
|
||
if len(clean) > 128 or any(ch in clean for ch in (";", "'", '"', "`", "\n", "\r", "\x00", "(", ")")):
|
||
raise MetricInvalid(f"列名「{clean}」包含非法字符", field=field)
|
||
if "." in clean and not allow_qualified:
|
||
raise MetricInvalid(f"单表模式下字段「{field}」请直接填写物理列名,不要带表别名前缀({clean})", field=field)
|
||
parts = [p.strip() for p in clean.split(".")]
|
||
if any(not p for p in parts) or len(parts) > 2:
|
||
raise MetricInvalid(f"列名「{clean}」格式不合法", field=field)
|
||
leaf_col = parts[-1]
|
||
if masked_cols and (name_key(clean) in masked_cols or name_key(leaf_col) in masked_cols):
|
||
raise MetricInvalid(f"列「{clean}」属于数据源敏感遮罩列,不能用于指标定义", field=field)
|
||
return clean
|
||
|
||
|
||
def normalize_dimension(
|
||
raw: Any,
|
||
*,
|
||
index: int = 0,
|
||
masked_cols: set[str] | None = None,
|
||
allow_qualified: bool = False,
|
||
) -> dict[str, Any]:
|
||
at = f"dimensions[{index}]"
|
||
if not isinstance(raw, Mapping):
|
||
raise MetricInvalid("指标维度需要是对象", field=at)
|
||
col = _validate_column_identifier(
|
||
str(raw.get("column") or ""),
|
||
field=f"{at}.column",
|
||
masked_cols=masked_cols,
|
||
allow_qualified=allow_qualified,
|
||
)
|
||
raw_key = str(raw.get("key") or "").strip().lower()
|
||
if not raw_key:
|
||
leaf = col.rsplit(".", 1)[-1]
|
||
slug = re.sub(r"[^a-z0-9_]+", "_", leaf.lower()).strip("_")
|
||
raw_key = slug if slug and slug[0].isalpha() else f"dim_{index + 1}"
|
||
if not _KEY_RE.match(raw_key):
|
||
raise MetricInvalid(f"维度标识「{raw_key}」须以小写字母开头,仅含小写字母、数字和下划线", field=f"{at}.key")
|
||
label = str(raw.get("label") or raw_key).strip() or raw_key
|
||
entity_key = str(raw.get("entity_key") or "").strip().lower()
|
||
if entity_key and not _KEY_RE.match(entity_key):
|
||
raise MetricInvalid(f"关联实体标识「{entity_key}」格式不合法", field=f"{at}.entity_key")
|
||
return {
|
||
"key": raw_key,
|
||
"label": label[:80],
|
||
"column": col,
|
||
"entity_key": entity_key,
|
||
}
|
||
|
||
|
||
def infer_aggregation_type(sql_expression: str) -> str:
|
||
"""从聚合 SQL 表达式推断展示用聚合类型标签(sum / count_distinct / count / avg / max / min / ratio / custom)。"""
|
||
expr = str(sql_expression or "").strip()
|
||
if not expr:
|
||
return "custom"
|
||
upper = expr.upper()
|
||
if "/" in upper and ("NULLIF" in upper or upper.startswith("ROUND(") or upper.count("(") >= 2):
|
||
if not upper.startswith(("SUM(", "COUNT(", "AVG(", "MAX(", "MIN(")):
|
||
return "ratio"
|
||
if re.match(r"^COUNT\s*\(\s*DISTINCT\b", upper):
|
||
return "count_distinct"
|
||
for prefix, tag in (
|
||
("SUM(", "sum"),
|
||
("COUNT(", "count"),
|
||
("AVG(", "avg"),
|
||
("MAX(", "max"),
|
||
("MIN(", "min"),
|
||
):
|
||
if upper.startswith(prefix) or re.match(rf"^{prefix[:-1]}\s*\(", upper):
|
||
return tag
|
||
if "/" in upper:
|
||
return "ratio"
|
||
return "custom"
|
||
|
||
|
||
def validate_sql_fragment(
|
||
fragment: str,
|
||
*,
|
||
dialect: str | None,
|
||
field: str,
|
||
is_filter: bool = False,
|
||
allow_empty: bool = False,
|
||
masked_cols: set[str] | None = None,
|
||
known_cols: Mapping[str, str] | set[str] | None = None,
|
||
table_name: str = "",
|
||
) -> str:
|
||
"""校验聚合表达式或过滤条件为单层安全只读表达式,不含注释、分号、写操作、子查询、不存在列或敏感遮罩列。"""
|
||
clean = str(fragment or "").strip()
|
||
if not clean:
|
||
if allow_empty:
|
||
return ""
|
||
raise MetricInvalid(f"字段「{field}」不能为空", field=field)
|
||
if len(clean) > 2000:
|
||
raise MetricInvalid(f"字段「{field}」长度不能超过 2000 字符", field=field)
|
||
if ";" in clean or "--" in clean or "/*" in clean or "*/" in clean:
|
||
raise MetricInvalid(f"字段「{field}」不能包含分号或 SQL 注释", field=field)
|
||
first_word = clean.split(None, 1)[0].lower() if clean.split() else ""
|
||
if first_word in _ALWAYS_DENIED or _HIDDEN_WRITE.search(clean) or _SIDE_EFFECT_CALL.search(clean):
|
||
raise MetricInvalid(f"字段「{field}」包含不允许的关键字或有副作用的函数", field=field)
|
||
|
||
probe_sql = f"SELECT 1 FROM _metric_tbl WHERE ({clean})" if is_filter else f"SELECT ({clean}) AS _val FROM _metric_tbl"
|
||
read_dialect = dialect or "mysql"
|
||
try:
|
||
parsed = sqlglot.parse_one(probe_sql, read=read_dialect)
|
||
except Exception as exc: # noqa: BLE001
|
||
# 回退默认方言再试一次,兼容通用 SQL 写法
|
||
try:
|
||
parsed = sqlglot.parse_one(probe_sql)
|
||
except Exception as exc2: # noqa: BLE001
|
||
raise MetricInvalid(f"字段「{field}」不是合法的 SQL 表达式({exc2})", field=field) from exc2
|
||
|
||
# 禁止嵌套 SELECT / 子查询 / UNION(保持指标底表血缘清晰且可受控加 WHERE 与 GROUP BY)
|
||
sub_selects = list(parsed.find_all(exp.Select))
|
||
if len(sub_selects) > 1 or list(parsed.find_all(exp.Union)):
|
||
raise MetricInvalid(f"字段「{field}」不允许包含嵌套 SELECT 子查询,请使用聚合表达式(如 SUM(CASE WHEN cond THEN val END))", field=field)
|
||
|
||
for col_node in parsed.find_all(exp.Column):
|
||
cname = str(col_node.name or "").strip()
|
||
if not cname or cname == "*":
|
||
continue
|
||
if read_dialect == "oracle" and cname.lower() in _ORACLE_PSEUDOCOLUMNS:
|
||
continue
|
||
ckey = name_key(cname)
|
||
if masked_cols and ckey in masked_cols:
|
||
raise MetricInvalid(f"字段「{field}」引用了数据源敏感遮罩列「{cname}」,不允许用于指标定义", field=field)
|
||
if known_cols and ckey not in known_cols:
|
||
tbl_desc = f"表「{table_name}」" if table_name else "目标物理表"
|
||
raise MetricInvalid(f"字段「{field}」引用了{tbl_desc}中不存在的列「{cname}」", field=field)
|
||
|
||
return clean
|
||
|
||
|
||
def metric_to_dict(
|
||
row: SemanticMetric,
|
||
*,
|
||
source_name: str = "",
|
||
restricted: bool = False,
|
||
) -> dict[str, Any]:
|
||
dims = list(row.dimensions or [])
|
||
if restricted:
|
||
dims = [
|
||
{"key": d.get("key"), "label": d.get("label"), "entity_key": d.get("entity_key") or ""}
|
||
for d in dims
|
||
if isinstance(d, Mapping)
|
||
]
|
||
calc_mode = str(getattr(row, "calc_mode", None) or "expression").strip().lower() or "expression"
|
||
sql_tpl = str(getattr(row, "sql_template", None) or "").strip()
|
||
base: dict[str, Any] = {
|
||
"id": row.id,
|
||
"key": row.key,
|
||
"name": row.name,
|
||
"aliases": list(row.aliases or []),
|
||
"description": row.description or "",
|
||
"unit": row.unit or "",
|
||
"source_id": row.source_id,
|
||
"source_name": source_name,
|
||
"calc_mode": calc_mode,
|
||
"aggregation_type": "template" if calc_mode == "sql_template" else infer_aggregation_type(row.sql_expression),
|
||
"dimensions": dims,
|
||
"default_time_grain": row.default_time_grain or "day",
|
||
"has_date_column": bool(str(row.date_column or "").strip()),
|
||
"status": row.status or "enabled",
|
||
"source": row.source or "human",
|
||
"version": int(row.version or 1),
|
||
"created_by": row.created_by,
|
||
"updated_by": row.updated_by,
|
||
"created_at": _iso(row.created_at),
|
||
"updated_at": _iso(row.updated_at),
|
||
}
|
||
if not restricted:
|
||
base.update(
|
||
table_name=row.table_name,
|
||
sql_expression=row.sql_expression,
|
||
sql_template=sql_tpl,
|
||
date_column=row.date_column or "",
|
||
filter_sql=row.filter_sql or "",
|
||
)
|
||
return base
|
||
|
||
|
||
async def _resolve_source(session: AsyncSession, source_id_or_name: str) -> DataSource:
|
||
clean = str(source_id_or_name or "").strip()
|
||
if not clean:
|
||
raise MetricInvalid("请选择指标归属的数据源(source_id)", field="source_id")
|
||
ds = await session.get(DataSource, clean)
|
||
if ds is None:
|
||
ds = (await session.execute(select(DataSource).where(DataSource.name == clean))).scalar_one_or_none()
|
||
if ds is None:
|
||
raise MetricNotFound(f"找不到数据源「{clean}」")
|
||
return ds
|
||
|
||
|
||
def _resolve_table_and_columns(ds: DataSource, raw_table: str) -> tuple[str, dict[str, str]]:
|
||
"""返回对齐后的表名与该表的 {name_key(col): col_type} 字典(未探查时仅做基本标识符校验)。"""
|
||
clean_table = str(raw_table or "").strip()
|
||
if not clean_table:
|
||
raise MetricInvalid("请填写物理表名(table_name)", field="table_name")
|
||
if any(ch in clean_table for ch in (";", "'", '"', "`", "\n", "\r", "(", ")")):
|
||
raise MetricInvalid(f"表名「{clean_table}」包含非法字符", field="table_name")
|
||
cache = ds.schema_cache if isinstance(ds.schema_cache, Mapping) else {}
|
||
tables = cache.get("tables") if isinstance(cache, Mapping) else None
|
||
if isinstance(tables, Mapping) and tables:
|
||
resolved = catalog.resolve_table_name(cache, clean_table)
|
||
if resolved is None:
|
||
raise MetricInvalid(f"数据源「{ds.name}」的已探查表结构中不存在表「{clean_table}」", field="table_name")
|
||
tinfo = tables.get(resolved) or {}
|
||
cols: dict[str, str] = {}
|
||
for c in tinfo.get("columns") or []:
|
||
if isinstance(c, Mapping) and c.get("name"):
|
||
cols[name_key(str(c["name"]))] = str(c.get("type") or "").lower()
|
||
return resolved, cols
|
||
return clean_table, {}
|
||
|
||
|
||
def _validate_and_analyze_sql_template(
|
||
raw_template: str,
|
||
*,
|
||
ds: DataSource,
|
||
dialect: str | None,
|
||
masked_set: set[str],
|
||
metric_key: str,
|
||
raw_table: str,
|
||
sql_expression: str,
|
||
date_column: str,
|
||
filter_sql: str,
|
||
dims: list[dict[str, Any]],
|
||
grain: str,
|
||
) -> tuple[str, str, str]:
|
||
"""校验多表 / 参数化 SQL 模板(支持 {{select_dims}}、{{and_where}}、{{period_days}} 等安全槽位),
|
||
返回 (clean_template, primary_table_name, effective_sql_expression)。
|
||
"""
|
||
clean_tpl = str(raw_template or "").strip().rstrip(";").strip()
|
||
if not clean_tpl:
|
||
raise MetricInvalid("多表或参数化模板模式下,请填写「SQL 模板」内容", field="sql_template")
|
||
if len(clean_tpl) > 8000:
|
||
raise MetricInvalid("「SQL 模板」长度不能超过 8000 字符", field="sql_template")
|
||
if ";" in clean_tpl or "--" in clean_tpl or "/*" in clean_tpl or "*/" in clean_tpl:
|
||
raise MetricInvalid("「SQL 模板」不能包含多语句分号或 SQL 注释", field="sql_template")
|
||
if _HIDDEN_WRITE.search(clean_tpl) or _SIDE_EFFECT_CALL.search(clean_tpl):
|
||
raise MetricInvalid("「SQL 模板」包含不允许的写操作或有副作用的函数", field="sql_template")
|
||
|
||
slots_found = [m.group(1).strip().lower() for m in _SLOT_TOKEN_RE.finditer(clean_tpl)]
|
||
unknown_slots = sorted({s for s in slots_found if s not in ALLOWED_TEMPLATE_SLOTS})
|
||
if unknown_slots:
|
||
allowed_txt = ", ".join(f"{{{{{s}}}}}" for s in sorted(ALLOWED_TEMPLATE_SLOTS))
|
||
raise MetricInvalid(
|
||
f"「SQL 模板」使用了不支持的占位符:{', '.join(unknown_slots)};仅支持:{allowed_txt}",
|
||
field="sql_template",
|
||
)
|
||
|
||
cache = ds.schema_cache if isinstance(ds.schema_cache, Mapping) else {}
|
||
tables = cache.get("tables") if isinstance(cache, Mapping) else None
|
||
has_schema = isinstance(tables, Mapping) and bool(tables)
|
||
|
||
# 先预编译两种典型形态(无筛选汇总 + 带日期/维度切片),双重验证语法与只读安全守卫
|
||
draft_metric = {
|
||
"key": metric_key,
|
||
"name": metric_key,
|
||
"calc_mode": "sql_template",
|
||
"table_name": raw_table or "_anchor",
|
||
"sql_expression": sql_expression,
|
||
"sql_template": clean_tpl,
|
||
"date_column": date_column,
|
||
"filter_sql": filter_sql,
|
||
"dimensions": dims,
|
||
"default_time_grain": grain,
|
||
}
|
||
test_variants: list[dict[str, Any]] = [
|
||
{"date_from": None, "date_to": None, "group_by_date": False, "group_by": []},
|
||
]
|
||
if date_column or dims:
|
||
test_variants.append(
|
||
{
|
||
"date_from": "2026-01-01" if date_column else None,
|
||
"date_to": "2026-01-07" if date_column else None,
|
||
"group_by_date": bool(date_column),
|
||
"group_by": [dims[0]["key"]] if dims else [],
|
||
}
|
||
)
|
||
|
||
referenced_physical_tables: list[str] = []
|
||
all_valid_cols: set[str] = set()
|
||
read_dialect = dialect or "mysql"
|
||
|
||
for v_idx, variant in enumerate(test_variants):
|
||
compiled_sql, _ = compile_metric_sql(
|
||
draft_metric,
|
||
source=ds,
|
||
entities=(),
|
||
date_from=variant["date_from"],
|
||
date_to=variant["date_to"],
|
||
group_by_date=variant["group_by_date"],
|
||
group_by=variant["group_by"],
|
||
limit=50,
|
||
)
|
||
try:
|
||
checked_stmt = guard_check(compiled_sql, readonly=True, dialect=dialect, source_name=ds.name)
|
||
except SqlRejected as exc:
|
||
raise MetricInvalid(f"「SQL 模板」未通过只读安全守卫:{exc}", field="sql_template") from exc
|
||
verb = first_verb(checked_stmt, dialect=dialect)
|
||
if verb not in ("select", "with"):
|
||
raise MetricInvalid("「SQL 模板」必须是只读的 SELECT 或带 WITH 子句的查询语句", field="sql_template")
|
||
|
||
try:
|
||
parsed = sqlglot.parse_one(checked_stmt, read=read_dialect)
|
||
except Exception: # noqa: BLE001
|
||
try:
|
||
parsed = sqlglot.parse_one(checked_stmt)
|
||
except Exception as exc2: # noqa: BLE001
|
||
mode_label = "汇总态" if v_idx == 0 else "按日期或维度分组态"
|
||
raise MetricInvalid(f"「SQL 模板」在{mode_label}下编译出的 SQL 语法有误:{exc2}", field="sql_template") from exc2
|
||
|
||
cte_names = {name_key(str(cte.alias_or_name or "")) for cte in parsed.find_all(exp.CTE) if cte.alias_or_name}
|
||
cte_names.add(name_key("_metric_src"))
|
||
cte_names.add(name_key("metric_src"))
|
||
if read_dialect == "oracle":
|
||
cte_names.add(name_key("dual"))
|
||
|
||
if v_idx == 0:
|
||
for tbl_node in parsed.find_all(exp.Table):
|
||
t_raw = str(tbl_node.name or "").strip()
|
||
if not t_raw or name_key(t_raw) in cte_names:
|
||
continue
|
||
db_part = str(tbl_node.db or "").strip()
|
||
qualified = f"{db_part}.{t_raw}" if db_part else t_raw
|
||
if has_schema:
|
||
resolved = catalog.resolve_table_name(cache, qualified) or catalog.resolve_table_name(cache, t_raw)
|
||
if resolved is None:
|
||
raise MetricInvalid(
|
||
f"「SQL 模板」引用了数据源「{ds.name}」中不存在的物理表「{qualified}」",
|
||
field="sql_template",
|
||
)
|
||
if resolved not in referenced_physical_tables:
|
||
referenced_physical_tables.append(resolved)
|
||
tinfo = (tables or {}).get(resolved) or {}
|
||
for c in tinfo.get("columns") or []:
|
||
if isinstance(c, Mapping) and c.get("name"):
|
||
all_valid_cols.add(name_key(str(c["name"])))
|
||
else:
|
||
if qualified not in referenced_physical_tables:
|
||
referenced_physical_tables.append(qualified)
|
||
|
||
derived_aliases = {
|
||
name_key(str(al.alias or ""))
|
||
for al in parsed.find_all(exp.Alias)
|
||
if str(al.alias or "").strip()
|
||
}
|
||
derived_aliases.update(name_key(str(d.get("key") or "")) for d in dims if d.get("key"))
|
||
for col_node in parsed.find_all(exp.Column):
|
||
cname = str(col_node.name or "").strip()
|
||
if not cname or cname == "*":
|
||
continue
|
||
if read_dialect == "oracle" and cname.lower() in _ORACLE_PSEUDOCOLUMNS:
|
||
continue
|
||
ckey = name_key(cname)
|
||
if masked_set and ckey in masked_set:
|
||
raise MetricInvalid(
|
||
f"「SQL 模板」引用了数据源敏感遮罩列「{cname}」,不允许用于指标定义",
|
||
field="sql_template",
|
||
)
|
||
if has_schema and all_valid_cols and ckey not in all_valid_cols and ckey not in derived_aliases:
|
||
if cname.lower() not in ("period", "stat_date", metric_key.lower()):
|
||
raise MetricInvalid(
|
||
f"「SQL 模板」引用了所涉物理表中不存在的列「{cname}」",
|
||
field="sql_template",
|
||
)
|
||
|
||
if raw_table.strip():
|
||
primary_table, _ = _resolve_table_and_columns(ds, raw_table)
|
||
elif referenced_physical_tables:
|
||
primary_table = referenced_physical_tables[0]
|
||
else:
|
||
raise MetricInvalid("「SQL 模板」至少需要引用一张物理业务表", field="sql_template")
|
||
|
||
effective_expr = sql_expression.strip()
|
||
if not effective_expr:
|
||
joined_hint = " + ".join(referenced_physical_tables[:3])
|
||
if len(referenced_physical_tables) > 3:
|
||
joined_hint += f" 等 {len(referenced_physical_tables)} 表"
|
||
effective_expr = f"[多表模板] {joined_hint or primary_table}"
|
||
return clean_tpl, primary_table, effective_expr
|
||
|
||
|
||
async def _normalize_metric_payload(
|
||
session: AsyncSession,
|
||
payload: Mapping[str, Any],
|
||
*,
|
||
existing: SemanticMetric | None = None,
|
||
) -> tuple[DataSource, dict[str, Any]]:
|
||
source_ref = payload.get("source_id") if "source_id" in payload else (existing.source_id if existing else "")
|
||
ds = await _resolve_source(session, str(source_ref or ""))
|
||
masked_set = {name_key(c) for c in masked_columns(ds.options)}
|
||
dialect = dialect_of(ds.kind)
|
||
|
||
key = normalize_metric_key(
|
||
payload.get("key") if "key" in payload else (existing.key if existing else ""),
|
||
field="key",
|
||
)
|
||
name = str(payload.get("name") if "name" in payload else (existing.name if existing else "")).strip()
|
||
if not name:
|
||
raise MetricInvalid("指标名称(name)不能为空", field="name")
|
||
if len(name) > 200:
|
||
raise MetricInvalid("指标名称不能超过 200 字符", field="name")
|
||
|
||
raw_tpl = (
|
||
payload.get("sql_template")
|
||
if "sql_template" in payload
|
||
else (getattr(existing, "sql_template", "") if existing else "")
|
||
)
|
||
raw_mode = (
|
||
payload.get("calc_mode")
|
||
if "calc_mode" in payload
|
||
else (getattr(existing, "calc_mode", "expression") if existing else "")
|
||
)
|
||
calc_mode = str(raw_mode or "").strip().lower()
|
||
if not calc_mode:
|
||
calc_mode = "sql_template" if str(raw_tpl or "").strip() else "expression"
|
||
if calc_mode not in CALC_MODES:
|
||
raise MetricInvalid(f"计算模式「{calc_mode}」不支持,可选:{', '.join(CALC_MODES)}", field="calc_mode")
|
||
|
||
raw_table = str(
|
||
payload.get("table_name") if "table_name" in payload else (existing.table_name if existing else "")
|
||
).strip()
|
||
raw_expr = str(
|
||
payload.get("sql_expression") if "sql_expression" in payload else (existing.sql_expression if existing else "")
|
||
).strip()
|
||
raw_date = str(
|
||
payload.get("date_column") if "date_column" in payload else (existing.date_column if existing else "")
|
||
).strip()
|
||
raw_filter = str(
|
||
payload.get("filter_sql") if "filter_sql" in payload else (existing.filter_sql if existing else "")
|
||
).strip()
|
||
|
||
grain = str(
|
||
payload.get("default_time_grain")
|
||
if "default_time_grain" in payload
|
||
else (existing.default_time_grain if existing else "day")
|
||
).strip().lower() or "day"
|
||
if grain not in TIME_GRAINS:
|
||
raise MetricInvalid(f"默认时间粒度「{grain}」不支持,可选:{', '.join(TIME_GRAINS)}", field="default_time_grain")
|
||
|
||
raw_dims = payload.get("dimensions") if "dimensions" in payload else (existing.dimensions if existing else [])
|
||
if raw_dims is None:
|
||
raw_dims = []
|
||
if not isinstance(raw_dims, list):
|
||
raise MetricInvalid("维度列表(dimensions)需要是数组", field="dimensions")
|
||
|
||
is_tpl = calc_mode == "sql_template"
|
||
dims: list[dict[str, Any]] = []
|
||
seen_dim_keys: set[str] = set()
|
||
for idx, item in enumerate(raw_dims):
|
||
norm = normalize_dimension(item, index=idx, masked_cols=masked_set, allow_qualified=is_tpl)
|
||
if norm["key"] in seen_dim_keys:
|
||
raise MetricInvalid(f"维度标识「{norm['key']}」重复", field=f"dimensions[{idx}].key")
|
||
seen_dim_keys.add(norm["key"])
|
||
dims.append(norm)
|
||
|
||
if not is_tpl:
|
||
table_name, known_cols = _resolve_table_and_columns(ds, raw_table)
|
||
sql_expression = validate_sql_fragment(
|
||
raw_expr,
|
||
dialect=dialect,
|
||
field="sql_expression",
|
||
is_filter=False,
|
||
allow_empty=False,
|
||
masked_cols=masked_set,
|
||
known_cols=known_cols,
|
||
table_name=table_name,
|
||
)
|
||
date_column = ""
|
||
if raw_date:
|
||
date_column = _validate_column_identifier(raw_date, field="date_column", masked_cols=masked_set)
|
||
if known_cols and name_key(date_column) not in known_cols:
|
||
raise MetricInvalid(f"表「{table_name}」中不存在时间列「{date_column}」", field="date_column")
|
||
filter_sql = validate_sql_fragment(
|
||
raw_filter,
|
||
dialect=dialect,
|
||
field="filter_sql",
|
||
is_filter=True,
|
||
allow_empty=True,
|
||
masked_cols=masked_set,
|
||
known_cols=known_cols,
|
||
table_name=table_name,
|
||
)
|
||
for idx, norm in enumerate(dims):
|
||
if known_cols and name_key(norm["column"]) not in known_cols:
|
||
raise MetricInvalid(f"表「{table_name}」中不存在维度列「{norm['column']}」", field=f"dimensions[{idx}].column")
|
||
sql_template = ""
|
||
else:
|
||
date_column = ""
|
||
if raw_date:
|
||
date_column = _validate_column_identifier(
|
||
raw_date, field="date_column", masked_cols=masked_set, allow_qualified=True
|
||
)
|
||
filter_sql = validate_sql_fragment(
|
||
raw_filter,
|
||
dialect=dialect,
|
||
field="filter_sql",
|
||
is_filter=True,
|
||
allow_empty=True,
|
||
masked_cols=masked_set,
|
||
known_cols=None,
|
||
table_name=raw_table,
|
||
)
|
||
# 若用户在无占位符的基础多表查询之上另填了外层聚合表达式,也校验其聚合安全性(跳过以 [多表模板] 开头的自动摘要)
|
||
clean_outer_expr = ""
|
||
if raw_expr and not raw_expr.startswith("[多表模板]") and not _SLOT_TOKEN_RE.search(str(raw_tpl or "")):
|
||
clean_outer_expr = validate_sql_fragment(
|
||
raw_expr,
|
||
dialect=dialect,
|
||
field="sql_expression",
|
||
is_filter=False,
|
||
allow_empty=True,
|
||
masked_cols=masked_set,
|
||
known_cols=None,
|
||
table_name=raw_table,
|
||
)
|
||
sql_template, table_name, sql_expression = _validate_and_analyze_sql_template(
|
||
str(raw_tpl or ""),
|
||
ds=ds,
|
||
dialect=dialect,
|
||
masked_set=masked_set,
|
||
metric_key=key,
|
||
raw_table=raw_table,
|
||
sql_expression=clean_outer_expr,
|
||
date_column=date_column,
|
||
filter_sql=filter_sql,
|
||
dims=dims,
|
||
grain=grain,
|
||
)
|
||
|
||
aliases = _clean_aliases(
|
||
payload.get("aliases") if "aliases" in payload else (existing.aliases if existing else []),
|
||
field="aliases",
|
||
)
|
||
description = str(
|
||
payload.get("description") if "description" in payload else (existing.description if existing else "")
|
||
).strip()[:2000]
|
||
unit = str(payload.get("unit") if "unit" in payload else (existing.unit if existing else "")).strip()[:40]
|
||
|
||
status = str(
|
||
payload.get("status") if "status" in payload else (existing.status if existing else "enabled")
|
||
).strip().lower() or "enabled"
|
||
if status not in METRIC_STATUSES:
|
||
raise MetricInvalid(f"指标状态「{status}」不支持,可选:{', '.join(METRIC_STATUSES)}", field="status")
|
||
|
||
source_origin = str(
|
||
payload.get("source") if "source" in payload else (existing.source if existing else "human")
|
||
).strip().lower() or "human"
|
||
if source_origin not in METRIC_SOURCES:
|
||
source_origin = "human"
|
||
|
||
return ds, {
|
||
"key": key,
|
||
"name": name,
|
||
"aliases": aliases,
|
||
"description": description,
|
||
"unit": unit,
|
||
"source_id": ds.id,
|
||
"calc_mode": calc_mode,
|
||
"table_name": table_name,
|
||
"sql_expression": sql_expression,
|
||
"sql_template": sql_template,
|
||
"date_column": date_column,
|
||
"filter_sql": filter_sql,
|
||
"dimensions": dims,
|
||
"default_time_grain": grain,
|
||
"status": status,
|
||
"source": source_origin,
|
||
}
|
||
|
||
|
||
async def list_metrics(
|
||
session: AsyncSession,
|
||
*,
|
||
source_id: str | None = None,
|
||
status: str | None = None,
|
||
q: str | None = None,
|
||
) -> list[SemanticMetric]:
|
||
stmt = select(SemanticMetric).order_by(SemanticMetric.updated_at.desc(), SemanticMetric.key.asc())
|
||
if source_id:
|
||
stmt = stmt.where(SemanticMetric.source_id == source_id.strip())
|
||
if status:
|
||
stmt = stmt.where(SemanticMetric.status == status.strip())
|
||
rows = list((await session.execute(stmt)).scalars().all())
|
||
if q and q.strip():
|
||
needle = q.strip().lower()
|
||
rows = [
|
||
r
|
||
for r in rows
|
||
if needle in (r.key or "").lower()
|
||
or needle in (r.name or "").lower()
|
||
or needle in (r.description or "").lower()
|
||
or needle in (r.table_name or "").lower()
|
||
or any(needle in str(a).lower() for a in (r.aliases or []))
|
||
]
|
||
return rows
|
||
|
||
|
||
async def get_metric(session: AsyncSession, id_or_key: str) -> SemanticMetric:
|
||
clean = str(id_or_key or "").strip()
|
||
if not clean:
|
||
raise MetricNotFound("未指定指标标识")
|
||
row = await session.get(SemanticMetric, clean)
|
||
if row is None:
|
||
row = (
|
||
await session.execute(
|
||
select(SemanticMetric).where(or_(SemanticMetric.key == clean.lower(), SemanticMetric.name == clean))
|
||
)
|
||
).scalars().first()
|
||
if row is None:
|
||
raise MetricNotFound(f"找不到语义指标「{clean}」")
|
||
return row
|
||
|
||
|
||
async def create_metric(
|
||
session: AsyncSession,
|
||
payload: Mapping[str, Any],
|
||
*,
|
||
actor: str | None = None,
|
||
) -> tuple[SemanticMetric, DataSource]:
|
||
ds, fields = await _normalize_metric_payload(session, payload, existing=None)
|
||
dup = (await session.execute(select(SemanticMetric).where(SemanticMetric.key == fields["key"]))).scalar_one_or_none()
|
||
if dup is not None:
|
||
raise MetricConflict(f"指标标识「{fields['key']}」已存在,请换用其他标识或直接编辑现有指标", field="key")
|
||
row = SemanticMetric(
|
||
**fields,
|
||
version=1,
|
||
created_by=actor,
|
||
updated_by=actor,
|
||
)
|
||
session.add(row)
|
||
await session.flush()
|
||
return row, ds
|
||
|
||
|
||
async def update_metric(
|
||
session: AsyncSession,
|
||
metric_id: str,
|
||
payload: Mapping[str, Any],
|
||
*,
|
||
actor: str | None = None,
|
||
) -> tuple[SemanticMetric, DataSource]:
|
||
row = await get_metric(session, metric_id)
|
||
expected_version = payload.get("expected_version")
|
||
if expected_version is not None and int(expected_version) != int(row.version or 1):
|
||
raise MetricConflict("该指标已被其他人修改,请刷新后重试", field="version")
|
||
ds, fields = await _normalize_metric_payload(session, payload, existing=row)
|
||
if fields["key"] != row.key:
|
||
dup = (
|
||
await session.execute(select(SemanticMetric).where(SemanticMetric.key == fields["key"], SemanticMetric.id != row.id))
|
||
).scalar_one_or_none()
|
||
if dup is not None:
|
||
raise MetricConflict(f"指标标识「{fields['key']}」已被其他指标占用", field="key")
|
||
for k, v in fields.items():
|
||
setattr(row, k, v)
|
||
row.version = int(row.version or 1) + 1
|
||
row.updated_by = actor
|
||
await session.flush()
|
||
return row, ds
|
||
|
||
|
||
async def delete_metric(session: AsyncSession, metric_id: str) -> SemanticMetric:
|
||
row = await get_metric(session, metric_id)
|
||
await session.delete(row)
|
||
await session.flush()
|
||
return row
|
||
|
||
|
||
# --------------------------------------------------------------------------
|
||
# 确定性 SQL 编译与业务实体编码翻译
|
||
# --------------------------------------------------------------------------
|
||
|
||
|
||
@lru_cache(maxsize=1)
|
||
def _oracle_reserved_words() -> frozenset[str]:
|
||
try:
|
||
return frozenset(str(w).lower() for w in oracle_dialect.dialect().identifier_preparer._reserved_words)
|
||
except Exception: # noqa: BLE001
|
||
return frozenset({"date", "level", "number", "uid", "user", "comment", "group", "order", "size", "mode", "share"})
|
||
|
||
|
||
def _quote_single_part(part: str, kind: str | None, *, force_quote: bool = False, is_alias: bool = False) -> str:
|
||
clean = str(part or "").strip()
|
||
if not clean:
|
||
return ""
|
||
k = (kind or "").strip().lower()
|
||
if k == "oracle":
|
||
if force_quote:
|
||
return '"' + clean.replace('"', '""') + '"'
|
||
if _ORACLE_SIMPLE_IDENT_RE.match(clean):
|
||
if clean.lower() in _oracle_reserved_words():
|
||
val = clean if is_alias else clean.upper()
|
||
return '"' + val.replace('"', '""') + '"'
|
||
return clean
|
||
return '"' + clean.replace('"', '""') + '"'
|
||
q = "`" if k in ("mysql", "mariadb") else '"'
|
||
return f"{q}{clean.replace(q, q + q)}{q}"
|
||
|
||
|
||
def _quote_ident(ident: str, kind: str | None, *, is_alias: bool = False) -> str:
|
||
parts = [p.strip() for p in str(ident or "").split(".") if p.strip()]
|
||
return ".".join(_quote_single_part(p, kind, is_alias=is_alias and idx == len(parts) - 1) for idx, p in enumerate(parts))
|
||
|
||
|
||
def _format_table_ref(source: DataSource, table_name: str) -> str:
|
||
clean = str(table_name or "").strip()
|
||
if not clean:
|
||
return ""
|
||
cache = source.schema_cache if isinstance(source.schema_cache, Mapping) else {}
|
||
tables = cache.get("tables") if isinstance(cache, Mapping) else None
|
||
if isinstance(tables, Mapping) and tables:
|
||
resolved = catalog.resolve_table_name(cache, clean)
|
||
if resolved and resolved in tables:
|
||
meta = tables.get(resolved) or {}
|
||
force_t = bool(meta.get("quote_name") is True)
|
||
if "." in resolved:
|
||
schema_part, tbl_part = resolved.split(".", 1)
|
||
else:
|
||
schema_part = str(meta.get("schema") or "").strip()
|
||
tbl_part = resolved
|
||
tbl_sql = _quote_single_part(tbl_part, source.kind, force_quote=force_t)
|
||
if schema_part:
|
||
return f"{_quote_single_part(schema_part, source.kind)}.{tbl_sql}"
|
||
return tbl_sql
|
||
return _quote_ident(clean, source.kind)
|
||
|
||
|
||
def _lookup_column_meta(source: DataSource, table_name: str, column_name: str) -> Mapping[str, Any] | None:
|
||
cache = source.schema_cache if isinstance(source.schema_cache, Mapping) else {}
|
||
tables = cache.get("tables") if isinstance(cache, Mapping) else None
|
||
if not isinstance(tables, Mapping) or not tables:
|
||
return None
|
||
target = name_key(str(column_name or "").rsplit(".", 1)[-1])
|
||
if not target:
|
||
return None
|
||
if table_name:
|
||
resolved = catalog.resolve_table_name(cache, table_name) or table_name
|
||
tinfo = tables.get(resolved) or {}
|
||
for col in tinfo.get("columns") or []:
|
||
if isinstance(col, Mapping) and name_key(str(col.get("name") or "")) == target:
|
||
return col
|
||
for tinfo in tables.values():
|
||
if not isinstance(tinfo, Mapping):
|
||
continue
|
||
for col in tinfo.get("columns") or []:
|
||
if isinstance(col, Mapping) and name_key(str(col.get("name") or "")) == target:
|
||
return col
|
||
return None
|
||
|
||
|
||
def _format_col_ref(source: DataSource, table_name: str, col_expr: str) -> str:
|
||
parts = [p.strip() for p in str(col_expr or "").split(".") if p.strip()]
|
||
if not parts:
|
||
return ""
|
||
col_meta = _lookup_column_meta(source, table_name, parts[-1])
|
||
force_col = bool(col_meta and col_meta.get("quote_name") is True)
|
||
col_name = str(col_meta["name"]) if (force_col and col_meta and col_meta.get("name")) else parts[-1]
|
||
if len(parts) == 1:
|
||
return _quote_single_part(col_name, source.kind, force_quote=force_col)
|
||
prefix = ".".join(_quote_single_part(p, source.kind) for p in parts[:-1])
|
||
return f"{prefix}.{_quote_single_part(col_name, source.kind, force_quote=force_col)}"
|
||
|
||
|
||
def _sql_literal(val: Any) -> str:
|
||
if isinstance(val, bool):
|
||
return "1" if val else "0"
|
||
if isinstance(val, (int, float)):
|
||
return str(val)
|
||
text = str(val if val is not None else "")
|
||
escaped = text.replace("'", "''")
|
||
return f"'{escaped}'"
|
||
|
||
|
||
def _column_temporal_kind(source: DataSource, table_name: str, column_name: str) -> str:
|
||
"""识别业务时间列的底层存储形态:text | datetime_text | date | datetime。
|
||
注意:Oracle 的 DATE 类型自带时分秒(YYYY-MM-DD HH24:MI:SS),必须按 datetime 处理。
|
||
"""
|
||
k = (source.kind or "").strip().lower()
|
||
col_meta = _lookup_column_meta(source, table_name, column_name)
|
||
ctype = str((col_meta or {}).get("type") or "").lower()
|
||
if k == "sqlite":
|
||
if "timestamp" in ctype or "datetime" in ctype:
|
||
return "datetime_text"
|
||
return "text"
|
||
if ctype:
|
||
if any(tok in ctype for tok in ("char", "text", "clob", "string")):
|
||
return "text"
|
||
if k == "oracle":
|
||
if "date" in ctype or "timestamp" in ctype:
|
||
return "datetime"
|
||
else:
|
||
if "timestamp" in ctype or "datetime" in ctype:
|
||
return "datetime"
|
||
if "date" in ctype:
|
||
return "date"
|
||
if k == "oracle":
|
||
return "datetime"
|
||
if k in ("postgres", "postgresql", "mysql", "mariadb"):
|
||
return "date"
|
||
return "text"
|
||
|
||
|
||
def _date_expr_for_grain(quoted_col: str, grain: str, kind: str | None, *, temporal_kind: str = "") -> str:
|
||
k = (kind or "").strip().lower()
|
||
if k == "oracle":
|
||
if temporal_kind == "text":
|
||
if grain == "month":
|
||
return f"SUBSTR({quoted_col}, 1, 7)"
|
||
if grain == "hour":
|
||
return f"SUBSTR({quoted_col}, 1, 13) || ':00'"
|
||
return f"SUBSTR({quoted_col}, 1, 10)"
|
||
if grain == "month":
|
||
return f"TO_CHAR({quoted_col}, 'YYYY-MM')"
|
||
if grain == "hour":
|
||
return f"TO_CHAR({quoted_col}, 'YYYY-MM-DD HH24') || ':00'"
|
||
return f"TO_CHAR({quoted_col}, 'YYYY-MM-DD')"
|
||
if k in ("postgres", "postgresql"):
|
||
if temporal_kind == "text":
|
||
if grain == "month":
|
||
return f"SUBSTR({quoted_col}, 1, 7)"
|
||
if grain == "hour":
|
||
return f"SUBSTR({quoted_col}, 1, 13) || ':00'"
|
||
return f"SUBSTR({quoted_col}, 1, 10)"
|
||
if grain == "month":
|
||
return f"TO_CHAR({quoted_col}, 'YYYY-MM')"
|
||
if grain == "hour":
|
||
return f"TO_CHAR({quoted_col}, 'YYYY-MM-DD HH24') || ':00'"
|
||
return f"CAST({quoted_col} AS DATE)"
|
||
if k in ("mysql", "mariadb"):
|
||
if grain == "month":
|
||
return f"DATE_FORMAT({quoted_col}, '%Y-%m')"
|
||
if grain == "hour":
|
||
return f"DATE_FORMAT({quoted_col}, '%Y-%m-%d %H:00')"
|
||
return f"DATE({quoted_col})"
|
||
if grain == "month":
|
||
return f"SUBSTR(CAST({quoted_col} AS TEXT), 1, 7)"
|
||
if grain == "hour":
|
||
return f"SUBSTR(CAST({quoted_col} AS TEXT), 1, 13) || ':00'"
|
||
return f"SUBSTR(CAST({quoted_col} AS TEXT), 1, 10)"
|
||
|
||
|
||
def _format_date_bound_sql(bound: str, *, kind: str | None, temporal_kind: str) -> str:
|
||
k = (kind or "").strip().lower()
|
||
if temporal_kind in ("text", "datetime_text") or k == "sqlite":
|
||
return _sql_literal(bound)
|
||
if k == "oracle":
|
||
if _ISO_DATE_RE.match(bound):
|
||
return f"DATE '{bound}'"
|
||
norm_ts = bound.replace("T", " ")
|
||
if len(norm_ts) == 16:
|
||
norm_ts += ":00"
|
||
return f"TO_DATE({_sql_literal(norm_ts)}, 'YYYY-MM-DD HH24:MI:SS')"
|
||
return _sql_literal(bound)
|
||
|
||
|
||
def _validate_date_bound(raw: str, *, field: str) -> str:
|
||
clean = str(raw or "").strip()
|
||
if not clean:
|
||
return ""
|
||
if not re.match(r"^\d{4}-\d{2}-\d{2}(?:[ T]\d{2}:\d{2}(?::\d{2})?)?$", clean):
|
||
raise MetricInvalid(f"日期参数「{field}={clean}」格式无效,请使用 YYYY-MM-DD", field=field)
|
||
return clean
|
||
|
||
|
||
def _compute_period_days_sql(
|
||
*,
|
||
source: DataSource,
|
||
table_name: str,
|
||
d_from: str,
|
||
d_to: str,
|
||
group_by_date: bool,
|
||
grain: str,
|
||
date_col: str,
|
||
kind: str | None,
|
||
) -> str:
|
||
if group_by_date and grain == "day":
|
||
return "1"
|
||
if not group_by_date and d_from and d_to:
|
||
try:
|
||
d1 = date.fromisoformat(d_from[:10])
|
||
d2 = date.fromisoformat(d_to[:10])
|
||
return str(max(1, (d2 - d1).days + 1))
|
||
except ValueError:
|
||
pass
|
||
if date_col:
|
||
t_kind = _column_temporal_kind(source, table_name, date_col)
|
||
q_col = _format_col_ref(source, table_name, date_col)
|
||
day_expr = _date_expr_for_grain(q_col, "day", kind, temporal_kind=t_kind)
|
||
return f"COALESCE(NULLIF(COUNT(DISTINCT {day_expr}), 0), 1)"
|
||
return "1"
|
||
|
||
|
||
def _split_top_level_with_clause(sql: str) -> tuple[str, str]:
|
||
"""若 SQL 以顶层 WITH 子句开头,拆分为 (with_prefix, main_select);否则返回 ('', sql)。
|
||
用于 Oracle 套 ROWNUM 或外层子查询包装时将 WITH 保持在最外层,避免 ORA-32034。
|
||
"""
|
||
stripped = sql.strip()
|
||
if not re.match(r"^WITH\b", stripped, re.IGNORECASE):
|
||
return "", stripped
|
||
n = len(stripped)
|
||
i = 0
|
||
depth = 0
|
||
while i < n:
|
||
ch = stripped[i]
|
||
if ch == "'":
|
||
i += 1
|
||
while i < n:
|
||
if stripped[i] == "'":
|
||
if i + 1 < n and stripped[i + 1] == "'":
|
||
i += 2
|
||
continue
|
||
i += 1
|
||
break
|
||
i += 1
|
||
continue
|
||
if ch in ('"', "`"):
|
||
quote_ch = ch
|
||
i += 1
|
||
while i < n:
|
||
if stripped[i] == quote_ch:
|
||
if i + 1 < n and stripped[i + 1] == quote_ch:
|
||
i += 2
|
||
continue
|
||
i += 1
|
||
break
|
||
i += 1
|
||
continue
|
||
if ch == "(":
|
||
depth += 1
|
||
i += 1
|
||
continue
|
||
if ch == ")":
|
||
if depth > 0:
|
||
depth -= 1
|
||
if depth == 0:
|
||
j = i + 1
|
||
while j < n and stripped[j].isspace():
|
||
j += 1
|
||
if j < n and stripped[j] == ",":
|
||
i = j + 1
|
||
continue
|
||
if re.match(r"^(?:SELECT\b|\(\s*SELECT\b)", stripped[j:], re.IGNORECASE):
|
||
return stripped[: i + 1].rstrip(), stripped[j:].lstrip()
|
||
i += 1
|
||
continue
|
||
i += 1
|
||
return "", stripped
|
||
|
||
|
||
def _apply_row_limit(sql: str, limit: int, kind: str | None, *, force_if_missing: bool = True) -> str:
|
||
clean = sql.strip()
|
||
safe_n = max(1, int(limit))
|
||
k = (kind or "").strip().lower()
|
||
if k == "oracle":
|
||
# Oracle 不支持 LIMIT N(会报 ORA-00933);统一剥离并转换为兼容 Oracle 11g/19c 的顶层 WITH + ROWNUM 包裹
|
||
m_lim = _TRAILING_LIMIT_RE.search(clean)
|
||
if m_lim:
|
||
safe_n = max(1, int(m_lim.group(1)))
|
||
clean = clean[: m_lim.start()].rstrip()
|
||
force_if_missing = True
|
||
if not force_if_missing or _HAS_ORACLE_ROW_LIMIT_RE.search(clean):
|
||
return clean
|
||
with_prefix, main_select = _split_top_level_with_clause(clean)
|
||
wrapped = f"SELECT * FROM ({main_select}) WHERE ROWNUM <= {safe_n}"
|
||
return f"{with_prefix} {wrapped}" if with_prefix else wrapped
|
||
|
||
if not force_if_missing:
|
||
return clean
|
||
if _TRAILING_LIMIT_RE.search(clean) or _HAS_ORACLE_ROW_LIMIT_RE.search(clean):
|
||
return clean
|
||
return f"{clean} LIMIT {safe_n}"
|
||
|
||
|
||
def compile_metric_sql(
|
||
metric: Mapping[str, Any] | SemanticMetric,
|
||
*,
|
||
source: DataSource,
|
||
entities: Sequence[Any] = (),
|
||
date_from: str | None = None,
|
||
date_to: str | None = None,
|
||
group_by_date: bool = False,
|
||
time_grain: str | None = None,
|
||
group_by: Sequence[str] | None = None,
|
||
filters: Mapping[str, Any] | None = None,
|
||
limit: int = 200,
|
||
) -> tuple[str, list[dict[str, Any]]]:
|
||
"""把语义指标卡片(单表表达式或多表参数化 SQL 模板)与动态查询条件编译为单条确定性只读 SELECT 语句。
|
||
|
||
返回 (sql, entity_alignments)。
|
||
"""
|
||
m = (
|
||
{
|
||
"key": metric.key,
|
||
"name": metric.name,
|
||
"calc_mode": getattr(metric, "calc_mode", None) or "expression",
|
||
"table_name": metric.table_name,
|
||
"sql_expression": metric.sql_expression,
|
||
"sql_template": getattr(metric, "sql_template", None) or "",
|
||
"date_column": metric.date_column or "",
|
||
"filter_sql": metric.filter_sql or "",
|
||
"dimensions": list(metric.dimensions or []),
|
||
"default_time_grain": metric.default_time_grain or "day",
|
||
}
|
||
if isinstance(metric, SemanticMetric)
|
||
else dict(metric)
|
||
)
|
||
kind = source.kind
|
||
calc_mode = str(m.get("calc_mode") or ("sql_template" if m.get("sql_template") else "expression")).strip().lower()
|
||
table_name = str(m.get("table_name") or "").strip()
|
||
sql_expr = str(m.get("sql_expression") or "").strip()
|
||
sql_template = str(m.get("sql_template") or "").strip().rstrip(";").strip()
|
||
date_col = str(m.get("date_column") or "").strip()
|
||
filter_sql = str(m.get("filter_sql") or "").strip()
|
||
dims = [d for d in (m.get("dimensions") or []) if isinstance(d, Mapping)]
|
||
|
||
# 构建维度查找索引(支持按 key / label / column / bare_column / entity_key 引用)
|
||
dim_lookup: dict[str, Mapping[str, Any]] = {}
|
||
for d in dims:
|
||
raw_col = str(d.get("column") or "")
|
||
bare_col = raw_col.rsplit(".", 1)[-1]
|
||
for tok in (d.get("key"), d.get("label"), raw_col, bare_col, d.get("entity_key")):
|
||
s = str(tok or "").strip().lower()
|
||
if s and s not in dim_lookup:
|
||
dim_lookup[s] = d
|
||
|
||
# 构建实体索引(key -> CanonicalEntity,以及 col -> CanonicalEntity)
|
||
entity_by_key: dict[str, Any] = {}
|
||
entity_by_col: dict[str, Any] = {}
|
||
for ent in entities or []:
|
||
entity_by_key[str(ent.key).lower()] = ent
|
||
for b in confirmed_bindings(ent):
|
||
if name_key(str(b.get("source_name") or "")) == name_key(source.name):
|
||
b_tbl = name_key(str(b.get("table_name") or "").rsplit(".", 1)[-1])
|
||
c_key = name_key(str(b.get("column_name") or ""))
|
||
if not table_name or b_tbl == name_key(table_name.rsplit(".", 1)[-1]) or calc_mode == "sql_template":
|
||
entity_by_col.setdefault(c_key, ent)
|
||
|
||
dim_selects: list[str] = []
|
||
group_exprs: list[str] = []
|
||
order_parts: list[str] = []
|
||
tpl_order_parts: list[str] = []
|
||
|
||
grain = str(time_grain or m.get("default_time_grain") or "day").strip().lower()
|
||
if grain not in TIME_GRAINS or grain == "none":
|
||
grain = "day"
|
||
|
||
date_temporal_kind = _column_temporal_kind(source, table_name, date_col) if date_col else "text"
|
||
|
||
if group_by_date:
|
||
if not date_col:
|
||
raise MetricInvalid(f"指标「{m.get('key')}」未配置业务时间列(date_column),无法按日期分组", field="group_by_date")
|
||
q_date = _format_col_ref(source, table_name, date_col)
|
||
# 当粒度是 day 且列本身是不含时分秒的纯 DATE 类型(非 Oracle DATE / 非 TIMESTAMP)时直接按原列分组
|
||
if grain == "day" and date_temporal_kind == "date":
|
||
bare_date_lower = date_col.rsplit(".", 1)[-1].lower()
|
||
date_alias = _quote_ident("stat_date" if bare_date_lower != "stat_date" else date_col.rsplit(".", 1)[-1], kind, is_alias=True)
|
||
dim_selects.append(f"{q_date} AS {date_alias}" if bare_date_lower != "stat_date" else q_date)
|
||
group_exprs.append(q_date)
|
||
order_parts.append("1 ASC")
|
||
tpl_order_parts.append(f"{q_date} ASC")
|
||
else:
|
||
d_expr = _date_expr_for_grain(q_date, grain, kind, temporal_kind=date_temporal_kind)
|
||
dim_selects.append(f"{d_expr} AS {_quote_ident('period', kind, is_alias=True)}")
|
||
group_exprs.append(d_expr)
|
||
order_parts.append("1 ASC")
|
||
tpl_order_parts.append(f"{d_expr} ASC")
|
||
|
||
selected_dims: list[Mapping[str, Any]] = []
|
||
for raw_g in group_by or []:
|
||
g_tok = str(raw_g or "").strip().lower()
|
||
if not g_tok:
|
||
continue
|
||
matched_dim = dim_lookup.get(g_tok)
|
||
if matched_dim is None:
|
||
avail = "、".join(f"{d.get('key')}({d.get('label')})" for d in dims) or "无已声明维度"
|
||
raise MetricInvalid(f"指标「{m.get('key')}」不支持按「{raw_g}」分组;可用维度:{avail}", field="group_by")
|
||
if matched_dim not in selected_dims:
|
||
selected_dims.append(matched_dim)
|
||
|
||
for d in selected_dims:
|
||
raw_d_col = str(d["column"])
|
||
q_col = _format_col_ref(source, table_name, raw_d_col)
|
||
d_key = str(d["key"])
|
||
if raw_d_col.lower() == d_key.lower():
|
||
dim_selects.append(q_col)
|
||
else:
|
||
dim_selects.append(f"{q_col} AS {_quote_ident(d_key, kind, is_alias=True)}")
|
||
group_exprs.append(q_col)
|
||
|
||
where_parts: list[str] = []
|
||
if filter_sql:
|
||
where_parts.append(f"({filter_sql})")
|
||
|
||
d_from = _validate_date_bound(date_from or "", field="date_from")
|
||
d_to = _validate_date_bound(date_to or "", field="date_to")
|
||
if (d_from or d_to) and not date_col:
|
||
raise MetricInvalid(f"指标「{m.get('key')}」未配置业务时间列,不支持按日期区间筛选", field="date_from")
|
||
|
||
if d_from:
|
||
q_date = _format_col_ref(source, table_name, date_col)
|
||
where_parts.append(f"{q_date} >= {_format_date_bound_sql(d_from, kind=kind, temporal_kind=date_temporal_kind)}")
|
||
if d_to:
|
||
q_date = _format_col_ref(source, table_name, date_col)
|
||
if _ISO_DATE_RE.match(d_to) and date_temporal_kind in ("datetime", "datetime_text"):
|
||
next_day = (date.fromisoformat(d_to) + timedelta(days=1)).isoformat()
|
||
where_parts.append(f"{q_date} < {_format_date_bound_sql(next_day, kind=kind, temporal_kind=date_temporal_kind)}")
|
||
else:
|
||
where_parts.append(f"{q_date} <= {_format_date_bound_sql(d_to, kind=kind, temporal_kind=date_temporal_kind)}")
|
||
|
||
alignments: list[dict[str, Any]] = []
|
||
for raw_fk, raw_fv in (filters or {}).items():
|
||
if raw_fv is None or raw_fv == "" or raw_fv == []:
|
||
continue
|
||
fk_tok = str(raw_fk or "").strip().lower()
|
||
matched_dim = dim_lookup.get(fk_tok)
|
||
if matched_dim is None:
|
||
avail = "、".join(f"{d.get('key')}({d.get('label')})" for d in dims) or "无已声明过滤维度"
|
||
raise MetricInvalid(f"指标「{m.get('key')}」不支持按「{raw_fk}」过滤;可用维度:{avail}", field="filters")
|
||
col_name = str(matched_dim["column"])
|
||
bare_col_key = name_key(col_name.rsplit(".", 1)[-1])
|
||
ent = (
|
||
entity_by_key.get(str(matched_dim.get("entity_key") or "").lower())
|
||
or entity_by_col.get(name_key(col_name))
|
||
or entity_by_col.get(bare_col_key)
|
||
)
|
||
values_list = list(raw_fv) if isinstance(raw_fv, (list, tuple, set)) else [raw_fv]
|
||
resolved_vals: list[Any] = []
|
||
for v in values_list:
|
||
if ent is not None:
|
||
translated = translate_entity_value(ent, v, to_source=source.name)
|
||
if translated is not None:
|
||
target_val = (
|
||
str(translated.get("target_value") or "").strip()
|
||
if isinstance(translated, Mapping)
|
||
else str(translated).strip()
|
||
)
|
||
if target_val:
|
||
if target_val != str(v):
|
||
alignments.append(
|
||
{
|
||
"dimension": matched_dim["key"],
|
||
"entity_key": ent.key,
|
||
"entity_label": ent.label,
|
||
"input": str(v),
|
||
"input_value": v,
|
||
"translated": [target_val],
|
||
"resolved_code": target_val,
|
||
"canonical_value": translated.get("canonical", "") if isinstance(translated, Mapping) else "",
|
||
"canonical_label": translated.get("label", "") if isinstance(translated, Mapping) else "",
|
||
}
|
||
)
|
||
resolved_vals.append(target_val)
|
||
continue
|
||
resolved_vals.append(v)
|
||
q_col = _format_col_ref(source, table_name, col_name)
|
||
if len(resolved_vals) == 1:
|
||
where_parts.append(f"{q_col} = {_sql_literal(resolved_vals[0])}")
|
||
else:
|
||
in_list = ", ".join(_sql_literal(x) for x in resolved_vals)
|
||
where_parts.append(f"{q_col} IN ({in_list})")
|
||
|
||
safe_limit = max(1, min(int(limit or 200), 1000))
|
||
metric_col_alias = _quote_ident(str(m.get("key") or "metric_value"), kind, is_alias=True)
|
||
desc_clause = _desc_order_clause(kind)
|
||
|
||
if calc_mode == "sql_template" and sql_template:
|
||
slots_used = {tok.group(1).strip().lower() for tok in _SLOT_TOKEN_RE.finditer(sql_template)}
|
||
uses_outer_wrapper = (
|
||
not slots_used
|
||
and bool(sql_expr)
|
||
and not sql_expr.startswith("[多表模板]")
|
||
)
|
||
if uses_outer_wrapper:
|
||
with_prefix, main_select = _split_top_level_with_clause(sql_template)
|
||
select_parts = [*dim_selects, f"({sql_expr}) AS {metric_col_alias}"]
|
||
# 子查询别名不写 AS,兼容 Oracle(Oracle 表或子查询别名写 AS 会报 ORA-00933)
|
||
sql = f"SELECT {', '.join(select_parts)} FROM ({main_select}) metric_src"
|
||
if with_prefix:
|
||
sql = f"{with_prefix} {sql}"
|
||
if where_parts:
|
||
sql += " WHERE " + " AND ".join(where_parts)
|
||
if group_exprs:
|
||
sql += " GROUP BY " + ", ".join(group_exprs)
|
||
if not order_parts:
|
||
order_parts.append(f"{len(select_parts)} {desc_clause}")
|
||
if order_parts:
|
||
sql += " ORDER BY " + ", ".join(order_parts)
|
||
sql = _apply_row_limit(sql, safe_limit, kind, force_if_missing=bool(group_exprs))
|
||
return sql, alignments
|
||
|
||
if group_exprs and not tpl_order_parts:
|
||
tpl_order_parts.append(f"{len(dim_selects) + 1} {desc_clause}")
|
||
|
||
period_days_sql = _compute_period_days_sql(
|
||
source=source,
|
||
table_name=table_name,
|
||
d_from=d_from,
|
||
d_to=d_to,
|
||
group_by_date=group_by_date,
|
||
grain=grain,
|
||
date_col=date_col,
|
||
kind=kind,
|
||
)
|
||
compiled = _SELECT_DIMS_SLOT_RE.sub(
|
||
(f"{', '.join(dim_selects)}, " if dim_selects else ""),
|
||
sql_template,
|
||
)
|
||
compiled = _WHERE_SLOT_RE.sub(
|
||
(f"WHERE {' AND '.join(where_parts)}" if where_parts else ""),
|
||
compiled,
|
||
)
|
||
compiled = _AND_WHERE_SLOT_RE.sub(
|
||
(f"AND ({' AND '.join(where_parts)})" if where_parts else ""),
|
||
compiled,
|
||
)
|
||
compiled = _GROUP_BY_SLOT_RE.sub(
|
||
(f"GROUP BY {', '.join(group_exprs)}" if group_exprs else ""),
|
||
compiled,
|
||
)
|
||
compiled = _ORDER_BY_SLOT_RE.sub(
|
||
(f"ORDER BY {', '.join(tpl_order_parts)}" if tpl_order_parts else ""),
|
||
compiled,
|
||
)
|
||
compiled = _DATE_FROM_SLOT_RE.sub(_sql_literal(d_from) if d_from else "NULL", compiled)
|
||
compiled = _DATE_TO_SLOT_RE.sub(_sql_literal(d_to) if d_to else "NULL", compiled)
|
||
compiled = _PERIOD_DAYS_SLOT_RE.sub(period_days_sql, compiled)
|
||
compiled = _LIMIT_SLOT_RE.sub(str(safe_limit), compiled)
|
||
compiled = re.sub(r"[ \t]+\n", "\n", compiled).strip()
|
||
compiled = _apply_row_limit(
|
||
compiled,
|
||
safe_limit,
|
||
kind,
|
||
force_if_missing=bool(group_exprs and "limit" not in slots_used),
|
||
)
|
||
return compiled, alignments
|
||
|
||
select_parts = [*dim_selects, f"({sql_expr}) AS {metric_col_alias}"]
|
||
sql = f"SELECT {', '.join(select_parts)} FROM {_format_table_ref(source, table_name)}"
|
||
if where_parts:
|
||
sql += " WHERE " + " AND ".join(where_parts)
|
||
if group_exprs:
|
||
sql += " GROUP BY " + ", ".join(group_exprs)
|
||
if not order_parts:
|
||
order_parts.append(f"{len(select_parts)} {desc_clause}")
|
||
if order_parts:
|
||
sql += " ORDER BY " + ", ".join(order_parts)
|
||
sql = _apply_row_limit(sql, safe_limit, kind, force_if_missing=bool(group_exprs))
|
||
return sql, alignments
|
||
|
||
|
||
def _desc_order_clause(kind: str | None) -> str:
|
||
"""Oracle / PostgreSQL 默认 DESC 将 NULL 排在最前(NULLS FIRST),外层套 ROWNUM 或 LIMIT 时会把有效数值行截掉。"""
|
||
k = (kind or "").strip().lower()
|
||
if k in ("oracle", "postgres", "postgresql", "sqlite"):
|
||
return "DESC NULLS LAST"
|
||
return "DESC"
|
||
|
||
|
||
async def probe_latest_metric_date(
|
||
source: DataSource,
|
||
*,
|
||
table_name: str,
|
||
date_column: str,
|
||
filter_sql: str = "",
|
||
) -> str | None:
|
||
"""当某日查询无结果或聚合值为 NULL 时,快速探查底表最新可用业务日期(限时 3s)。"""
|
||
if not date_column or not table_name:
|
||
return None
|
||
bare_date = date_column.rsplit(".", 1)[-1]
|
||
q_date = _format_col_ref(source, table_name, bare_date)
|
||
q_table = _format_table_ref(source, table_name)
|
||
sql = f"SELECT MAX({q_date}) AS latest_date FROM {q_table}"
|
||
if filter_sql and "." not in filter_sql:
|
||
sql += f" WHERE ({filter_sql})"
|
||
try:
|
||
res = await run_query(source, sql, limits=QueryLimits(timeout_seconds=3, max_rows=1))
|
||
if res.rows and res.rows[0] and res.rows[0][0] is not None:
|
||
return str(res.rows[0][0])
|
||
except Exception: # noqa: BLE001
|
||
return None
|
||
return None
|
||
|
||
|
||
def is_empty_metric_result(rows: Sequence[Sequence[Any]], *, has_groups: bool) -> bool:
|
||
if not rows:
|
||
return True
|
||
if not has_groups and len(rows) == 1 and all(cell is None for cell in rows[0]):
|
||
return True
|
||
return False
|
||
|
||
|
||
async def preview_metric(
|
||
session: AsyncSession,
|
||
*,
|
||
metric_id: str | None = None,
|
||
definition: Mapping[str, Any] | None = None,
|
||
date_from: str | None = None,
|
||
date_to: str | None = None,
|
||
group_by_date: bool = False,
|
||
time_grain: str | None = None,
|
||
group_by: Sequence[str] | None = None,
|
||
filters: Mapping[str, Any] | None = None,
|
||
limit: int = 50,
|
||
) -> dict[str, Any]:
|
||
"""在线试算一条已保存或未保存草稿状态的指标定义(搭建者工作台预览用)。"""
|
||
if metric_id:
|
||
row = await get_metric(session, metric_id)
|
||
ds = await _resolve_source(session, row.source_id)
|
||
normalized = metric_to_dict(row, source_name=ds.name)
|
||
elif definition is not None:
|
||
ds, normalized = await _normalize_metric_payload(session, definition, existing=None)
|
||
else:
|
||
raise MetricInvalid("请提供要试算的 metric_id 或 definition")
|
||
|
||
entities = await list_entities(session)
|
||
sql, alignments = compile_metric_sql(
|
||
normalized,
|
||
source=ds,
|
||
entities=entities,
|
||
date_from=date_from,
|
||
date_to=date_to,
|
||
group_by_date=group_by_date,
|
||
time_grain=time_grain,
|
||
group_by=group_by,
|
||
filters=filters,
|
||
limit=limit,
|
||
)
|
||
started = time.perf_counter()
|
||
result = await run_query(ds, sql, limits=QueryLimits(timeout_seconds=15, max_rows=max(1, min(int(limit or 50), 200))))
|
||
elapsed_ms = int((time.perf_counter() - started) * 1000)
|
||
payload = result.to_payload()
|
||
|
||
has_groups = bool(group_by_date or group_by)
|
||
latest_date: str | None = None
|
||
if is_empty_metric_result(result.rows, has_groups=has_groups) and normalized.get("date_column"):
|
||
latest_date = await probe_latest_metric_date(
|
||
ds,
|
||
table_name=str(normalized["table_name"]),
|
||
date_column=str(normalized["date_column"]),
|
||
filter_sql=str(normalized.get("filter_sql") or ""),
|
||
)
|
||
|
||
return {
|
||
"sql": sql,
|
||
"source_name": ds.name,
|
||
"columns": payload["columns"],
|
||
"rows": payload["rows"],
|
||
"row_count": payload["row_count"],
|
||
"truncated": payload["truncated"],
|
||
"elapsed_ms": elapsed_ms,
|
||
"entity_alignments": alignments,
|
||
"latest_available_date": latest_date,
|
||
}
|
||
|
||
|
||
# --------------------------------------------------------------------------
|
||
# 通用关联维表 / 字典配置表发现与低基数分类列实探(零行业/列名硬编码)
|
||
# --------------------------------------------------------------------------
|
||
|
||
_GENERIC_AUDIT_COL_KEYS = frozenset(
|
||
{
|
||
"id",
|
||
"rowid",
|
||
"createdat",
|
||
"updatedat",
|
||
"createtime",
|
||
"updatetime",
|
||
"createdby",
|
||
"updatedby",
|
||
"creator",
|
||
"updater",
|
||
"isdeleted",
|
||
"deleted",
|
||
"delflag",
|
||
"version",
|
||
"remark",
|
||
"remarks",
|
||
"note",
|
||
"notes",
|
||
"memo",
|
||
"description",
|
||
"sort",
|
||
"sortorder",
|
||
"seq",
|
||
}
|
||
)
|
||
|
||
|
||
def _sql_type_family(ctype: str) -> str:
|
||
t = (ctype or "").strip().lower()
|
||
if not t:
|
||
return "other"
|
||
if any(k in t for k in ("date", "time", "timestamp")):
|
||
return "temporal"
|
||
if any(k in t for k in ("char", "text", "string", "clob", "enum", "json")):
|
||
return "string"
|
||
if any(k in t for k in ("float", "double", "real")):
|
||
return "float"
|
||
if any(k in t for k in ("int", "serial", "bit")):
|
||
return "int"
|
||
if any(k in t for k in ("num", "dec", "money")):
|
||
return "int" if re.search(r"\(\s*\d+\s*,\s*0\s*\)", t) else "numeric"
|
||
return "other"
|
||
|
||
|
||
def _is_compact_lookup_table(
|
||
tinfo: Mapping[str, Any] | None,
|
||
*,
|
||
visible_notes: Mapping[str, Any] | None = None,
|
||
masked_set: set[str] | None = None,
|
||
) -> bool:
|
||
"""判断物理表是否为紧凑的维表、码表、枚举字典或元数据配置表(纯结构与目录角色判定)。"""
|
||
if not isinstance(tinfo, Mapping):
|
||
return False
|
||
v_notes = visible_notes or {}
|
||
t_kind = str((v_notes.get("kind") or {}).get("value") or "").strip().lower() if isinstance(v_notes.get("kind"), Mapping) else ""
|
||
if t_kind in ("fact", "log", "snapshot"):
|
||
return False
|
||
masked = masked_set or set()
|
||
unmasked_cols = [
|
||
c
|
||
for c in (tinfo.get("columns") or [])
|
||
if isinstance(c, Mapping) and c.get("name") and name_key(str(c["name"])) not in masked
|
||
]
|
||
if t_kind in ("dimension", "config") and 2 <= len(unmasked_cols) <= 24:
|
||
return True
|
||
if not (2 <= len(unmasked_cols) <= 16):
|
||
return False
|
||
families = [_sql_type_family(str(c.get("type") or "")) for c in unmasked_cols]
|
||
str_cnt = sum(1 for f in families if f == "string")
|
||
num_measure_cnt = sum(1 for f in families if f in ("float", "numeric"))
|
||
temporal_cnt = sum(1 for f in families if f == "temporal")
|
||
return str_cnt >= 2 and str_cnt * 2 >= len(unmasked_cols) and num_measure_cnt <= 3 and temporal_cnt <= 2
|
||
|
||
|
||
def find_companion_lookup_tables(
|
||
tables: Mapping[str, Any],
|
||
selected_tables: Sequence[str],
|
||
*,
|
||
visible_by_table: Mapping[str, Mapping[str, Any]] | None = None,
|
||
rel_graph: Mapping[str, Sequence[Any]] | None = None,
|
||
masked_set: set[str] | None = None,
|
||
) -> list[dict[str, Any]]:
|
||
"""基于关系图(FK / 目录关系)与高区分度共享键(IDF),通用发现候选事实表关联的紧凑维表或字典配置表。"""
|
||
if not isinstance(tables, Mapping) or not tables or not selected_tables:
|
||
return []
|
||
masked = masked_set or set()
|
||
vis_map = visible_by_table or {}
|
||
|
||
col_table_freq: dict[str, int] = {}
|
||
table_cols_by_key: dict[str, dict[str, tuple[str, str]]] = {}
|
||
table_col_counts: dict[str, int] = {}
|
||
for tname, tinfo in tables.items():
|
||
if not isinstance(tinfo, Mapping):
|
||
continue
|
||
raw_cols = [c for c in (tinfo.get("columns") or []) if isinstance(c, Mapping) and c.get("name")]
|
||
table_col_counts[str(tname)] = len(raw_cols)
|
||
ck_map: dict[str, tuple[str, str]] = {}
|
||
for c in raw_cols:
|
||
cname = str(c["name"])
|
||
ck = name_key(cname)
|
||
if not ck or ck in masked or ck in _GENERIC_AUDIT_COL_KEYS:
|
||
continue
|
||
fam = _sql_type_family(str(c.get("type") or ""))
|
||
if fam in ("temporal", "float"):
|
||
continue
|
||
ck_map[ck] = (cname, fam)
|
||
table_cols_by_key[str(tname)] = ck_map
|
||
for ck in ck_map:
|
||
col_table_freq[ck] = col_table_freq.get(ck, 0) + 1
|
||
|
||
max_shared_freq = max(3, int(len(tables) * 0.45))
|
||
lookup_candidates = {
|
||
str(tname)
|
||
for tname, tinfo in tables.items()
|
||
if _is_compact_lookup_table(tinfo, visible_notes=vis_map.get(str(tname)), masked_set=masked)
|
||
}
|
||
if not lookup_candidates:
|
||
return []
|
||
|
||
found_by_name: dict[str, dict[str, Any]] = {}
|
||
for src_tbl in selected_tables:
|
||
if src_tbl not in table_cols_by_key:
|
||
continue
|
||
src_col_cnt = table_col_counts.get(src_tbl, 0)
|
||
src_keys = table_cols_by_key[src_tbl]
|
||
|
||
# 1) 显式关系图(外键、目录确认关系、命名推断关系)连接到的紧凑维表/配置表
|
||
for edge in (rel_graph or {}).get(src_tbl, ()):
|
||
target = str(getattr(edge, "to_table", "") or "")
|
||
if not target or target == src_tbl or target not in lookup_candidates:
|
||
continue
|
||
from_cols = [
|
||
str(c)
|
||
for c in (getattr(edge, "from_columns", None) or ())
|
||
if name_key(str(c)) not in masked
|
||
]
|
||
to_cols = [
|
||
str(c)
|
||
for c in (getattr(edge, "to_columns", None) or ())
|
||
if name_key(str(c)) not in masked
|
||
]
|
||
if not from_cols or not to_cols:
|
||
continue
|
||
entry = found_by_name.get(target)
|
||
t_col_cnt = table_col_counts.get(target, 99)
|
||
score = (0 if getattr(edge, "status", "") in ("verified", "confirmed") else 1, t_col_cnt, target)
|
||
if entry is None or score < entry["_score"]:
|
||
found_by_name[target] = {
|
||
"table_name": target,
|
||
"linked_to": src_tbl,
|
||
"source_columns": from_cols,
|
||
"lookup_columns": to_cols,
|
||
"col_count": t_col_cnt,
|
||
"_score": score,
|
||
}
|
||
|
||
# 2) 无外键视图 / 宽表与紧凑字典表之间的高区分度同名键匹配
|
||
for target in lookup_candidates:
|
||
if target == src_tbl:
|
||
continue
|
||
t_col_cnt = table_col_counts.get(target, 99)
|
||
t_kind = (
|
||
str(((vis_map.get(target) or {}).get("kind") or {}).get("value") or "").strip().lower()
|
||
if isinstance((vis_map.get(target) or {}).get("kind"), Mapping)
|
||
else ""
|
||
)
|
||
if t_col_cnt >= src_col_cnt and t_kind not in ("dimension", "config"):
|
||
continue
|
||
tgt_keys = table_cols_by_key.get(target) or {}
|
||
shared_cks = [
|
||
ck
|
||
for ck in src_keys
|
||
if ck in tgt_keys and col_table_freq.get(ck, 999) <= max_shared_freq
|
||
]
|
||
if not shared_cks:
|
||
continue
|
||
shared_cks.sort(key=lambda ck: col_table_freq.get(ck, 999))
|
||
best_freq = col_table_freq.get(shared_cks[0], 99)
|
||
score = (2, best_freq, t_col_cnt, target)
|
||
entry = found_by_name.get(target)
|
||
if entry is None or score < entry["_score"]:
|
||
found_by_name[target] = {
|
||
"table_name": target,
|
||
"linked_to": src_tbl,
|
||
"source_columns": [src_keys[ck][0] for ck in shared_cks[:2]],
|
||
"lookup_columns": [tgt_keys[ck][0] for ck in shared_cks[:2]],
|
||
"col_count": t_col_cnt,
|
||
"_score": score,
|
||
}
|
||
|
||
results = sorted(found_by_name.values(), key=lambda x: x["_score"])
|
||
for item in results:
|
||
item.pop("_score", None)
|
||
return results
|
||
|
||
|
||
async def probe_schema_lookup_and_discriminators(
|
||
ds: DataSource,
|
||
tables: Mapping[str, Any],
|
||
selected_tables: Sequence[str],
|
||
*,
|
||
visible_by_table: Mapping[str, Mapping[str, Any]] | None = None,
|
||
rel_graph: Mapping[str, Sequence[Any]] | None = None,
|
||
masked_set: set[str] | None = None,
|
||
bound_entity_cols: set[tuple[str, str]] | None = None,
|
||
) -> str:
|
||
"""在提取口径/拆解目录时,通用实探候选表的低基数分类/粒度列取值与紧凑字典配置表内容,杜绝跨类混算与编码盲猜。"""
|
||
if not isinstance(tables, Mapping) or not tables or not selected_tables:
|
||
return ""
|
||
masked = masked_set or set()
|
||
vis_map = visible_by_table or {}
|
||
ent_cols = bound_entity_cols or set()
|
||
|
||
companions = find_companion_lookup_tables(
|
||
tables,
|
||
selected_tables,
|
||
visible_by_table=vis_map,
|
||
rel_graph=rel_graph,
|
||
masked_set=masked,
|
||
)
|
||
|
||
# 收集候选低基数分类/粒度/类型鉴别列:(table_name, col_name, priority)
|
||
disc_candidates: list[tuple[int, str, str]] = []
|
||
seen_disc: set[tuple[str, str]] = set()
|
||
|
||
def _add_disc(prio: int, tbl: str, col_name: str) -> None:
|
||
key = (tbl, name_key(col_name))
|
||
if not col_name or key[1] in masked or key[1] in _GENERIC_AUDIT_COL_KEYS or key in seen_disc:
|
||
return
|
||
seen_disc.add(key)
|
||
disc_candidates.append((prio, tbl, col_name))
|
||
|
||
for comp in companions:
|
||
if comp["col_count"] <= 10:
|
||
for sc in comp["source_columns"]:
|
||
_add_disc(0, comp["linked_to"], sc)
|
||
|
||
for tname in selected_tables:
|
||
tinfo = tables.get(tname)
|
||
if not isinstance(tinfo, Mapping):
|
||
continue
|
||
if _is_compact_lookup_table(tinfo, visible_notes=vis_map.get(tname), masked_set=masked):
|
||
continue
|
||
v_notes = vis_map.get(tname) or {}
|
||
col_notes = v_notes.get("columns") if isinstance(v_notes.get("columns"), Mapping) else {}
|
||
raw_cols = [c for c in (tinfo.get("columns") or []) if isinstance(c, Mapping) and c.get("name")]
|
||
fams = [(str(c["name"]), _sql_type_family(str(c.get("type") or ""))) for c in raw_cols]
|
||
num_cnt = sum(1 for _, f in fams if f in ("float", "numeric", "int"))
|
||
str_cols = [n for n, f in fams if f == "string" and name_key(n) not in masked and name_key(n) not in _GENERIC_AUDIT_COL_KEYS]
|
||
|
||
for cname, _ in fams:
|
||
cn = col_notes.get(cname) if isinstance(col_notes, Mapping) else None
|
||
c_meas = (
|
||
str((cn.get("measure") or {}).get("value") or "").strip().lower()
|
||
if isinstance(cn, Mapping) and isinstance(cn.get("measure"), Mapping)
|
||
else ""
|
||
)
|
||
if c_meas in ("status", "attribute"):
|
||
_add_disc(1, tname, cname)
|
||
|
||
# 对典型异构表(键值窄表 1~2 个数值列,或多指标宽表 >=8 个数值列),抽样未绑定实体的字符分类列验证是否为低基数分区列
|
||
if (1 <= num_cnt <= 2 and len(str_cols) >= 2) or (num_cnt >= 8 and str_cols):
|
||
t_short = name_key(tname.rsplit(".", 1)[-1])
|
||
for sc in str_cols[:4]:
|
||
if (t_short, name_key(sc)) not in ent_cols:
|
||
_add_disc(2, tname, sc)
|
||
|
||
disc_candidates.sort(key=lambda x: x[0])
|
||
|
||
sampled_disc_lines: list[str] = []
|
||
for _, tname, cname in disc_candidates[:5]:
|
||
q_col = _format_col_ref(ds, tname, cname)
|
||
q_tbl = _format_table_ref(ds, tname)
|
||
sql = _apply_row_limit(
|
||
f"SELECT DISTINCT {q_col} FROM {q_tbl} WHERE {q_col} IS NOT NULL ORDER BY 1",
|
||
21,
|
||
ds.kind,
|
||
force_if_missing=True,
|
||
)
|
||
try:
|
||
res = await run_query(ds, sql, limits=QueryLimits(timeout_seconds=3, max_rows=21))
|
||
except Exception: # noqa: BLE001
|
||
continue
|
||
vals = [str(r[0]) for r in (res.rows or []) if r and r[0] is not None and str(r[0]).strip()]
|
||
# 仅保留 1~20 个不同值的真实低基数分类/粒度/状态列;高基数业务主键自动排除
|
||
if 1 <= len(vals) <= 20 and not res.truncated:
|
||
lits = "、".join(_sql_literal(v) for v in vals)
|
||
sampled_disc_lines.append(f"- 表 `{tname}` 低基数分类/粒度列 `{cname}` 实探全部取值(共 {len(vals)} 种):{lits}")
|
||
|
||
lookup_lines: list[str] = []
|
||
char_budget = 3400
|
||
compact_dicts = [c for c in companions if c["col_count"] <= 10]
|
||
for comp in compact_dicts[:3]:
|
||
if char_budget <= 0:
|
||
break
|
||
lt_name = comp["table_name"]
|
||
lt_info = tables.get(lt_name) or {}
|
||
unmasked_cols = [
|
||
str(c["name"])
|
||
for c in (lt_info.get("columns") or [])
|
||
if isinstance(c, Mapping) and c.get("name") and name_key(str(c["name"])) not in masked
|
||
][:10]
|
||
if len(unmasked_cols) < 2:
|
||
continue
|
||
sel_exprs = [_format_col_ref(ds, lt_name, c) for c in unmasked_cols]
|
||
q_lt = _format_table_ref(ds, lt_name)
|
||
sql = _apply_row_limit(
|
||
f"SELECT {', '.join(sel_exprs)} FROM {q_lt} ORDER BY 1, 2",
|
||
160,
|
||
ds.kind,
|
||
force_if_missing=True,
|
||
)
|
||
try:
|
||
lres = await run_query(ds, sql, limits=QueryLimits(timeout_seconds=3, max_rows=160))
|
||
except Exception: # noqa: BLE001
|
||
continue
|
||
if not lres.rows or len(lres.rows) > 150:
|
||
continue
|
||
|
||
join_pair = " = ".join(
|
||
f"{comp['linked_to']}.{sc} ↔ {lt_name}.{lc}"
|
||
for sc, lc in zip(comp["source_columns"], comp["lookup_columns"])
|
||
)
|
||
lookup_lines.append(
|
||
f"- 关联字典/配置维表 `{lt_name}`(关联键:{join_pair};字段:{', '.join(unmasked_cols)}):"
|
||
)
|
||
key_col_name = comp["lookup_columns"][0] if comp["lookup_columns"] else unmasked_cols[0]
|
||
key_idx = next((i for i, c in enumerate(unmasked_cols) if name_key(c) == name_key(key_col_name)), 0)
|
||
|
||
if len(lres.rows) <= 12:
|
||
for r in lres.rows:
|
||
kv = ",".join(
|
||
f"{unmasked_cols[i]}={_sql_literal(r[i])}"
|
||
for i in range(min(len(unmasked_cols), len(r)))
|
||
if r[i] is not None and str(r[i]).strip() != ""
|
||
)
|
||
if kv:
|
||
row_line = f" · {kv}"
|
||
lookup_lines.append(row_line)
|
||
char_budget -= len(row_line)
|
||
else:
|
||
grouped: dict[str, list[str]] = {}
|
||
other_idxs = [i for i in range(len(unmasked_cols)) if i != key_idx]
|
||
for r in lres.rows:
|
||
if key_idx >= len(r) or r[key_idx] is None:
|
||
continue
|
||
k_val = str(r[key_idx]).strip()
|
||
rest_vals = [
|
||
str(r[i]).strip()
|
||
for i in other_idxs
|
||
if i < len(r) and r[i] is not None and str(r[i]).strip() != ""
|
||
]
|
||
if not k_val or not rest_vals:
|
||
continue
|
||
item_s = " / ".join(rest_vals)
|
||
bucket = grouped.setdefault(k_val, [])
|
||
if item_s not in bucket:
|
||
bucket.append(item_s)
|
||
for k_val, items in grouped.items():
|
||
line = f" · {unmasked_cols[key_idx]}={_sql_literal(k_val)}:{';'.join(items[:32])}"
|
||
if len(line) > char_budget:
|
||
line = line[:char_budget] + "…"
|
||
lookup_lines.append(line)
|
||
char_budget -= len(line)
|
||
if char_budget <= 0:
|
||
break
|
||
|
||
if not sampled_disc_lines and not lookup_lines:
|
||
return ""
|
||
|
||
out = [
|
||
"\n【关联字典配置表与低基数分类列实探】",
|
||
"若同一物理表存在多种记录类型、汇总层级或由字典表定义的字段分组,不同分类下度量列含义或粒度可能不同,严禁不加筛选直接全表混算:",
|
||
*sampled_disc_lines,
|
||
*lookup_lines,
|
||
"建模注意:若指标仅适用于特定业务分类、层级或配置组,必须在 `filter_sql`(单表模式)或 `WHERE`(模板模式)中使用主事实表实探出的精确字面值限定过滤条件(注意保留前导零或大小写,切勿臆造码值),并将核心业务层级列放入 `dimensions`。",
|
||
]
|
||
return "\n".join(out)
|
||
|
||
|
||
# --------------------------------------------------------------------------
|
||
# AI 辅助从数据目录与业务文档批量提取指标卡片
|
||
# --------------------------------------------------------------------------
|
||
|
||
SUGGEST_SYSTEM_PROMPT = """你是 AgentLab 的「语义指标建模专家」。请根据给定的数据源物理表结构、数据目录(中文名、粒度、业务日期、有效过滤条件、列角色与符号映射)以及可选的业务指标文档,提取高价值、可直接查询的「独立语义指标卡片(SemanticMetric)」。
|
||
|
||
硬性纪律:
|
||
1. 只输出一个合法 JSON 对象,格式为 {"metrics": [...]},不要输出任何额外解释文字。
|
||
2. 表名(table_name)、时间列(date_column)、维度列(dimensions[].column)及表达式或模板引用的所有物理表名与列名,必须 100% 逐字存在于输入提供的表结构中,严禁臆造不存在的表或列。若文档里某指标注明「暂无数据源」,不要生成该指标。
|
||
3. 标有「[遮罩列]」的敏感列严禁出现在 sql_expression、sql_template、filter_sql 或 dimensions 中。
|
||
4. 支持两种计算模式(calc_mode):
|
||
- 单表模式("calc_mode": "expression",默认优先使用):指标可由单张物理表聚合得到时使用。sql_expression 写单表聚合表达式(如 SUM(total_sys)、ROUND(SUM(qualified_qty) * 100.0 / NULLIF(SUM(total_qty), 0), 2)),严禁写完整 SELECT 或子查询;sql_template 置空串 ""。注意文档提示的存储陷阱(如某比率列已存为百分数 99.65 时,切勿再乘 100 或重复除 100)。
|
||
- 多表 / 参数化模板模式("calc_mode": "sql_template"):当口径依赖跨表 JOIN、WITH CTE 预聚合、兜底系数(如 COALESCE(mj.mjxs, 0.5))或动态统计周期天数(如 TEEP 自然日历秒数 = {{period_days}} * 86400 * COUNT(DISTINCT s.line_code))时使用!
|
||
· sql_template 写单条只读 SELECT 或 WITH ... SELECT 语句,不含分号与 SQL 注释;
|
||
· 仅允许使用以下安全槽位:{{select_dims}}(放在 SELECT 开头用于动态展开分组列)、{{and_where}}(放在 WHERE 1=1 之后承接日期与维度筛选)、{{where}}、{{group_by}}、{{order_by}}、{{period_days}}、{{date_from}}、{{date_to}}、{{limit}};
|
||
· 多表模板下,date_column 与 dimensions[].column 必须带主表别名前缀(如 "s.stat_date"、"s.line_code"),table_name 填主事实表名;若表旁标注了「SQL 全名:<schema.table>」,在 sql_template 的 FROM / JOIN 中务必使用该带 schema 前缀的全名;
|
||
· 严格遵循目标数据源方言语法:例如当方言为 oracle 时,严禁写 LIMIT(行数限制由系统自动套外层 ROWNUM)、严禁写 CAST(... AS TEXT)(转字符用 TO_CHAR、转数值用 TO_NUMBER)、表或子查询别名不要写 AS(写 FROM tbl t),排序统一交由 {{order_by}} 槽位展开(自动带 DESC NULLS LAST)。
|
||
5. 同表异构记录、多粒度与字典驱动表隔离准则:若主事实表混存多种业务类型、汇总层级、科目/指标代码或由关联配置字典表区分字段含义的分组记录,严禁不加过滤直接跨类混算!必须在 filter_sql(单表模式)或模板 WHERE 中使用主表真实存储的精确码值限定目标分类或层级,并将核心下钻维度列完整放入 dimensions。
|
||
6. 若数据目录注明某度量列为「存量(stock,不可跨期直接求和)」,优先使用 AVG(...) 或在 description 中明确标注「存量指标,多日查询请开启按日分组查看趋势」。
|
||
7. 若物理列旁标注了「已绑定标准实体:<entity_key>」,在对应 dimension 的 entity_key 字段填入该实体键。
|
||
8. 避免与「已存在的指标标识」重复;提取文档或目录中最核心的 4~16 个业务指标(单表与多表复杂指标均可覆盖)。
|
||
|
||
单表指标示例:
|
||
{
|
||
"key": "zy_daily_footfall_sys",
|
||
"name": "张园日客流(系统去重)",
|
||
"aliases": ["客流", "总客流"],
|
||
"description": "统计每日系统去重客流人次",
|
||
"unit": "人次",
|
||
"calc_mode": "expression",
|
||
"table_name": "t_zy_daily_stat",
|
||
"sql_expression": "SUM(total_sys)",
|
||
"sql_template": "",
|
||
"date_column": "stat_date",
|
||
"filter_sql": "",
|
||
"dimensions": [{"key": "zone", "label": "区域", "column": "zone_code", "entity_key": ""}]
|
||
}
|
||
|
||
多表 / 参数化 SQL 模板指标示例:
|
||
{
|
||
"key": "oee_multi_table",
|
||
"name": "设备综合效率 OEE(跨表精算)",
|
||
"aliases": ["OEE", "综合效率"],
|
||
"description": "联合班次日报、不合格品记录与模具系数表精算 OEE;含判异红线说明",
|
||
"unit": "%",
|
||
"calc_mode": "sql_template",
|
||
"table_name": "mes_shift_daily",
|
||
"sql_expression": "",
|
||
"sql_template": "WITH mj AS (SELECT prod_code, AVG(mjxs) AS mjxs FROM mes_jzhmjxs GROUP BY prod_code), sc AS (SELECT line_code, stat_date, SUM(scrap_qty) AS bad_qty FROM mes_bhgsj GROUP BY line_code, stat_date) SELECT {{select_dims}} ROUND(SUM(s.good_qty * COALESCE(mj.mjxs, 0.5)) * 100.0 / NULLIF(SUM(s.good_qty + COALESCE(sc.bad_qty, 0)), 0), 2) AS oee_multi_table FROM mes_shift_daily s LEFT JOIN mj ON mj.prod_code = s.prod_code LEFT JOIN sc ON sc.line_code = s.line_code AND sc.stat_date = s.stat_date WHERE 1=1 {{and_where}} {{group_by}} {{order_by}}",
|
||
"date_column": "s.stat_date",
|
||
"filter_sql": "",
|
||
"dimensions": [{"key": "line_code", "label": "产线", "column": "s.line_code", "entity_key": ""}]
|
||
}
|
||
"""
|
||
|
||
|
||
def _parse_suggested_metrics(raw_text: str) -> list[dict[str, Any]]:
|
||
text = (raw_text or "").strip()
|
||
if not text:
|
||
return []
|
||
m = _FENCE_RE.search(text)
|
||
candidate = m.group(1).strip() if m else text
|
||
for snippet in (candidate, text):
|
||
start = snippet.find("{")
|
||
end = snippet.rfind("}")
|
||
if start >= 0 and end > start:
|
||
try:
|
||
obj = json.loads(snippet[start : end + 1])
|
||
if isinstance(obj, Mapping) and isinstance(obj.get("metrics"), list):
|
||
return [x for x in obj["metrics"] if isinstance(x, Mapping)]
|
||
except json.JSONDecodeError:
|
||
continue
|
||
return []
|
||
|
||
|
||
async def suggest_metrics_from_source(
|
||
session: AsyncSession,
|
||
*,
|
||
source_id: str,
|
||
table_names: Sequence[str] | None = None,
|
||
document_ids: Sequence[str] | None = None,
|
||
note: str = "",
|
||
) -> dict[str, Any]:
|
||
"""基于数据源目录、标准业务实体绑定与可选知识库文档,智能生成已通过语法与物理列核验的候选指标卡片。"""
|
||
ds = await _resolve_source(session, source_id)
|
||
cache = ds.schema_cache if isinstance(ds.schema_cache, Mapping) else {}
|
||
tables = cache.get("tables") if isinstance(cache, Mapping) else None
|
||
if not isinstance(tables, Mapping) or not tables:
|
||
raise MetricInvalid(f"数据源「{ds.name}」尚未探查表结构,请先在数据源设置中探查结构", field="source_id")
|
||
|
||
entries = await catalog.read_catalog(session, ds.id)
|
||
masked_set = {name_key(c) for c in masked_columns(ds.options)}
|
||
entities = await list_entities(session)
|
||
existing = await list_metrics(session, source_id=ds.id)
|
||
existing_keys = {m.key for m in existing}
|
||
|
||
# (table_short_key, col_key) -> entity_key
|
||
col_entity: dict[tuple[str, str], str] = {}
|
||
for ent in entities:
|
||
for b in confirmed_bindings(ent):
|
||
if name_key(str(b.get("source_name") or "")) == name_key(ds.name):
|
||
t_short = name_key(str(b.get("table_name") or "").rsplit(".", 1)[-1])
|
||
c_key = name_key(str(b.get("column_name") or ""))
|
||
col_entity[(t_short, c_key)] = ent.key
|
||
|
||
selected_tables: list[str] = []
|
||
if table_names:
|
||
for raw_t in table_names:
|
||
resolved = catalog.resolve_table_name(cache, str(raw_t))
|
||
if resolved and resolved not in selected_tables:
|
||
selected_tables.append(resolved)
|
||
else:
|
||
# 优先排有数据目录说明或名称含 stat/daily/fact/order/summary/detail 的业务表(最多 30 张控制上下文)
|
||
def _table_priority(tname: str) -> tuple[int, str]:
|
||
has_cat = 0 if (tname in entries and catalog.visible_notes(entries[tname].notes)) else 1
|
||
return (has_cat, tname)
|
||
|
||
selected_tables = sorted(tables.keys(), key=_table_priority)[:30]
|
||
|
||
lines: list[str] = [f"数据源:{ds.name}(方言:{ds.kind})"]
|
||
if (ds.kind or "").strip().lower() == "oracle":
|
||
lines.append("方言注意:Oracle 严禁写 LIMIT、严禁写 CAST(x AS TEXT)、表或子查询别名不写 AS;动态周期天数请直接用 {{period_days}} 槽位。")
|
||
if existing_keys:
|
||
lines.append(f"已存在的指标标识(请勿重复):{', '.join(sorted(existing_keys))}")
|
||
lines.append("\n【物理表与数据目录】")
|
||
raw_notes_map = {t: e.notes for t, e in entries.items()}
|
||
visible_by_table = {t: catalog.visible_notes(e.notes) for t, e in entries.items()}
|
||
rel_graph = catalog.relation_graph(raw_notes_map, schema_cache=cache)
|
||
|
||
for tname in selected_tables:
|
||
tinfo = tables.get(tname) or {}
|
||
notes = visible_by_table.get(tname) or {}
|
||
t_label = (notes.get("label") or {}).get("value") if isinstance(notes.get("label"), Mapping) else ""
|
||
t_comment = str(tinfo.get("comment") or "").strip()
|
||
t_desc = (notes.get("description") or {}).get("value") if isinstance(notes.get("description"), Mapping) else ""
|
||
t_grain = (notes.get("grain") or {}).get("value") if isinstance(notes.get("grain"), Mapping) else ""
|
||
t_dedup = (notes.get("dedup") or {}).get("value") if isinstance(notes.get("dedup"), Mapping) else ""
|
||
b_date_raw = (notes.get("business_date") or {}).get("value") if isinstance(notes.get("business_date"), Mapping) else ""
|
||
if isinstance(b_date_raw, Mapping):
|
||
b_col = str(b_date_raw.get("column") or "").strip()
|
||
b_rule = str(b_date_raw.get("rule") or "").strip()
|
||
b_date = f"{b_col}({b_rule})" if (b_col and b_rule) else (b_col or b_rule)
|
||
else:
|
||
b_date = str(b_date_raw or "").strip()
|
||
v_filt = (notes.get("valid_filter") or {}).get("value") if isinstance(notes.get("valid_filter"), Mapping) else ""
|
||
head_bits = [f"表 {tname}"]
|
||
qualified_t = str(tinfo.get("qualified") or "").strip()
|
||
if qualified_t and qualified_t != tname:
|
||
head_bits.append(f"SQL 全名:{qualified_t}")
|
||
if t_label or t_comment:
|
||
head_bits.append(f"中文名:{t_label or t_comment}")
|
||
if t_grain:
|
||
head_bits.append(f"粒度:{t_grain}")
|
||
if b_date:
|
||
head_bits.append(f"业务日期列:{b_date}")
|
||
if v_filt:
|
||
head_bits.append(f"有效过滤:{v_filt}")
|
||
if t_dedup:
|
||
head_bits.append(f"去重约束:{t_dedup}")
|
||
if t_desc:
|
||
head_bits.append(f"说明:{str(t_desc)[:120]}")
|
||
lines.append("- " + " | ".join(head_bits))
|
||
col_notes = notes.get("columns") if isinstance(notes.get("columns"), Mapping) else {}
|
||
t_short = name_key(tname.rsplit(".", 1)[-1])
|
||
col_descs: list[str] = []
|
||
for col in (tinfo.get("columns") or [])[:90]:
|
||
if not isinstance(col, Mapping) or not col.get("name"):
|
||
continue
|
||
cname = str(col["name"])
|
||
ctype = str(col.get("type") or "")
|
||
if name_key(cname) in masked_set:
|
||
col_descs.append(f" · {cname} ({ctype}) [遮罩列]")
|
||
continue
|
||
c_comment = str(col.get("comment") or "").strip()
|
||
cn = col_notes.get(cname) if isinstance(col_notes, Mapping) else None
|
||
c_label = (cn.get("label") or {}).get("value") if isinstance(cn, Mapping) and isinstance(cn.get("label"), Mapping) else ""
|
||
c_unit = (cn.get("unit") or {}).get("value") if isinstance(cn, Mapping) and isinstance(cn.get("unit"), Mapping) else ""
|
||
c_measure = (cn.get("measure") or {}).get("value") if isinstance(cn, Mapping) and isinstance(cn.get("measure"), Mapping) else ""
|
||
c_meaning = (cn.get("meaning") or {}).get("value") if isinstance(cn, Mapping) and isinstance(cn.get("meaning"), Mapping) else ""
|
||
c_codes_raw = (cn.get("codes") or {}).get("value") if isinstance(cn, Mapping) and isinstance(cn.get("codes"), Mapping) else None
|
||
c_codes_str = ""
|
||
if isinstance(c_codes_raw, Mapping) and c_codes_raw:
|
||
code_pairs = [f"{k}={v}" if v else str(k) for k, v in list(c_codes_raw.items())[:16]]
|
||
c_codes_str = f"码值:{'/'.join(code_pairs)}"
|
||
ent_key = col_entity.get((t_short, name_key(cname)), "")
|
||
extra_bits = [b for b in (
|
||
c_label or c_comment,
|
||
f"单位:{c_unit}" if c_unit else "",
|
||
f"度量:{c_measure}" if c_measure else "",
|
||
f"含义:{str(c_meaning)[:100]}" if c_meaning else "",
|
||
c_codes_str,
|
||
f"已绑定标准实体:{ent_key}" if ent_key else "",
|
||
) if b]
|
||
suffix = f" — {','.join(extra_bits)}" if extra_bits else ""
|
||
col_descs.append(f" · {cname} ({ctype}){suffix}")
|
||
lines.extend(col_descs)
|
||
|
||
probe_hints = await probe_schema_lookup_and_discriminators(
|
||
ds,
|
||
tables,
|
||
selected_tables,
|
||
visible_by_table=visible_by_table,
|
||
rel_graph=rel_graph,
|
||
masked_set=masked_set,
|
||
bound_entity_cols=set(col_entity.keys()),
|
||
)
|
||
if probe_hints:
|
||
lines.append(probe_hints)
|
||
|
||
doc_titles: list[str] = []
|
||
if document_ids:
|
||
docs = list(
|
||
(await session.execute(select(Document).where(Document.id.in_([str(d) for d in document_ids])))).scalars().all()
|
||
)
|
||
if docs:
|
||
lines.append("\n【参考业务口径文档】")
|
||
budget = 16_000
|
||
for doc in docs:
|
||
body = str(doc.content or "").strip()
|
||
if not body or budget <= 0:
|
||
continue
|
||
doc_label = str(doc.title or doc.source or doc.id)
|
||
doc_titles.append(doc_label)
|
||
snippet = body[: min(len(body), budget)]
|
||
budget -= len(snippet)
|
||
lines.append(f"--- 文档《{doc_label}》 ---\n{snippet}")
|
||
|
||
if note and note.strip():
|
||
lines.append(f"\n【用户补充要求】\n{note.strip()[:1000]}")
|
||
|
||
model, model_id = await catalog_assistant.resolve_model(session)
|
||
msgs = [
|
||
SystemMessage(content=SUGGEST_SYSTEM_PROMPT),
|
||
HumanMessage(content="\n".join(lines)),
|
||
]
|
||
reply = await model.ainvoke(msgs)
|
||
raw_candidates = _parse_suggested_metrics(message_text(reply))
|
||
|
||
accepted: list[dict[str, Any]] = []
|
||
skipped: list[dict[str, str]] = []
|
||
seen_batch_keys: set[str] = set(existing_keys)
|
||
for raw in raw_candidates:
|
||
candidate = {**raw, "source_id": ds.id, "source": "ai", "status": "enabled"}
|
||
raw_k = str(candidate.get("key") or "").strip().lower()
|
||
if raw_k in seen_batch_keys:
|
||
skipped.append({"key": raw_k, "reason": "与已有指标或同批候选标识重复"})
|
||
continue
|
||
try:
|
||
_, norm = await _normalize_metric_payload(session, candidate, existing=None)
|
||
seen_batch_keys.add(norm["key"])
|
||
accepted.append({**norm, "source_name": ds.name})
|
||
except Exception as exc: # noqa: BLE001
|
||
skipped.append({"key": raw_k or str(candidate.get("name") or "?"), "reason": str(exc)})
|
||
|
||
return {
|
||
"source_id": ds.id,
|
||
"source_name": ds.name,
|
||
"model_id": model_id,
|
||
"documents_used": doc_titles,
|
||
"candidates": accepted,
|
||
"skipped": skipped,
|
||
}
|