viw_kg_edge_ref.sql
3.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- ============================================================
-- 视图：viw_kg_edge_ref —— ERP 知识图谱·实体引用边 (ER/FK graph, L3, NL2SQL 接地)
-- 一条边 = 源表.外键字段 --引用--> 目标表.主键 (显示 显示字段)
-- 例：salsalesordermaster.sCustomerId --> elecustomer.sId (显示 sCustomerName)
--
-- 边的来源：字段控件上的 gdsconfigformslave.sRelation
-- 格式 = 逗号分隔的多跳查找，每跳 `字段&关联表&主键&显示字段`
-- 单跳：sDepartId&sisdepart&sId&sName
-- 多跳(链式带出)：sEmployeeId&eleemployee&sId&sDepartId,sDepartId&sisdepart&sId&sName
-- —— 本视图取【第一跳】作为主外键边（源 = 该表单自身的数据表）。
--
-- 源表 = 字段所属主表单的 sTbName (gdsconfigformslave.sParentId → gdsconfigformmaster.sTbName)。
-- 目标表多为主数据/字典表：ele*(员工/产品/客户/物料 等)、sis*(各类分类字典)。
-- bTgtMaster=1 标记「引用到主数据/字典」，是 NL2SQL join 的关键信号。
-- iFormCount = 有多少张表单声明了这条外键 —— 边权重/显著度，可用于排序召回。
--
-- 边粒度 = 一条外键 (源表.字段→目标表.主键)。同一外键在不同控件可带出多个显示字段，
-- 聚合进 sDisplayFields(逗号去重列表)，不拆成多条边。
-- 表名统一小写作稳定节点id（MySQL 表名大小写不敏感；与 sDataSource 对齐）。
-- 校验（本地库 xlyweberp_saas）：1536 条去重外键边 / 368 源表 / 109 目标表。
-- ============================================================
CREATE OR REPLACE VIEW viw_kg_edge_ref AS
SELECT
LOWER(h.src_table) AS sSrcTable, -- 源表(小写节点id)
h.src_field AS sSrcField, -- 外键字段
LOWER(h.tgt_table) AS sTgtTable, -- 目标表(小写节点id)
h.tgt_key AS sTgtKey, -- 目标主键
GROUP_CONCAT(DISTINCT NULLIF(h.display_field,'') ORDER BY h.display_field SEPARATOR ',') AS sDisplayFields, -- 可带出的显示字段集
COUNT(DISTINCT h.sParentId) AS iFormCount, -- 声明此外键的表单数(边权重)
CAST(LOWER(LEFT(h.tgt_table,3)) IN ('ele','sis') AS UNSIGNED) AS bTgtMaster -- 1=引用主数据/字典表
FROM (
SELECT
m.sTbName AS src_table,
SUBSTRING_INDEX(hop1, '&', 1) AS src_field,
SUBSTRING_INDEX(SUBSTRING_INDEX(hop1, '&', 2), '&', -1) AS tgt_table,
SUBSTRING_INDEX(SUBSTRING_INDEX(hop1, '&', 3), '&', -1) AS tgt_key,
CASE WHEN (LENGTH(hop1) - LENGTH(REPLACE(hop1, '&', ''))) >= 3
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(hop1, '&', 4), '&', -1)
ELSE '' END AS display_field,
s.sParentId
FROM (
SELECT s0.sParentId, s0.sRelation,
SUBSTRING_INDEX(s0.sRelation, ',', 1) AS hop1 -- 只取第一跳
FROM gdsconfigformslave s0
WHERE s0.sRelation LIKE '%&%&%' -- 至少 字段&表&主键
) s
JOIN gdsconfigformmaster m ON s.sParentId = m.sId
WHERE IFNULL(m.sTbName, '') <> ''
AND (LENGTH(hop1) - LENGTH(REPLACE(hop1, '&', ''))) >= 2 -- 第一跳须 >=3 段
AND SUBSTRING_INDEX(hop1, '&', 1) <> ''
AND SUBSTRING_INDEX(SUBSTRING_INDEX(hop1, '&', 2), '&', -1) <> ''
) h
GROUP BY LOWER(h.src_table), h.src_field, LOWER(h.tgt_table), h.tgt_key;