Sql 将一名成员金额分配给群组内的其他成员

发布于07月19日

我有每年的销售渠道成本表(T):ch=渠道(bo=后台).

YYYY . CH   COST
=====+====+======
2022 . c1 .  20
2022 . c2 .  30
2022 . bo .  50
2023 . c1 .  25
2023 . c2 .  40
2023 . bo .  80

后台成本(ch=bo)应根据分配表(D)中的系数分配给其他销售渠道:

CH . BO_PERC
===+========
c1 .   0.2    <- 20% from BO cost should be added to c1 channel cost (per year)
c2 .   0.4    <- 40% from BO cost should be added to c2 channel cost (per year)

生成的表格应该如下所示:

YYYY . CH . COST
=====+====+=====
2022 . c1 .  30     <- 20 + 50*0.2
2022 . c2 .  50     <- 30 + 50*0.4
2023 . c1 .  41     <- 25 + 80*0.2
2023 . c2 .  72     <- 40 + 80*0.4

如何得到将年度BO成本分配到其他渠道的结果表？

推荐答案

您需要处理您的表名和列名.除了它们太短和非描述性之外，BO_PERC实际上是错误的:它没有以百分比的形式存储.

以下查询输出预期结果:

create table channel_costs(
  year smallint not null check(year between 1900 and 2200),
  channel varchar(2) not null,
  cost money not null check (cost > cast(0 as money))
);

insert into channel_costs(year, channel, cost) values
(2022, 'c1',  20),
(2022, 'c2',  30),
(2022, 'bo',  50),
(2023, 'c1',  25),
(2023, 'c2',  40),
(2023, 'bo',  80);

create table distribution(
  channel varchar(2) not null,
  backoffice_fraction real not null check(backoffice_fraction between 0 and 1)
);

insert into distribution(channel, backoffice_fraction) values
('c1', 0.2),
('c2', 0.4);

select front_costs.year,
       front_costs.channel,
       front_costs.cost + distribution.backoffice_fraction * back_costs.cost as cost
from channel_costs as front_costs
join channel_costs as back_costs on back_costs.year = front_costs.year
                                and back_costs.channel = 'bo'
                                and front_costs.channel <> 'bo'
join distribution on distribution.channel = front_costs.channel
order by front_costs.year, front_costs.channel;