"SQL Server 数据库有的发票表里每个项目会有一笔金额，项目表里每个项目有多个账户，两者以 ProjectID 关联。 # Invoices InvoiceID Amount Proje .."

jinxing
乾学院 53 号会员
1 回帖 • 51 浏览 • 1 周前

从 SQL 到 SPL：补齐某个平均值保证总和不变

应用计算

SQL Server 数据库有的发票表里每个项目会有一笔金额，项目表里每个项目有多个账户，两者以 ProjectID 关联。

# Invoices

InvoiceID	Amount	ProjectID
1	100.0000	1
2	100.0000	2
3	100.0000	3
4	100.0000	4

#Projects

ProjectID	AccountCode
1	12345
2	12345
2	7890
3	800
3	234
3	987
4	800
4	234
4	987
4	2579

现在要将两表关联起来，新增分账字段，按项目里的账户数大致平均分钱，比如 100 分成 3 份，其中 N-1 个账户的金额要按 1/N 保留 2 位小数，即 33.33，第 N 个账户要补齐平均值，保证总额不变，即 100-33.33*2=33.34。

InvoiceID	Amount	ProjectID	AccountCode	SplitAmount
1	100.0	1	12345	100.0
2	100.0	2	12345	50.0
2	100.0	2	7890	50.0
3	100.0	3	800	33.34
3	100.0	3	234	33.33
3	100.0	3	987	33.33
4	100.0	4	800	25.0
4	100.0	4	234	25.0
4	100.0	4	987	25.0
4	100.0	4	2579	25.0

SQL：

select *,
       SplitAmount 
       + case when rn = 1 
              then i.Amount - sum  (i.SplitAmount) 
                              over (partition by i.ProjectID)
              else 0
              end  as AdjustedSplitAmount
from(
  select 
      I.*, P.AccountCode,
      round(I.Amount / count(I.InvoiceID) over (partition by P.ProjectID), 2) as SplitAmount,
      row_number() over (partition by P.ProjectID order by p.AccountCode) as rn
  from 
      #Invoices I Inner Join #Projects P on I.ProjectID = P.ProjectID
) i

SQL 分组后必须立刻汇总，不能保持分组子集，在子集上直接按规则新增分账字段，要用嵌套子查询 + 窗口函数间接实现，序号也用窗口函数额外生成，整体代码繁琐。

有分组子集时，SPL 代码可以更自然：

	A
1	=mssql.query("select I.*,P.AccountCode, from #Invoices I,#Projects P where I.ProjectID = P.ProjectID")
2	=A1.group(ProjectID)
3	=A2.(cnt=~.count(),avg=round(Amount/cnt,2), ~. derive(avg+if(#==1, Amount-avg*cnt): SplitAmount))
4	=A3.conj()

A1：简单关联，加载数据。

A2：分组，但不汇总。

A3：处理每组数据，按规则直接新增分账字段。# 是天然序号，不必额外计算。

A4：合并各组。

问题来源：https://stackoverflow.com/questions/78235564/split-invoice-total-into-multiple-rows-but-split-always-equals-total