sql - 如何将 X% 的行更新为 A、Y% 的行更新为 B、Z% 的行更新为 C

Question

我有一个这样的表:

Products
(
   ID int not null primary key,
   Type int not null,
   Route varchar(20) null
)

我在客户端上有一个以下格式的列表:

Type=1, Percent=0.4, Route=A
Type=1, Percent=0.4, Route=B
Type=1, Percent=0.2, Route=C
Type=2, Percent=0.5, Route=A
Type=2, Percent=0.5, Route=B
Type=3, Percent=1.0, Route=C
...etc

完成后，我想将 40% 的 1 类产品分配给路线 A，将 40% 分配给路线 B，将 20% 分配给路线 C。然后将 50% 的 2 类产品分配给路线 A，将 50% 的 2 类产品分配给路线 A。产品到路线B等

有什么方法可以在单个更新语句中执行此操作吗？

如果不是在一个巨大的语句中，是否可以在每种类型的一个语句或每个路由的一个语句中完成？目前我们正在为每种类型+路线做一个，以上任何一项都将是一种改进。

Answer 1

这是我在您发布您正在使用 SQL-Server 之前准备的 Oracle 语句，但它可能会给您一些想法，尽管您必须使用 CTE 和自联接来推出您自己的ratio_to_report 分析函数。我们计算产品和客户路由表中每种类型的累积比例，并对匹配的比例带进行非等值连接。我使用的示例数据有一些舍入，但对于较大的数据集，这些舍入会减少。

设置如下:

create table products (id int not null primary key, "type" int not null, route varchar (20) null);
create table clienttable ( "type" int not null, percent number (10, 2) not null, route varchar (20) not null);
insert into clienttable ("type", percent, route) values (1, 0.4, 'A');
insert into clienttable ("type", percent, route) values (1, 0.4, 'B');
insert into clienttable ("type", percent, route) values (1, 0.2, 'C');
insert into clienttable ("type", percent, route) values (2, 0.5, 'A');
insert into clienttable ("type", percent, route) values (2, 0.5, 'B');
insert into clienttable ("type", percent, route) values (3, 1.0, 'C');

insert into products (id, "type", route) values (1, 1, null);
insert into products (id, "type", route) values (2, 1, null);
insert into products (id, "type", route) values (3, 1, null);
insert into products (id, "type", route) values (4, 1, null);
insert into products (id, "type", route) values (5, 1, null);
insert into products (id, "type", route) values (6, 1, null);
insert into products (id, "type", route) values (7, 1, null);
-- 7 rows for product type 1 so we will expect 3 of route A, 3 of route B, 1 of route C (rounded)

insert into products (id, "type", route) values (8, 2, null);
insert into products (id, "type", route) values (9, 2, null);
insert into products (id, "type", route) values (10, 2, null);
insert into products (id, "type", route) values (11, 2, null);
insert into products (id, "type", route) values (12, 2, null);
-- 5 rows for product type 2 so we will expect 3 of route A and 2 of route B (rounded)

insert into products (id, "type", route) values (13, 3, null);
insert into products (id, "type", route) values (14, 3, null);
-- 2 rows for product type 3 so we will expect 2 of route C

这是声明

select prods.id, prods."type", client.route cr from
(
select
p.id, 
p."type", 
row_number () over (partition by p."type" order by p.id) / count (*) over (partition by p."type") cum_ratio
from
products p
) prods
inner join 
(
select "type", route, nvl (lag (cum_ratio, 1) over (partition by "type" order by route), 0) ratio_start, cum_ratio ratio_end from 
(select "type", route, sum (rr) over (partition by "type" order by route) cum_ratio
from (select c."type", c.route, ratio_to_report (c.percent) over (partition by "type") rr from clienttable c))) client 
on prods."type" = client."type" 
and prods.cum_ratio >= client.ratio_start and prods.cum_ratio < client.ratio_end

这给出了以下结果:-

+----+------+----+
| ID | type | CR |
+----+------+----+
|  1 |    1 | A  |
|  2 |    1 | A  |
|  3 |    1 | B  |
|  4 |    1 | B  |
|  5 |    1 | B  |
|  6 |    1 | C  |
|  8 |    2 | A  |
|  9 |    2 | A  |
| 10 |    2 | B  |
| 11 |    2 | B  |
| 13 |    3 | C  |
+----+------+----+