用两份合同,拆开业务事件、汇总粒度与金额归属
作者:壹典 ID ONE 团队
CRM显示1800元,ERP显示1300元,财务显示1250元。把这三个数接到同一个问数入口,业务人员仍会追问:销售额到底是多少?要回答这句话,先要确认三套系统分别记录了什么。签约、发货和收入确认可以同时正确,也可以因为关联错误而同时失真。跨系统问数的验收,应当能分清这两种情况,并说明每一笔差额落在哪里。
以下以两份合同为例。合同A为1000元,合同B为800元;A分两次发货,分别为600元和400元,B已发货300元。财务提供四条已经确认的金额记录:R1属于A,为500元;R2属于A,为400元;R3属于B,为200元;R4为150元,已知属于这两份合同的范围,但尚未拆清各自金额。这里的收入确认结果由财务提供,分析系统只处理归属与核对。
这组输入统一使用人民币元,金额均不含税,不涉及汇率与合同变更。三套快照的截止点都设为11月15日18时,比较的是同一组合同截至该时点的累计金额。它不是拿当月新签合同、当月全部发货和当月全部收入直接相减。实际项目若需要月度流量,应另行定义各类事件的期间及合同范围,不能沿用这张累计表的差额解释。
合同表每份合同一行,发货表每张发货记录一行,财务表每条确认记录一行。三者都有金额列,却没有相同的行粒度。Microsoft的星型模型说明将事实表用于记录事件,并要求事实数据保持一致粒度;这里据此把三种事件分开保存,再约定共同的比较层级。1
读者可以先把上述输入录成三张小表,并为每条记录保留唯一编号。实施验收时,还应附上抽取批次、数据范围和完成状态。只有三份快照都完整,才进入金额比较;源系统未导出成功、当前账号无权读取和确实没有业务发生,必须分别处理。
-- SQLite:同一范围、同一截止点的完整快照;金额单位为元
PRAGMA foreign_keys = ON;
CREATE TABLE contracts (
contract_id TEXT PRIMARY KEY NOT NULL, amount INTEGER NOT NULL
);
CREATE TABLE shipments (
shipment_id TEXT PRIMARY KEY NOT NULL,
contract_id TEXT NOT NULL REFERENCES contracts, amount INTEGER NOT NULL
);
CREATE TABLE recognitions (
recognition_id TEXT PRIMARY KEY NOT NULL,
contract_id TEXT REFERENCES contracts, amount INTEGER NOT NULL
);
INSERT INTO contracts VALUES ('A',1000),('B',800);
INSERT INTO shipments VALUES ('S1','A',600),('S2','A',400),('S3','B',300);
INSERT INTO recognitions VALUES
('R1','A',500),('R2','A',400),('R3','B',200),('R4',NULL,150);一个常见写法是按合同编号把合同、发货和财务明细直接连接,再分别求和。对A而言,两条发货会与两条已归属的财务记录两两配对,生成四行;B生成一行。于是合同合计变成4800元,发货合计变成2300元,财务合计变成2000元。原来的三本账都被改变,未分配合同的R4还在内连接中消失了。
这不是三套系统对销售额的定义有分歧,而是计算路径引入了重复。SQLite的SELECT文档说明,连接按行组合并应用连接条件;本例的倍增正是这一语义的直接结果。2 排查时应同时看关联前后记录数、事件编号出现次数和金额,而不只是检查最后一个合计。
把SUM改成SUM(DISTINCT amount)也不可靠。DISTINCT针对金额值去重,并不识别业务事件。3 如果B后来又有一张独立的300元发货单,正确累计为600元,按金额去重却只剩300元。修正必须回到记录标识和汇总层级;相同金额不等于重复业务。
如果每条记录已经有明确合同归属,最直接的方法是先按合同分别汇总发货和财务金额,再与唯一的合同清单连接。这样每个汇总结果都保证一份合同一行,不会因另一张表记录多而重复累计。对输入完整、归属稳定的固定报表,这种方案容易复核,维护对象也较少。
本例得到:A的合同、发货、已归属财务金额依次为1000、1000、900元;B依次为800、300、200元。合计为1800、1300、1100元。但1100元只是已落实到合同的部分,完整财务金额仍为1250元。R4的150元必须保留在待分配明细中,与主表一起交付,不能因为不能连接就丢弃。
此时1300减1100得到200元,只能称为发货金额与已归属财务金额的暂时差额。R4尚未完成归属,不能把这200元全部命名为未确认收入,更不能据此评价某个合同的履约或财务处理。问数答案应同时说明已归属1100元、待分配150元,并允许查看R4。
在BI模型中,也可以让共同的合同维度分别连接各事件表,再独立计算度量。Microsoft对多事实表关系的建议强调使用共同维度,避免直接建立缺少约束的事实表多对多关系。4 这与本例SQL预汇总的实现不同,但都要求明确过滤路径和粒度。不能把SQL连接的倍增现象直接当作所有BI模型的运行方式。
WITH s AS (
SELECT contract_id, SUM(amount) AS amount
FROM shipments GROUP BY contract_id
), r AS (
SELECT contract_id, SUM(amount) AS amount
FROM recognitions
WHERE contract_id IS NOT NULL GROUP BY contract_id
)
SELECT c.contract_id, c.amount AS contract_amount,
COALESCE(s.amount, 0) AS shipment_amount,
COALESCE(r.amount, 0) AS assigned_finance_amount
FROM contracts c
LEFT JOIN s USING (contract_id)
LEFT JOIN r USING (contract_id);
-- 同时交付待分配明细
SELECT recognition_id, amount
FROM recognitions WHERE contract_id IS NULL;当一条财务记录对应多份合同,只存一个合同编号就不够了。可以增加金额分配表,每行记录财务记录编号、合同编号和分配金额。这张表表达已经确认的业务归属,不让算法为了对平总数自行选择比例。它应有负责确认的人、依据及生效版本;归属没有确定时,继续保留待分配状态。
沿用这组输入,再假设财务确认R4中的50元属于A、100元属于B。把R1、R2、R3各自的完整金额也写入分配表,形成五行:R1/A/500、R2/A/400、R3/B/200、R4/A/50、R4/B/100。此后按合同汇总时,应统一读取这张分配表,不能同时把原财务表的已归属金额再加一次。
按此方法,A的财务金额为950元,B为300元,合计仍为1250元;与发货1300元的算术差额为50元。合同1800元与发货1300元之间仍相差500元。三组数没有被强制改成相同,它们保留各自业务意义。剩余差额是否属于正常时间差,还需要对应事件与财务依据,金额算对并不等于原因已经查明。
分配表比预汇总多出维护责任:新增记录要确认归属,变更需要版本控制,无法分配的记录要有人处理。其价值是保留跨合同明细的解释路径。若业务要求到发货单或合同明细行追踪,本例的合同层分配仍不够,必须取得更细的归属信息后重新验收,不能从合同级合计倒推每一行。
CREATE TABLE allocations (
recognition_id TEXT NOT NULL REFERENCES recognitions,
contract_id TEXT NOT NULL REFERENCES contracts,
amount INTEGER NOT NULL,
PRIMARY KEY (recognition_id, contract_id)
);
-- 本例进一步设定的批准分配,不根据差额自动生成
INSERT INTO allocations VALUES
('R1','A',500),('R2','A',400),('R3','B',200),
('R4','A',50),('R4','B',100);
-- 正常情况下返回零行;有返回时先处理异常
SELECT r.recognition_id, r.amount AS source_amount,
COALESCE(SUM(a.amount),0) AS allocated_amount,
r.amount-COALESCE(SUM(a.amount),0) AS unallocated_amount
FROM recognitions r LEFT JOIN allocations a USING (recognition_id)
GROUP BY r.recognition_id, r.amount
HAVING r.amount != COALESCE(SUM(a.amount),0);
-- 仅在分配和完整性检查通过后生成正式结果
WITH s AS (
SELECT contract_id, SUM(amount) AS amount
FROM shipments GROUP BY contract_id
), a AS (
SELECT contract_id, SUM(amount) AS amount
FROM allocations GROUP BY contract_id
)
SELECT c.contract_id, c.amount AS contract_amount,
COALESCE(s.amount,0) AS shipment_amount,
COALESCE(a.amount,0) AS allocated_finance_amount
FROM contracts c LEFT JOIN s USING (contract_id)
LEFT JOIN a USING (contract_id);分配至少要通过两道独立检查。第一道按每条财务记录核对源金额与分配合计。R4若只分给A50元,会留下100元未分配;若给A50元、给B150元,合计200元,比源金额多50元。前者不能标记为已完成,后者应拒绝进入正式结果。补回或改正分配后,重新核对R4,再核对合同层汇总。
第二道检查目标是否存在、是否属于批准的业务范围。即使把R4的150元全部分给不存在的合同Z,源金额仍然守恒;只看总额就发现不了错误。应检查目标合同有效性和归属依据。本例使用外键拒绝不存在的合同,金额守恒则由单独的汇总检查完成,二者不能互相替代。
同一财务记录与合同组合重复写入,也会抬高结果。对于本例的一份当前分配快照,可以用这两个编号作为联合主键阻止重复。若系统需要保留多个版本,应增加版本维度,并规定查询只能使用同一批批准的版本;不能为了保存历史而取消唯一性,却让报表把所有版本一起求和。
前面的SQL使用COALESCE补零,前提是源快照完整,且该合同确实没有匹配的业务记录。若财务抽取失败,所有合同都可能查不到记录;这时批量补零会制造一份看似完整的错误报表。应先核对批次完成状态、记录数及已知控制金额,数据未就绪时明确返回不可比较,而不是进入业务差额分析。
即使两次运行使用相同截止时间,也可能因为迟到记录或修订而得到不同答案。因此验收包要固定数据快照和分配版本。若后续补录导致R4调整,应保存旧结果,单独生成新版本并说明变化来自哪条记录。复验先重放旧版本,确认旧结果可再现,再检查新版本的变化;不能把历史结果悄悄替换成最新数字。
退款、退货和冲销也不能只改其中一列来追求对平。应由各自事件规则决定何时、以什么符号进入本账,并保留原记录与更正关系。采购验收可以要求实施方演示一条更正如何影响三张表,但不应预设三张表在同一天、同一金额发生变化。
这组小数据可以作为实施前的共同验收样本。业务人员负责确认三种事件含义及比较范围;财务负责确认收入输入和分配依据;数据与实施团队负责快照、关联和检查结果。交付不能只放一张最终截图,应包含源记录、执行口径、异常明细及结果版本,便于换一位验收人仍能得到同一结论。
检查表至少记录下面六项,每项都填写实际结果与责任人。前三项先证明原始金额没有丢失或倍增,后三项加入边界记录或主动制造错误,检查系统能否保留有效业务、拒绝或标出异常。若只在正常输入下得到正确答案,还不能说明跨系统链路已经具备可用的错误处理。
本例的SQL使用整数金额,能够避开小数表示差异;实际金额有分时,可以使用整数分或数据库的合适精确数值类型,并统一舍入位置。验收表应写明单位与精度,不能把元与分混在一起,再通过调整容差使结果通过。样本通过后,还要用获授权的真实数据范围和实际权限复验。
检查项 | 操作与预期 | 验收责任 |
|---|---|---|
源金额 | 合同1800、发货1300、财务1250;R4的150保留 | 业务、财务 |
预汇总 | A为1000/1000/900;B为800/300/200;另列待分配150 | 数据、实施 |
批准分配 | A财务950、B财务300;合计1250;算术差额50 | 财务、实施 |
漏分与超分 | R4 只分 50 元时列出未分配 100 元;分配 200 元时列出超分 50 元,暂停出正式表 | 财务、实施 |
错误对象与重复 | 合同Z被拒绝;重复R4/A被拒绝;原有效结果不变 | 数据、实施 |
同金额不同事件 | B增加独立300元发货后,发货合计为600,不能去重成300 | 业务、实施 |
选择哪种方法,取决于业务归属是否已经明确。单一归属且只需合同层比较,可以从分别汇总开始;一笔记录横跨多份合同,且需要说明每笔金额去向,就增加经过批准的分配表。两种方法都要保留异常,区别在于所需数据和持续维护的工作量,不能仅按演示界面的简洁程度决定。
这些工作也应写进实施范围:由谁提供合同映射、由谁批准分配、谁处理待分配记录、范围变更后谁负责回归检查。费用比较时据此判断报价包含了哪些交付,而不是默认接通三个接口就完成全部口径工作。同一张表能放下三套金额,不代表三套金额已经可以互相替代。
当用户问销售额时,系统应先明确要看签约、发货还是财务确认,必要时展示三者并列及未解决事项。能够下钻到记录、说清比较前提、保留尚不能解释的差额,才让跨系统问数具有可复核的依据。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。