我有一个或多或少看起来像这样的表:
+--------+---------------------+--------+------+
| CustId | TransDate | Amount | Coef |
+--------+---------------------+--------+------+
| 1 | 2020-09-04 11:02:00 | 400 | 0.1 |
| 1 | 2020-09-04 12:05:00 | 500 | 0.2 |
| 1 | 2020-09-04 13:02:00 | 400 | 0.1 |
| 1 | 2020-09-04 13:11:00 | 600 | 0.4 |
| 2 | 2020-09-04 10:01:00 | 300 | 0.3 |
| 2 | 2020-09-04 11:02:00 | 700 | 0.2 |
+--------+---------------------+--------+------+
我试图实现的是给每个客户一个";奖金";按Amount*Coef计算,但交易总额上限为1500。";奖金";对于SUM为<1500(按交易顺序(。
例如,客户";1〃;前3笔交易将获得全额奖金,第4笔交易的差额将获得1500英镑的奖金。基本上,最终结果应该是这样的:
+--------+---------------------+--------+------+---------------------------------------------------+
| CustId | TransDate | Amount | Coef | Bonus |
+--------+---------------------+--------+------+---------------------------------------------------+
| 1 | 2020-09-04 11:02:00 | 400 | 0.1 | 40 |
| 1 | 2020-09-04 12:05:00 | 500 | 0.2 | 100 |
| 1 | 2020-09-04 13:02:00 | 400 | 0.1 | 40 |
| 1 | 2020-09-04 13:11:00 | 600 | 0.4 | 80 /*(this is given only for the remaining 200)*/ |
| 2 | 2020-09-04 10:01:00 | 300 | 0.3 | 90 |
| 2 | 2020-09-04 11:02:00 | 700 | 0.2 | 140 |
+--------+---------------------+--------+------+---------------------------------------------------+
提前感谢!
干杯。
如果我理解正确,你需要一个累积和和一些逻辑:
select t.*,
(case when sum(amount) over (partition by custid order by transdate) <= 1500
then coef * amount
when sum(amount) over (partition by custid order by transdate) - amount < 1500
then (1500 - sum(amount) over (partition by custid order by transdate)) as coef
else 0
end) as bonus
from t
[EDIT]:这现在使用MAX函数来限制奖金金额。
数据
drop table if exists #tTEST;
go
select * INTO #tTEST from (values
(1, '2020-09-04 11:02:00', 400, 0.1),
(1, '2020-09-04 12:05:00', 500, 0.2),
(1, '2020-09-04 13:02:00', 400, 0.1),
(1, '2020-09-04 13:11:00', 600, 0.4),
(1, '2020-09-05 13:11:00', 600, 0.4),
(2, '2020-09-04 10:01:00', 300, 0.3),
(3, '2020-09-04 11:02:00', 700, 0.2)) V(CustId, TransDate, Amount, Coef);
查询
declare @bonus_limit int=1500;
with data_cte as (
select *, sum(amount) over (partition by custid order by transdate) cum_amount,
@bonus_limit-sum(amount) over (partition by custid order by transdate) cum_amount_diff
from #tTEST t)
select *,
(SELECT Max(v) FROM (VALUES (0), (case when cum_amount_diff<0 then (Amount+cum_amount_diff)*Coef
else Amount*Coef end)) AS value(v)) Bonus
from data_cte;
结果
+--------+---------------------+--------+------+------------+-----------------+-------+
| CustId | TransDate | Amount | Coef | cum_amount | cum_amount_diff | Bonus |
+--------+---------------------+--------+------+------------+-----------------+-------+
| 1 | 2020-09-04 11:02:00 | 400 | 0.1 | 400 | 1100 | 40.0 |
| 1 | 2020-09-04 12:05:00 | 500 | 0.2 | 900 | 600 | 100.0 |
| 1 | 2020-09-04 13:02:00 | 400 | 0.1 | 1300 | 200 | 40.0 |
| 1 | 2020-09-04 13:11:00 | 600 | 0.4 | 1900 | -400 | 80.0 |
| 1 | 2020-09-05 13:11:00 | 600 | 0.4 | 2500 | -1000 | 0.0 |
| 2 | 2020-09-04 10:01:00 | 300 | 0.3 | 300 | 1200 | 90.0 |
| 3 | 2020-09-04 11:02:00 | 700 | 0.2 | 700 | 800 | 140.0 |
+--------+---------------------+--------+------+------------+-----------------+-------+