mysql8 hive oracle 窗口函数，分摊金额

注：技术交流可以加我VX：k-loop，昵称：默读者。

需求：

一个订单2000块钱对应4个子订单，这笔订单优惠了200块钱，然后将200块钱需要根据子订单的金额/订单2000块钱，进行分摊。但是最后一笔，需要用200-之前3比分摊出去的金额，这样4笔子订单加一块才是200元。

-- 创建订单表
create table `order`(
id int, -- 订单id
price DOUBLE -- 订单金额
);
-- 插入订单数据
insert into `order` values(1,2000);
insert into `order` values(2,3000);

-- 创建子订单表
create table order_sub(
id int, -- 子订单id
order_id int, -- 订单id
price DOUBLE -- 子订单金额
);

-- 插入子订单数据
insert into order_sub values(1,1,100);
insert into order_sub values(2,1,700);
insert into order_sub values(3,1,900);
insert into order_sub values(4,1,300);

insert into order_sub values(5,2,1400);
insert into order_sub values(6,2,500);
insert into order_sub values(7,2,1100);

-- 创建优惠券表
create table awords(
id int, -- 优惠券id
order_id int, -- 订单id
price DOUBLE -- 优惠券金额
);

-- 插入优惠券数据
insert into awords values(1,1,200);
insert into awords values(2,2,250);

数据准备妥当，下面开始代码编写

​
SELECT
she.id
,she.`订单ID`
,she.`子订单金额`
,she.`订单优惠金额`
,CASE WHEN she.MM=1 THEN she.`订单优惠金额`-she.`分摊优惠券金额累加`+she.`分摊优惠券金额` ELSE she.`分摊优惠券金额` END `分摊优惠券金额`
FROM (
SELECT
hh.*
,ROW_NUMBER() OVER(PARTITION BY hh.`订单ID` ORDER BY hh.`子订单金额`) MM
FROM (
SELECT
s.id
,s.order_id `订单ID`
,s.price `子订单金额`
,o.price `订单总金额`
,aw.price `订单优惠金额`
,ROUND(s.price/o.price,2) AS  `子订单金额占比`
,aw.price*ROUND(s.price/o.price,2) AS `分摊优惠券金额`
,SUM(aw.price*ROUND(s.price/o.price,2)) OVER (PARTITION BY s.order_id ORDER BY s.price DESC) AS `分摊优惠券金额累加` 
FROM order_sub s -- 子订单表
LEFT JOIN `order` o -- 订单表
ON s.order_id = o.id
LEFT JOIN awords aw -- 优惠券表
ON s.order_id=aw.order_id
) hh
) she

​

运行结果如下：

订单ID为1的，优惠券金额200元。订单金额是2000元，下图没有列出这一列。共4个子订单，根据子订单金额进行优惠券的分摊

叮当ID为2的，优惠券金额250元。订单金额是3000元，下图没有列出这一列。共3个子订单，根据子订单金额进行优惠券的分摊